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>
In-Reply-To: <2a32c870-3f69-4b09-854d-ffea03b1e537@gmail.com>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Mon, 23 Mar 2026 15:58:40 -0600
Message-ID: 
 <CAHyXU0yAibBKPaJ-A59inJa+iv1Q1bOfMp+VPZUyNjvquBNVSQ@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="000000000000e1564a064db8238b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHyXU0yAibBKPaJ-A59inJa%2Biv1Q1bOfMp%2BVPZUyNjvquBNVSQ%40mail.gmail.com>
Precedence: bulk

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

On Thu, Mar 19, 2026 at 1:09=E2=80=AFAM Andrei Lepikhov <lepihov@gmail.com>=
 wrote:

> On 18/3/26 19:38, Merlin Moncure wrote:
> > On Tue, Mar 17, 2026 at 11:27=E2=80=AFPM Alexey Ermakov <alexius.work@g=
mail.com
> > I think the planner is not giving enough bonus for an exact match versu=
s
> > an inexact match on partial index mathcing,  (A=3DA should be better th=
an
> > A IN(A,B,C)), and it's unclear why the planner things bitmap heap + sor=
t
> > is outperforming a raw read off the index base on marginal estimated ro=
w
> > counts.  Lowering random_page_cost definitely biases the plan I like,
> > but it skews both estimates.
>
> One ongoing shortcoming is that cardinality estimation takes place early
> in the optimisation process and uses all filter conditions. This can be
> frustrating because a partial index covers just part of the table and
> could give the optimiser better statistics. If we ignored the index
> condition, we might get a more accurate estimate.
>

Thanks.  I understand the challenge with estimation around partial
indexes.  Something deeper seems to be at play here.

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 plan
cost certainly did.  It's also worth noting ANALYZE doesn't help, only
REINDEX does.

I keep coming back to this: the bitmap scan noted above makes no sense. I'm
trying to figure out what is steering the planner in that direction and
eliminate it.

This problem reliably reproduces about once a month (taking down
production). I'll wait for it to recur and look at it with fresh eyes.

merlin

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Mar 19, 2026 at 1:09=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">On 18/3/26 19:38, Merlin Moncure=
 wrote:<br>
&gt; On Tue, Mar 17, 2026 at 11:27=E2=80=AFPM Alexey Ermakov &lt;<a href=3D=
"mailto:alexius.work@gmail.com" target=3D"_blank">alexius.work@gmail.com</a=
> <br>
&gt; I think the planner is not giving enough bonus for an exact match vers=
us <br>
&gt; an inexact match on partial index mathcing,=C2=A0 (A=3DA should be bet=
ter than <br>
&gt; A IN(A,B,C)), and it&#39;s unclear why the planner things bitmap heap=
=C2=A0+ sort <br>
&gt; is outperforming a raw read off the index base on marginal estimated r=
ow <br>
&gt; counts.=C2=A0 Lowering random_page_cost definitely biases the plan I l=
ike, <br>
&gt; but=C2=A0it skews both estimates.<br>
<br>
One ongoing shortcoming is that cardinality estimation takes place early <b=
r>
in the optimisation process and uses all filter conditions. This can be <br=
>
frustrating because a partial index covers just part of the table and <br>
could give the optimiser better statistics. If we ignored the index <br>
condition, we might get a more accurate estimate.<br></blockquote><div><br>=
</div><div>Thanks.=C2=A0 I understand the challenge with estimation around =
partial indexes.=C2=A0 Something deeper seems to be at play here.</div><div=
><br></div><div>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 although the net plan cost certainly did.=C2=A0 It&#39;s also worth=
 noting ANALYZE doesn&#39;t help, only REINDEX does.=C2=A0</div><div><br></=
div><div>I keep coming back to this: the bitmap scan noted above makes=C2=
=A0no sense. I&#39;m trying to figure out what is steering the planner in t=
hat direction and eliminate it.</div><div><br></div><div>This problem relia=
bly reproduces about once a month (taking down production). I&#39;ll wait f=
or it to recur and look at it with fresh eyes.</div><div><br></div><div>mer=
lin</div></div></div>

--000000000000e1564a064db8238b--