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 a (id serial, column1 int, column2 int);
create table b ( column1 int, column2 int);
create table c (column1 int, column2 int);

CREATE TABLE

CREATE TABLE

CREATE TABLE

insert into a values(default, 1,1)

INSERT 0 1

insert into b values(1,1),(1,5),(2,4),(2,3);
insert into c values(1,3),(1,1),(2,4),(2,5);

INSERT 0 4

INSERT 0 4

with cte as (
SELECT 'b' "tab",row_number() over() "seq", column1, column2 FROM b
UNION all
SELECT 'c',row_number() over(),column1, column2 FROM c

),
cte1 as (
select distinct on (column1,column2) * from cte 
where not exists (select 1 from a where column1=cte.column1 and column2=cte.column2)
order by 3,4
)

insert into a(column1,column2)
select column1,column2 from cte1 order by tab,seq

INSERT 0 5

select * from a

id
column1
column2

1
1
1

2
1
5

3
2
4

4
2
3

5
1
3

6
2
5

SELECT 6