Re: [BUG / PATCH] PG19: VACUUM misestimates reltuples (often 0) after on-access VM setting

On Thu, Nov 12, 2026 at 5:51 PM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
>
> Is -1 a valid value for approx_tuple_count?
>
> CREATE EXTENSION pgstattuple;
> CREATE TABLE t (id int, pad text) WITH (autovacuum_enabled = off);
> INSERT INTO t SELECT g, repeat('x', 100) FROM generate_series(1, 40000) g;
> SELECT count(*) FROM t;
> UPDATE t SET pad = 'y' WHERE id IN (1, 20000);
> SELECT scanned_percent, approx_tuple_count FROM pgstattuple_approx('t');
>
> Before 1a001d930ee (the already committed part) this shows
> approx_tuple_count 35420 to me, after -1.
...
> Without the UPDATE, it also returns -1 and already did so before, so
> this is not entirely new, but I don't see this being mentioned
> anywhere

The pgstattuple_approx() after the UPDATE only scans 2 pages (if run
before the update, it scans 0 or 1 pages) because the count(*) sets
everything all-visible in the VM and pgstattuple skips those pages.
The intent of 1a001d930ee was to avoid extrapolating from such small
samples, which, after my work to set the VM on-access, end up
happening more often because pgstattuple and vacuum skip all-visible
pages. So I did mean for a sentinel/unknown value to be returned here.
However, it looks like the value should be 0 and not -1.

(In fact, if you go back and try this on PG 18, you'll notice that
approx_tuple_count is 40000 before or after the UPDATE because the
count(*) doesn't set the pages all-visible)

I didn't see the result of vac_estimate_reltuples() (which returns a
double) was being stored in a uint64 in statapprox_heap(). This seems
like it was already wrong since it was always possible for
vac_estimate_reltuples() to return -1. That was then getting stored as
UINT64_MAX in stat->tuple_count and then SQL expects bigint so I guess
it was converting it back to -1. An altogether undesirable
signed/unsigned conversion mess.

This ill-fated part you pointed out:

/* It's not clear if we could get -1 here, but be safe. */
stat->tuple_count = Max(stat->tuple_count, 0);

happens after we have already converted the value to unsigned (too
late). So, to clamp it, we should save the return value of
vac_estimate_reltuples() in a double and clamp it to 0 before
assigning to stat->tuple_count.

- Melanie