Re: Bad query plan when you add many OR conditions

--00000000000011b29d059bcc3d8c
Content-Type: text/plain; charset="UTF-8"

On Thu, Jan 9, 2020 at 8:11 PM Marco Colli <[email protected]> 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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Jan 9, 2020 at 8:11 PM Marco Coll=
i &lt;<a href=3D"mailto:[email protected]">[email protected]</a>&=
gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><d=
iv dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=
=3D"ltr"><div dir=3D"ltr">Hello!<div><br></div><div>I have a query on a lar=
ge table that is very fast (0s):</div><div><a href=3D"https://gist.github.c=
om/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt" target=
=3D"_blank">https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff=
29db#file-fast_query-txt</a><br></div><div><br></div><div>Basically the que=
ry matches the rows that have a tag1 OR tag2 OR tag3 OR tag4 OR tag5...=C2=
=A0</div></div></div></div></div></div></div></div></div></div></blockquote=
><div><br></div><div>Each branch of the OR is anticipated to return 400 row=
s, but it actually returns 0.=C2=A0 If it actually were to return 400 rows =
per branch, than eventually the plan switch actually would make sense.</div=
><div><br></div><div>Why is the estimate off by so much?=C2=A0 If you run a=
 simple select, what the actual and expected number of rows WHERE project_i=
d =3D 12345?=C2=A0 WHERE tags @&gt; &#39;{crt:2018_11}&#39;?=C2=A0 Is one o=
f those estimates way off reality, or is it only the conjunction which is d=
eranged?</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><=
div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=
=3D"ltr"><div></div><div>How can I encourage PostgreSQL to use the Bitmap I=
ndex Scan even when there are many OR conditions? I have tried with VACUUM =
ANALYZE subscriptions but it doesn&#39;t help.</div><div><br></div><div>Not=
e: the query is generated dynamically by customers of a SaaS, so I don&#39;=
t have full control on it</div></div></div></div></div></div></div></div></=
div></div></blockquote><div><br></div><div>Do you have enough control to ch=
ange the ORDER BY to:</div><div><br></div><div><span style=3D"color:rgb(36,=
41,46);font-family:SFMono-Regular,Consolas,&quot;Liberation Mono&quot;,Menl=
o,monospace;font-size:12px;white-space:pre">ORDER BY (&quot;subscriptions&q=
uot;.&quot;created_at&quot; + interval &#39;0 days&#39;) DESC</span>=C2=A0=
=C2=A0</div><div><br></div><div>Cheers,</div><div><br></div><div>Jeff</div>=
</div></div>

--00000000000011b29d059bcc3d8c--