I didn't find any CREATE TABLE's in your description, or else I would have
tried it with the sequences and all that, but I think this ought to work.
postgres=# explain select * from ((select * from parent inner join child on
parent.child_id = child.id limit 10) union all (select * from parent left
outer join child on parent.child_id = child.id where child.id is null limit
10)) as v limit 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Limit (cost=0.15..3.72 rows=10 width=88)
-> Append (cost=0.15..7.29 rows=20 width=88)
-> Limit (cost=0.15..2.46 rows=10 width=88)
-> Nested Loop (cost=0.15..246.54 rows=1070 width=88)
-> Seq Scan on parent (cost=0.00..20.70 rows=1070
width=48)
-> Index Scan using child_pkey on child
(cost=0.15..0.21 rows=1 width=40)
Index Cond: (id = parent.child_id)
-> Limit (cost=0.15..4.63 rows=10 width=88)
-> Nested Loop Anti Join (cost=0.15..239.71 rows=535
width=88)
-> Seq Scan on parent parent_1 (cost=0.00..20.70
rows=1070 width=48)
-> Index Scan using child_pkey on child child_1
(cost=0.15..0.21 rows=1 width=40)
Index Cond: (parent_1.child_id = id)
(12 rows)
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html