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 table_name(id INT, a_c_id VARCHAR(50), a_c_dttm VARCHAR(50), a_c_code VARCHAR(50));

Insert Into table_name Values(5,'a','a','a'),
(9,'a','a','a'),
(8,'b','b','b'),
(12,'b','b','b'),
(13,'b','b','b'),
(6,'c','c','c'),
(10,'c','c','c'),
(7,'d','d','d'),
(11,'d','d','d');

CREATE TABLE



INSERT 0 9


select array_agg(id) as ids
from 
(
  select *,
    max(id) over (partition by a_c_id, a_c_dttm, a_c_code) maxid
  from table_name
) t
where id <> maxid
group by a_c_id, a_c_dttm, a_c_code



ids


{5}

{8,12}

{6}

{7}



SELECT 4


select array_agg(id) as ids
from 
(
  select *,
    max(id) over (partition by a_c_id, a_c_dttm, a_c_code) maxid
  from table_name
) t
where id <> maxid
--group by a_c_id, a_c_dttm, a_c_code


ids


{5,8,12,6,7}



SELECT 1