Postgres Pro
Downloads
Home   >   mailing lists

Re: Combining two queries - Mailing list pgsql-general

From Robert DiFalco
Subject Re: Combining two queries
Date
Msg-id CAAXGW-zEWLartZCLFuDbMGVWBwNfef4xWipicgWoeo4KutWRwA@mail.gmail.com
Whole thread Raw
In response to Re: Combining two queries  (David Johnston <david.g.johnston@gmail.com>)
List pgsql-general
Tree view
The INNER JOIN to itself with a count turns out to have a lower cost query plan than the INTERSECT approach. On the ROW approach, it also seems to take longer than the simple EXISTS query. But I suppose I can put both of those into CTEs for convenience. I guess I was just hoping there was a lower cost approach than what I was already doing.

On Thu, Dec 18, 2014 at 2:07 PM, David Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Dec 18, 2014 at 3:02 PM, Robert DiFalco <robert.difalco@gmail.com> wrote:
Is the intersect any better than what I originally showed? On the ROW approach, I'm not sure where the context for that is coming from since it may not be in the intersection. Consider n1 and n2 are NOT friends but they have >0 mutual friends between them.


​The INTERSECT is a lot more direct about finding mutual friends.  The ROW() = ROW() piece is independent of the mutual friends question - it should be put in a WHERE clause and you can test whether a row is returned which, if one is, means the two people are friends.​

​"One Query" does not mean you need to do everything​ all-at-once.  I suggest you make use of CTEs (WITH) subqueries for each distinct calculation you need then join all of the CTE items together in a final query the outputs the data in the format desired.

David J.

pgsql-general by date:

Previous
From: Patrick Krecker
Date:
Subject: Re: Combining two queries
Next
From: M Tarkeshwar Rao
Date:
Subject: can you please share sample Postgres config file to enable max logging with syslog support?