From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Marko Tiikkaja <marko@joh.to>
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #11500: PRIMARY KEY index not being used
Date: Sat, 04 Oct 2025 13:17:37 -0400
Message-ID: <1525405.1759598257@sss.pgh.pa.us> (raw)
In-Reply-To: <CAApHDvp=+EN2o=8fD47nyKKOSiQbLdYGLfUb=2obLWwQxUjyfg@mail.gmail.com>
References: <20140926080211.7639.68358@wrigleys.postgresql.org>
<8043.1411753479@sss.pgh.pa.us>
<CAL9smLB1szUHLMfpN19FKiCHRCs4WvfjqXbxKaCUjmDzEtT=ng@mail.gmail.com>
<1314567.1759501872@sss.pgh.pa.us>
<CAL9smLD2GHwB1vvDKob3UQ_y4MHU9thiMBY1PU1Yb2o5RMwSCQ@mail.gmail.com>
<CAApHDvp=+EN2o=8fD47nyKKOSiQbLdYGLfUb=2obLWwQxUjyfg@mail.gmail.com>
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.g...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, marko@joh.to
Subject: Re: [BUGS] BUG #11500: PRIMARY KEY index not being used
In-Reply-To: <1525405.1759598257@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox