Marko Tiikkaja <marko@joh.to> writes: > ... 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.
You haven't given us a lot to go on: no reproducible test case,
I've provided two. Both make the planner look bad.
no clear description of what triggers the issue,
..
not even the complete EXPLAIN output for the problem query. But it's hard to believe that the planner would estimate a probe on a unique index as costing more than 94812.85 units, which is what this fragment seems to suggest.
That was an after-the-fact demonstration of how expensive gambling on the index can be.
If you look at eqsel() you will observe that the presence of a unique index overrides any information from statistics, so there is no "casino" behavior here IMO: it should realize that "order_id = $1" selects a single row no matter what
Then explain what's going on in the test case. I'll be at the slot machine with the planner.
