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 t2 (a INT, b INT, c INT);
INSERT INTO t1 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9),             (13,14,NULL);
INSERT INTO t2 VALUES            (4, 5, 6), (7, 8, 9), (10, 11, 12),(13,14,NULL);
4 rows affected

4 rows affected

(TABLE t1 EXCEPT TABLE t2) 
UNION
(TABLE t2 EXCEPT TABLE t1)
ORDER BY a, b, c


a
b
c


1
2
3

10
11
12


SELECT *
FROM (
  SELECT 't1' AS t1, t1.* FROM t1
) t1 NATURAL FULL JOIN (
  SELECT 't2' AS t2, t2.* FROM t2
) t2 
WHERE NOT (t1, t2) IS NOT NULL;


a
b
c
t1
t2


1
2
3
t1
null

13
14
null
t1
null

10
11
12
null
t2

13
14
null
null
t2

a	b	c	t1	t2
1	2	3	t1	null
13	14	null	t1	null
10	11	12	null	t2
13	14	null	null	t2