Postgres 15

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 (id serial, block_number int, value int, name varchar);

CREATE TABLE


insert into test (block_number, value, name) values (47, 100, 'one'), (52, 200, 'one'), (58, 120, 'one'), (60, 180, 'one');

INSERT 0 4


SELECT  block_number, first_value(value) over (partition by grp) 
FROM (
 SELECT 
  sum(CASE WHEN value IS NOT NULL THEN 1 END) OVER (ORDER BY block_number) AS grp
  , value
  , block_number
 FROM   (
   SELECT x.block_number, t.value
   FROM (
    SELECT generate_series(min(block_number), max(block_number)) AS block_number
    FROM   test
   ) x
   LEFT   JOIN test t USING (block_number)
 ) y
) z


block_number
first_value


47
100

48
100

49
100

50
100

51
100

52
200

53
200

54
200

55
200

56
200

57
200

58
120

59
120

60
180



SELECT 14

block_number	first_value
47	100
48	100
49	100
50	100
51	100
52	200
53	200
54	200
55	200
56	200
57	200
58	120
59	120
60	180

block_number	first_value
47	100
48	100
49	100
50	100
51	100
52	200
53	200
54	200
55	200
56	200
57	200
58	120
59	120
60	180

block_number	first_value
47	100
48	100
49	100
50	100
51	100
52	200
53	200
54	200
55	200
56	200
57	200
58	120
59	120
60	180