Re: Behavior change in PostgreSQL 14Beta3 or bug? - Mailing list pgsql-general

From Laurenz Albe
Subject Re: Behavior change in PostgreSQL 14Beta3 or bug?
Date
Msg-id 6fb7ffe9f584cb76e5c7aadc83d7562009224cb8.camel@cybertec.at
Whole thread Raw
In response to Re: Behavior change in PostgreSQL 14Beta3 or bug?  (Peter Geoghegan <pg@bowt.ie>)
Responses Re: Behavior change in PostgreSQL 14Beta3 or bug?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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

Attachment

pgsql-general by date:

Previous
From: "Daniel Westermann (DWE)"
Date:
Subject: Re: Behavior change in PostgreSQL 14Beta3 or bug?
Next
From: Tom Lane
Date:
Subject: Re: Behavior change in PostgreSQL 14Beta3 or bug?