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

On Sun, Oct 5, 2025 at 3:34 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> Marko did mention:
>
> On Sat, 4 Oct 2025 at 15:40, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> > That was an after-the-fact demonstration of how expensive gambling on the index can be.
>
> I assumed since the EXPLAIN didn't match the query that the EXPLAIN
> output was fabricated afterwards from the server without the
> "order_id" qual to try to illustrate the index that was used and the
> row numbers that index had to visit. It would be good to get
> confirmation of that from Marko.

That's exactly it.

I can't believe I actually have to do this, but run:

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'
);
CREATE INDEX orders_wait_event_idx ON orders ((1)) WHERE state = 'WAIT_EVENT';
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, BUFFERS) SELECT 1 FROM orders WHERE order_id = 1 AND
state = 'WAIT_EVENT';

and I get:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using orders_wait_event_idx on orders (cost=0.38..1.99
rows=1 width=4) (actual time=22.482..22.482 rows=0 loops=1)
Filter: (order_id = 1)
Buffers: shared hit=4746
Planning:
Buffers: shared hit=18
Planning Time: 0.085 ms
Execution Time: 22.488 ms
(7 rows)

.m