Re: hashed subplan 5000x slower than two sequential operations

Список
Период
Сортировка
От Tom Lane
Тема Re: hashed subplan 5000x slower than two sequential operations
Дата
Msg-id 20170.1291839146@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: hashed subplan 5000x slower than two sequential operations  (Shrirang Chitnis)
Ответы Re: hashed subplan 5000x slower than two sequential operations  (Pavel Stehule)
Список pgsql-performance
Дерево обсуждения
hashed subplan 5000x slower than two sequential operations  (Bryce Nesbitt, )
 Re: hashed subplan 5000x slower than two sequential operations  (Shrirang Chitnis, )
  Re: hashed subplan 5000x slower than two sequential operations  (Bryce Nesbitt, )
  Re: hashed subplan 5000x slower than two sequential operations  (Tom Lane, )
   Re: hashed subplan 5000x slower than two sequential operations  (Pavel Stehule, )
 Re: hashed subplan 5000x slower than two sequential operations  ("Marc Mamin", )
  Re: hashed subplan 5000x slower than two sequential operations  (Bryce Nesbitt, )
 Re: hashed subplan 5000x slower than two sequential operations  ("Marc Mamin", )
  Re: hashed subplan 5000x slower than two sequential operations  (Bryce Nesbitt, )
   Re: hashed subplan 5000x slower than two sequential operations  (masterchief, )
    Re: hashed subplan 5000x slower than two sequential operations  (Віталій Тимчишин, )
Shrirang Chitnis <> writes:
> Bryce,
> The two queries are different:

I suspect the second one is a typo and not what he really wanted.

> WHERE (contexts.parent_key = 392210
>       OR contexts.context_key IN
>          (SELECT collection_data.context_key
>          FROM collection_data
>           WHERE collection_data.collection_context_key = 392210)

The only really effective way the planner knows to optimize an
"IN (sub-SELECT)" is to turn it into a semi-join, which is not possible
here because of the unrelated OR clause.  You might consider replacing
this with a UNION of two scans of "contexts".  (And yes, I know it'd be
nicer if the planner did that for you.)

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: Robert Haas
Дата:
Сообщение: Re: Performance under contention
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: Hardware recommendations