Re: Bad query plan when you add many OR conditions

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

@Justin Pryzby I have tried this as you suggested:

CREATE STATISTICS statistics_on_subscriptions_project_id_and_tags ON
project_id, tags FROM subscriptions;
VACUUM ANALYZE subscriptions;

Unfortunately nothing changes and Postgresql continues to use the wrong
plan (maybe stats don't work well on array fields like tags??).

On Fri, Jan 10, 2020 at 4:06 AM Justin Pryzby <[email protected]> wrote:

> On Fri, Jan 10, 2020 at 02:11:14AM +0100, Marco Colli wrote:
> > I have a query on a large table that is very fast (0s):
> >
> https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt
>
> ORDER BY + LIMIT is a query which sometimes has issues, you can probably
> find
> more by searching.  The planner thinks it'll hit the LIMIT pretty soon and
> only
> run a fraction of the index scan - but then it turns out to be wrong.
>
> You might have poor statistics on project_id and/or tags.  This *might*
> help:
> ALTER TABLE subscriptions ALTER project_id SET STATISTICS 2000; ANALYZE
> subscriptions;
>
> But I'm guessing there's correlation between the two, which the planner
> doesn't
> know.  If you're running at least v10, I'm guessing it would help to CREATE
> STATISTICS on those columns (and analyze).
>
> See one similar problem here (not involving LIMIT).
>
> https://www.postgresql.org/message-id/flat/CABFxtPedz4zL%2BaPWut4%2B%3Dum4av1aAXr6OVRfRB_6K7mJKMbEcw%40mail.gmail.com
>

--000000000000a8252f059bc92144
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">@Justin=
 Pryzby=C2=A0I have tried this as you suggested:</div><div dir=3D"ltr"><br>=
</div><div dir=3D"ltr">CREATE STATISTICS statistics_on_subscriptions_projec=
t_id_and_tags ON project_id, tags FROM subscriptions;<br></div><div dir=3D"=
ltr">VACUUM ANALYZE subscriptions;<br></div><div dir=3D"ltr"><br></div><div=
>Unfortunately nothing changes and Postgresql continues to use the wrong pl=
an (maybe stats don&#39;t work well on array fields like tags??).</div></di=
v></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmai=
l_attr">On Fri, Jan 10, 2020 at 4:06 AM Justin Pryzby &lt;<a href=3D"mailto=
:[email protected]">[email protected]</a>&gt; wrote:<br></div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-wi=
dth:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-=
left:1ex">On Fri, Jan 10, 2020 at 02:11:14AM +0100, Marco Colli wrote:<br>
&gt; I have a query on a large table that is very fast (0s):<br>
&gt; <a href=3D"https://gist.github.com/collimarco/039412b4fe0dcf39955888f9=
6eff29db#file-fast_query-txt" rel=3D"noreferrer" target=3D"_blank">https://=
gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query=
-txt</a><br>
<br>
ORDER BY + LIMIT is a query which sometimes has issues, you can probably fi=
nd<br>
more by searching.=C2=A0 The planner thinks it&#39;ll hit the LIMIT pretty =
soon and only<br>
run a fraction of the index scan - but then it turns out to be wrong.<br>
<br>
You might have poor statistics on project_id and/or tags.=C2=A0 This *might=
* help:<br>
ALTER TABLE subscriptions ALTER project_id SET STATISTICS 2000; ANALYZE sub=
scriptions;<br>
<br>
But I&#39;m guessing there&#39;s correlation between the two, which the pla=
nner doesn&#39;t<br>
know.=C2=A0 If you&#39;re running at least v10, I&#39;m guessing it would h=
elp to CREATE<br>
STATISTICS on those columns (and analyze).<br>
<br>
See one similar problem here (not involving LIMIT).<br>
<a href=3D"https://www.postgresql.org/message-id/flat/CABFxtPedz4zL%2BaPWut=
4%2B%3Dum4av1aAXr6OVRfRB_6K7mJKMbEcw%40mail.gmail.com" rel=3D"noreferrer" t=
arget=3D"_blank">https://www.postgresql.org/message-id/flat/CABFxtPedz4zL%2=
BaPWut4%2B%3Dum4av1aAXr6OVRfRB_6K7mJKMbEcw%40mail.gmail.com</a><br>
</blockquote></div>

--000000000000a8252f059bc92144--