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

Hi,

Resurrecting since this has been causing us issues again, though this
time on a different index.  Given a schema similar to this:

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.020 rows=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

and subsequent executions also take ~100ms, instead of the ~0.05ms
through the PRIMARY KEY.

Just testing locally, if I add some data:

INSERT INTO orders (state) SELECT 'DONE' FROM generate_series(1, 65536 * 12);
ANALYZE orders;

I get:

EXPLAIN (ANALYZE, BUFFERS FALSE) 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.12..8.14
rows=1 width=4) (actual time=0.003..0.003 rows=0.00 loops=1)
   Filter: (order_id = 1)
   Index Searches: 1
 Planning Time: 0.278 ms
 Execution Time: 0.035 ms
(5 rows)

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.


.m