Re: Behavior change in PostgreSQL 14Beta3 or bug?

[Laurenz Albe <laurenz.albe@xxxxxxxxxxx>]

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

From d98f4c4cb62b564e8f9a26ed4e8da80dadfbc55c Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@xxxxxxxxxxx>
Date: Mon, 6 Sep 2021 17:47:15 +0200
Subject: [PATCH] Document VACUUM tips for index-only scans

Add hints for tuning autovacuum to get efficient
index-only scans.  This has become even more relevant
than before, because the default "auto" option of
"index_cleanup" introduced by commit 3499df0dee
adds yet another thing to consider.
---
 doc/src/sgml/indices.sgml | 13 +++++++++++--
 1 file changed, 11 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 56fbd45178..4257615b85 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -1134,8 +1134,9 @@ SELECT x FROM tab WHERE x = 'key' AND z &lt; 42;
    problem.  <productname>PostgreSQL</productname> tracks, for each page in
    a table's heap, whether all rows stored in that page are old enough to be
    visible to all current and future transactions.  This information is
-   stored in a bit in the table's <firstterm>visibility map</firstterm>.  An
-   index-only scan, after finding a candidate index entry, checks the
+   stored in a bit in the table's
+   <link linkend="storage-vm"><firstterm>visibility map</firstterm></link>.
+   An index-only scan, after finding a candidate index entry, checks the
    visibility map bit for the corresponding heap page.  If it's set, the row
    is known visible and so the data can be returned with no further work.
    If it's not set, the heap entry must be visited to find out whether it's
@@ -1155,6 +1156,14 @@ SELECT x FROM tab WHERE x = 'key' AND z &lt; 42;
    make this type of scan very useful in practice.
   </para>
 
+  <para>
+   To make sure that index-only scans are efficient, it can be a good idea
+   to see that the table is <command>VACUUM</command>ed often enough.  This
+   can be done by lowering <xref linkend="reloption-autovacuum-vacuum-scale-factor"/>
+   on that table and setting <xref linkend="reloption-vacuum-index-cleanup"/>
+   to <literal>auto</literal>.
+  </para>
+
   <para>
    <indexterm>
     <primary><literal>INCLUDE</literal></primary>
-- 
2.31.1