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

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

> Marko Tiikkaja <marko@joh.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