Re: [NOVICE] Queries with Joins before filtering taking too muchtime! Filter (where clause) *first* -- suggestions ?

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [NOVICE] Queries with Joins before filtering taking too muchtime! Filter (where clause) *first* -- suggestions ?
Дата
Msg-id CAKFQuwYg5YgmKwVf1OGeEktO99yGPq_ed7_uSArciiBdR6tU6w@mail.gmail.com
обсуждение исходный текст
Ответ на [NOVICE] Re: Queries with Joins before filtering taking too much time! Filter(where clause) *first* -- suggestions ?  (Hursh Jain <hurshj@gmail.com>)
Ответы Re: [NOVICE] Queries with Joins before filtering taking too muchtime! Filter (where clause) *first* -- suggestions ?  (Hursh Jain <hurshj@gmail.com>)
Список pgsql-novice
Дерево обсуждения
On Sunday, January 15, 2017, Hursh Jain <hurshj@gmail.com> wrote:

SELECT * from property
WHERE pid in (
  SELECT pid FROM reward WHERE reward_type = 'DAILYPROMO_WIN')
;

And this also runs (again taking a long time) but without any errors. How can this be ? (there is no pid in the reward table, so why does the
subquery not throw an error)?


The "pid" column is the one from the outer query property table - this is a correlated subquery that you've written. 

As long as one record with that reward_type exists your query devolves to "where true" thus making indexes useless and returning every row in property.

David J.

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

Предыдущее
От: Hursh Jain
Дата:
Сообщение: [NOVICE] Re: Queries with Joins before filtering taking too much time! Filter(where clause) *first* -- suggestions ?
Следующее
От: Piyush Katariya
Дата:
Сообщение: [NOVICE] (Stateless and Distributed) Transaction in Event Driven Architecture