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

On Fri, Oct 3, 2025 at 9:53 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> 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.

I didn't want to include too much in the small repro that I had, but
as I alluded to in the comment, the index has real data in it.
Deduplication really plays no real part here. But yes, the index is
still physically smaller because it indexes a small subset of the
table.

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

What I'm trying to say is that I don't think there is any data you
could put in the stats tables to justify gambling on this index. But
feel free to try out my example yourself. This is a bit more like
what the production data looks like:

INSERT INTO orders (state) SELECT CASE WHEN random() <= 0.8 THEN
order_state 'WAIT_EVENT' ELSE order_state 'DONE' END FROM
generate_series(1, 65536 * 12);
UPDATE orders SET state = 'DONE' WHERE state = 'WAIT_EVENT';
ANALYZE orders;

=# EXPLAIN ANALYZE SELECT 1 FROM orders WHERE order_id = 1 AND state =
'WAIT_EVENT';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Index Scan using orders_wait_event_idx on orders (cost=0.38..8.39
rows=1 width=4) (actual time=54.650..54.651 rows=0.00 loops=1)
Filter: (order_id = 1)
Index Searches: 1
Buffers: shared hit=5239
Planning:
Buffers: shared hit=30
Planning Time: 1.221 ms
Execution Time: 54.682 ms
(8 rows)

=# EXPLAIN ANALYZE SELECT 1 FROM orders WHERE order_id = 1 AND state =
'WAIT_EVENT';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Scan using orders_wait_event_idx on orders (cost=0.38..8.39
rows=1 width=4) (actual time=0.459..0.459 rows=0.00 loops=1)
Filter: (order_id = 1)
Index Searches: 1
Buffers: shared hit=495
Planning Time: 0.091 ms
Execution Time: 0.476 ms
(6 rows)

=# EXPLAIN ANALYZE SELECT 1 FROM orders WHERE order_id = 1 AND
state::text = 'WAIT_EVENT';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using orders_pkey on orders (cost=0.42..8.45 rows=1
width=4) (actual time=0.039..0.039 rows=0.00 loops=1)
Index Cond: (order_id = 1)
Filter: ((state)::text = 'WAIT_EVENT'::text)
Rows Removed by Filter: 1
Index Searches: 1
Buffers: shared hit=10
Planning:
Buffers: shared hit=99
Planning Time: 2.864 ms
Execution Time: 0.077 ms
(10 rows)

.m