Possible planner deficiency? - Mailing list pgsql-hackers

I have a situation where i need to select a couple of rows from an
inherited table
collection.  The statement I'm using is:

SELECT * FROM parent NATURAL JOIN interesting

where interesting is a 1 column temporary table with the primary key's
of the rows
I'm interested in.

All the child tables use the same primary key (it's actually unique across the
whole inheritance tree but I'm not enforcing that)  The plan that
would make sense
to me is:
Nested Loop  ->  Seq Scan on interesting  ->  Append        ->  Seq Scan on parent        ->  Index Scan using
child1_pkeyon child1 parent              Index Cond: (child1.pk = "outer".pk)        ->  Index Scan using child2_pkey
onchild2 parent              Index Cond: (child2.pk = "outer".pk) 

or something bloody close to that,  but it seems that append inside a
nested loop
just doesn't want to happen.  Is this a deficiency in the planner?

With Setup:

CREATE TABLE parent (pk INTEGER PRIMARY KEY, value CHAR(80)) WITHOUT OIDS;
CREATE TABLE child1 () INHERITS (parent) WITHOUT OIDS;
CREATE TABLE child2 () INHERITS (parent) WITHOUT OIDS;
INSERT INTO child1  SELECT pk, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
AS value  FROM (SELECT generate_series(1,50000) * 2 - 1 AS pk) g;
INSERT INTO child2  SELECT pk, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
AS value  FROM (SELECT generate_series(1,50000) * 2 AS pk) g;
CREATE TABLE interesting (pk INTEGER PRIMARY KEY) WITHOUT OIDS;
INSERT INTO interesting SELECT generate_series(1,10) * 999;
ALTER TABLE child1 ADD PRIMARY KEY (pk);
ALTER TABLE child2 ADD PRIMARY KEY (pk);
CLUSTER parent_pkey ON parent;
CLUSTER child1_pkey ON child1;
CLUSTER child2_pkey ON child2;
CLUSTER interesting_pkey ON interesting;
VACUUM ANALYZE;

Doing:

EXPLAIN ANALYZE SELECT * FROM ONLY parent NATURAL JOIN interesting;
EXPLAIN ANALYZE SELECT * FROM ONLY child1 NATURAL JOIN interesting;
EXPLAIN ANALYZE SELECT * FROM ONLY child2 NATURAL JOIN interesting;
EXPLAIN ANALYZE SELECT * FROM parent NATURAL JOIN interesting;
EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM child1 UNION ALL SELECT *
FROM child2) u NATURAL JOIN interesting;
EXPLAIN ANALYZE SELECT * FROM child1 NATURAL JOIN interesting UNION
ALL SELECT * FROM child2 NATURAL JOIN interesting;

Results (on Debian 8.0.3-10):

EXPLAIN ANALYZE SELECT * FROM ONLY parent NATURAL JOIN interesting;
                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------Hash
Join (cost=1.12..15.43 rows=10 width=248) (actual 
time=0.272..0.272 rows=0 loops=1)  Hash Cond: ("outer".pk = "inner".pk)  ->  Seq Scan on parent  (cost=0.00..12.80
rows=280width=248) 
(actual time=0.004..0.004 rows=0 loops=1)  ->  Hash  (cost=1.10..1.10 rows=10 width=4) (actual
time=0.094..0.094 rows=0 loops=1)        ->  Seq Scan on interesting  (cost=0.00..1.10 rows=10
width=4) (actual time=0.009..0.046 rows=10 loops=1)Total runtime: 0.342 ms
(6 rows)

EXPLAIN ANALYZE SELECT * FROM ONLY child1 NATURAL JOIN interesting;
                                                        QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=0.00..31.36 rows=10 width=88) (actual 
time=0.091..0.504 rows=5 loops=1)  ->  Seq Scan on interesting  (cost=0.00..1.10 rows=10 width=4)
(actual time=0.007..0.048 rows=10 loops=1)  ->  Index Scan using child1_pkey on child1  (cost=0.00..3.01 rows=1
width=88) (actual time=0.030..0.032 rows=0 loops=10)        Index Cond: (child1.pk = "outer".pk)Total runtime: 11.798
ms
(5 rows)

EXPLAIN ANALYZE SELECT * FROM ONLY child2 NATURAL JOIN interesting;
                                                        QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=0.00..31.36 rows=10 width=88) (actual 
time=0.109..0.365 rows=5 loops=1)  ->  Seq Scan on interesting  (cost=0.00..1.10 rows=10 width=4)
(actual time=0.007..0.047 rows=10 loops=1)  ->  Index Scan using child2_pkey on child2  (cost=0.00..3.01 rows=1
width=88) (actual time=0.017..0.019 rows=0 loops=10)        Index Cond: (child2.pk = "outer".pk)Total runtime: 0.450 ms
(5 rows)

EXPLAIN ANALYZE SELECT * FROM parent NATURAL JOIN interesting;
                                                          QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------Hash
Join (cost=1.12..3023.14 rows=3582 width=248) (actual 
time=7.531..1500.865 rows=10 loops=1)  Hash Cond: ("outer".pk = "inner".pk)  ->  Append  (cost=0.00..2484.80
rows=100280width=248) (actual 
time=0.049..1113.450 rows=100000 loops=1)        ->  Seq Scan on parent  (cost=0.00..12.80 rows=280 width=248)
(actual time=0.003..0.003 rows=0 loops=1)        ->  Seq Scan on child1 parent  (cost=0.00..1236.00 rows=50000
width=88) (actual time=0.034..217.244 rows=50000 loops=1)        ->  Seq Scan on child2 parent  (cost=0.00..1236.00
rows=50000
width=88) (actual time=0.031..194.513 rows=50000 loops=1)  ->  Hash  (cost=1.10..1.10 rows=10 width=4) (actual
time=0.092..0.092 rows=0 loops=1)        ->  Seq Scan on interesting  (cost=0.00..1.10 rows=10
width=4) (actual time=0.007..0.044 rows=10 loops=1)Total runtime: 1501.001 ms
(9 rows)

EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM child1 UNION ALL SELECT *
FROM child2) u NATURAL JOIN interesting;
                                                              QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------Hash
Join (cost=1.12..5023.12 rows=5000 width=36) (actual 
time=15.204..2934.058 rows=10 loops=1)  Hash Cond: ("outer".pk = "inner".pk)  ->  Subquery Scan u  (cost=0.00..4472.00
rows=100000width=36) 
(actual time=0.066..2585.932 rows=100000 loops=1)        ->  Append  (cost=0.00..3472.00 rows=100000 width=88) (actual
time=0.057..1844.174 rows=100000 loops=1)              ->  Subquery Scan "*SELECT* 1"  (cost=0.00..1736.00
rows=50000 width=88) (actual time=0.051..588.502 rows=50000 loops=1)                    ->  Seq Scan on child1
(cost=0.00..1236.00
rows=50000 width=88) (actual time=0.041..204.605 rows=50000 loops=1)              ->  Subquery Scan "*SELECT* 2"
(cost=0.00..1736.00
rows=50000 width=88) (actual time=0.043..572.005 rows=50000 loops=1)                    ->  Seq Scan on child2
(cost=0.00..1236.00
rows=50000 width=88) (actual time=0.033..197.624 rows=50000 loops=1)  ->  Hash  (cost=1.10..1.10 rows=10 width=4)
(actual
time=0.100..0.100 rows=0 loops=1)        ->  Seq Scan on interesting  (cost=0.00..1.10 rows=10
width=4) (actual time=0.014..0.051 rows=10 loops=1)Total runtime: 2934.287 ms
(11 rows)

EXPLAIN ANALYZE SELECT * FROM child1 NATURAL JOIN interesting UNION
ALL SELECT * FROM child2 NATURAL JOIN interesting;
                                                              QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------Append
(cost=0.00..62.92 rows=20 width=88) (actual time=0.106..0.863 
rows=10 loops=1)  ->  Subquery Scan "*SELECT* 1"  (cost=0.00..31.46 rows=10 width=88)
(actual time=0.099..0.429 rows=5 loops=1)        ->  Nested Loop  (cost=0.00..31.36 rows=10 width=88) (actual
time=0.090..0.381 rows=5 loops=1)              ->  Seq Scan on interesting  (cost=0.00..1.10 rows=10
width=4) (actual time=0.013..0.055 rows=10 loops=1)              ->  Index Scan using child1_pkey on child1
(cost=0.00..3.01 rows=1 width=88) (actual time=0.017..0.020 rows=0
loops=10)                    Index Cond: (child1.pk = "outer".pk)  ->  Subquery Scan "*SELECT* 2"  (cost=0.00..31.46
rows=10width=88) 
(actual time=0.065..0.355 rows=5 loops=1)        ->  Nested Loop  (cost=0.00..31.36 rows=10 width=88) (actual
time=0.057..0.309 rows=5 loops=1)              ->  Seq Scan on interesting  (cost=0.00..1.10 rows=10
width=4) (actual time=0.005..0.045 rows=10 loops=1)              ->  Index Scan using child2_pkey on child2
(cost=0.00..3.01 rows=1 width=88) (actual time=0.012..0.014 rows=0
loops=10)                    Index Cond: (child2.pk = "outer".pk)Total runtime: 1.016 ms
(12 rows)