Postgres 10

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 "test_over"
(
  "uid" int,
  "sum" int,
  "date" date
);

CREATE TABLE


insert into "test_over" ("uid","sum","date") values 
(1, 50,'2018-01-01'),
(1, 30,'2018-01-05'),
(1, 60,'2018-01-20'),
(2,  0,'2018-01-05'),
(2, 30,'2018-01-03'),
(3, 80,'2018-01-15'),
(8,100,'2018-01-22'),
(8, 50,'2018-01-23');

INSERT 0 8


select distinct
  "uid",
  first_value ("sum") over (partition by "uid" order by "date" asc),
  first_value ("date") over (partition by "uid" order by "date" asc)
from "test_over"
order by
  "uid"
;


uid
first_value
first_value


1
50
2018-01-01

2
30
2018-01-03

3
80
2018-01-15

8
100
2018-01-22



SELECT 4