Postgres 16

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 table1(col1, col2, col3, col4) as values
 (1,	'a',	'x1',	'asdc')
,(2,	'b',	'x2',	'czxa')
,(3,	'c',	'x3',	'xfsdaa');

create table table2(col2,col3) as values
 ('l',	'x56')
,('q',	'x99')
,('c',	'x3');

select col1, col2, col3, col4 from table1
union all
select null,col2,col3,null from table2;

SELECT 3



SELECT 3




col1
col2
col3
col4


1
a
x1
asdc

2
b
x2
czxa

3
c
x3
xfsdaa

null
l
x56
null

null
q
x99
null

null
c
x3
null



SELECT 6


select * from table1 natural full outer join table2;


col2
col3
col1
col4


a
x1
1
asdc

b
x2
2
czxa

c
x3
3
xfsdaa

l
x56
null
null

q
x99
null
null



SELECT 5


select * 
from (select tableoid::regclass,null as force_mismatch,* from table1) a
natural full outer join
     (select tableoid::regclass,null as force_mismatch,* from table2) b;


tableoid
force_mismatch
col2
col3
col1
col4


table1
null
a
x1
1
asdc

table1
null
b
x2
2
czxa

table1
null
c
x3
3
xfsdaa

table2
null
c
x3
null
null

table2
null
l
x56
null
null

table2
null
q
x99
null
null



SELECT 6

col1	col2	col3	col4
1	a	x1	asdc
2	b	x2	czxa
3	c	x3	xfsdaa
null	l	x56	null
null	q	x99	null
null	c	x3	null

tableoid	force_mismatch	col2	col3	col1	col4
table1	null	a	x1	1	asdc
table1	null	b	x2	2	czxa
table1	null	c	x3	3	xfsdaa
table2	null	c	x3	null	null
table2	null	l	x56	null	null
table2	null	q	x99	null	null