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]

 



I need it the way it is. It's a foreign key in the actual query.

Thanks!

On Thu, Jul 10, 2014 at 8:31 AM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:
>> -----Original Message-----
>> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-
>> owner@xxxxxxxxxxxxxx] On Behalf Of Chris Hanks
>> Sent: Thursday, July 10, 2014 5:02 AM
>> To: PostgreSQL General
>> Subject: [GENERAL] Joining on a view containing a UNION ALL produces a
>> suboptimal plan on 9.3.4
>>
>> Hi everyone -
>>
>> I have a slow query issue in an app I'm working on. I'm unfortunately not at
>> liberty to share the query/schema details, but I've put together a very similar
>> reproduction of the issue:
>>
>> -----
>>
>> CREATE TABLE a (id integer primary key, col integer); CREATE TABLE b (id
>> integer primary key, col integer); CREATE TABLE c (id integer primary key, col
>> integer); CREATE TABLE d (id integer primary key, col integer); CREATE TABLE
>> e (id integer primary key, col integer);
>>
>> INSERT INTO a (id, col) SELECT i, floor(random() * 100000) FROM
>> generate_series(1, 100000, 2) i; INSERT INTO b (id, col) SELECT i,
>> floor(random() * 100000) FROM generate_series(1, 100000, 2) i; INSERT INTO
>> c (id, col) SELECT i, floor(random() * 100000) FROM generate_series(2,
>> 100000, 2) i; INSERT INTO d (id, col) SELECT i, floor(random() * 100000) FROM
>> generate_series(2, 100000, 2) i; INSERT INTO e (id, col) SELECT i,
>> floor(random() * 100000) FROM generate_series(1, 100000, 1) i;
>>
>> ANALYZE;
>>
>> 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?
>>
>> Thanks!
>> Chris
>>
>
> Chris,
>
> " JOIN tables ON e.col = tables.id"  - is this a typo?
>
> Shouldn't it be " JOIN tables ON e.id = tables.id" ?
>
> Or, you need it the way it is?
>
> Regards,
> Igor Neyman
>
>



[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