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 mytable (
  id int,
  time	timestamp,
  firstname	varchar(20),
  lastname	varchar(20),
  salary varchar(20),
  location	varchar(20),
  country varchar(20)
);

		

CREATE TABLE


insert into mytable values
(1,	'2023-03-08 07:47:58', 'John','',10000,'',''),		
(1,	'2023-03-08 07:50:58','','Lenny','','Phoenix','USA'),
(1,	'2023-03-08 07:55:58','','',5000,'','');

INSERT 0 3


select array_agg(firstname order by time) filter (where firstname <> '' ) AS firstname,
  array_agg(lastname order by time) filter (where lastname <> '' ) AS firstname,
  array_agg(salary order by time) filter (where salary <> '' ) AS salary,
  array_agg(location order by time) filter (where location <> '' ) AS location,
  array_agg(country order by time) filter (where country <> '' ) AS country
from mytable
group by id



firstname
firstname
salary
location
country


{John}
{Lenny}
{10000,5000}
{Phoenix}
{USA}



SELECT 1


select id, max(time),
  to_json(array_agg(firstname order by time) filter (where firstname <> '' ))->-1 AS firstname,
  to_json(array_agg(lastname order by time) filter (where lastname <> '' ))->-1 AS firstname,
  to_json(array_agg(salary order by time) filter (where salary <> '' ))->-1 AS salary,
  to_json(array_agg(location order by time) filter (where location <> '' ))->-1 AS location,
  to_json(array_agg(country order by time) filter (where country <> '' ))->-1 AS country
from mytable
group by id


id
max
firstname
firstname
salary
location
country


1
2023-03-08 07:55:58
"John"
"Lenny"
"5000"
"Phoenix"
"USA"



SELECT 1