From: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
---|---|
To: | David Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Combining two queries |
Date: | 2014-12-18 22:14:10 |
Message-ID: | CAAXGW-zEWLartZCLFuDbMGVWBwNfef4xWipicgWoeo4KutWRwA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)g(dot)johnston(at)gmail(dot)com>
wrote:
>
> On Thu, Dec 18, 2014 at 3:02 PM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)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.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | M Tarkeshwar Rao | 2014-12-19 05:00:26 | can you please share sample Postgres config file to enable max logging with syslog support? |
Previous Message | Patrick Krecker | 2014-12-18 22:13:59 | Re: Combining two queries |