From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marko Tiikkaja <marko@joh.to>
cc: David Rowley <dgrowleyml@gmail.com>, pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #11500: PRIMARY KEY index not being used
In-reply-to: 
 <CAL9smLAe2mu5N6GB-Y01HjE1NoSKn+XXi-3tUWT_3GaZSW0Jeg@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>
 <1525405.1759598257@sss.pgh.pa.us>
 <CAApHDvpVcN=yKT2Jg96GotHyP2_nB4M6mWiLXqAqrvvXan-y2g@mail.gmail.com>
 <CAL9smLAe2mu5N6GB-Y01HjE1NoSKn+XXi-3tUWT_3GaZSW0Jeg@mail.gmail.com>
Comments: In-reply-to Marko Tiikkaja <marko@joh.to>
	message dated "Sun, 05 Oct 2025 09:38:14 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1704044.1759687759.1@sss.pgh.pa.us>
Date: Sun, 05 Oct 2025 14:09:19 -0400
Message-ID: <1704045.1759687759@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1704045.1759687759%40sss.pgh.pa.us>
Precedence: bulk

Marko Tiikkaja <marko@joh.to> writes:
> I can't believe I actually have to do this, but run:

That's not what was asked for.  Your original report showed a
case where the planner chose orders_wait_event_idx despite
estimating that it would have to scan 80K-plus rows.
That's the case where I don't understand how it could think
that the primary key index would be even more expensive.

I'm not particularly concerned about the case you show here,
because if you run the EXPLAIN a second time it gets a lot
cheaper.  I believe the reason is that the first time is
visiting a bunch of just-deleted rows and so it has to stop
and update their hint bits, both in the heap and the index.
That is not a cost that the planner can reasonably predict
in advance, and even if we could it's not really fair to
blame it on the choice of index.  The pkey alternative only
visits one row and therefore only updates one hint bit, but
we'd have to update the rest sooner or later.  So IMO the
speed difference is largely illusory because it arises from
deferring maintenance.  You can demonstrate this by changing
the "ANALYZE orders" in the test case to "VACUUM ANALYZE
orders": the EXPLAIN still chooses orders_wait_event_idx,
but now it's fast because all that work got done by VACUUM.

			regards, tom lane