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 the_table  (id int, date timestamp, data jsonb);

CREATE TABLE


insert into the_table
VALUES
  (1, '2021-12-22 16:14:03', '{"x": 1, "y": 2}'),
  (2, '2021-12-22 16:14:13', '{"x": 5, "y": 4}'),
  (3, '2021-12-22 16:14:23', '{"x": 6, "y": 5}'),
  (4, '2021-12-22 16:15:33', '{"x": 7, "y": 6}'),
  (5, '2021-12-22 16:15:48', '{"x": 8, "y": 10}');

INSERT 0 5


select id, to_char("date", 'yyyy-mm-dd hh24:mi'), data
from (
   select id, "date", data, 
          dense_rank() over (partition by date_trunc('minute', "date") order by date desc) as rnk
   from the_table
) t
where rnk = 1
order by id;


id
to_char
data


3
2021-12-22 16:14
{"x": 6, "y": 5}

5
2021-12-22 16:15
{"x": 8, "y": 10}



SELECT 2

id	to_char	data
3	2021-12-22 16:14	{"x": 6, "y": 5}
5	2021-12-22 16:15	{"x": 8, "y": 10}