Обсуждение: Relstats after VACUUM FULL and CLUSTER

> Does this seem like a bug or is it intentional?

pg_class.reltuples/relpages are only an estimate as per documentation.

However, I cannot reproduce the situation you are talking about on HEAD.
In the below example, I create a table without indexes, then insert and
delete some rows. run vacuum to update the pg_class.reltuples, then run
another delete to generate some more "recent" dead tuples.

The result shows pg_class.reltuples with the expected value,
but maybe I did not repro the same way you did?

( I am surprised that n_live_tup, n_dead_tup is off and also that
VACUUM FULL does not appear to update the stats in pg_stat_all_tables)

```
postgres=# drop table if exists t;
create table t ( id int );
alter table t set (autovacuum_enabled = off);
insert into t select n from generate_series(1, 1000000) as n;
delete from t where id between 1 and 5000;
vacuum t;
delete from t where id between 5001 and 10000;
select reltuples::int from pg_class where relname = 't';
-- might take a bit of time for n_dead_tup to be set
select n_dead_tup, n_live_tup from pg_stat_all_tables where relname = 't';
DROP TABLE
CREATE TABLE
ALTER TABLE
INSERT 0 1000000
DELETE 5000
VACUUM
DELETE 5000
 reltuples
-----------
    995000
(1 row)

 n_dead_tup | n_live_tup
------------+------------
      10000 |     985000
(1 row)

postgres=# VACUUM (verbose, full) t;
INFO:  vacuuming "public.t"
INFO:  "public.t": found 5000 removable, 990000 nonremovable row
versions in 4425 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.79 s, system: 0.02 s, elapsed: 0.86 s.
VACUUM

select reltuples::int from pg_class where relname = 't';
select n_dead_tup from pg_stat_all_tables where relname = 't';

postgres=# select reltuples::int from pg_class where relname = 't';
select n_dead_tup, n_live_tup from pg_stat_all_tables where relname = 't';
 reltuples
-----------
    990000
(1 row)

postgres=# select n_dead_tup, n_live_tup from pg_stat_all_tables where
relname = 't';
 n_dead_tup | n_live_tup
------------+------------
      10000 |     985000
(1 row)

--
Sami Imseih
Amazon Web Services (AWS)

> You need a concurrent transaction to recreate the situation. I am attaching an isolation test to show the behavior,

Thanks! That helps.
Indeed heapam_relation_copy_for_cluster and heapam_index_build_range_scan
are counting HEAPTUPLE_RECENTLY_DEAD ( tuples removed but cannot be removed )
different. In heapam_relation_copy_for_cluster they are considered live and in
heapam_index_build_range_scan they are considered dead.

in heapam_relation_copy_for_cluster
```
case HEAPTUPLE_RECENTLY_DEAD:
       *tups_recently_dead += 1;
       /* fall through */
case HEAPTUPLE_LIVE:
       /* Live or recently dead, must copy it */
       isdead = false;
        break;
```

In both cases, he recently dead tuples must be copied to the table or index, but
they should not be counted towards reltuples. So, I think we need to fix this in
heapam_relation_copy_for_cluster by probably subtracting
tups_recently_dead from num_tuples ( which is the value set in
pg_class.reltuples )
after we process all the tuples, which looks like the best fix to me.


--
Sami Imseih
Amazon Web Services (AWS)

> If you subtract recently dead from that number within the heap implementation, then it will no longer
> reflect non-removable tuples and the log message in the cluster
> function "found %.0f removable, %.0f nonremovable row versions" will no longer be correct.

Yes, that's correct. I did not pay attention to the logging aspect.

Here is a test with and without the patch. While there is a long-running
serializable transaction in another session, running an update followed
by a normal vacuum sets the reltuples value correctly to 10. A follow-up
VACUUM FULL then sets it incorrectly to 14. The patch, which sets
the num_tuples after the logging, ensures that the logging is correct
and that pg_class.reltuples matches the actual number of live tuples.


-- without the patch

test=# UPDATE stats SET a=1 WHERE a > 6;
UPDATE 4
test=# SELECT reltuples FROM pg_class WHERE relname = 'stats';
 reltuples
-----------
        -1
(1 row)

test=# vacuum verbose stats;
INFO:  vacuuming "test.public.stats"
INFO:  finished vacuuming "test.public.stats": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 14 remain, 4 are dead but not yet removable
removable cutoff: 789, which was 1 XIDs old when operation ended
new relfrozenxid: 788, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0
were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead
item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 9.159 MB/s
buffer usage: 14 hits, 0 reads, 3 dirtied
WAL usage: 4 records, 3 full page images, 24981 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
test=# SELECT reltuples FROM pg_class WHERE relname = 'stats';
 reltuples
-----------
        10
(1 row)

test=# VACUUM (verbose, FULL) stats;
INFO:  vacuuming "public.stats"
INFO:  "public.stats": found 0 removable, 14 nonremovable row versions
in 1 pages
DETAIL:  4 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
test=# SELECT reltuples FROM pg_class WHERE relname = 'stats';
 reltuples
-----------
        14
(1 row)



-- with the patch

test=# UPDATE stats SET a=1 WHERE a > 6;
UPDATE 4
test=#
test=# SELECT reltuples FROM pg_class WHERE relname = 'stats';
 reltuples
-----------
        -1
(1 row)

test=# VACUUM verbose stats;
INFO:  vacuuming "test.public.stats"
INFO:  finished vacuuming "test.public.stats": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 14 remain, 4 are dead but not yet removable
removable cutoff: 794, which was 1 XIDs old when operation ended
new relfrozenxid: 793, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0
were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead
item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 9.195 MB/s
buffer usage: 17 hits, 0 reads, 3 dirtied
WAL usage: 4 records, 3 full page images, 24981 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
test=# SELECT reltuples FROM pg_class WHERE relname = 'stats';
 reltuples
-----------
        10
(1 row)

test=# VACUUM (verbose, FULL) stats;
INFO:  vacuuming "public.stats"
INFO:  "public.stats": found 0 removable, 14 nonremovable row versions
in 1 pages
DETAIL:  4 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
test=# SELECT reltuples FROM pg_class WHERE relname = 'stats';
 reltuples
-----------
        10
(1 row)


--
Sami