That did the trick! Thanks, Tom! On Thu, Jul 10, 2014 at 8:47 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Chris Hanks <christopher.m.hanks@xxxxxxxxx> writes: >> CREATE VIEW tables AS >> SELECT a.*, b.col AS other_col >> FROM a >> LEFT JOIN b ON a.id = b.id >> UNION ALL >> SELECT c.*, d.col AS other_col >> FROM c >> LEFT JOIN d ON c.id = d.id; > >> EXPLAIN ANALYZE >> SELECT * >> FROM tables >> WHERE id = 89; -- Index scans, as expected. > >> EXPLAIN ANALYZE >> SELECT * >> FROM e >> JOIN tables ON e.col = tables.id >> WHERE e.id = 568; -- Big merge joins, when simple index scans should >> be possible? > >> Would this be considered a deficiency in the optimizer? Is there a simple fix? > > Don't hold your breath. To arrive at the > union-on-the-inside-of-a-nestloop plan you're hoping for, the planner > would have to create a "parameterized path" for the UNION ALL structure. > But when you have joins in the arms of the UNION ALL, they are considered > to be independent subqueries, and we currently have a policy decision not > to try to generate parameterized paths for subqueries. It'd be quite > expensive and I think the planner is probably lacking some necessary > mechanisms anyway. > > Given that e.id is unique, you could possibly fake it with something like > > select * from tables where id = (select e.col from e where e.id = 568); > > regards, tom lane
