Re: Visibility map, partial vacuums

Gregory Stark wrote:
> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>> Gregory Stark wrote:
>>> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>>>> Hmm. It just occurred to me that I think this circumvented the anti-wraparound
>>>> vacuuming: a normal vacuum doesn't advance relfrozenxid anymore. We'll need to
>>>> disable the skipping when autovacuum is triggered to prevent wraparound. VACUUM
>>>> FREEZE does that already, but it's unnecessarily aggressive in freezing.
>> FWIW, it seems the omission is actually the other way 'round. Autovacuum always
>> forces a full-scanning vacuum, making the visibility map useless for
>> autovacuum. This obviously needs to be fixed.
>
> How does it do that? Is there some option in the VacStmt to control this? Do
> we just need a syntax to set that option?

The way it works now is that if VacuumStmt->freeze_min_age is not -1
(which means "use the default"), the visibility map is not used and the
whole table is scanned. Autovacuum always sets freeze_min_age, so it's
never using the visibility map. Attached is a patch I'm considering to
fix that.

> How easy is it to tell what percentage of the table needs to be vacuumed? If
> it's > 50% perhaps it would make sense to scan the whole table? (Hm. Not
> really if it's a contiguous 50% though...)

Hmm. You could scan the visibility map to see how much you could skip by
using it. You could account for contiguity.

> Another idea: Perhaps each page of the visibility map should have a frozenxid
> (or multiple frozenxids?). Then if an individual page of the visibility map is
> old we could force scanning all the heap pages covered by that map page and
> update it. I'm not sure we can do that safely though without locking issues --
> or is it ok because it's vacuum doing the updating?

We discussed that a while ago:

http://archives.postgresql.org/message-id/492A6032.6080000@enterprisedb.com

Tom was concerned about making the visibility map not just a hint but
critical data. Rightly so. This is certainly 8.5 stuff; perhaps it would
be more palatable after we get the index-only-scans working using the
visibility map, since the map would be critical data anyway.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index fd2429a..3e3cb9d 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -171,10 +171,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
     vacrelstats->hasindex = (nindexes > 0);

     /* Should we use the visibility map or scan all pages? */
-    if (vacstmt->freeze_min_age != -1)
-        scan_all = true;
-    else
-        scan_all = false;
+    scan_all = vacstmt->scan_all;

     /* Do the vacuuming */
     lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, scan_all);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index eb7ab4d..2781f6e 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2771,6 +2771,7 @@ _copyVacuumStmt(VacuumStmt *from)
     COPY_SCALAR_FIELD(analyze);
     COPY_SCALAR_FIELD(verbose);
     COPY_SCALAR_FIELD(freeze_min_age);
+    COPY_SCALAR_FIELD(scan_all));
     COPY_NODE_FIELD(relation);
     COPY_NODE_FIELD(va_cols);

diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index d4c57bb..86a032f 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1436,6 +1436,7 @@ _equalVacuumStmt(VacuumStmt *a, VacuumStmt *b)
     COMPARE_SCALAR_FIELD(analyze);
     COMPARE_SCALAR_FIELD(verbose);
     COMPARE_SCALAR_FIELD(freeze_min_age);
+    COMPARE_SCALAR_FIELD(scan_all);
     COMPARE_NODE_FIELD(relation);
     COMPARE_NODE_FIELD(va_cols);

diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 85f4616..1aab75c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5837,6 +5837,7 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
                     n->analyze = false;
                     n->full = $2;
                     n->freeze_min_age = $3 ? 0 : -1;
+                    n->scan_all = $3;
                     n->verbose = $4;
                     n->relation = NULL;
                     n->va_cols = NIL;
@@ -5849,6 +5850,7 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
                     n->analyze = false;
                     n->full = $2;
                     n->freeze_min_age = $3 ? 0 : -1;
+                    n->scan_all = $3;
                     n->verbose = $4;
                     n->relation = $5;
                     n->va_cols = NIL;
@@ -5860,6 +5862,7 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
                     n->vacuum = true;
                     n->full = $2;
                     n->freeze_min_age = $3 ? 0 : -1;
+                    n->scan_all = $3;
                     n->verbose |= $4;
                     $$ = (Node *)n;
                 }
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 8d8947f..2c68779 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2649,6 +2649,7 @@ autovacuum_do_vac_analyze(autovac_table *tab,
     vacstmt.full = false;
     vacstmt.analyze = tab->at_doanalyze;
     vacstmt.freeze_min_age = tab->at_freeze_min_age;
+    vacstmt.scan_all = tab->at_wraparound;
     vacstmt.verbose = false;
     vacstmt.relation = NULL;    /* not used since we pass a relid */
     vacstmt.va_cols = NIL;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index bb71ac1..df19f7e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1966,6 +1966,7 @@ typedef struct VacuumStmt
     bool        full;            /* do FULL (non-concurrent) vacuum */
     bool        analyze;        /* do ANALYZE step */
     bool        verbose;        /* print progress info */
+    bool        scan_all;        /* force scan of all pages */
     int            freeze_min_age; /* min freeze age, or -1 to use default */
     RangeVar   *relation;        /* single table to process, or NULL */
     List       *va_cols;        /* list of column names, or NIL for all */