Re: query gets very slow when :jsonb ?& operator is used

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: query gets very slow when :jsonb ?& operator is used
Дата
Msg-id CAMkU=1w_=vKHxOgfGm18CS1tQtNEXC0rnOBtmVzQgFwQo82HtA@mail.gmail.com
обсуждение исходный текст
Ответ на query gets very slow when :jsonb ?& operator is used  (<davidjesse091@aol.com>)
Список pgsql-performance
Дерево обсуждения
On Thu, Sep 6, 2018 at 7:52 PM <davidjesse091@aol.com> wrote:
I have also asked this question on Stackoverflow and DBA stack exchange with no answer. It's a fairly long post, so I will post a link to it, as on Stackoverflow it is formatted nicely 


Any idea why my query slows down so much when I add account.residence_details::jsonb ?& array['city', 'state', 'streetName'] ?

The planner has no insight into what fraction of rows will satisfy the ?& condition, and falls back on the assumption that  very few will.  This is (apparently) a very bad assumption, and causes it choose a bad plan.  

Rewriting the `phone_number.account_id IN (subquery)` into an exists query might help.

Cheers,

Jeff

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

Предыдущее
От:
Дата:
Сообщение: query gets very slow when :jsonb ?& operator is used
Следующее
От: Dinesh Kumar
Дата:
Сообщение: Re: Performance difference in accessing differrent columns in aPostgres Table