Re: postgres chooses objectively wrong index

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