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 document (
  id	varchar(20),
  version_name varchar(10),
  parnet_id1 int,
  parent_id2 int,
  timestamp timestamp
);

insert into document values
('UUI1',	'A',	1,	100,	'2022-01-01'),
('UUI2',	'B',	1,	100,	'2022-01-02'),
('UUI3',	'C',	2,	100,	'2022-01-03'),
('UUI4',	'D',	2,	100,	'2022-01-04'),
('UUI5',	'E',	2,	100,	'2022-01-05');


CREATE TABLE history  (
  document_id	varchar(20),
  status varchar(10),
  timestamp timestamp
);

insert into history values
('UUI1',	'Active',	'2022-01-01'),
('UUI1',	'Inactive',	'2022-01-02'),
('UUI2',	'InActive',	'2022-01-03'),
('UUI2',	'Active',	'2022-01-04'),
('UUI3',	'InActive',	'2022-01-03'),
('UUI3',	'Active',	'2022-01-04'),
('UUI4',	'InActive',	'2022-01-03'),
('UUI4',	'Active',	'2022-01-04'),
('UUI5',	'Active',	'2022-01-03'),
('UUI5',	'Inactive',	'2022-01-04');

CREATE TABLE



INSERT 0 5



CREATE TABLE



INSERT 0 10



select h3.*, h2.status
from history h2
inner join (
  select t.id, t.version_name, t.parnet_id1, t.parent_id2, max(h.timestamp) as timestamp
  from history h
  inner join (
    select d.id, d.version_name, d.parnet_id1, d.parent_id2
    from document d
    inner join (
      select parnet_id1, parent_id2, max(timestamp) as timestamp
      from document
      group by parnet_id1, parent_id2
    ) as s on s.parnet_id1 = d.parnet_id1 and s.parent_id2 = d.parent_id2 and s.timestamp = d.timestamp
  ) t on t.id = h.document_id
  group by t.id, t.version_name, t.parnet_id1, t.parent_id2
) as h3 on h2.document_id = h3.id and h3.timestamp = h2.timestamp


id
version_name
parnet_id1
parent_id2
timestamp
status


UUI2
B
1
100
2022-01-04 00:00:00
Active

UUI5
E
2
100
2022-01-04 00:00:00
Inactive



SELECT 2

id	version_name	parnet_id1	parent_id2	timestamp	status
UUI2	B	1	100	2022-01-04 00:00:00	Active
UUI5	E	2	100	2022-01-04 00:00:00	Inactive