Re: [BUGS] BUG #11500: PRIMARY KEY index not being used

On Fri, Oct 3, 2025 at 16:31 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Marko Tiikkaja <marko(at)joh(dot)to> writes:
> > ... we have a frequent query like this:
>
> > SELECT ..
> > FROM orders
> > WHERE
> > order_id = $1 AND
> > state = 'WAIT_EVENT';
>
> > which almost always uses the primary key. But sometimes, perhaps
> > after an autovacuum or something, something changes and postgres
> > decides to start serving that query through the orders_wait_event_idx
> > index.
>
> You haven't given us a lot to go on: no reproducible test case,

I've provided two. Both make the planner look bad.

no clear description of what triggers the issue,

..

not even the complete
> EXPLAIN output for the problem query. But it's hard to believe that
> the planner would estimate a probe on a unique index as costing more
> than 94812.85 units, which is what this fragment seems to suggest.

That was an after-the-fact demonstration of how expensive gambling on the
index can be.

> If you look at eqsel() you will observe that the presence of a
> unique index overrides any information from statistics, so there
> is no "casino" behavior here IMO: it should realize that
> "order_id = $1" selects a single row no matter what

Then explain what's going on in the test case. I'll be at the slot machine
with the planner.

.m