Search Postgresql Archives

Re: Joining on a view containing a UNION ALL produces a suboptimal plan on 9.3.4

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux