David Rowley <dgrowleyml@gmail.com> writes:
> On Sat, 4 Oct 2025 at 15:40, Marko Tiikkaja <marko@joh.to> wrote:
>> On Fri, Oct 3, 2025 at 16:31 Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> You haven't given us a lot to go on: no reproducible test case,
>> I've provided two. Both make the planner look bad.
You've provided *no* reproducible test case that makes the planner
look bad. The filled-in test case has two possibilities that are
both pretty cheap and the planner knows they are pretty cheap, so
it hardly matters which one it takes. You showed us a fragment
of a case where it chose a very expensive scan that it shouldn't
have, but no useful information about how to reproduce that.
> I expect what might be happening here is that ANALYZE runs when there
> are no state = 'WAIT_EVENT' rows and thinks using the index on that
> column is a better idea than the primary key index.
Marko's fragment at [1] shows
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
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?)
regards, tom lane
[1] https://www.postgresql.org/message-id/CAL9smLB1szUHLMfpN19FKiCHRCs4WvfjqXbxKaCUjmDzEtT%3Dng%40mail.gmail.com
