Hi hackers,
In PostgreSQL 19 (reproduced on REL_19_STABLE at 48b43081e55 and master),
VACUUM can misestimate pg_class.reltuples -- often setting reltuples = 0
and resetting pg_stat_all_tables.n_live_tup to 0 on a newly populated table
--
if a read-only query runs between loading the table and the first VACUUM.
It
can also corrupt an existing reltuples estimate that was just computed by
ANALYZE.
1. Root cause
-------------
vac_estimate_reltuples() assumes that a relation with no prior tuple-density
estimate (old_rel_tuples = -1, or old_rel_pages = 0) cannot have most of its
pages marked all-visible in the visibility map, and therefore scales the
tuple
density of scanned_pages across total_pages whenever scanned_pages > 1.
Since commits b46e1e54d ("Allow on-access pruning to set pages
all-visible"),
378a216187a ("Set pd_prune_xid on insert"), and 4a2f5533f3c ("Correct
on-access VM setting heuristic"), a read-only query after an ordinary COPY,
INSERT, or CREATE TABLE AS marks all full pages all-visible in the VM before
VACUUM ever runs. When the first VACUUM runs, it skips nearly the entire
relation via the VM and scans only the tail end:
* at most the last, partially filled data page, plus
* any uninitialized (PageIsNew / all-zeroes) pages left behind at the end
of
the relation by bulk extension (up to 64 blocks).
lazy_scan_new_or_empty()
intentionally does not mark PageIsNew pages all-visible in the VM, so
every VACUUM scans them and counts them in vacrel->scanned_pages with 0
tuples.
This produces four failure modes (plus a pgstat side effect):
Mode 1 (Never-vacuumed / unanalyzed table -> reltuples = 0 or tiny
fraction):
When a newly loaded table is read before its first VACUUM, VACUUM skips all
the all-visible data pages and scans only the tail page(s) and any trailing
PageIsNew blocks from bulk extension. vac_estimate_reltuples() then
extrapolates that zero-heavy tail sample across the entire relation, setting
reltuples to 0 (or a tiny fraction of the true row count). Because
old_rel_pages then matches total_pages, subsequent VACUUMs hit the < 2% /
<= 1 page guard and preserve the wrong reltuples value indefinitely.
Mode 2 (Table vacuumed while empty [relpages=0, reltuples=0] -> reltuples =
0
and n_live_tup reset to 0):
If a table was previously vacuumed while empty and is later populated and
read, VACUUM either extrapolates 0 from the PageIsNew tail or hits the
`scanned_pages <= 1` rule and returns `old_rel_tuples` (0). Furthermore,
whenever vac_estimate_reltuples() returns -1 (for instance, when
scanned_pages == 1), heap_vacuum_rel() clamps `Max(vacrel->new_live_tuples,
0)`
when calling pgstat_report_vacuum(), wiping pg_stat_all_tables.n_live_tup
back to 0.
Mode 3 (Table with valid ANALYZE stats -> reltuples drops sharply on
VACUUM):
Even when ANALYZE is run right after a bulk load, ANALYZE includes any
trailing PageIsNew blocks in old_rel_pages when recording old_rel_tuples.
When a subsequent read marks the populated pages all-visible, VACUUM skips
the data pages and scans only the trailing PageIsNew blocks (which contain 0
tuples and can easily exceed 2% of total_pages, defeating the 2% stability
guard). vac_estimate_reltuples() then applies the diluted old_density to
only the unscanned data pages and adds 0 scanned tuples -- effectively
double-counting the empty PageIsNew pages and sharply reducing the reltuples
estimate that ANALYZE just computed.
Mode 4 (Table with valid ANALYZE stats grows -> reltuples stays stale):
When a table with prior statistics grows and a read-only query marks the
newly added pages all-visible before VACUUM runs, VACUUM may scan only a
single page. Because `if (scanned_pages <= 1) return old_rel_tuples;` does
not check whether `old_rel_pages == total_pages`, VACUUM leaves reltuples
unchanged at the pre-growth value instead of scaling by old_density.
2. Minimal reproduction
-----------------------
-- 1. Never-vacuumed table (4,000 rows): reltuples becomes 0 (PG18: 4000)
CREATE TABLE repro_new WITH (autovacuum_enabled = off) AS
SELECT g AS a, repeat('x', 100) AS b FROM generate_series(1, 4000) g;
SELECT count(*) FROM repro_new;
VACUUM repro_new;
SELECT relpages, reltuples FROM pg_class WHERE oid = 'repro_new'::regclass;
-- PG19: relpages = 69, reltuples = 0 (EXPLAIN estimates rows=1)
-- PG18: relpages = 69, reltuples = 4000
-- 2. Empty-vacuumed table: reltuples and n_live_tup both become 0 (PG18:
4000)
CREATE TABLE repro_empty (a int, b text) WITH (autovacuum_enabled = off);
VACUUM repro_empty;
INSERT INTO repro_empty
SELECT g, repeat('x', 100) FROM generate_series(1, 4000) g;
SELECT count(*) FROM repro_empty;
SELECT pg_stat_force_next_flush();
VACUUM repro_empty;
SELECT pg_stat_force_next_flush();
SELECT c.relpages, c.reltuples, s.n_live_tup
FROM pg_class c JOIN pg_stat_all_tables s ON s.relid = c.oid
WHERE c.oid = 'repro_empty'::regclass;
-- PG19: relpages = 69, reltuples = 0, n_live_tup = 0
-- PG18: relpages = 69, reltuples = 4000, n_live_tup = 4000
-- 3. Post-ANALYZE drift: reltuples drops from 4000 to 2156 (PG18: 4000)
CREATE TABLE repro_analyzed WITH (autovacuum_enabled = off) AS
SELECT g AS a, repeat('x', 100) AS b FROM generate_series(1, 4000) g;
ANALYZE repro_analyzed;
SELECT count(*) FROM repro_analyzed;
VACUUM repro_analyzed;
SELECT relpages, reltuples FROM pg_class WHERE oid =
'repro_analyzed'::regclass;
-- PG19: relpages = 69, reltuples = 2156
-- PG18: relpages = 69, reltuples = 4000
3. Proposed fix and design trade-offs
-------------------------------------
The attached patch fixes these issues as follows:
1. Count PageIsNew blocks separately (`vacrel->scanned_new_pages`) in
lazy_scan_new_or_empty() (and contrib/pgstattuple's statapprox_heap())
and
pass `scanned_new_pages` to vac_estimate_reltuples(). Exclude those
uninitialized pages from both the sampled pages (`sampled_pages =
scanned_pages - scanned_new_pages`) and sampleable pages
(`sampleable_pages
= total_pages - scanned_new_pages`), since they are never marked
all-visible and are not representative of the relation's initialized
pages.
2. When prior tuple density is unknown (`old_rel_tuples < 0 ||
old_rel_pages == 0`)
and VACUUM sampled `<= 1` initialized page or `< 2%` of sampleable pages,
return `-1` (unknown) rather than extrapolating from the tail or
returning
`0` for a previously empty relation.
3. Restrict the `sampled_pages <= 1` early return to relations whose size
has
not changed (`old_rel_pages == total_pages`), matching the `< 2%` guard,
so
that relations that grew since the last VACUUM/ANALYZE still scale using
`old_density`.
4. Pass `vacrel->new_live_tuples` directly to pgstat_report_vacuum() and
keep
the existing `tabentry->live_tuples` when `livetuples < 0`, so a VACUUM
that could not estimate live tuples does not wipe `n_live_tup` to 0.
Note on returning -1 vs. PG18 behavior:
When a table has no prior statistics (`reltuples = -1` or `relpages = 0`)
and
on-access pruning marks nearly all pages all-visible before the first
VACUUM,
returning `-1` means `pg_class.reltuples` remains `-1` after that VACUUM
(which vac_estimate_reltuples() already did in PG19 when `scanned_pages <=
1`
and `old_rel_tuples == -1`), whereas in PG18 the first VACUUM always scanned
all pages and set `reltuples` to the exact count. With `relpages > 0` and
`reltuples = -1`, table_block_relation_estimate_size() falls back to its
relpages / tuple-width estimate and autovacuum schedules an ANALYZE (since
`n_live_tup` and `n_mod_since_analyze` are preserved).
Two alternatives could avoid leaving `reltuples = -1` after VACUUM:
(a) Disable VM page-skipping in VACUUM (`vacrel->skipwithvm = false`) when
`rel->rd_rel->reltuples < 0 || rel->rd_rel->relpages == 0`, forcing
the
first VACUUM on an unanalyzed table to scan all pages as in PG18, at
the
cost of giving up the I/O savings of b46e1e54d/378a216187a on the
first
VACUUM when ANALYZE has not run first.
(b) Fall back to pgstat's `live_tuples` count when
vac_estimate_reltuples()
returns `-1`, though that would introduce a dependency from
`pg_class.reltuples` on cumulative stats.
Thoughts on whether returning `-1` (as in the attached patch) is preferred,
or
whether we should also do (a) or (b)? If either (a) or (b) is needed, I can
share separate patches for them.
Thanks,
Vaibhav Jain
From e2a1190d55a94b038362b09d8f7962b288f1be20 Mon Sep 17 00:00:00 2001
From: Vaibhav Jain <[email protected]>
Date: Wed, 21 Oct 2026 06:18:21 +0000
Subject: [PATCH] Fix VACUUM's reltuples estimate when it skips most of the
relation
vac_estimate_reltuples() extrapolates the tuple density of the scanned
pages to the whole relation when there is no prior density measurement
(reltuples = -1, or relpages = 0). That was reasonable as long as such
a relation could not have many all-visible pages, so that VACUUM had to
scan nearly all of it.
Since commits b46e1e54d and 378a216187a, read-only queries set pages
all-visible during on-access pruning, including pages that were only
ever inserted to. So the first VACUUM after a bulk load followed by any
read of the data can skip nearly the entire relation, scanning only its
tail end: the last, partially filled page, plus any new (all-zeroes)
pages left behind by bulk extension, which are never set all-visible.
Extrapolating from those few, unrepresentative pages produced wildly
wrong reltuples values, often 0 for a table with many rows. That leads
to bad plans until the next ANALYZE, and because the relation size is
then unchanged, subsequent VACUUMs kept the bogus value.
There were related problems for relations with prior statistics:
* A relation that was empty when last vacuumed (relpages = 0,
reltuples = 0) kept reltuples = 0 when VACUUM scanned a single page.
* New pages are scanned by every VACUUM, so they could defeat the
heuristic that keeps reltuples unchanged when less than 2% of an
unchanged-size relation was scanned, making reltuples drift down with
each VACUUM. This has been possible since bulk extension by up to 64
pages was introduced in v16 (and COPY FREEZE could already hit the
unknown-density case), but it now happens in the first VACUUM after a
bulk load.
* When VACUUM scanned a single page of a relation that had grown since
the last VACUUM or ANALYZE, reltuples was left unchanged, even though
the added pages might have been set all-visible without ever having
been counted.
To fix, have VACUUM count the new pages it scans, and pass that count to
vac_estimate_reltuples(), which no longer treats them as part of its
sample of the relation's tuple density. When the old density is
unknown, report the tuple count as still unknown (-1) instead of
extrapolating if we sampled no more than a single page or less than 2%
of the relation. Only keep the existing reltuples on account of a tiny
sample when the relation's size is unchanged. Also, don't let a VACUUM
that couldn't estimate the number of live tuples reset the cumulative
statistics system's live tuple count to zero.
pgstattuple_approx() is adjusted in the same way. Since
vac_estimate_reltuples() has a new argument, extensions calling it need
to be adjusted too.
Backpatch-through: 19
---
contrib/pgstattuple/pgstatapprox.c | 6 +-
doc/src/sgml/catalogs.sgml | 5 +-
src/backend/access/heap/vacuumlazy.c | 32 +++--
src/backend/commands/vacuum.c | 71 +++++++++---
src/backend/utils/activity/pgstat_relation.c | 10 +-
src/include/commands/vacuum.h | 1 +
src/test/regress/expected/vacuum.out | 116 +++++++++++++++++++
src/test/regress/sql/vacuum.sql | 52 +++++++++
8 files changed, 267 insertions(+), 26 deletions(-)
diff --git a/contrib/pgstattuple/pgstatapprox.c b/contrib/pgstattuple/pgstatapprox.c
index 21e0b50fb4b..5940dd3e6ad 100644
--- a/contrib/pgstattuple/pgstatapprox.c
+++ b/contrib/pgstattuple/pgstatapprox.c
@@ -120,6 +120,7 @@ statapprox_heap(Relation rel, output_type *stat)
TransactionId OldestXmin;
StatApproxReadStreamPrivate p;
ReadStream *stream;
+ BlockNumber scanned_new_pages = 0;
OldestXmin = GetOldestNonRemovableTransactionId(rel);
bstrategy = GetAccessStrategy(BAS_BULKREAD);
@@ -169,6 +170,8 @@ statapprox_heap(Relation rel, output_type *stat)
if (PageIsNew(page) || PageIsEmpty(page))
{
+ if (PageIsNew(page))
+ scanned_new_pages++;
UnlockReleaseBuffer(buf);
continue;
}
@@ -245,9 +248,10 @@ statapprox_heap(Relation rel, output_type *stat)
* we already accounted for the space in those pages, too.
*/
stat->tuple_count = vac_estimate_reltuples(rel, nblocks, p.scanned,
+ scanned_new_pages,
stat->tuple_count);
- /* It's not clear if we could get -1 here, but be safe. */
+ /* We get -1 if the number of tuples is unknown; report zero then. */
stat->tuple_count = Max(stat->tuple_count, 0);
/*
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 641ce03e66d..8ac2ee9ad9b 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2047,7 +2047,10 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
If the table has never yet been vacuumed or
analyzed, <structfield>reltuples</structfield>
contains <literal>-1</literal> indicating that the row count is
- unknown.
+ unknown. It can also contain <literal>-1</literal> after a
+ <command>VACUUM</command> that skipped nearly all of the table's pages
+ using the visibility map, if the row count was previously unknown or
+ the table was previously empty.
</para></entry>
</row>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 53e3a1ab0b9..3681c7278d8 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -312,6 +312,13 @@ typedef struct LVRelState
*/
BlockNumber eager_scanned_pages;
+ /*
+ * Count of new (all-zeroes) pages scanned. These are never set
+ * all-visible, so every VACUUM scans them. They're excluded from the
+ * sample used to estimate the relation's tuple density.
+ */
+ BlockNumber scanned_new_pages;
+
BlockNumber removed_pages; /* # pages removed by relation truncation */
BlockNumber new_frozen_tuple_pages; /* # pages with newly frozen tuples */
@@ -741,6 +748,7 @@ heap_vacuum_rel(Relation rel, const VacuumParams *params,
/* Initialize page counters explicitly (be tidy) */
vacrel->scanned_pages = 0;
vacrel->eager_scanned_pages = 0;
+ vacrel->scanned_new_pages = 0;
vacrel->removed_pages = 0;
vacrel->new_frozen_tuple_pages = 0;
vacrel->lpdead_item_pages = 0;
@@ -956,9 +964,11 @@ heap_vacuum_rel(Relation rel, const VacuumParams *params,
/*
* Now actually update rel's pg_class entry.
*
- * In principle new_live_tuples could be -1 indicating that we (still)
- * don't know the tuple count. In practice that can't happen, since we
- * scan every page that isn't skipped using the visibility map.
+ * new_live_tuples could be -1 indicating that we (still) don't know the
+ * tuple count. That happens when we skip nearly all of the pages of a
+ * relation that has no prior statistics using the visibility map, which
+ * is possible because on-access pruning can set pages all-visible before
+ * the relation is ever vacuumed.
*/
vac_update_relstats(rel, new_rel_pages, vacrel->new_live_tuples,
new_rel_allvisible, new_rel_allfrozen,
@@ -967,7 +977,9 @@ heap_vacuum_rel(Relation rel, const VacuumParams *params,
&frozenxid_updated, &minmulti_updated, false);
/*
- * Report results to the cumulative stats system, too.
+ * Report results to the cumulative stats system, too. If we don't know
+ * the number of live tuples, pass -1 so that the stats system keeps its
+ * current count.
*
* Deliberately avoid telling the stats system about LP_DEAD items that
* remain in the table due to VACUUM bypassing index and heap vacuuming.
@@ -977,7 +989,7 @@ heap_vacuum_rel(Relation rel, const VacuumParams *params,
* vacuuming.
*/
pgstat_report_vacuum(rel,
- Max(vacrel->new_live_tuples, 0),
+ vacrel->new_live_tuples,
vacrel->recently_dead_tuples +
vacrel->missed_dead_tuples,
starttime);
@@ -1598,11 +1610,12 @@ lazy_scan_heap(LVRelState *vacrel)
/* now we can compute the new value for pg_class.reltuples */
vacrel->new_live_tuples = vac_estimate_reltuples(vacrel->rel, rel_pages,
vacrel->scanned_pages,
+ vacrel->scanned_new_pages,
vacrel->live_tuples);
/*
- * Also compute the total number of surviving heap entries. In the
- * (unlikely) scenario that new_live_tuples is -1, take it as zero.
+ * Also compute the total number of surviving heap entries. If
+ * new_live_tuples is -1 (unknown), take it as zero.
*/
vacrel->new_rel_tuples =
Max(vacrel->new_live_tuples, 0) + vacrel->recently_dead_tuples +
@@ -1913,7 +1926,12 @@ lazy_scan_new_or_empty(LVRelState *vacrel, Buffer buf, BlockNumber blkno,
* Do that by testing if there's any space recorded for the page. If
* not, enter it. We do so after releasing the lock on the heap page,
* the FSM is approximate, after all.
+ *
+ * Also count the page separately, so that the unused tail end of a
+ * bulk-extended relation doesn't distort our estimate of the
+ * relation's tuple density. See vac_estimate_reltuples().
*/
+ vacrel->scanned_new_pages++;
UnlockReleaseBuffer(buf);
if (GetRecordedFreeSpace(vacrel->rel, blkno) == 0)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 168e68d80e2..b17087239c5 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1329,6 +1329,15 @@ vacuum_xid_failsafe_check(const struct VacuumCutoffs *cutoffs)
* the old value of pg_class.reltuples/pg_class.relpages as a measurement
* of the tuple density in the unscanned pages.
*
+ * scanned_new_pages is the number of scanned pages that were found to be
+ * new (all-zeroes), such as the unused tail end of a bulk-extended
+ * relation. They are included in scanned_pages. Such pages are never
+ * marked all-visible, so they are scanned every time, and they contain
+ * no tuples. They are not representative of the rest of the relation,
+ * so we don't treat them as part of our sample of its tuple density.
+ *
+ * Returns -1 if the number of tuples is (still) unknown.
+ *
* Note: scanned_tuples should count only *live* tuples, since
* pg_class.reltuples is defined that way.
*/
@@ -1336,45 +1345,75 @@ double
vac_estimate_reltuples(Relation relation,
BlockNumber total_pages,
BlockNumber scanned_pages,
+ BlockNumber scanned_new_pages,
double scanned_tuples)
{
BlockNumber old_rel_pages = relation->rd_rel->relpages;
double old_rel_tuples = relation->rd_rel->reltuples;
+ BlockNumber sampled_pages;
+ double sampleable_pages;
double old_density;
double unscanned_pages;
double total_tuples;
+ Assert(scanned_new_pages <= scanned_pages);
+
/* If we did scan the whole table, just use the count as-is */
if (scanned_pages >= total_pages)
return scanned_tuples;
+ /*
+ * Only the initialized pages that we scanned tell us anything about the
+ * relation's tuple density.
+ */
+ sampled_pages = scanned_pages - scanned_new_pages;
+ sampleable_pages = (double) total_pages - (double) scanned_new_pages;
+
+ /*
+ * If old density is unknown, we can't do much except scale up
+ * scanned_tuples to match the size of the relation. That's only
+ * reasonable if we sampled a meaningful fraction of the relation, though.
+ * The pages that VACUUM scans are the ones that aren't all-visible, which
+ * is anything but a random sample: they're typically the most recently
+ * modified pages at the end of the relation. Since on-access pruning can
+ * set pages all-visible during read-only queries, even a relation that
+ * has never been vacuumed or analyzed may have almost all of its pages
+ * skipped. Rather than extrapolating from a few unrepresentative pages,
+ * report that the number of tuples is still unknown if we sampled no more
+ * than a single page, or less than 2% of the relation.
+ *
+ * A relation that was empty when last vacuumed or analyzed (relpages = 0,
+ * reltuples = 0) is treated the same way: that tells us nothing about the
+ * density of the pages that were added since.
+ */
+ if (old_rel_tuples < 0 || old_rel_pages == 0)
+ {
+ if (sampled_pages <= 1 || sampled_pages < sampleable_pages * 0.02)
+ return -1;
+ return floor((scanned_tuples / sampled_pages) * sampleable_pages + 0.5);
+ }
+
/*
* When successive VACUUM commands scan the same few pages again and
* again, without anything from the table really changing, there is a risk
* that our beliefs about tuple density will gradually become distorted.
* This might be caused by vacuumlazy.c implementation details, such as
- * its tendency to always scan the last heap page. Handle that here.
+ * its tendency to always scan the last heap page and any new pages.
+ * Handle that here.
*
* If the relation is _exactly_ the same size according to the existing
- * pg_class entry, and only a few of its pages (less than 2%) were
- * scanned, keep the existing value of reltuples. Also keep the existing
- * value when only a subset of rel's pages <= a single page were scanned.
- *
- * (Note: we might be returning -1 here.)
+ * pg_class entry, and only a few of its pages (less than 2%, or no more
+ * than a single page) were sampled, keep the existing value of reltuples.
+ * We don't do that when the relation's size has changed, though: pages
+ * that were added since the last VACUUM or ANALYZE might have been set
+ * all-visible by on-access pruning, so the existing value might not
+ * account for them at all. The normal calculation below estimates their
+ * contents using the old density instead.
*/
if (old_rel_pages == total_pages &&
- scanned_pages < (double) total_pages * 0.02)
- return old_rel_tuples;
- if (scanned_pages <= 1)
+ (sampled_pages <= 1 || sampled_pages < sampleable_pages * 0.02))
return old_rel_tuples;
- /*
- * If old density is unknown, we can't do much except scale up
- * scanned_tuples to match total_pages.
- */
- if (old_rel_tuples < 0 || old_rel_pages == 0)
- return floor((scanned_tuples / scanned_pages) * total_pages + 0.5);
-
/*
* Okay, we've covered the corner cases. The normal calculation is to
* convert the old measurement to a density (tuples per page), then
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 04f2eb21d0b..ae5d616e4ff 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -206,6 +206,8 @@ pgstat_drop_relation(Relation rel)
/*
* Report that the table was just vacuumed and flush IO statistics.
+ *
+ * livetuples is -1 if VACUUM couldn't estimate the number of live tuples.
*/
void
pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples,
@@ -232,7 +234,13 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples,
shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
tabentry = &shtabentry->stats;
- tabentry->live_tuples = livetuples;
+ /*
+ * If VACUUM doesn't know the number of live tuples, keep the current
+ * count, which reflects the tuples inserted and deleted since the last
+ * VACUUM or ANALYZE.
+ */
+ if (livetuples >= 0)
+ tabentry->live_tuples = livetuples;
tabentry->dead_tuples = deadtuples;
/*
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index a95abba6640..c74e60849e0 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -371,6 +371,7 @@ extern void vac_close_indexes(int nindexes, Relation *Irel, LOCKMODE lockmode);
extern double vac_estimate_reltuples(Relation relation,
BlockNumber total_pages,
BlockNumber scanned_pages,
+ BlockNumber scanned_new_pages,
double scanned_tuples);
extern void vac_update_relstats(Relation relation,
BlockNumber num_pages,
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index d4696bc3325..794e7b80390 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -730,3 +730,119 @@ SELECT pg_column_toast_chunk_id(f1) = :'id_2_chunk' AS same_chunk
(1 row)
DROP TABLE vac_rewrite_toast;
+-- Test VACUUM's estimate of pg_class.reltuples when it skips most of the
+-- relation using the visibility map. Read-only queries can set pages
+-- all-visible during on-access pruning, so even the first VACUUM after a bulk
+-- load may scan only the tail end of the relation, including any new pages
+-- left behind by bulk extension. That's not a representative sample of the
+-- relation. Temp tables are used because their visibility horizon depends
+-- only on this backend, which makes on-access pruning deterministic.
+--
+-- Never vacuumed or analyzed: reltuples should remain unknown, rather than
+-- being extrapolated from the few pages that VACUUM scanned.
+CREATE TEMP TABLE vac_reltuples_new AS
+ SELECT g AS a, repeat('x', 100) AS b FROM generate_series(1, 4000) g;
+SELECT relpages, reltuples FROM pg_class
+ WHERE oid = 'vac_reltuples_new'::regclass;
+ relpages | reltuples
+----------+-----------
+ 0 | -1
+(1 row)
+
+SELECT count(*) FROM vac_reltuples_new; -- sets most pages all-visible
+ count
+-------
+ 4000
+(1 row)
+
+VACUUM vac_reltuples_new;
+SELECT relpages > 0 AS has_pages, reltuples FROM pg_class
+ WHERE oid = 'vac_reltuples_new'::regclass;
+ has_pages | reltuples
+-----------+-----------
+ t | -1
+(1 row)
+
+-- Likewise for a relation that was empty when it was last vacuumed
+CREATE TEMP TABLE vac_reltuples_empty (a int, b text);
+VACUUM vac_reltuples_empty;
+SELECT relpages, reltuples FROM pg_class
+ WHERE oid = 'vac_reltuples_empty'::regclass;
+ relpages | reltuples
+----------+-----------
+ 0 | 0
+(1 row)
+
+INSERT INTO vac_reltuples_empty
+ SELECT g, repeat('x', 100) FROM generate_series(1, 4000) g;
+SELECT count(*) FROM vac_reltuples_empty;
+ count
+-------
+ 4000
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+VACUUM vac_reltuples_empty;
+SELECT relpages > 0 AS has_pages, reltuples FROM pg_class
+ WHERE oid = 'vac_reltuples_empty'::regclass;
+ has_pages | reltuples
+-----------+-----------
+ t | -1
+(1 row)
+
+-- VACUUM shouldn't reset the cumulative statistics' live tuple count either
+SELECT n_live_tup FROM pg_stat_all_tables
+ WHERE relid = 'vac_reltuples_empty'::regclass;
+ n_live_tup
+------------
+ 4000
+(1 row)
+
+-- With prior statistics, new pages at the end of a bulk-extended relation
+-- must not make reltuples drift
+CREATE TEMP TABLE vac_reltuples_known AS
+ SELECT g AS a, repeat('x', 100) AS b FROM generate_series(1, 4000) g;
+ANALYZE vac_reltuples_known;
+SELECT reltuples FROM pg_class WHERE oid = 'vac_reltuples_known'::regclass;
+ reltuples
+-----------
+ 4000
+(1 row)
+
+SELECT count(*) FROM vac_reltuples_known;
+ count
+-------
+ 4000
+(1 row)
+
+VACUUM vac_reltuples_known;
+SELECT reltuples FROM pg_class WHERE oid = 'vac_reltuples_known'::regclass;
+ reltuples
+-----------
+ 4000
+(1 row)
+
+-- ... and reltuples must not go stale when the relation grows, even if
+-- VACUUM scans only a single page
+INSERT INTO vac_reltuples_known
+ SELECT g, repeat('x', 100) FROM generate_series(4001, 8000) g;
+SELECT count(*) FROM vac_reltuples_known;
+ count
+-------
+ 8000
+(1 row)
+
+VACUUM vac_reltuples_known;
+SELECT reltuples BETWEEN 7600 AND 8400 AS reltuples_ok FROM pg_class
+ WHERE oid = 'vac_reltuples_known'::regclass;
+ reltuples_ok
+--------------
+ t
+(1 row)
+
+DROP TABLE vac_reltuples_new, vac_reltuples_empty, vac_reltuples_known;
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 247b8e23b23..32decc388bd 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -525,3 +525,55 @@ SELECT id, pg_column_toast_chunk_id(f1) IS NULL AS f1_chunk_null,
SELECT pg_column_toast_chunk_id(f1) = :'id_2_chunk' AS same_chunk
FROM vac_rewrite_toast WHERE id = 2;
DROP TABLE vac_rewrite_toast;
+
+-- Test VACUUM's estimate of pg_class.reltuples when it skips most of the
+-- relation using the visibility map. Read-only queries can set pages
+-- all-visible during on-access pruning, so even the first VACUUM after a bulk
+-- load may scan only the tail end of the relation, including any new pages
+-- left behind by bulk extension. That's not a representative sample of the
+-- relation. Temp tables are used because their visibility horizon depends
+-- only on this backend, which makes on-access pruning deterministic.
+--
+-- Never vacuumed or analyzed: reltuples should remain unknown, rather than
+-- being extrapolated from the few pages that VACUUM scanned.
+CREATE TEMP TABLE vac_reltuples_new AS
+ SELECT g AS a, repeat('x', 100) AS b FROM generate_series(1, 4000) g;
+SELECT relpages, reltuples FROM pg_class
+ WHERE oid = 'vac_reltuples_new'::regclass;
+SELECT count(*) FROM vac_reltuples_new; -- sets most pages all-visible
+VACUUM vac_reltuples_new;
+SELECT relpages > 0 AS has_pages, reltuples FROM pg_class
+ WHERE oid = 'vac_reltuples_new'::regclass;
+-- Likewise for a relation that was empty when it was last vacuumed
+CREATE TEMP TABLE vac_reltuples_empty (a int, b text);
+VACUUM vac_reltuples_empty;
+SELECT relpages, reltuples FROM pg_class
+ WHERE oid = 'vac_reltuples_empty'::regclass;
+INSERT INTO vac_reltuples_empty
+ SELECT g, repeat('x', 100) FROM generate_series(1, 4000) g;
+SELECT count(*) FROM vac_reltuples_empty;
+SELECT pg_stat_force_next_flush();
+VACUUM vac_reltuples_empty;
+SELECT relpages > 0 AS has_pages, reltuples FROM pg_class
+ WHERE oid = 'vac_reltuples_empty'::regclass;
+-- VACUUM shouldn't reset the cumulative statistics' live tuple count either
+SELECT n_live_tup FROM pg_stat_all_tables
+ WHERE relid = 'vac_reltuples_empty'::regclass;
+-- With prior statistics, new pages at the end of a bulk-extended relation
+-- must not make reltuples drift
+CREATE TEMP TABLE vac_reltuples_known AS
+ SELECT g AS a, repeat('x', 100) AS b FROM generate_series(1, 4000) g;
+ANALYZE vac_reltuples_known;
+SELECT reltuples FROM pg_class WHERE oid = 'vac_reltuples_known'::regclass;
+SELECT count(*) FROM vac_reltuples_known;
+VACUUM vac_reltuples_known;
+SELECT reltuples FROM pg_class WHERE oid = 'vac_reltuples_known'::regclass;
+-- ... and reltuples must not go stale when the relation grows, even if
+-- VACUUM scans only a single page
+INSERT INTO vac_reltuples_known
+ SELECT g, repeat('x', 100) FROM generate_series(4001, 8000) g;
+SELECT count(*) FROM vac_reltuples_known;
+VACUUM vac_reltuples_known;
+SELECT reltuples BETWEEN 7600 AND 8400 AS reltuples_ok FROM pg_class
+ WHERE oid = 'vac_reltuples_known'::regclass;
+DROP TABLE vac_reltuples_new, vac_reltuples_empty, vac_reltuples_known;
--
2.56.0.218.g1ec904b173-goog
