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>
 <CAHyXU0zhdKYDvt_iON5kVSa4AXa=p_vLP1JMs3u0FFA29rP-aQ@mail.gmail.com>
 <2a32c870-3f69-4b09-854d-ffea03b1e537@gmail.com>
 <CAHyXU0yAibBKPaJ-A59inJa+iv1Q1bOfMp+VPZUyNjvquBNVSQ@mail.gmail.com>
 <217AB71B-F368-4DA1-A3B5-ABED94E1BD0A@gmail.com>
In-Reply-To: <217AB71B-F368-4DA1-A3B5-ABED94E1BD0A@gmail.com>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Wed, 25 Mar 2026 10:53:42 -0600
Message-ID: 
 <CAHyXU0yu1EBgsMWr=7gfNjj-eo8oFm2TtxY6fzcbSpS5TSPijA@mail.gmail.com>
Subject: Re: postgres chooses objectively wrong index
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: Alexey Ermakov <alexius.work@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000e52deb064ddc1c43"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHyXU0yu1EBgsMWr%3D7gfNjj-eo8oFm2TtxY6fzcbSpS5TSPijA%40mail.gmail.com>
Precedence: bulk

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

On Tue, Mar 24, 2026 at 3:21=E2=80=AFAM Andrei Lepikhov <lepihov@gmail.com>=
 wrote:

>
>
> > On 23 Mar 2026, at 22:58, Merlin Moncure <mmoncure@gmail.com> wrote:
> > On Thu, Mar 19, 2026 at 1:09=E2=80=AFAM Andrei Lepikhov <lepihov@gmail.=
com>
> wrote:
> > Poking around more, I see that the bad plans are related to bloat.   A
> simple REINDEX of one of the indexes made the problem disappear; however,
> what's odd is that the estimates didn't really change although the net pl=
an
> cost certainly did.  It's also worth noting ANALYZE doesn't help, only
> REINDEX does.
>
> We already have plan-freezing and plan-hinting extensions.


A couple of thoughts here. Re: plan-hinting, I'm a multi-decade disciple of
the philosophy: "You are not smarter than the planner," "We need
performance feedback on plans to improve planning," and "Do not exclude
yourself from potential new types of plans," as offered many years ago by a
significant poster on this thread.  So, if I were to use it, it would be
more for exploration and debugging. As things stand, I get by mostly by
manipulating queries and the very occasional GUC (read: disable nestloop).

Freezing the plan, however, seems to be essential functionality.  My loose
observation is that postgres plannner stability has generally declined over
time, causing many production outages.  Many reasons exist for this,
including my personal tendency to design around optimal outcomes; this
thread is a pretty good example of that.

The various rules for preparing and managing plans are generally good but
require more precise control in specific situations. Plan management ought
to be in core, perhaps even at the syntax level.  I say this because
extensions like these are generally written in C and not offered by cloud
providers, which highly limits their audience.  This is why pgasync
<https://github.com/merlinm/pgasync/tree/main> was written; it's a souped
up pgbackground / pgmq written entirely at the SQL level, requiring only
dblink which is now generally offered.


> If I understand you correctly, it makes sense to invent a
> statistics-freezing module right now. I think such a module will be quite
> simple - is it a good crutch for you?
> Also, we have some stuff already to work out your case someday:
>

Interesting question.  I suppose a simple plan lock should be enough, but
I'm not sure about that.   We might be slightly off here though, my point
is that the chosen plan seems indefensible even with the supplied
statistics?  Specifically, pre-REINDEX, postgres thinks that this plan:

        ->  Bitmap Heap Scan on task  (cost=3D9.10..21.89 rows=3D179 width=
=3D563)
(actual time=3D8.902..8.903 rows=3D0 loops=3D1)
              Recheck Cond: ((async.task_execution_state(task.*) =3D ANY
('{READY,RUNNING,YIELDED}'::async.task_execution_state_t[])) AND
(concurrency_pool =3D 'xyz'::text) AND (async.task_execution_state(task.*) =
=3D
'READY'::async.task_execution_state_t))
              ->  BitmapAnd  (cost=3D9.10..9.10 rows=3D3 width=3D0) (actual
time=3D8.883..8.883 rows=3D0 loops=3D1)
                    ->  Bitmap Index Scan on task_task_id_idx
 (cost=3D0.00..4.38 rows=3D575191 width=3D0) (actual time=3D8.828..8.828 ro=
ws=3D16
loops=3D1)
                    ->  Bitmap Index Scan on
task_concurrency_pool_priority_entered_idx  (cost=3D0.00..4.38 rows=3D179
width=3D0) (actual time=3D0.053..0.053 rows=3D0 loops=3D1)
                          Index Cond: (concurrency_pool =3D 'xyz'::text)

is better than this plan:

 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 r=
ows=3D0
loops=3D1)
         Index Cond: (concurrency_pool =3D 'xyz'::text)

which is something I just don't understand.  After reindexing
task_task_id_idx, things cleared up, and both plans ran well, which I also
don't understand.  ISTM a plan lock ought to keep things buttoned up though=
.


> 1. Postgres already scans indexes during planning to improve estimations
> of inequality clauses (get_actual_variable_range). Here may be a way to
> estimate the bloat effect. Not sure how to do it, but allowing index AM t=
o
> read the page number of the returned tuple, you might, in principle, dete=
ct
> anomalies in the index.
> 2. We are quite close to vacuum statistics and detailed index statistics.
> This is also a way to estimate issues of stale statistics/bloated indexes
> and decide on the scan type.
>
> So, keep the community posted and provide more real-life examples to buil=
d
> up a proper solution.
>

very much appreciate your insight.

merlin

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Mar 24, 2026 at 3:21=E2=80=AFAM A=
ndrei Lepikhov &lt;<a href=3D"mailto:lepihov@gmail.com">lepihov@gmail.com</=
a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><br>
<br>
&gt; On 23 Mar 2026, at 22:58, Merlin Moncure &lt;<a href=3D"mailto:mmoncur=
e@gmail.com" target=3D"_blank">mmoncure@gmail.com</a>&gt; wrote:<br>
&gt; On Thu, Mar 19, 2026 at 1:09=E2=80=AFAM Andrei Lepikhov &lt;<a href=3D=
"mailto:lepihov@gmail.com" target=3D"_blank">lepihov@gmail.com</a>&gt; wrot=
e:<br>
&gt; Poking around more, I see that the bad plans are related to bloat.=C2=
=A0 =C2=A0A simple REINDEX of one of the indexes made the problem disappear=
; however, what&#39;s odd is that the estimates didn&#39;t really change al=
though the net plan cost certainly did.=C2=A0 It&#39;s also worth noting AN=
ALYZE doesn&#39;t help, only REINDEX does. <br>
<br>
We already have plan-freezing and plan-hinting extensions. </blockquote><di=
v><br></div><div>A couple of thoughts here. Re: plan-hinting, I&#39;m a mul=
ti-decade disciple of the philosophy: &quot;You are not smarter than the pl=
anner,&quot; &quot;We need performance feedback on plans to improve plannin=
g,&quot; and &quot;Do not exclude yourself from potential new types of plan=
s,&quot; as offered many years ago by a significant poster on this thread.=
=C2=A0 So, if I were to use it, it would be more for exploration and debugg=
ing. As things stand, I get by mostly by manipulating queries and the very =
occasional GUC (read: disable nestloop).</div><div><br></div><div>Freezing =
the plan, however, seems to be essential functionality.=C2=A0 My loose obse=
rvation is that postgres plannner stability has generally declined over tim=
e, causing many production outages.=C2=A0 Many reasons exist for this, incl=
uding my personal tendency to design around optimal outcomes; this thread i=
s a pretty good example of that.=C2=A0=C2=A0</div><div><br></div><div>The v=
arious rules for preparing and managing plans are generally good but requir=
e more precise control in specific situations. Plan management ought to be =
in core, perhaps even at the syntax level.=C2=A0 I say this because extensi=
ons like these are generally written in C and not offered by cloud provider=
s, which highly limits their audience.=C2=A0 This is why=C2=A0<a href=3D"ht=
tps://github.com/merlinm/pgasync/tree/main">pgasync</a> was written; it&#39=
;s a souped up pgbackground / pgmq written entirely at the SQL level, requi=
ring only dblink which is now generally offered.</div><div>=C2=A0</div><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex">If I understand you correctly=
, it makes sense to invent a statistics-freezing module right now. I think =
such a module will be quite simple - is it a good crutch for you?<br>
Also, we have some stuff already to work out your case someday:<br></blockq=
uote><div><br></div><div>Interesting question.=C2=A0 I suppose a simple pla=
n lock should be enough, but I&#39;m not sure about that.=C2=A0 =C2=A0We mi=
ght be slightly off here though, my point is that the chosen plan seems ind=
efensible even with the supplied statistics?=C2=A0 Specifically, pre-REINDE=
X, postgres thinks that this plan:</div><div><br></div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 -&gt; =C2=A0Bitmap Heap Scan on task =C2=A0(cost=3D9.10..21.8=
9 rows=3D179 width=3D563) (actual time=3D8.902..8.903 rows=3D0 loops=3D1)<b=
r>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Recheck Cond: ((async.ta=
sk_execution_state(task.*) =3D ANY (&#39;{READY,RUNNING,YIELDED}&#39;::asyn=
c.task_execution_state_t[])) AND (concurrency_pool =3D &#39;xyz&#39;::text)=
 AND (async.task_execution_state(task.*) =3D &#39;READY&#39;::async.task_ex=
ecution_state_t))<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -&gt;=
 =C2=A0BitmapAnd =C2=A0(cost=3D9.10..9.10 rows=3D3 width=3D0) (actual time=
=3D8.883..8.883 rows=3D0 loops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -&gt; =C2=A0Bitmap Index Scan on task_ta=
sk_id_idx =C2=A0(cost=3D0.00..4.38 rows=3D575191 width=3D0) (actual time=3D=
8.828..8.828 rows=3D16 loops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -&gt; =C2=A0Bitmap Index Scan on task_concu=
rrency_pool_priority_entered_idx =C2=A0(cost=3D0.00..4.38 rows=3D179 width=
=3D0) (actual time=3D0.053..0.053 rows=3D0 loops=3D1)<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 I=
ndex Cond: (concurrency_pool =3D &#39;xyz&#39;::text)</div><div><br></div><=
div>is better than this plan:</div><div><br></div><div>=C2=A0Limit =C2=A0(c=
ost=3D0.38..39.74 rows=3D10 width=3D563) (actual time=3D0.054..0.054 rows=
=3D0 loops=3D1)<br>=C2=A0 =C2=A0-&gt; =C2=A0Index Scan using task_concurren=
cy_pool_priority_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>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (concurrency_pool =3D &#39;xyz&#39;::te=
xt)</div><div><br></div><div>which is something I just don&#39;t understand=
.=C2=A0 After reindexing task_task_id_idx, things cleared up, and both plan=
s ran well, which I also don&#39;t understand.=C2=A0 ISTM a plan lock ought=
 to keep things buttoned up though.</div><div>=C2=A0</div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">
1. Postgres already scans indexes during planning to improve estimations of=
 inequality clauses (get_actual_variable_range). Here may be a way to estim=
ate the bloat effect. Not sure how to do it, but allowing index AM to read =
the page number of the returned tuple, you might, in principle, detect anom=
alies in the index.<br>
2. We are quite close to vacuum statistics and detailed index statistics. T=
his is also a way to estimate issues of stale statistics/bloated indexes an=
d decide on the scan type.<br>
<br>
So, keep the community posted and provide more real-life examples to build =
up a proper solution.<br></blockquote><div><br></div><div>very much appreci=
ate your insight.</div><div><br></div><div>merlin=C2=A0</div></div></div>

--000000000000e52deb064ddc1c43--