Re: Bad query plan when you add many OR conditions

--0000000000002aadbc059bca4ab7
Content-Type: text/plain; charset="UTF-8"

Before trying other solutions I would like to make PG use an index-only
scan (it should be fast enough for our purpose).

I have tried to disable the other indexes and forced PG to use this index
(which includes all the fields of the query):
index_subscriptions_on_project_id_and_created_at_and_tags

The problem is that the query plan is this:
https://gist.github.com/collimarco/03f3dde372f001485518b8deca2f3b24#file-index_scan_instead_of_index_only-txt

As you can see it is a *index scan* and not an *index only* scan... I don't
understand why. The index includes all the fields used by the query... so
an index only scan should be possible.


On Fri, Jan 10, 2020 at 2:34 PM Justin Pryzby <[email protected]> wrote:

> On Fri, Jan 10, 2020 at 12:03:39PM +0100, Marco Colli wrote:
> > 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
>
> Are those the only columns in subscriptions ?
>
> > 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)
>
> Did you vacuum the table ?
> Did you try to "explain" the query after dropping the 1st index (like:
> begin;
> DROP INDEX..; explain analyze..; rollback).
>
> Also, is the first (other) index btree_gin (you can \dx to show
> extensions) ?
>
> I think it needs to be a gin index to search tags ?
>
> On Fri, Jan 10, 2020 at 01:42:24PM +0100, Marco Colli wrote:
> > I would like to try your solution but I read that ALTER TABLE... SET
> > STATISTICS  locks the table... Since it is just an experiment and we
> don't
> > know if it actually works it would be greate to avoid locking a large
> table
> > (50M) in production.
>
> I suggest to CREATE TABLE test_subscriptions (LIKE subscriptions INCLUDING
> ALL); INSERT INTO test_subscriptions SELECT * FROM subscriptions; ANALYZE
> test_subscriptions;
>
> Anyway, ALTER..SET STATS requires a strong lock but for only a brief moment
> (assuming it doesn't have to wait).  Possibly you'd be ok doing SET
> statement_timeout='1s'; ALTER TABLE....
>
> > Does CREATE  STATISTICS lock the table too?
>
> You can check by SET client_min_messages=debug; SET lock_timeout=333; SET
> log_lock_waits=on;
> Looks like it needs ShareUpdateExclusiveLock.
>
> > Does statistics work on an array field like tags? (I can't find any
> > information)
>
> It think it'd be data type agnostic.  And seems to work with arrays.
>
> On Fri, Jan 10, 2020 at 02:30:27PM +0100, Marco Colli wrote:
> > @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??).
>
> It'd help to see SELECT stxddependencies FROM pg_statistic_ext WHERE
> stxoid='subscriptions'::regclass
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>Before trying other solutions I woul=
d like to make PG use an index-only scan (it should be fast enough for our =
purpose).</div><div dir=3D"ltr"><br></div><div dir=3D"ltr">I have tried to =
disable the other indexes and forced PG to use this index (which includes a=
ll the fields of the query):<div>index_subscriptions_on_project_id_and_crea=
ted_at_and_tags<br></div><div><br></div><div>The problem is that the query =
plan is this:</div><div><a href=3D"https://gist.github.com/collimarco/03f3d=
de372f001485518b8deca2f3b24#file-index_scan_instead_of_index_only-txt">http=
s://gist.github.com/collimarco/03f3dde372f001485518b8deca2f3b24#file-index_=
scan_instead_of_index_only-txt</a><br></div><div><br></div><div>As you can =
see it is a *index scan* and not an *index only* scan... I don&#39;t unders=
tand why. The index includes all the fields used by the query... so an inde=
x only scan should be possible.</div><div><br></div></div></div></div><br><=
div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Jan=
 10, 2020 at 2:34 PM Justin Pryzby &lt;<a href=3D"mailto:[email protected]=
om">[email protected]</a>&gt; wrote:<br></div><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left=
-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">On Fri, J=
an 10, 2020 at 12:03:39PM +0100, Marco Colli wrote:<br>
&gt; I have added this index which would allow an index only scan:<br>
&gt; &quot;index_subscriptions_on_project_id_and_created_at_and_tags&quot; =
btree<br>
&gt; (project_id, created_at DESC, tags) WHERE trashed_at IS NULL<br>
<br>
Are those the only columns in subscriptions ?<br>
<br>
&gt; But Postgresql continues to use this index (which has less information=
 and<br>
&gt; then requires slow access to disk):<br>
&gt; &quot;index_subscriptions_on_project_id_and_created_at&quot; btree (pr=
oject_id,<br>
&gt; created_at DESC)<br>
<br>
Did you vacuum the table ?<br>
Did you try to &quot;explain&quot; the query after dropping the 1st index (=
like: begin;<br>
DROP INDEX..; explain analyze..; rollback).<br>
<br>
Also, is the first (other) index btree_gin (you can \dx to show extensions)=
 ?<br>
<br>
I think it needs to be a gin index to search tags ?<br>
<br>
On Fri, Jan 10, 2020 at 01:42:24PM +0100, Marco Colli wrote:<br>
&gt; I would like to try your solution but I read that ALTER TABLE... SET<b=
r>
&gt; STATISTICS=C2=A0 locks the table... Since it is just an experiment and=
 we don&#39;t<br>
&gt; know if it actually works it would be greate to avoid locking a large =
table<br>
&gt; (50M) in production.<br>
<br>
I suggest to CREATE TABLE test_subscriptions (LIKE subscriptions INCLUDING<=
br>
ALL); INSERT INTO test_subscriptions SELECT * FROM subscriptions; ANALYZE t=
est_subscriptions;<br>
<br>
Anyway, ALTER..SET STATS requires a strong lock but for only a brief moment=
<br>
(assuming it doesn&#39;t have to wait).=C2=A0 Possibly you&#39;d be ok doin=
g SET<br>
statement_timeout=3D&#39;1s&#39;; ALTER TABLE....=C2=A0 <br>
<br>
&gt; Does CREATE=C2=A0 STATISTICS lock the table too?<br>
<br>
You can check by SET client_min_messages=3Ddebug; SET lock_timeout=3D333; S=
ET log_lock_waits=3Don;<br>
Looks like it needs ShareUpdateExclusiveLock.<br>
<br>
&gt; Does statistics work on an array field like tags? (I can&#39;t find an=
y<br>
&gt; information)<br>
<br>
It think it&#39;d be data type agnostic.=C2=A0 And seems to work with array=
s.<br>
<br>
On Fri, Jan 10, 2020 at 02:30:27PM +0100, Marco Colli wrote:<br>
&gt; @Justin Pryzby I have tried this as you suggested:<br>
&gt; <br>
&gt; CREATE STATISTICS statistics_on_subscriptions_project_id_and_tags ON<b=
r>
&gt; project_id, tags FROM subscriptions;<br>
&gt; VACUUM ANALYZE subscriptions;<br>
&gt; <br>
&gt; Unfortunately nothing changes and Postgresql continues to use the wron=
g<br>
&gt; plan (maybe stats don&#39;t work well on array fields like tags??).<br=
>
<br>
It&#39;d help to see SELECT stxddependencies FROM pg_statistic_ext WHERE<br=
>
stxoid=3D&#39;subscriptions&#39;::regclass<br>
</blockquote></div>

--0000000000002aadbc059bca4ab7--