public inbox for [email protected]  

help / color / mirror / Atom feed

From: Merlin Moncure <[email protected]>
To: Andrei Lepikhov <[email protected]>
Cc: Alexey Ermakov <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: postgres chooses objectively wrong index
Date: Mon, 23 Mar 2026 15:58:40 -0600
Message-ID: <CAHyXU0yAibBKPaJ-A59inJa+iv1Q1bOfMp+VPZUyNjvquBNVSQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAHyXU0xkTBW4p2N9QNLwye3O5SgzSGSMiPjFb7P8MyXCrEhhwg@mail.gmail.com>
	<[email protected]>
	<CAHyXU0yGKAvREkT47Oiw19xhicA2=peCSYc235s1hhna==BAVQ@mail.gmail.com>
	<[email protected]>
	<CAHyXU0zhdKYDvt_iON5kVSa4AXa=p_vLP1JMs3u0FFA29rP-aQ@mail.gmail.com>
	<[email protected]>

On Thu, Mar 19, 2026 at 1:09 AM Andrei Lepikhov <[email protected]> wrote:

> On 18/3/26 19:38, Merlin Moncure wrote:
> > On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov <[email protected]
> > I think the planner is not giving enough bonus for an exact match versus
> > an inexact match on partial index mathcing,  (A=A 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.
>
> 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

view thread (12+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: postgres chooses objectively wrong index
  In-Reply-To: <CAHyXU0yAibBKPaJ-A59inJa+iv1Q1bOfMp+VPZUyNjvquBNVSQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox