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 contact (first_name text, last_name text , email text, work_email text, another_email text);
insert into contact
values
('John', 'Smith', 'jh@jh.com', 'test@test.com', 'yougettheIdea.com'),
('John', 'Smith', 'test@test.com', null, null),
('John', 'Smith', 'imAdifferent.jh.com', null, null);
3 rows affected

select *
from contact c1
where exists (select * 
              from contact c2
              where (c1.first_name, c1.last_name) = (c2.first_name, c2.last_name)
                and (    
                    array_remove(array[c1.email, c1.work_email, c1.another_email],null) @> array_remove(array[c2.email, c2.work_email, c2.another_email], null)
                 or array_remove(array[c1.email, c1.work_email, c1.another_email],null) <@ array_remove(array[c2.email, c2.work_email, c2.another_email], null) 
                )
                and c1.ctid <> c2.ctid
              );


first_name
last_name
email
work_email
another_email


John
Smith
jh@jh.com
test@test.com
yougettheIdea.com

John
Smith
test@test.com
null
null

first_name	last_name	email	work_email	another_email
John	Smith	jh@jh.com	test@test.com	yougettheIdea.com
John	Smith	test@test.com	null	null