Postgres 14

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