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
