Postgres 12

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 people (uniquekey int, passport char(20), id char(20))

CREATE TABLE


insert into people values
(1, 'P12345', 'ID1'),
(2, 'PPKL', 'ID2'),
(3, 'P54321','ID3'),
(4, 'PPKL', 'ID4'),
(5, 'PPKL', 'ID1'),
(6, 'P11111', 'ID6')

INSERT 0 6


select t1.uniquekey,
       t1.passport ppt, 
       t2.passport dup_ppt
from people t1, people t2
where t1.passport = t2.passport AND t1.uniquekey <> t2.uniquekey


uniquekey
ppt
dup_ppt


2
PPKL
PPKL

2
PPKL
PPKL

4
PPKL
PPKL

4
PPKL
PPKL

5
PPKL
PPKL

5
PPKL
PPKL



SELECT 6