Sync up our various ways of estimating pg_class.reltuples.

VACUUM thought that reltuples represents the total number of tuples in
the relation, while ANALYZE counted only live tuples.  This can cause
"flapping" in the value when background vacuums and analyzes happen
separately.  The planner's use of reltuples essentially assumes that
it's the count of live (visible) tuples, so let's standardize on having
it mean live tuples.

Another issue is that the definition of "live tuple" isn't totally clear;
what should be done with INSERT_IN_PROGRESS or DELETE_IN_PROGRESS tuples?
ANALYZE's choices in this regard are made on the assumption that if the
originating transaction commits at all, it will happen after ANALYZE
finishes, so we should ignore the effects of the in-progress transaction
--- unless it is our own transaction, and then we should count it.
Let's propagate this definition into VACUUM, too.

Likewise propagate this definition into CREATE INDEX, and into
contrib/pgstattuple's pgstattuple_approx() function.

Tomas Vondra, reviewed by Haribabu Kommi, some corrections by me

Discussion: https://postgr.es/m/16db4468-edfa-830a-f921-39a50498e77e@2ndquadrant.com

1 files changed, 21 insertions, 15 deletions

diff --git a/contrib/pgstattuple/pgstatapprox.c b/contrib/pgstattuple/pgstatapprox.c
index 474c3bd517..ef33cacec6 100644
--- a/contrib/pgstattuple/pgstatapprox.c
+++ b/contrib/pgstattuple/pgstatapprox.c
@@ -68,7 +68,6 @@ statapprox_heap(Relation rel, output_type *stat)
 	Buffer		vmbuffer = InvalidBuffer;
 	BufferAccessStrategy bstrategy;
 	TransactionId OldestXmin;
-	uint64		misc_count = 0;
 
 	OldestXmin = GetOldestXmin(rel, PROCARRAY_FLAGS_VACUUM);
 	bstrategy = GetAccessStrategy(BAS_BULKREAD);
@@ -114,14 +113,15 @@ statapprox_heap(Relation rel, output_type *stat)
 		else
 			stat->free_space += BLCKSZ - SizeOfPageHeaderData;
 
+		/* We may count the page as scanned even if it's new/empty */
+		scanned++;
+
 		if (PageIsNew(page) || PageIsEmpty(page))
 		{
 			UnlockReleaseBuffer(buf);
 			continue;
 		}
 
-		scanned++;
-
 		/*
 		 * Look at each tuple on the page and decide whether it's live or
 		 * dead, then count it and its size. Unlike lazy_scan_heap, we can
@@ -153,25 +153,23 @@ statapprox_heap(Relation rel, output_type *stat)
 			tuple.t_tableOid = RelationGetRelid(rel);
 
 			/*
-			 * We count live and dead tuples, but we also need to add up
-			 * others in order to feed vac_estimate_reltuples.
+			 * We follow VACUUM's lead in counting INSERT_IN_PROGRESS tuples
+			 * as "dead" while DELETE_IN_PROGRESS tuples are "live".  We don't
+			 * bother distinguishing tuples inserted/deleted by our own
+			 * transaction.
 			 */
 			switch (HeapTupleSatisfiesVacuum(&tuple, OldestXmin, buf))
 			{
-				case HEAPTUPLE_RECENTLY_DEAD:
-					misc_count++;
-					/* Fall through */
-				case HEAPTUPLE_DEAD:
-					stat->dead_tuple_len += tuple.t_len;
-					stat->dead_tuple_count++;
-					break;
 				case HEAPTUPLE_LIVE:
+				case HEAPTUPLE_DELETE_IN_PROGRESS:
 					stat->tuple_len += tuple.t_len;
 					stat->tuple_count++;
 					break;
+				case HEAPTUPLE_DEAD:
+				case HEAPTUPLE_RECENTLY_DEAD:
 				case HEAPTUPLE_INSERT_IN_PROGRESS:
-				case HEAPTUPLE_DELETE_IN_PROGRESS:
-					misc_count++;
+					stat->dead_tuple_len += tuple.t_len;
+					stat->dead_tuple_count++;
 					break;
 				default:
 					elog(ERROR, "unexpected HeapTupleSatisfiesVacuum result");
@@ -184,8 +182,16 @@ statapprox_heap(Relation rel, output_type *stat)
 
 	stat->table_len = (uint64) nblocks * BLCKSZ;
 
+	/*
+	 * We don't know how many tuples are in the pages we didn't scan, so
+	 * extrapolate the live-tuple count to the whole table in the same way
+	 * that VACUUM does.  (Like VACUUM, we're not taking a random sample, so
+	 * just extrapolating linearly seems unsafe.)  There should be no dead
+	 * 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, scanned,
-											   stat->tuple_count + misc_count);
+											   stat->tuple_count);
 
 	/*
 	 * Calculate percentages if the relation has one or more pages.