Message-ID: <2a32c870-3f69-4b09-854d-ffea03b1e537@gmail.com>
Date: Thu, 19 Mar 2026 08:09:33 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: postgres chooses objectively wrong index
To: Merlin Moncure <mmoncure@gmail.com>,
 Alexey Ermakov <alexius.work@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@lists.postgresql.org
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>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAHyXU0zhdKYDvt_iON5kVSa4AXa=p_vLP1JMs3u0FFA29rP-aQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/2a32c870-3f69-4b09-854d-ffea03b1e537%40gmail.com>
Precedence: bulk

On 18/3/26 19:38, Merlin Moncure wrote:
> On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov <alexius.work@gmail.com 
> 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