From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marko Tiikkaja <marko@joh.to>
cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #11500: PRIMARY KEY index not being used
In-reply-to: 
 <CAL9smLB1szUHLMfpN19FKiCHRCs4WvfjqXbxKaCUjmDzEtT=ng@mail.gmail.com>
References: <20140926080211.7639.68358@wrigleys.postgresql.org>
 <8043.1411753479@sss.pgh.pa.us>
 <CAL9smLB1szUHLMfpN19FKiCHRCs4WvfjqXbxKaCUjmDzEtT=ng@mail.gmail.com>
Comments: In-reply-to Marko Tiikkaja <marko@joh.to>
	message dated "Fri, 03 Oct 2025 09:23:20 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1314566.1759501872.1@sss.pgh.pa.us>
Date: Fri, 03 Oct 2025 10:31:12 -0400
Message-ID: <1314567.1759501872@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1314567.1759501872%40sss.pgh.pa.us>
Precedence: bulk

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,
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.
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.

I'm wondering if it didn't use that index because it *couldn't* use
that index, for who-knows-what reason.  There are operations that
will leave an index marked as invalid and hence unsafe to query on.
Anyway, -EINSUFFICIENTINFORMATION.

			regards, tom lane