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

On Sun, Oct 5, 2025 at 9:09 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

Sure, it get cheaper, but it's still a bad plan, and significantly
slower than the obvious one.

> 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.

On the production server the query still consistently took ~100ms even
after all that maintenance had been done. Compared to <0.05ms when
going through the primary key.

But it sounds like you're saying the planner is working as expected
here, so I'll just drop the issue. I've already implemented a
workaround in production via a no-op function.

.m