Postgres 14

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 parent (col1 int);
CREATE TABLE child (col2 int) INHERITS(parent);

INSERT INTO parent(col1) VALUES
  (1)
, (1)           -- duplicate
, (NULL)        -- special case with NULL
;

INSERT INTO child(col1, col2) VALUES
  (2, 2)
, (2, 2)        -- duplicate
, (null, null)  -- special case with NULL
, (null, 7)
;
3 rows affected

4 rows affected

SELECT * FROM ONLY parent NATURAL FULL JOIN child;


col1
col2


2
2

2
2

null
null

null
7

null
null

1
null

1
null


SELECT  COALESCE(p.tableoid, c.tableoid)::regclass AS tbl, *
FROM    ONLY parent p
NATURAL FULL JOIN child c;


tbl
col1
col2


child
2
2

child
2
2

child
null
null

child
null
7

parent
null
null

parent
1
null

parent
1
null


-- cba's query
SELECT p.col1, c.col2 FROM parent p LEFT OUTER JOIN child c ON c.col1 = p.col1;


col1
col2


1
null

1
null

2
2

2
2

2
2

2
2

null
null

null
null

null
null

col1	col2
2	2
2	2
null	null
null	7
null	null
1	null
1	null

col1	col2
1	null
1	null
2	2
2	2
2	2
2	2
null	null
null	null
null	null

tbl	col1	col2
child	2	2
child	2	2
child	null	null
child	null	7
parent	null	null
parent	1	null
parent	1	null