Thread: 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)