From d98f4c4cb62b564e8f9a26ed4e8da80dadfbc55c Mon Sep 17 00:00:00 2001 From: Laurenz Albe 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 < 42; problem. PostgreSQL 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 visibility map. An - index-only scan, after finding a candidate index entry, checks the + stored in a bit in the table's + visibility map. + 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 < 42; make this type of scan very useful in practice. + + To make sure that index-only scans are efficient, it can be a good idea + to see that the table is VACUUMed often enough. This + can be done by lowering + on that table and setting + to auto. + + INCLUDE -- 2.31.1