On Mon, 2021-09-06 at 07:46 -0700, Peter Geoghegan wrote:
> It's a non-hot update, and so there is a single dead index tuple. You're seeing
> the new optimization that makes vacuum skip indexes in marginal cases.
>
> Try running vacuum with index cleanup = on.
It occurs to me that this new default "auto" setting for "index_cleanup"
may cause a performance regression for people who VACUUM tables frequently
in order to get fast index-only scans.
That is not a bug, but it would be good to alert the users.
It is not an incompatibility that warrants a mention in the release notes,
but perhaps somthing in
https://www.postgresql.org/docs/14/indexes-index-only-scans.html
and/or
https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-VISIBILITY-MAP
could be added that recommends that people should consider frequent
VACUUM with "index_cleanup = on" for best performance with index-only scans.
Suggested patch attached, should be backpatched to v14.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com