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

From: Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com>
To: Igor Neyman <ineyman(at)perceptron(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:36:54
Message-ID: CAK7KUdD1D+CgVHHRFmi6rGAi-sWkZ3hFwueQSv1x0QsSXkaG5w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)perceptron(dot)com> wrote:
>> -----Original Message-----
>> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
>> owner(at)postgresql(dot)org] 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
>
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message AlexK 2014-07-10 15:40:59 Re: Should I partition this table?
Previous Message Igor Neyman 2014-07-10 15:31:48 Re: Joining on a view containing a UNION ALL produces a suboptimal plan on 9.3.4