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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
