Re: [PERFORM] IS NOT NULL and LEFT JOIN

2014-10-22 Thread Laurent Martelli
Le Mardi 21 Octobre 2014 10:44 CEST, David Rowley dgrowle...@gmail.com a écrit: For what it's worth I'd say they are identical, at least, if you discount deferring foreign key constraints or also executing the query from within a volatile function which was called by a query which just

Re: [PERFORM] IS NOT NULL and LEFT JOIN

2014-10-21 Thread David Rowley
On Tue, Oct 21, 2014 at 2:58 AM, Tom Lane t...@sss.pgh.pa.us wrote: Laurent Martelli laurent.marte...@enercoop.org 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

Re: [PERFORM] IS NOT NULL and LEFT JOIN

2014-10-20 Thread Laurent Martelli
Hi David, Do we agree that both queries are identical ? Since we join on c.user_info=u.id http://u.id having u.id http://u.id is not null or c.user_info is not null in the where clause is the same, isn't it ? Since c.user_info=u.id http://u.id the condition onu.id is not null does not use

Re: [PERFORM] IS NOT NULL and LEFT JOIN

2014-10-20 Thread Tom Lane
Laurent Martelli laurent.marte...@enercoop.org 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

Re: [PERFORM] IS NOT NULL and LEFT JOIN

2014-10-20 Thread Laurent Martelli
Le 20/10/2014 15:58, Tom Lane a écrit : Laurent Martelli laurent.marte...@enercoop.org 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

Re: [PERFORM] IS NOT NULL and LEFT JOIN

2014-10-20 Thread David G Johnston
Laurent Martelli wrote Le 20/10/2014 15:58, Tom Lane a écrit : Laurent Martelli lt; laurent.martelli@ gt; 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:

Re: [PERFORM] IS NOT NULL and LEFT JOIN

2014-10-20 Thread David G Johnston
David G Johnston wrote Laurent Martelli wrote Le 20/10/2014 15:58, Tom Lane a écrit : Laurent Martelli lt; laurent.martelli@ gt; 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

Re: [PERFORM] IS NOT NULL and LEFT JOIN

2014-10-19 Thread David Rowley
On Sun, Oct 19, 2014 at 5:10 PM, Laurent Martelli laurent.marte...@enercoop.org wrote: Hello there, I have a strange query plan involving an IS NOT NULL and a LEFT JOIN. I grant you that the query can be written without the JOIN on user_user_info, but it is generated like this by

[PERFORM] IS NOT NULL and LEFT JOIN

2014-10-18 Thread Laurent Martelli
Hello there, I have a strange query plan involving an IS NOT NULL and a LEFT JOIN. I grant you that the query can be written without the JOIN on user_user_info, but it is generated like this by hibernate. Just changing the IS NOT NULL condition to the other side of useless JOIN makes a big