By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table points (
id serial primary key,
"date" date not null,
point integer not null
);
CREATE TABLE
insert into points ("date", point) values
('2022/01/07', 99),
('2022/01/06', 9),
('2022/01/05', 7),
('2022/01/04', 1),
('2022/01/03', 4),
('2022/01/02', 6),
('2022/01/01', 1);
INSERT 0 7
select sum(point)
from points
where date between (
select "date"
from points
where point = 1 and date <= '2022/01/06'
order by "date" desc
limit 1
) and '2022/01/06';
sum |
---|
17 |
SELECT 1