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
