From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: IS NOT NULL and LEFT JOIN
Date: Mon, 20 Oct 2014 15:30:05 -0700 (PDT)
Message-ID: <1413844205376-5823739.post@n5.nabble.com> (raw)
In-Reply-To: <1413843590010-5823737.post@n5.nabble.com>
References: <544339CA.5090202@enercoop.org>
<CAApHDvoqMm-hdoktQ+9ErGZ3rkF6=vgeHTJ-M6OeQepr1XLDoQ@mail.gmail.com>
<5444D5FF.5070407@enercoop.org>
<3181.1413813488@sss.pgh.pa.us>
<54457869.3030601@enercoop.org>
<1413843590010-5823737.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
David G Johnston wrote
>
> Laurent Martelli wrote
>> Le 20/10/2014 15:58, Tom Lane a écrit :
>>> Laurent Martelli <
>> laurent.martelli@
>> > writes:
>>>> Do we agree that both queries are identical ?
>>> No, they *aren't* identical. Go consult any SQL reference. Left join
>>> conditions don't work the way you seem to be thinking: after the join,
>>> the RHS column might be null, rather than equal to the LHS column.
>> Yes, I was wrong to assume that c.user_info=u.id because of the LEFT
>> JOIN.
>>
>> But since I only want rows where u.id IS NOT NULL, in any case I will
>> also have c.user_info IS NOT NULL.
>>
>> Also, having a foreign key, if c.user_info is not null, it will have a
>> match in u. So in that case, either both c.user_info and c.id are null
>> in the result rows, or they are equal.
> The planner only expends so much effort converting between equivalent
> query forms. By adding u.id IS NOT NULL you are saying that you really
> meant to use INNER JOIN instead of LEFT JOIN but whether the planner can
> and/or does act on that information in the WHERE clause to modify its
> joins is beyond my knowledge. It doesn't seem to and probably correctly
> isn't worth adding the planner cycles to fix a poorly written/generated
> query on-the-fly.
>
>
> Now that it has been pointed out that the two queries you supplied are
> semantically different it is unclear what your point here is. It is known
> that Hibernate (and humans too) will generate sub-optimal plans that can
> be rewritten using relational algebra and better optimized for having done
> so. But such work takes resources that would be expended for every single
> query while manually rewriting the sub-optimal query solves the problem
> once-and-for-all.
>
> David J.
Didn't sound right what I wrote above...
The presence of the "OR" screws things up even further since it does force
the use of LEFT JOIN mechanics for the single case where the name and e-mail
match.
I would maybe try a UNION DISTINCT query instead of an OR clause if you want
to have a query that performs better than the Hibernate one...otherwise
others more knowledgeable than myself have not made any indication that the
planner is unintentionally deficient in its handling of your original query.
You may try posting your actual question, and not the SQL, and see if that
sparks any suggestions.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/IS-NOT-NULL-and-LEFT-JOIN-tp5823591p5823739.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
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: david.g.johnston@gmail.com
Subject: Re: IS NOT NULL and LEFT JOIN
In-Reply-To: <1413844205376-5823739.post@n5.nabble.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