MIME-Version: 1.0
References: 
 <CAHyXU0xkTBW4p2N9QNLwye3O5SgzSGSMiPjFb7P8MyXCrEhhwg@mail.gmail.com>
 <574349.1773786268@sss.pgh.pa.us>
 <CAHyXU0yGKAvREkT47Oiw19xhicA2=peCSYc235s1hhna==BAVQ@mail.gmail.com>
 <381e7d47-8021-458d-9a64-0cdb80b004f0@gmail.com>
In-Reply-To: <381e7d47-8021-458d-9a64-0cdb80b004f0@gmail.com>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Wed, 18 Mar 2026 12:38:07 -0600
Message-ID: 
 <CAHyXU0zhdKYDvt_iON5kVSa4AXa=p_vLP1JMs3u0FFA29rP-aQ@mail.gmail.com>
Subject: Re: postgres chooses objectively wrong index
To: Alexey Ermakov <alexius.work@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000073f063064d50c11e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHyXU0zhdKYDvt_iON5kVSa4AXa%3Dp_vLP1JMs3u0FFA29rP-aQ%40mail.gmail.com>
Precedence: bulk

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

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

> On 2026-03-18 04:52, Merlin Moncure wrote:
>
> 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_e=
xecution_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.
>
> Interesting... In that case functional index should help (as it also make=
s
> statistic for the planner):
>
> create index concurrently on task_task_execution_state_idx async.task usi=
ng btree ((async.task_execution_state(task)));
>
> analyze async.task;
>
>
This can't help performance, as the index...
CREATE INDEX ON async.task(concurrency_pool, priority, entered)
WHERE async.task_execution_state(task) =3D 'READY';

...is very precisely configured to provide exactly what's needed; I need
tasks for that exact pool in that exact order if and only if ready.  The
partial predicate is designed to keep the index nice and small since only a
small portion of tasks would be eligible at any specific time.

@Tom Lane <tgl@sss.pgh.pa.us> I'm pretty sure you were following me, but my
abstraction earlier was a bit off;

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;


Should have been:
SELECT * FROM foo WHERE a=3D? AND d=3DK ORDER BY b, c LIMIT N;
CREATE INDEX ON foo(a,b,c) WHERE d=3DK;

Point being, the index match in on (=3D, order, order). If a contains any
less than 100% of the total records, and N is small relative to table size,
this ought to be the ideal index for just about any case, the exact match
on partial qual is just gravy.

I think the planner is not giving enough bonus for an exact match versus an
inexact match on partial index mathcing,  (A=3DA should be better than A
IN(A,B,C)), and it's unclear why the planner things bitmap heap + sort is
outperforming a raw read off the index base on marginal estimated row
counts.  Lowering random_page_cost definitely biases the plan I like,
but it skews both estimates.


@Alexey Ermakov <alexius.work@gmail.com>
If you're interested in more context, see:

pgasync <https://github.com/merlinm/pgasync>
pgflow <https://github.com/merlinm/pgflow>
graph example <https://imgur.com/a/LZNpTC1>

merlin

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">On Tue, Mar 17, 2026 at =
11:27=E2=80=AFPM Alexey Ermakov &lt;<a href=3D"mailto:alexius.work@gmail.co=
m" target=3D"_blank">alexius.work@gmail.com</a>&gt; wrote:</div><div dir=3D=
"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex"><u></u>

 =20
   =20
 =20
  <div>
    <div>On 2026-03-18 04:52, Merlin Moncure
      wrote:<br>
    </div>
    <blockquote type=3D"cite">
     =20
      <div dir=3D"ltr">
        <div dir=3D"ltr">
          <blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div dir=
=3D"ltr"><div dir=3D"ltr"><div>Hello. I think planner doesn&#39;t have info=
rmation 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+):</div></div></div=
>
              <pre>create statistics (mcv) task_task_execution_state_stat o=
n ((async.task_execution_state(task))) from async.task;
analyze async.task;</pre>
              <p>If that won&#39;t help - please show distribution from
                pg_stats_ext view for extended statistic above.<br>
              </p>
            </div>
          </blockquote>
          <div><br>
          </div>
          <div>This unfortunately fails, probably because the table type
            includes system columns (despite not using them).</div>
          <div>=C2=A0</div>
          <div>orchestrator_service_user@orchestrator=3D&gt; create
            statistics =C2=A0task_stats (mcv) on
            (async.task_execution_state(task)) from async.task;<br>
            ERROR: =C2=A0statistics creation on system columns is not
            supported</div>
          <div><br>
          </div>
          <div>This would require some refactoring to fix. <br>
          </div>
        </div>
      </div>
    </blockquote>
    <p>Interesting... In that case functional index should help (as it
      also makes statistic for the planner):<br>
    </p>
    <pre>create index concurrently on task_task_execution_state_idx async.t=
ask using btree ((async.task_execution_state(task)));
</pre>
    <pre>analyze async.task;</pre></div></blockquote><div><br></div><div>Th=
is can&#39;t help performance, as the index...</div><div>CREATE INDEX ON as=
ync.task(concurrency_pool, priority, entered)<br>WHERE async.task_execution=
_state(task) =3D &#39;READY&#39;;</div><div><br></div><div>...is very preci=
sely configured to provide exactly what&#39;s needed; I need tasks for that=
 exact pool in that exact order if and only if ready.=C2=A0 The partial pre=
dicate is designed to keep the index nice and small since only a small port=
ion of tasks would be eligible at any specific time.</div><div><br></div><d=
iv><a class=3D"gmail_plusreply" id=3D"m_7734949046248117624plusReplyChip-0"=
 href=3D"mailto:tgl@sss.pgh.pa.us" target=3D"_blank">@Tom Lane</a>=C2=A0I&#=
39;m pretty sure you were following me, but my abstraction earlier was a bi=
t off;<br></div><div><br></div><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">Simplified, the query is:<br>SELECT * FROM foo WHERE a=3D? AND b=
=3DK ORDER BY c, d LIMIT N;<br>CREATE INDEX ON foo(a,b,c) WHERE b=3DK;</blo=
ckquote></div><div><br></div><div><div>Should have been:</div><div>SELECT *=
 FROM foo WHERE a=3D? AND d=3DK ORDER BY b, c LIMIT N;</div><div>CREATE IND=
EX ON foo(a,b,c) WHERE d=3DK;</div></div><div><br></div><div>Point being, t=
he index match in on (=3D, order, order). If a contains any less than 100% =
of the total records, and N is small relative to table size, this ought to =
be the ideal index for just about any case, the exact match on partial qual=
 is just gravy.</div><div><br></div><div>I think the planner is not giving =
enough bonus for an exact match versus an inexact match on partial index ma=
thcing,=C2=A0 (A=3DA should be better than A IN(A,B,C)), and it&#39;s uncle=
ar why the planner things bitmap heap=C2=A0+ sort is outperforming a raw re=
ad off the index base on marginal estimated row counts.=C2=A0 Lowering rand=
om_page_cost definitely biases the plan I like, but=C2=A0it skews both esti=
mates.</div><div><br></div><div><br></div><div><a class=3D"gmail_plusreply"=
 id=3D"plusReplyChip-0" href=3D"mailto:alexius.work@gmail.com" tabindex=3D"=
-1">@Alexey Ermakov</a>=C2=A0<br></div><div>If you&#39;re interested in mor=
e context, see:</div><div><br></div><div><a href=3D"https://github.com/merl=
inm/pgasync">pgasync</a><br></div><div><a href=3D"https://github.com/merlin=
m/pgflow">pgflow</a><br></div><div><a href=3D"https://imgur.com/a/LZNpTC1">=
graph example</a><br></div><div><br></div><div>merlin</div><div><br></div><=
div>=C2=A0</div></div></div>
</div>
</div>

--00000000000073f063064d50c11e--