Re: Bad query plan when you add many OR conditions

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Bad query plan when you add many OR conditions
Дата
Msg-id CAMkU=1z+QijUWAYgeqeyw+AvD7adPgOmEnY+OcTw6qDVFtD7cQ@mail.gmail.com
обсуждение исходный текст
Ответ на Bad query plan when you add many OR conditions  (Marco Colli <collimarco91@gmail.com>)
Список pgsql-performance
Дерево обсуждения
On Thu, Jan 9, 2020 at 8:11 PM Marco Colli <collimarco91@gmail.com> wrote:
Hello!

I have a query on a large table that is very fast (0s):

Basically the query matches the rows that have a tag1 OR tag2 OR tag3 OR tag4 OR tag5... 

Each branch of the OR is anticipated to return 400 rows, but it actually returns 0.  If it actually were to return 400 rows per branch, than eventually the plan switch actually would make sense.

Why is the estimate off by so much?  If you run a simple select, what the actual and expected number of rows WHERE project_id = 12345?  WHERE tags @> '{crt:2018_11}'?  Is one of those estimates way off reality, or is it only the conjunction which is deranged?
 
How can I encourage PostgreSQL to use the Bitmap Index Scan even when there are many OR conditions? I have tried with VACUUM ANALYZE subscriptions but it doesn't help.

Note: the query is generated dynamically by customers of a SaaS, so I don't have full control on it

Do you have enough control to change the ORDER BY to:

ORDER BY ("subscriptions"."created_at" + interval '0 days') DESC  

Cheers,

Jeff

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

Предыдущее
От: Marco Colli
Дата:
Сообщение: Re: Bad query plan when you add many OR conditions
Следующее
От: Shira Bezalel
Дата:
Сообщение: Seeking reason behind performance gain in 12 with HashAggregate