Postgres 16

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 t(the_user varchar, the_timestamp timestamp, sometimes_null_column varchar);

CREATE TABLE


insert into t values
('a', current_timestamp, 'random_text'),
('a', current_timestamp + interval '1 seconds', 'some_text'),
('a', current_timestamp + interval '2 seconds', null),
('a', current_timestamp + interval '3 seconds', null),
('b', current_timestamp + interval '4 seconds', 'more_text'),
('c', current_timestamp + interval '4 seconds', 'diff_text'),
('c', current_timestamp + interval '5 seconds', null)
;

INSERT 0 7


select
  the_user,
  the_timestamp,
  max(sometimes_null_column)
    over (
      partition by the_user
      order by the_timestamp
      rows between unbounded preceding and current row
  ) as sometimes_null_column
from t
order by the_user, the_timestamp;


the_user
the_timestamp
sometimes_null_column


a
2024-04-22 20:52:39.824516
random_text

a
2024-04-22 20:52:40.824516
some_text

a
2024-04-22 20:52:41.824516
some_text

a
2024-04-22 20:52:42.824516
some_text

b
2024-04-22 20:52:43.824516
more_text

c
2024-04-22 20:52:43.824516
diff_text

c
2024-04-22 20:52:44.824516
diff_text



SELECT 7

the_user	the_timestamp	sometimes_null_column
a	2024-04-22 20:52:39.824516	random_text
a	2024-04-22 20:52:40.824516	some_text
a	2024-04-22 20:52:41.824516	some_text
a	2024-04-22 20:52:42.824516	some_text
b	2024-04-22 20:52:43.824516	more_text
c	2024-04-22 20:52:43.824516	diff_text
c	2024-04-22 20:52:44.824516	diff_text