From: Hedayat Vatankhah <hedayat.fwd@gmail.com>
To: postgres performance list <pgsql-performance@postgresql.org>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Subject: Re: PostgreSQL seems to create inefficient plans in simple conditional joins
Date: Mon, 8 Feb 2016 12:20:49 +0330
Message-ID: <56B856E9.8070703@gmail.com> (raw)
In-Reply-To: <56AD303D.8090800@gmail.com>
References: <CAK9_=2HpMntYCBwWwtkZ7RyeBwrd6Ou=oHWRZCL45FW9612pWg@mail.gmail.com>
<CAKJS1f-0=25E0TnCOz2dy9aaJna9MtHC70Gv_NdpRxR4+iiJVA@mail.gmail.com>
<56AD303D.8090800@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi again,
/*Hedayat Vatankhah*/ wrote on Sun, 31 Jan 2016 01:20:53 +0330:
> Hi,
>
> /*David Rowley*/ wrote on Sun, 31 Jan 2016 04:57:04 +1300:
>> On 31 January 2016 at 01:30, Hedayat Vatankhah
>> <hedayat.fwd@gmail.com> wrote:
>>> Personally, I expect both queries below to perform exactly the same:
>>>
>>> SELECT
>>> t1.id, *
>>> FROM
>>> t1
>>> INNER JOIN
>>> t2 ON t1.id = t2.id
>>> where t1.id > -9223372036513411363;
>>>
>>> And:
>>>
>>> SELECT
>>> t1.id, *
>>> FROM
>>> t1
>>> INNER JOIN
>>> t2 ON t1.id = t2.id
>>> where t1.id > -9223372036513411363 and t2.id >
>>> -9223372036513411363;
>>>
>>> Unfortunately, they do not. PostgreSQL creates different plans for
>>> these
>>> queries, which results in very poor performance for the first one
>>> compared
>>> to the second (What I'm testing against is a DB with around 350 million
>>> rows in t1, and slightly less in t2).
>>>
>>> EXPLAIN output:
>>> First query: http://explain.depesz.com/s/uauk
>>> Second query: link: http://explain.depesz.com/s/uQd
>> Yes, unfortunately you've done about the only thing that you can do,
>> and that's just include both conditions in the query. Is there some
>> special reason why you can't just write the t2.id > ... condition in
>> the query too? or is the query generated dynamically by some software
>> that you have no control over?
I just found another issue with using a query like the second one (using
LEFT JOINs instead of INNER JOINs): referencing id columns of joined
tables explicitly disables PostgreSQL join removal optimization when you
only select column(s) from t1! :(
I should forget about creating views on top of JOIN queries, and build
appropriate JOIN queries with referenced table and appropriate
conditions manually, so the whole data model should be exposed to the
application.
If I'm not wrong, PostgreSQL should understand that ANY condition on t2
doesn't change the LEFT JOIN output when t2 columns are not SELECTed.
Regards,
Hedayat
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hedayat.fwd@gmail.com, david.rowley@2ndquadrant.com
Subject: Re: PostgreSQL seems to create inefficient plans in simple conditional joins
In-Reply-To: <56B856E9.8070703@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox