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
