public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: Merlin Moncure <[email protected]>
To: Alexey Ermakov <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: postgres chooses objectively wrong index
Date: Thu, 19 Mar 2026 08:09:33 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHyXU0zhdKYDvt_iON5kVSa4AXa=p_vLP1JMs3u0FFA29rP-aQ@mail.gmail.com>
References: <CAHyXU0xkTBW4p2N9QNLwye3O5SgzSGSMiPjFb7P8MyXCrEhhwg@mail.gmail.com>
<[email protected]>
<CAHyXU0yGKAvREkT47Oiw19xhicA2=peCSYc235s1hhna==BAVQ@mail.gmail.com>
<[email protected]>
<CAHyXU0zhdKYDvt_iON5kVSa4AXa=p_vLP1JMs3u0FFA29rP-aQ@mail.gmail.com>
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.
I haven’t tried to redesign this part myself. If it were simple, someone
likely would have fixed it by now. Maybe Tom has some ideas about it.
--
regards, Andrei Lepikhov,
pgEdge
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: <[email protected]>
* 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