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

On Sun, 5 Oct 2025 at 06:17, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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)

> That's a pretty darn accurate rowcount estimate, so "out of date
> stats" doesn't seem to be the problem.  I don't think it could
> possibly have believed that scanning the pkey index would fetch that
> many rows, or cost that much.  So why didn't it pick that index?
> No way to tell from this amount of info, but I'm suspecting a
> moving part that we don't know about.
>
> (Hmmm ... why is there not a "Filter: (order_id = $1)" line here?)

Marko did mention:

On Sat, 4 Oct 2025 at 15:40, Marko Tiikkaja <marko@joh.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. Otherwise, indeed, something very
strange is going on if the planner produced the above plan for the
given query.

David