Re: Bad query plan when you add many OR conditions

--0000000000009f30af059bc71460
Content-Type: text/plain; charset="UTF-8"

I am trying different solutions and what I have found is even more
surprising to me...

The query is always this:
https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-slow_query-txt

I have added this index which would allow an index only scan:
"index_subscriptions_on_project_id_and_created_at_and_tags" btree
(project_id, created_at DESC, tags) WHERE trashed_at IS NULL

But Postgresql continues to use this index (which has less information and
then requires slow access to disk):
"index_subscriptions_on_project_id_and_created_at" btree (project_id,
created_at DESC)



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
>

--0000000000009f30af059bc71460
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">I am tr=
ying different solutions and what I have found is even more surprising to m=
e...</div><div dir=3D"ltr"><br></div><div>The query is always this:</div><d=
iv><a href=3D"https://gist.github.com/collimarco/039412b4fe0dcf39955888f96e=
ff29db#file-slow_query-txt" target=3D"_blank">https://gist.github.com/colli=
marco/039412b4fe0dcf39955888f96eff29db#file-slow_query-txt</a><br></div><di=
v dir=3D"ltr"><div><br></div>I have added this index which would allow an i=
ndex only scan:<br>&quot;index_subscriptions_on_project_id_and_created_at_a=
nd_tags&quot; btree (project_id, created_at DESC, tags) WHERE trashed_at IS=
 NULL</div><div dir=3D"ltr"><br>But Postgresql continues to use this index =
(which has less information and then requires slow access to disk):<br>&quo=
t;index_subscriptions_on_project_id_and_created_at&quot; btree (project_id,=
 created_at DESC)<div>
<p style=3D"margin:0px;font-stretch:normal;font-size:11px;line-height:norma=
l;font-family:Menlo;color:rgb(0,0,0)"><span style=3D"font-variant-ligatures=
:no-common-ligatures"></span></p></div></div><div dir=3D"ltr"><br></div></d=
iv></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gma=
il_attr">On Fri, Jan 10, 2020 at 4:06 AM Justin Pryzby &lt;<a href=3D"mailt=
o:[email protected]">[email protected]</a>&gt; wrote:<br></div><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-w=
idth: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>

--0000000000009f30af059bc71460--