MIME-Version: 1.0
References: 
 <CAHyXU0xkTBW4p2N9QNLwye3O5SgzSGSMiPjFb7P8MyXCrEhhwg@mail.gmail.com>
 <574349.1773786268@sss.pgh.pa.us>
In-Reply-To: <574349.1773786268@sss.pgh.pa.us>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Tue, 17 Mar 2026 16:52:42 -0600
Message-ID: 
 <CAHyXU0yGKAvREkT47Oiw19xhicA2=peCSYc235s1hhna==BAVQ@mail.gmail.com>
Subject: Re: postgres chooses objectively wrong index
To: Tom Lane <tgl@sss.pgh.pa.us>, Alexey Ermakov <alexius.work@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000000fe717064d4032bc"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHyXU0yGKAvREkT47Oiw19xhicA2%3DpeCSYc235s1hhna%3D%3DBAVQ%40mail.gmail.com>
Precedence: bulk

--0000000000000fe717064d4032bc
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 17, 2026 at 4:16=E2=80=AFPM Alexey Ermakov <alexius.work@gmail.=
com>
wrote:

> On 2026-03-18 03:01, Merlin Moncure wrote:
>
> I've been maintaining an airflow style orchestrator in pl/pgsql, and it's
> revealed a performance issue I just can't solve.  There is a table, task,
> which may normally contain billions of rows, but only a tiny portion is
> interesting for specific reasons=E2=80=94a common pattern in task-type sy=
stems.
>
> ...
>
> I'm wondering if there are other tricks that might apply here, for
> example, multi column index statistics...curious if anyone has thoughts o=
n
> that.
>
> Any suggestions?
>
> merlin
>
> Hello. I think planner doesn't have information about distribution of
> *async.task_execution_state(task)* unless it's part of any full index. I
> would try to give that with extended statistics (postgresql 14+):
>
> create statistics (mcv) task_task_execution_state_stat on ((async.task_ex=
ecution_state(task))) from async.task;
> analyze async.task;
>
> If that won't help - please show distribution from pg_stats_ext view for
> extended statistic above.
>

This unfortunately fails, probably because the table type includes system
columns (despite not using them).

orchestrator_service_user@orchestrator=3D> create statistics  task_stats
(mcv) on (async.task_execution_state(task)) from async.task;
ERROR:  statistics creation on system columns is not supported

This would require some refactoring to fix.

On Tue, Mar 17, 2026 at 4:24=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Merlin Moncure <mmoncure@gmail.com> writes:
> > I've been maintaining an airflow style orchestrator in pl/pgsql, and it=
's
> > revealed a performance issue I just can't solve.  There is a table, tas=
k,
> > which may normally contain billions of rows, but only a tiny portion is
> > interesting for specific reasons=E2=80=94a common pattern in task-type =
systems.
> > ...
>
> > Usually, we get a plan that looks like this:
>
> >  Limit  (cost=3D0.38..39.74 rows=3D10 width=3D563) (actual time=3D0.054=
..0.054
> rows=3D0 loops=3D1)
> >    ->  Index Scan using task_concurrency_pool_priority_entered_idx on
> task  (cost=3D0.38..705.08 rows=3D179 width=3D563) (actual time=3D0.053..=
0.053
> rows=3D0 loops=3D1)
>
> > Sometimes, based on a certain data distribution, we get results like
> this:
>
> > Limit  (cost=3D25.75..25.78 rows=3D10 width=3D563) (actual time=3D8.909=
..8.911
> rows=3D0 loops=3D1)
> >   ->  Sort  (cost=3D25.75..26.20 rows=3D179 width=3D563) (actual
> time=3D8.908..8.909 rows=3D0 loops=3D1)
>
> I think the fundamental problem here is that the planner is estimating
> 179 matching rows when the true count is 0.  Getting that estimate
> down by, say, an order of magnitude would probably fix your issue.
> However, if the selectivity is already epsilon (are there really
> billions of rows?) it may be hard to get it down to a smaller epsilon.
> What statistics target are you using?
>

Potentially yes.  Maybe 40m in this particular database.

It's set to default, so it isn't very precise.  Is my earlier point
correct, though? No distribution of data should prefer that plan (barring
some low row count seqscan stuff)?  Let's say the row count was 179 rows,
it would make no difference in the disparity (in fact, it'd probably be
worse).

Simplified, the query is:
SELECT * FROM foo WHERE a=3D? AND b=3DK ORDER BY c, d LIMIT N;
CREATE INDEX ON foo(a,b,c) WHERE b=3DK;

why choose any other index? I was guessing mcv stats problem, but this can
be proved out without stats IMO.


> How often do tasks change state?
>

This is typical FIFO task processing system, pgmq, etc, with a huge number
of processed rows. and a small number of "processing" rows that get staged
and then complete.  Loads are highly transient; unprocessed rows may surge
up to millions before trending to zero.   This naturally puts a lot of
stress on statistics.   Tasks often resolve in seconds or minutes,
depending on depth of queue.

Could it be reasonable to partition the task table on state, rather than
> rely on an index?


I've thought about this; the basic issue is that the flow module extends
async.task with a BEFORE trigger.  This can be worked around but not
easily.   This is my drop back and punt option, but I'm curious if there is
an underlying solve here.

merlin

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr" class=3D"gmail_attr">On =
Tue, Mar 17, 2026 at 4:16=E2=80=AFPM Alexey Ermakov &lt;<a href=3D"mailto:a=
lexius.work@gmail.com">alexius.work@gmail.com</a>&gt; wrote:<br></div><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:=
1px solid rgb(204,204,204);padding-left:1ex"><u></u><div><div>On 2026-03-18=
 03:01, Merlin Moncure wrote:<br></div><blockquote type=3D"cite"><div dir=
=3D"ltr"><div dir=3D"ltr"><div>I&#39;ve been maintaining an airflow style o=
rchestrator in pl/pgsql, and it&#39;s revealed a performance issue I just c=
an&#39;t solve.=C2=A0 There is a table, task, which may normally=C2=A0conta=
in billions of rows, but only a tiny portion is interesting for specific re=
asons=E2=80=94a common pattern in task-type systems.</div><div><br></div>..=
.<br><div><br></div><div>I&#39;m wondering if there are other tricks that m=
ight apply here, for example, multi column index statistics...curious if an=
yone has thoughts on that.</div><div><br></div><div>Any suggestions?</div><=
div><br></div><div>merlin</div><div><br></div></div></div></blockquote><p>H=
ello. I think planner doesn&#39;t have information about distribution of=C2=
=A0<b>async.task_execution_state(task)</b>=C2=A0unless it&#39;s part of any=
 full index. I would try to give that with extended statistics (postgresql =
14+):<br></p><pre>create statistics (mcv) task_task_execution_state_stat on=
 ((async.task_execution_state(task))) from async.task;
analyze async.task;</pre><p>If that won&#39;t help - please show distributi=
on from pg_stats_ext view for extended statistic above.<br></p></div></bloc=
kquote><div><br></div><div>This unfortunately fails, probably because the t=
able type includes system columns (despite not using them).</div><div>=C2=
=A0</div><div>orchestrator_service_user@orchestrator=3D&gt; create statisti=
cs =C2=A0task_stats (mcv) on (async.task_execution_state(task)) from async.=
task;<br>ERROR: =C2=A0statistics creation on system columns is not supporte=
d</div><div><br></div><div>This would require some refactoring to fix.=C2=
=A0</div></div><br><div class=3D"gmail_quote gmail_quote_container"><div di=
r=3D"ltr" class=3D"gmail_attr">On Tue, Mar 17, 2026 at 4:24=E2=80=AFPM Tom =
Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wro=
te:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Merlin Moncu=
re &lt;<a href=3D"mailto:mmoncure@gmail.com" target=3D"_blank">mmoncure@gma=
il.com</a>&gt; writes:<br>
&gt; I&#39;ve been maintaining an airflow style orchestrator in pl/pgsql, a=
nd it&#39;s<br>
&gt; revealed a performance issue I just can&#39;t solve.=C2=A0 There is a =
table, task,<br>
&gt; which may normally contain billions of rows, but only a tiny portion i=
s<br>
&gt; interesting for specific reasons=E2=80=94a common pattern in task-type=
 systems.<br>
&gt; ...<br>
<br>
&gt; Usually, we get a plan that looks like this:<br>
<br>
&gt;=C2=A0 Limit=C2=A0 (cost=3D0.38..39.74 rows=3D10 width=3D563) (actual t=
ime=3D0.054..0.054 rows=3D0 loops=3D1)<br>
&gt;=C2=A0 =C2=A0 -&gt;=C2=A0 Index Scan using task_concurrency_pool_priori=
ty_entered_idx on task=C2=A0 (cost=3D0.38..705.08 rows=3D179 width=3D563) (=
actual time=3D0.053..0.053 rows=3D0 loops=3D1)<br>
<br>
&gt; Sometimes, based on a certain data distribution, we get results like t=
his:<br>
<br>
&gt; Limit=C2=A0 (cost=3D25.75..25.78 rows=3D10 width=3D563) (actual time=
=3D8.909..8.911 rows=3D0 loops=3D1)<br>
&gt;=C2=A0 =C2=A0-&gt;=C2=A0 Sort=C2=A0 (cost=3D25.75..26.20 rows=3D179 wid=
th=3D563) (actual time=3D8.908..8.909 rows=3D0 loops=3D1)<br>
<br>
I think the fundamental problem here is that the planner is estimating<br>
179 matching rows when the true count is 0.=C2=A0 Getting that estimate<br>
down by, say, an order of magnitude would probably fix your issue.<br>
However, if the selectivity is already epsilon (are there really<br>
billions of rows?) it may be hard to get it down to a smaller epsilon.<br>
What statistics target are you using?<br></blockquote><div><br></div><div>P=
otentially yes.=C2=A0 Maybe 40m in this particular database.</div><div><br>=
</div><div>It&#39;s set to default, so it isn&#39;t very precise.=C2=A0 Is =
my earlier point correct, though? No distribution of data should prefer tha=
t plan (barring some low row count seqscan stuff)?=C2=A0 Let&#39;s say the =
row count was 179 rows, it would make no difference in the disparity (in fa=
ct, it&#39;d probably be worse).</div><div><br></div><div>Simplified, the q=
uery is:</div><div>SELECT * FROM foo WHERE a=3D? AND b=3DK ORDER BY c, d LI=
MIT N;</div><div>CREATE INDEX ON foo(a,b,c) WHERE b=3DK;</div><div><br></di=
v><div>why choose any other index? I was guessing mcv stats problem, but th=
is can be proved out without stats IMO.</div><div>=C2=A0</div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex">
How often do tasks change state?=C2=A0=C2=A0<br></blockquote><div><br></div=
><div>This is typical FIFO task processing system, pgmq, etc, with=C2=A0a h=
uge number of processed rows. and a small number of &quot;processing&quot; =
rows that get staged and then complete.=C2=A0 Loads are highly transient; u=
nprocessed rows may surge up to millions before trending to zero.=C2=A0 =C2=
=A0This naturally puts a lot of stress on statistics.=C2=A0 =C2=A0Tasks oft=
en resolve in seconds or minutes, depending on depth of queue.=C2=A0</div><=
div><br></div><div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Could =
it be reasonable to partition the task table on state, rather than rely on =
an index?</blockquote></div><div><br></div><div>I&#39;ve thought about this=
; the basic issue is that the flow module extends async.task with a BEFORE =
trigger.=C2=A0 This can be worked around but not easily.=C2=A0 =C2=A0This i=
s my drop back and punt option, but I&#39;m curious if there is an underlyi=
ng solve here.</div><div><br></div><div>merlin</div><div>=C2=A0</div></div>=
</div>

--0000000000000fe717064d4032bc--