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.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/IS-NOT-NULL-and-LEFT-JOIN-tp5823591p5823737.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.