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);

CREATE TABLE



CREATE TABLE



INSERT 0 4



INSERT 0 4


(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 2


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



SELECT 4


SELECT
  coalesce(t1.a, t2.a) AS a,
  coalesce(t1.b, t2.b) AS b,
  coalesce(t1.c, t2.c) AS c,
  t1.t1,
  t2.t2
FROM (
  SELECT 't1' AS t1, t1.* FROM t1
) t1 FULL JOIN (
  SELECT 't2' AS t2, t2.* FROM t2
) t2 ON (t1.a, t1.b, t1.c) IS NOT DISTINCT FROM (t2.a, t2.b, t2.c) 
WHERE NOT (t1, t2) IS NOT NULL;

ERROR:  FULL JOIN is only supported with merge-joinable or hash-joinable join conditions

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