Re: [BUGS] BUG #11500: PRIMARY KEY index not being used - Mailing list pgsql-bugs

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