Postgres 13

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 t1(a INT, b INT, c INT);

CREATE TABLE


INSERT INTO t1(a,b,c) VALUES (1,1,1),(2,3,4)

INSERT 0 2


CREATE TABLE t2(a INT, b INT, c INT);

CREATE TABLE


INSERT INTO t2(a,b,c) VALUES (1,1,1),(2,3,5),(3,3,3)

INSERT 0 3


SELECT t1.*, t2.*, CASE WHEN t1 IS NULL OR t2 IS NULL THEN 'Not equal' ELSE 'Equal' END
FROM t1
NATURAL FULL JOIN t2


a
b
c
a
b
c
case


1
1
1
1
1
1
Equal

2
3
4
null
null
null
Not equal

null
null
null
2
3
5
Not equal

null
null
null
3
3
3
Not equal



SELECT 4


SELECT *
FROM (SELECT 't1' AS t1, t1.* FROM t1) t1 
NATURAL FULL JOIN (SELECT 't2' AS t2, t2.* FROM t2) t2 
WHERE t1 IS NULL OR t2 IS NULL -- show differences


a
b
c
t1
t2


2
3
4
t1
null

2
3
5
null
t2

3
3
3
null
t2



SELECT 3


SELECT *
FROM (SELECT 't1' AS t1, t1.* FROM t1) t1 
NATURAL FULL JOIN (SELECT 't2' AS t2, t2.* FROM t2) t2 
WHERE  t1 IS NOT NULL AND t2 IS NOT NULL    -- show the same


a
b
c
t1
t2


1
1
1
t1
t2



SELECT 1

a	b	c	a	b	c	case
1	1	1	1	1	1	Equal
2	3	4	null	null	null	Not equal
null	null	null	2	3	5	Not equal
null	null	null	3	3	3	Not equal