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 test (id int, value1 int, value2 int)
insert into test values
(1,11,21),
(2,12,22),
(3,13,23),
(4,14,24),
(5,12,24),
(6,16,26),
(7,17,24),
(8,18,28)
8 rows affected

select t1.id id, 
       t2.id dup_id,
       case when t1.value1 = t2.value1 then 'value 1'
            when t1.value2 = t2.value2 then 'value 2'
            else 'some error'
            end dup_field,
       case when t1.value1 = t2.value1 then t1.value1 :: text
            when t1.value2 = t2.value2 then t1.value2 :: text
            else 'some error'
            end dup_value
from test t1, test t2
where t1.id < t2.id
and ( t1.value1 = t2.value1
      or
      t1.value2 = t2.value2 )


id
dup_id
dup_field
dup_value


2
5
value 1
12

4
5
value 2
24

4
7
value 2
24

5
7
value 2
24