From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Joining on a view containing a UNION ALL produces a suboptimal plan on 9.3.4 |
Date: | 2014-07-10 15:47:12 |
Message-ID: | 7091.1405007232@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | AlexK | 2014-07-10 15:48:03 | Re: Should I partition this table? |
Previous Message | AlexK | 2014-07-10 15:40:59 | Re: Should I partition this table? |