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 table1(id int);

CREATE TABLE

CREATE TABLE table2(id int);

CREATE TABLE

INSERT INTO table1(id) VALUES (1), (3);
INSERT INTO table2(id) VALUES (3), (1);

WITH 
  query1 AS (SELECT id from table1),
  query2 AS (SELECT id from table2)
SELECT 0 id
WHERE (NOT EXISTS (SELECT 1 FROM query1)) OR (NOT EXISTS (SELECT 1 FROM query2))
UNION ALL
SELECT COALESCE(q1.id, q2.id) id
FROM query1 q1 FULL JOIN query2 q2
ON q2.id = q1.id
WHERE (q1.id IS NULL OR q2.id IS NULL)
  AND (EXISTS (SELECT 1 FROM query1)) AND (EXISTS (SELECT 1 FROM query2))

INSERT 0 2

INSERT 0 2

id

SELECT 0

DELETE FROM table1;
DELETE FROM table2;

INSERT INTO table1(id) VALUES (1), (3);
INSERT INTO table2(id) VALUES (4), (1);

WITH 
  query1 AS (SELECT id from table1),
  query2 AS (SELECT id from table2)
SELECT 0 id
WHERE (NOT EXISTS (SELECT 1 FROM query1)) OR (NOT EXISTS (SELECT 1 FROM query2))
UNION ALL
SELECT COALESCE(q1.id, q2.id) id
FROM query1 q1 FULL JOIN query2 q2
ON q2.id = q1.id
WHERE (q1.id IS NULL OR q2.id IS NULL)
  AND (EXISTS (SELECT 1 FROM query1)) AND (EXISTS (SELECT 1 FROM query2))

DELETE 2

DELETE 2

INSERT 0 2

INSERT 0 2

id

3

4

SELECT 2

DELETE FROM table1;
DELETE FROM table2;

INSERT INTO table1(id) VALUES (1), (3);

WITH 
  query1 AS (SELECT id from table1),
  query2 AS (SELECT id from table2)
SELECT 0 id
WHERE (NOT EXISTS (SELECT 1 FROM query1)) OR (NOT EXISTS (SELECT 1 FROM query2))
UNION ALL
SELECT COALESCE(q1.id, q2.id) id
FROM query1 q1 FULL JOIN query2 q2
ON q2.id = q1.id
WHERE (q1.id IS NULL OR q2.id IS NULL)
  AND (EXISTS (SELECT 1 FROM query1)) AND (EXISTS (SELECT 1 FROM query2))

DELETE 2

DELETE 2

INSERT 0 2

id

0

SELECT 1