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

On Fri, 2025-10-03 at 09:23 +0300, Marko Tiikkaja wrote:
> CREATE TYPE order_state AS ENUM ('INITIAL', 'WAIT_EVENT', 'DONE');
> CREATE TABLE orders(
>     order_id bigserial PRIMARY KEY,
>     state order_state NOT NULL DEFAULT 'INITIAL'
> );
> -- there are other indexed columns here, but nothing really reads
> through the entire index
> CREATE INDEX orders_wait_event_idx ON orders ((1)) WHERE state = 'WAIT_EVENT';
>
> where 80% of rows go through WAIT_EVENT before DONE.  Then 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.  Now those queries need to first suffer through this:
>
>  Index Scan using orders_wait_event_idx on orders (cost=0.54..94812.85 rows=85043 width=1223) (actual
time=0.166..7199.020rows=84535 loops=1) 
>    Buffers: shared hit=15676 read=91962 dirtied=1988
>  Planning:
>    Buffers: shared hit=807 read=11 dirtied=1
>  Planning Time: 4.634 ms
>  Execution Time: 7204.117 ms
>
> which just seems like an insane gamble to take compared to reading the
> primary key index.  If you're right, you save fractions of a
> millisecond, but if you're wrong, it could be the ten seconds like
> we've been seeing in production.
>
> We've been seeing this on 16.9 in prod, and with the code here I see
> the planner hitting the casino on latest master as well.
>
> Let me know what you think.

Did you check if the optimizer statistics are up to date?

The index you created is only useful if only a very small percentage of
the rows in the table match the WHERE condition.  It may be that the
optimizer chooses the index by mistake: deduplication of identical index
keys will render the index rather small, and PostgreSQL prefers the smaller
index if it thinks that both indexes will do the task equally well.

But I think that the root of the problem is that you created an index that
you don't want to get used.  I understand that you want it for some other
statement, but is there really nothing better to index than the constant 1?

Yours,
Laurenz Albe