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

On Fri, Nov 13, 2026 at 12:16 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
>
> Here's a reproducer for how you can get -1 for approx_tuple_count even
> in versions <= 18
>
> BEGIN;
> CREATE TABLE t(id int);
> COPY t FROM PROGRAM 'printf "1\n"' WITH (FREEZE);
> COMMIT;
> SELECT scanned_percent, approx_tuple_count FROM pgstattuple_approx('t');
>
> But the documentation doesn't say anything about how "unknown" is
> represented. In that sense, there is no documented contract about
> whether 0 or -1 means unknown. I would find it much clearer to return
> -1 when it is unknown, but we do not want to change this in
> backbranches. We only want to make it consistent. Though, people may
> be getting -1 today, as we can see from the repro. From the code it's
> clear that the intent was for the value to be clamped to 0. None of
> the other pgstattuple_approx() fields return -1 when we scanned 0 or 1
> pages. They do a variety of other things that are inconsistent and
> confusing, but they don't return -1.
>
> As for other places in Postgres, reltuples in pg_class is -1 when
> unknown, but cumulative relation stat counters return 0 when there
> isn't yet a stats entry. But these are not stats functions like
> pgstattuple_approx().
>
> My current inclination is to patch 15-master so pgstattuple_approx()
> returns 0 instead of -1, which is consistent with what the code was
> trying to do and document this. Then propose returning either NULL or
> -1 starting in master when we didn't scan enough pages to get good
> estimates but to do so for all the relevant return values from
> pgstattuple_approx().

I've pushed the last patch in the original series (the one excluding
new pages from the extrapolation calculation), however, I'm hesitant
to make this change (the -1/0 change) until we have consensus on the
desired behavior and review of the docs change. I'm pretty sure the
right immediate step is to make the 0 clamp work, though. The downside
of not committing this now is that in 19, getting -1 from
pgstat_approx() is more common than in older releases and 19 GA code
freeze is tomorrow. If we wait and commit the fix later, it will
change in 19.1 which might be confusing. However, it's probably better
to wait and get consensus and not change it twice in two versions.

Attached is what I propose doing and backpatching through 15.

- Melanie