On Wed, Oct 21, 2026 at 2:37 AM Vaibhav Jain <[email protected]> wrote:
>
> 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.

Thanks for the report. While reviewing the on-access VM setting
feature, an LLM did flag this misestimation but because much of it was
possible with repeated vacuums before and because a subsequent analyze
would fix many cases after, I didn't worry about it and thought of it
as a future enhancement.

The cases you've pointed out where a subsequent analyze doesn't fix
the estimate are problematic, though. So, I think we do have to fix
it. And, of course, some of the cases wouldn't ordinarily be hit
without on-access VM setting.

> 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.

I think 2-4 can be done in a preliminary commit and 1 in its own commit.

We'll need to preserve ABI compatibility of vac_estimate_reltuples()
in 19 since it is public. It's a bit unfortunate, but I think we can
just add and use a wrapper function. Existing users of
vac_estimate_reltuples() can upgrade to the new extended version if
they so choose. That's what I've done in the attached. On master, I'll
just modify vac_estimate_reltuples() directly.

A fun side effect of this work is that we can now set new pages in the
VM during vacuum. I think we can do a nice follow-on commit to master
to do this. It seems like a win.

> 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.

I think it's fine to set reltuples back to -1 when we no longer have a
good estimate. I don't think it's a problem at all. a) throws away one
of the most common useful cases of on-access VM setting and b) isn't a
viable dependency. The planner can cope with unknown reltuples until
the next analyze. And remember we only set reltuples from 0 -> -1 when
a table was empty when it was last vacuumed or analyzed.

Attached is what I propose to backpatch to 19. It is basically the
same as what you proposed but split up a bit differently.

- Melanie
From 5ecbf41a188f468331301da3685696b58120b85b Mon Sep 17 00:00:00 2001
From: Melanie Plageman <[email protected]>
Date: Wed, 11 Nov 2026 17:11:18 -0500
Subject: [PATCH v2 1/2] Avoid unreliable VACUUM reltuples estimates

VACUUM protects pg_class.reltuples from tiny, non-random samples by
retaining the old estimate when it scans very few pages. It did so even
when the relation size changed, preserving a stale absolute count instead
of scaling the old tuple density to the new size.

VACUUM also reported an unknown live-tuple estimate as zero to the
cumulative statistics system, overwriting its existing n_live_tup
estimate.

Commit b46e1e54d ("Allow on-access pruning to set pages all-visible")
made these pre-existing problems reachable in ordinary workloads. A read
can now mark most pages all-visible after INSERT, COPY, or CREATE TABLE AS
but before VACUUM or ANALYZE establishes a tuple-density estimate.
VACUUM may consequently skip nearly all populated pages and scan only a
tiny, non-random tail sample while reltuples is still unknown or reflects
a previously empty table.

When no prior density is available, treat a sample of at most one page or
less than two percent of the relation as insufficient, and leave
reltuples unknown rather than extrapolating. Apply the small-sample
stability rule only when the relation size is unchanged; otherwise, scale
the old density to the new size.

Pass an unknown estimate to the cumulative statistics system as -1, and
preserve its existing n_live_tup estimate rather than replacing it with
zero.
---
 src/backend/commands/vacuum.c                | 30 +++++++++++++-------
 src/backend/utils/activity/pgstat_relation.c |  4 ++-
 2 files changed, 22 insertions(+), 12 deletions(-)

diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 3a5c1efd6ac..c46de3c1a86 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1371,6 +1371,20 @@ vac_estimate_reltuples(Relation relation,
 	if (scanned_pages >= total_pages)
 		return scanned_tuples;
 
+	/*
+	 * With no prior density, a tiny non-random sample is not sufficient to
+	 * estimate the relation's tuple count.  This can happen when on-access
+	 * pruning marks most pages all-visible before the first VACUUM.
+	 */
+	if (old_rel_tuples < 0 || old_rel_pages == 0)
+	{
+		if (scanned_pages <= 1 ||
+			scanned_pages < (double) total_pages * 0.02)
+			return -1;
+
+		return floor((scanned_tuples / scanned_pages) * total_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
@@ -1381,23 +1395,17 @@ vac_estimate_reltuples(Relation relation,
 	 * 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.
+	 * value when at most one page was scanned.  When the relation size has
+	 * changed, use the old density to account for the added or removed pages
+	 * instead of preserving a stale tuple count.
 	 *
 	 * (Note: we might be returning -1 here.)
 	 */
 	if (old_rel_pages == total_pages &&
-		scanned_pages < (double) total_pages * 0.02)
-		return old_rel_tuples;
-	if (scanned_pages <= 1)
+		(scanned_pages <= 1 ||
+		 scanned_pages < (double) total_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 5c70543ba88..4be20826cd1 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -258,6 +258,7 @@ pgstat_drop_relation(Relation rel)
 
 /*
  * Report that the table was just vacuumed and flush IO statistics.
+ * livetuples is -1 if VACUUM could not produce a new estimate.
  */
 void
 pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples,
@@ -284,7 +285,8 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples,
 	shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
 	tabentry = &shtabentry->stats;
 
-	tabentry->live_tuples = livetuples;
+	if (livetuples >= 0)
+		tabentry->live_tuples = livetuples;
 	tabentry->dead_tuples = deadtuples;
 
 	/*
-- 
2.43.0

From 95fc25f6f5eb3b8adce7c3fb3eb099f37f09582b Mon Sep 17 00:00:00 2001
From: Melanie Plageman <[email protected]>
Date: Wed, 11 Nov 2026 17:12:11 -0500
Subject: [PATCH v2 2/2] Exclude new pages from VACUUM reltuples estimates

VACUUM always scans PageIsNew blocks because they have no visibility map
bits. They could already distort estimates when other pages were skipped
by repeated VACUUMs. On-access VM setting makes this more likely by
allowing populated pages to be skipped before the first VACUUM.

Count PageIsNew blocks separately in VACUUM and pgstattuple_approx(),
and exclude them from the sampled pages and sampleable population.

Preserve the existing vac_estimate_reltuples() API with a compatibility
wrapper around a new extended function that accepts the PageIsNew count.
---
 contrib/pgstattuple/pgstatapprox.c   | 16 ++++-
 src/backend/access/heap/vacuumlazy.c | 25 +++++---
 src/backend/commands/vacuum.c        | 44 +++++++++----
 src/include/commands/vacuum.h        |  5 ++
 src/test/regress/expected/vacuum.out | 93 ++++++++++++++++++++++++++++
 src/test/regress/sql/vacuum.sql      | 43 +++++++++++++
 6 files changed, 205 insertions(+), 21 deletions(-)

diff --git a/contrib/pgstattuple/pgstatapprox.c b/contrib/pgstattuple/pgstatapprox.c
index ade1b3bcebd..b1b02ac9bbb 100644
--- a/contrib/pgstattuple/pgstatapprox.c
+++ b/contrib/pgstattuple/pgstatapprox.c
@@ -57,6 +57,7 @@ typedef struct StatApproxReadStreamPrivate
 	BlockNumber current_blocknum;
 	BlockNumber nblocks;
 	BlockNumber scanned;		/* count of pages actually read */
+	BlockNumber scanned_new;	/* count of scanned PageIsNew pages */
 	Buffer		vmbuffer;		/* for VM lookups */
 } StatApproxReadStreamPrivate;
 
@@ -133,6 +134,7 @@ statapprox_heap(Relation rel, output_type *stat)
 	p.current_blocknum = 0;
 	p.nblocks = nblocks;
 	p.scanned = 0;
+	p.scanned_new = 0;
 	p.vmbuffer = InvalidBuffer;
 
 	/*
@@ -168,7 +170,14 @@ statapprox_heap(Relation rel, output_type *stat)
 
 		stat->free_space += PageGetExactFreeSpace(page);
 
-		if (PageIsNew(page) || PageIsEmpty(page))
+		if (PageIsNew(page))
+		{
+			p.scanned_new++;
+			UnlockReleaseBuffer(buf);
+			continue;
+		}
+
+		if (PageIsEmpty(page))
 		{
 			UnlockReleaseBuffer(buf);
 			continue;
@@ -245,8 +254,9 @@ statapprox_heap(Relation rel, output_type *stat)
 	 * tuples in all-visible pages, so no correction is needed for that, and
 	 * we already accounted for the space in those pages, too.
 	 */
-	stat->tuple_count = vac_estimate_reltuples(rel, nblocks, p.scanned,
-											   stat->tuple_count);
+	stat->tuple_count = vac_estimate_reltuples_extended(rel, nblocks, p.scanned,
+														p.scanned_new,
+														stat->tuple_count);
 
 	/* It's not clear if we could get -1 here, but be safe. */
 	stat->tuple_count = Max(stat->tuple_count, 0);
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 3e2fc8f0b24..6c1611d7e1f 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -305,6 +305,7 @@ typedef struct LVRelState
 
 	BlockNumber rel_pages;		/* total number of pages */
 	BlockNumber scanned_pages;	/* # pages examined (not skipped via VM) */
+	BlockNumber scanned_new_pages;	/* # scanned pages that were PageIsNew */
 
 	/*
 	 * Count of all-visible blocks eagerly scanned (for logging only). This
@@ -740,6 +741,7 @@ heap_vacuum_rel(Relation rel, const VacuumParams *params,
 
 	/* Initialize page counters explicitly (be tidy) */
 	vacrel->scanned_pages = 0;
+	vacrel->scanned_new_pages = 0;
 	vacrel->eager_scanned_pages = 0;
 	vacrel->removed_pages = 0;
 	vacrel->new_frozen_tuple_pages = 0;
@@ -955,9 +957,8 @@ 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 can be -1 when VACUUM skipped nearly all initialized
+	 * pages and had no prior tuple-density estimate.
 	 */
 	vac_update_relstats(rel, new_rel_pages, vacrel->new_live_tuples,
 						new_rel_allvisible, new_rel_allfrozen,
@@ -976,7 +977,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);
@@ -1595,9 +1596,11 @@ lazy_scan_heap(LVRelState *vacrel)
 								 rel_pages);
 
 	/* 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->live_tuples);
+	vacrel->new_live_tuples =
+		vac_estimate_reltuples_extended(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
@@ -1894,6 +1897,14 @@ lazy_scan_new_or_empty(LVRelState *vacrel, Buffer buf, BlockNumber blkno,
 
 	if (PageIsNew(page))
 	{
+		/*
+		 * New pages are never skipped using the visibility map, so their
+		 * known zero tuple count is not a representative sample of the
+		 * relation. Track them separately so that they can be excluded when
+		 * estimating reltuples.
+		 */
+		vacrel->scanned_new_pages++;
+
 		/*
 		 * All-zeroes pages can be left over if either a backend extends the
 		 * relation by a single page, but crashes before the newly initialized
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index c46de3c1a86..2e5c3872d9f 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1343,7 +1343,8 @@ vacuum_xid_failsafe_check(const struct VacuumCutoffs *cutoffs)
 }
 
 /*
- * vac_estimate_reltuples() -- estimate the new value for pg_class.reltuples
+ * vac_estimate_reltuples_extended() -- estimate the new value for
+ * pg_class.reltuples
  *
  *		If we scanned the whole relation then we should just use the count of
  *		live tuples seen; but if we did not, we should not blindly extrapolate
@@ -1356,21 +1357,29 @@ vacuum_xid_failsafe_check(const struct VacuumCutoffs *cutoffs)
  *		pg_class.reltuples is defined that way.
  */
 double
-vac_estimate_reltuples(Relation relation,
-					   BlockNumber total_pages,
-					   BlockNumber scanned_pages,
-					   double scanned_tuples)
+vac_estimate_reltuples_extended(Relation relation,
+								BlockNumber total_pages,
+								BlockNumber scanned_pages,
+								BlockNumber scanned_new_pages,
+								double scanned_tuples)
 {
 	BlockNumber old_rel_pages = relation->rd_rel->relpages;
+	BlockNumber sampled_pages;
+	BlockNumber sampleable_pages;
 	double		old_rel_tuples = relation->rd_rel->reltuples;
 	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;
 
+	sampled_pages = scanned_pages - scanned_new_pages;
+	sampleable_pages = total_pages - scanned_new_pages;
+
 	/*
 	 * With no prior density, a tiny non-random sample is not sufficient to
 	 * estimate the relation's tuple count.  This can happen when on-access
@@ -1378,11 +1387,11 @@ vac_estimate_reltuples(Relation relation,
 	 */
 	if (old_rel_tuples < 0 || old_rel_pages == 0)
 	{
-		if (scanned_pages <= 1 ||
-			scanned_pages < (double) total_pages * 0.02)
+		if (sampled_pages <= 1 ||
+			sampled_pages < (double) sampleable_pages * 0.02)
 			return -1;
 
-		return floor((scanned_tuples / scanned_pages) * total_pages + 0.5);
+		return floor((scanned_tuples / sampled_pages) * sampleable_pages + 0.5);
 	}
 
 	/*
@@ -1402,8 +1411,8 @@ vac_estimate_reltuples(Relation relation,
 	 * (Note: we might be returning -1 here.)
 	 */
 	if (old_rel_pages == total_pages &&
-		(scanned_pages <= 1 ||
-		 scanned_pages < (double) total_pages * 0.02))
+		(sampled_pages <= 1 ||
+		 sampled_pages < (double) sampleable_pages * 0.02))
 		return old_rel_tuples;
 
 	/*
@@ -1413,11 +1422,24 @@ vac_estimate_reltuples(Relation relation,
 	 * and finally add on the number of tuples in the scanned pages.
 	 */
 	old_density = old_rel_tuples / old_rel_pages;
-	unscanned_pages = (double) total_pages - (double) scanned_pages;
+	unscanned_pages = (double) sampleable_pages - (double) sampled_pages;
 	total_tuples = old_density * unscanned_pages + scanned_tuples;
 	return floor(total_tuples + 0.5);
 }
 
+/*
+ * Compatibility wrapper for callers that don't distinguish PageIsNew pages.
+ */
+double
+vac_estimate_reltuples(Relation relation,
+					   BlockNumber total_pages,
+					   BlockNumber scanned_pages,
+					   double scanned_tuples)
+{
+	return vac_estimate_reltuples_extended(relation, total_pages,
+										   scanned_pages, 0, scanned_tuples);
+}
+
 
 /*
  *	vac_update_relstats() -- update statistics for one relation
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 89fa1fcd261..a339804ad80 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -380,6 +380,11 @@ extern double vac_estimate_reltuples(Relation relation,
 									 BlockNumber total_pages,
 									 BlockNumber scanned_pages,
 									 double scanned_tuples);
+extern double vac_estimate_reltuples_extended(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,
 								double num_tuples,
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index d4696bc3325..906b958520d 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -730,3 +730,96 @@ SELECT pg_column_toast_chunk_id(f1) = :'id_2_chunk' AS same_chunk
 (1 row)
 
 DROP TABLE vac_rewrite_toast;
+-- Test estimates when on-access VM setting lets VACUUM skip most pages.
+CREATE TEMP TABLE vac_reltuples_new AS
+  SELECT g AS a, repeat('x', 100) AS b FROM generate_series(1, 4000) g;
+SELECT count(*) FROM vac_reltuples_new;
+ count 
+-------
+  4000
+(1 row)
+
+VACUUM vac_reltuples_new;
+SELECT reltuples = -1 AS reltuples_unknown
+  FROM pg_class WHERE oid = 'vac_reltuples_new'::regclass;
+ reltuples_unknown 
+-------------------
+ t
+(1 row)
+
+ANALYZE vac_reltuples_new;
+SELECT reltuples = 4000 AS reltuples_updated
+  FROM pg_class WHERE oid = 'vac_reltuples_new'::regclass;
+ reltuples_updated 
+-------------------
+ t
+(1 row)
+
+-- Likewise for a relation that was empty when it was last vacuumed.
+TRUNCATE vac_reltuples_new;
+VACUUM vac_reltuples_new;
+INSERT INTO vac_reltuples_new
+  SELECT g, repeat('x', 100) FROM generate_series(1, 4000) g;
+SELECT count(*) FROM vac_reltuples_new;
+ count 
+-------
+  4000
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+VACUUM vac_reltuples_new;
+SELECT reltuples = -1 AS reltuples_unknown
+  FROM pg_class WHERE oid = 'vac_reltuples_new'::regclass;
+ reltuples_unknown 
+-------------------
+ t
+(1 row)
+
+SELECT n_live_tup = 4000 AS n_live_tup_preserved
+  FROM pg_stat_all_tables WHERE relid = 'vac_reltuples_new'::regclass;
+ n_live_tup_preserved 
+----------------------
+ t
+(1 row)
+
+-- Preserve a valid estimate when the only scanned tail pages were new.
+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 count(*) FROM vac_reltuples_known;
+ count 
+-------
+  4000
+(1 row)
+
+VACUUM vac_reltuples_known;
+SELECT reltuples = 4000 AS reltuples_preserved
+  FROM pg_class WHERE oid = 'vac_reltuples_known'::regclass;
+ reltuples_preserved 
+---------------------
+ t
+(1 row)
+
+-- Scale a valid prior density when the relation has grown.
+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_known;
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 247b8e23b23..2a401d497ea 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -525,3 +525,46 @@ 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 estimates when on-access VM setting lets VACUUM skip most pages.
+CREATE TEMP TABLE vac_reltuples_new AS
+  SELECT g AS a, repeat('x', 100) AS b FROM generate_series(1, 4000) g;
+SELECT count(*) FROM vac_reltuples_new;
+VACUUM vac_reltuples_new;
+SELECT reltuples = -1 AS reltuples_unknown
+  FROM pg_class WHERE oid = 'vac_reltuples_new'::regclass;
+ANALYZE vac_reltuples_new;
+SELECT reltuples = 4000 AS reltuples_updated
+  FROM pg_class WHERE oid = 'vac_reltuples_new'::regclass;
+
+-- Likewise for a relation that was empty when it was last vacuumed.
+TRUNCATE vac_reltuples_new;
+VACUUM vac_reltuples_new;
+INSERT INTO vac_reltuples_new
+  SELECT g, repeat('x', 100) FROM generate_series(1, 4000) g;
+SELECT count(*) FROM vac_reltuples_new;
+SELECT pg_stat_force_next_flush();
+VACUUM vac_reltuples_new;
+SELECT reltuples = -1 AS reltuples_unknown
+  FROM pg_class WHERE oid = 'vac_reltuples_new'::regclass;
+SELECT n_live_tup = 4000 AS n_live_tup_preserved
+  FROM pg_stat_all_tables WHERE relid = 'vac_reltuples_new'::regclass;
+
+-- Preserve a valid estimate when the only scanned tail pages were new.
+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 count(*) FROM vac_reltuples_known;
+VACUUM vac_reltuples_known;
+SELECT reltuples = 4000 AS reltuples_preserved
+  FROM pg_class WHERE oid = 'vac_reltuples_known'::regclass;
+
+-- Scale a valid prior density when the relation has grown.
+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_known;
-- 
2.43.0

Reply via email to