From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Marko Tiikkaja <marko@joh.to>, pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #11500: PRIMARY KEY index not being used
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>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Sat, 04 Oct 2025 16:19:31 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1525404.1759598257.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 04 Oct 2025 13:17:37 -0400
Message-ID: <1525405.1759598257@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1525405.1759598257%40sss.pgh.pa.us>
Precedence: bulk

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 =3D '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=3D0.54..94812.85 rows=3D85043 width=3D1223) (actual
time=3D0.166..7199.020 rows=3D84535 loops=3D1)
   Buffers: shared hit=3D15676 read=3D91962 dirtied=3D1988
 Planning:
   Buffers: shared hit=3D807 read=3D11 dirtied=3D1
 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 =3D $1)" line here?)

			regards, tom lane

[1] https://www.postgresql.org/message-id/CAL9smLB1szUHLMfpN19FKiCHRCs4Wvf=
jqXbxKaCUjmDzEtT%3Dng%40mail.gmail.com