Bad query plan when you add many OR conditions

--000000000000f762bf059bbecd19
Content-Type: text/plain; charset="UTF-8"

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...

However if you increase the number of OR at some point PostgreSQL makes the
bad decision to change its query plan! And the new plan makes the query
terribly slow:
https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-slow_query-txt

Instead of this (which is fast):
  Bitmap Index Scan on index_subscriptions_on_project_id_and_tags
It starts using this (which is slow):
  Parallel Index Scan using index_subscriptions_on_project_id_and_created_at
The choice seems quite stupid since it doesn't have the tags on the new
index... and indeed the query takes about 1 minute instead of a few
milliseconds. Here's a list of the available indexes:
https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-_indexes-txt

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


Thank you very much for any advice!
Marco Colli

--000000000000f762bf059bbecd19
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div di=
r=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"lt=
r">Hello!<div><br></div><div>I have a query on a large table that is very f=
ast (0s):</div><div><a href=3D"https://gist.github.com/collimarco/039412b4f=
e0dcf39955888f96eff29db#file-fast_query-txt">https://gist.github.com/collim=
arco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt</a><br></div><div=
><br></div><div>Basically the query matches the rows that have a tag1 OR ta=
g2 OR tag3 OR tag4 OR tag5...=C2=A0</div><div><br></div><div>However if you=
 increase the number of OR at some point PostgreSQL makes the bad decision =
to change its query plan! And the new plan makes the query terribly slow:</=
div><div><a href=3D"https://gist.github.com/collimarco/039412b4fe0dcf399558=
88f96eff29db#file-slow_query-txt">https://gist.github.com/collimarco/039412=
b4fe0dcf39955888f96eff29db#file-slow_query-txt</a><br></div><div><br></div>=
<div>Instead of this (which is fast):</div><div>=C2=A0 Bitmap Index Scan on=
 index_subscriptions_on_project_id_and_tags</div><div>It starts using this =
(which is slow):</div><div>=C2=A0 Parallel Index Scan using index_subscript=
ions_on_project_id_and_created_at<br></div><div>The choice seems quite stup=
id since it doesn&#39;t have the tags on the new index... and indeed the qu=
ery takes about 1 minute instead of a few milliseconds. Here&#39;s a list o=
f the available indexes:</div><div><a href=3D"https://gist.github.com/colli=
marco/039412b4fe0dcf39955888f96eff29db#file-_indexes-txt">https://gist.gith=
ub.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-_indexes-txt</a><br=
></div><div><br></div><div>How can I encourage PostgreSQL to use the Bitmap=
 Index Scan even when there are many OR conditions? I have tried with VACUU=
M ANALYZE subscriptions but it doesn&#39;t help.</div><div><br></div><div>N=
ote: the query is generated dynamically by customers of a SaaS, so I don&#3=
9;t have full control on it</div><div><br></div><div><br></div><div>Thank y=
ou very much for any advice!</div><div>Marco Colli</div><div><br></div><div=
><br></div></div></div></div></div></div></div></div></div></div>

--000000000000f762bf059bbecd19--