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