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.
