Re: VACUUM/ANALYZE double-count the backend's own pending table stats

Hi Ilja,

Thanks for the quick reply and for the link to the 2021 thread. I agree
that n_mod_since_analyze staying at 1300 after TRUNCATE follows the
existing policy and is not something this patch should change.

Here are the scripts and output. Build: master c1694b7, with and without
v2, --enable-cassert --enable-debug, autovacuum=off, a fresh initdb for
each run, all statements run from one psql -f session.

E4 (TRUNCATE and SAVEPOINT):

CREATE TABLE e4(id int);
INSERT INTO e4 SELECT generate_series(1,1000);
SELECT pg_stat_force_next_flush();
BEGIN;
TRUNCATE e4;
SAVEPOINT s;
INSERT INTO e4 SELECT generate_series(1,5);
ROLLBACK TO s;
COMMIT;
INSERT INTO e4 SELECT generate_series(1,300);
SELECT pg_stat_force_next_flush();
SELECT n_tup_ins, n_live_tup, n_mod_since_analyze, n_ins_since_vacuum
FROM pg_stat_user_tables WHERE relname = 'e4';

n_tup_ins | n_live_tup | mod_since_an | ins_since_vac
unpatched 1305 | 1300 | 1300 | 1305
v2 1305 | 300 | 1300 | 305

count(*) is 300. So the TRUNCATE comes before the SAVEPOINT, and the 5
rows are inserted inside the savepoint and rolled back. The 305 is the
300 committed rows plus the 5 aborted inserts, which matches what you
said. n_live_tup is correct with v2.

pg_stat_reset() cases:

E1, reset between INSERT and ANALYZE:

CREATE TABLE e1(id int);
INSERT INTO e1 SELECT generate_series(1,1000);
SELECT pg_stat_reset();
ANALYZE e1;
SELECT pg_stat_force_next_flush();

n_tup_ins | n_live_tup | mod_since_an | ins_since_vac
unpatched 1000 | 2000 | 1000 | 1000
v2 1000 | 1000 | 0 | 1000

count(*) is 1000. Here v2 fixes the double count.

E2, reset after ANALYZE, then 50 more rows:

CREATE TABLE e2(id int);
INSERT INTO e2 SELECT generate_series(1,1000);
ANALYZE e2;
SELECT pg_stat_reset();
INSERT INTO e2 SELECT generate_series(1,50);
SELECT pg_stat_force_next_flush();

n_tup_ins | n_live_tup | mod_since_an | ins_since_vac
unpatched 1050 | 1050 | 1050 | 1050
v2 1050 | 50 | 50 | 1050

count(*) is 1050. In my first mail I only gave the 50 vs 1050 and did
not explain it, sorry. Looking closer, I don't think this is a problem
with v2. With v2, ANALYZE takes in the 1000 pending inserts, and then
pg_stat_reset() clears the value ANALYZE stored, so only the 50 new rows
remain. Without the patch, the 1000 pending inserts reach shared stats
only after the reset, so the result happens to match count(*). To check
this, I ran two variants on v2:

- E2b: the same, but with pg_stat_force_next_flush() and one more
statement between the INSERT and ANALYZE, so the 1000 inserts are
flushed before ANALYZE. Result: n_tup_ins 50, n_live_tup 50,
n_mod_since_analyze 50, n_ins_since_vacuum 50.
- E2c: the same as E2, but ANALYZE runs in a second session. Result:
1050 for all four columns.

So with v2, E2 gives the same n_live_tup as when the inserts are flushed
before ANALYZE. The only odd part is that n_tup_ins and
n_ins_since_vacuum are still 1050 while n_live_tup is 50, because only
the live/dead/mod counters are taken in by ANALYZE. After a reset these
numbers are approximate anyway, so I think this is fine, maybe worth a
line in the commit message.

E3 (partitioned parent): as you said, the parent shows zero, and that is
expected.

No assertion failures or warnings in the server logs in any run.

The runner script, in case it helps:

B=$HOME/inst7460/bin; D=$HOME/d7460
rm -rf $D; $B/initdb -D $D
echo "port=5492
autovacuum=off
unix_socket_directories='/tmp'
track_counts=on" >> $D/postgresql.conf
$B/pg_ctl -D $D -l $D/log -w start
$B/psql -X -h /tmp -p 5492 -d postgres -f edge.sql
$B/pg_ctl -D $D -m fast -w stop

Regards,
Joao Rito

Em qui., 12 de nov. de 2026 às 01:43, Staroverov Ilja
<i(dot)staroverov(at)ftdata(dot)ru> escreveu:
>
> Hi Joao,
>
> Thanks for testing both builds and for spelling out the coverage and
> limitations. Yes, please send the scripts and output, particularly for
> the TRUNCATE/SAVEPOINT and pg_stat_reset() cases.
>
> On n_mod_since_analyze, 1300 is consistent with the current behavior,
> since TRUNCATE does not reset that counter. Whether it should do so
> was discussed in 2021 [1], with a patch to reset it and discussion of
> whether that would delay a useful autoanalyze after TRUNCATE. I would
> not treat this observation as a new bug or propose changing that policy
> as part of this patch. It is distinct from losing a pending TRUNCATE
> flag when a later transaction commits.
>
> The 305 for n_ins_since_vacuum is also consistent with the existing
> accounting of aborted inserts. pgstat_relation_flush_cb() uses
> tuples_inserted for this counter and explicitly notes that it includes
> aborted inserts. I'd like to see the exact SAVEPOINT placement before
> relating that result to Manu's reproducers. It does not by itself show
> the same problem.
>
> The parent's zero counters are expected here too. Inserts are counted
> on the leaf partitions, and ANALYZE of a partitioned parent explicitly
> reports zero live/dead tuples to the cumulative stats system. The
> parent's pg_class.reltuples estimate is maintained separately.
>
> [1] https://www.postgresql.org/message-id/flat/CAD21AoAwdh5FqoVobm9qbQUR%3DtJjqnX8u5-d1RVFS1V%3DEexFMw%40mail.gmail.com
>
> --
> Ilya Staroverov
>