Re: Bad query plan when you add many OR conditions

On Thu, Jan 9, 2020 at 8:11 PM Marco Colli <collimarco91(at)gmail(dot)com> wrote:

> Hello!
>
> I have a query on a large table that is very fast (0s):
>
> https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt
>
> 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