Postgres 11

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 tbl (id int, a int, b int, c int);

CREATE TABLE


insert into tbl values
(1, 12, 4, 3),
(2, 10, 10, 5),
(3, 6, 12, 23),
(4, 6, null, 10),
(5, 7, null, 4),
(6, 1, 8, 10),
(7, 4, null, 3);

INSERT 0 7


select id, a, first_value(b) over (partition by grp) as b, c
from (
      select id, a, b, c, 
             sum(case when b is not null then 1 end) over (order by id) as grp
      from   tbl
     ) t


id
a
b
c


1
12
4
3

2
10
10
5

3
6
12
23

4
6
12
10

5
7
12
4

6
1
8
10

7
4
8
3



SELECT 7