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 having 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 the condition onu.id is not null does not

Re: [PERFORM] IS NOT NULL and LEFT JOIN

2014-10-20 Thread Tom Lane
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.

[PERFORM] Query with large number of joins

2014-10-20 Thread Marco Di Cesare
We are using Postgres for the first time after being SQLServer users for a long time so forgive for being noobs. We are using a BI tool that generates a query with an unusually large number of joins. My understanding is that with this many joins Postgres query planner can't possibly use an exha

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 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

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 < > 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 thinki

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 < >> 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

Re: [PERFORM] Query with large number of joins

2014-10-20 Thread Tom Lane
Marco Di Cesare writes: > We are using a BI tool that generates a query with an unusually large number > of joins. My understanding is that with this many joins Postgres query > planner can't possibly use an exhaustive search so it drops into a heuristics > algorithm. Unfortunately, the query r