Re: Combining two queries

Поиск
Список
Период
Сортировка
От Robert DiFalco
Тема Re: Combining two queries
Дата
Msg-id CAAXGW-zEWLartZCLFuDbMGVWBwNfef4xWipicgWoeo4KutWRwA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Combining two queries  (David Johnston <david.g.johnston@gmail.com>)
Список 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.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 по дате отправления:

Предыдущее
От: Patrick Krecker
Дата:
Сообщение: Re: Combining two queries
Следующее
От: M Tarkeshwar Rao
Дата:
Сообщение: can you please share sample Postgres config file to enable max logging with syslog support?