Hello,
running 11.5 I've some misconception about pgstatuple: it seems it
lags behind normal statistics.
I've almost double a table with an update.
The result is:
testdb=# select n_live_tup, n_dead_tup, last_autovacuum,
last_autoanalyze from pg_stat_user_tables where relname = 'foo';
-[ RECORD 1 ]----+------------------------------
n_live_tup | 5021619
n_dead_tup | 5000000
last_autovacuum | 2019-09-27 12:05:36.072376+02
last_autoanalyze | 2019-09-27 12:06:16.538413+02
but pgstatuple provides a quite different output, saying the table is
not full of dead rows:
testdb=# SELECT * FROM pgstattuple( 'foo' );
-[ RECORD 1 ]------+----------
table_len | 930897920
tuple_count | 5000000
tuple_len | 439595972
tuple_percent | 47.22
dead_tuple_count | 88
dead_tuple_len | 7744
dead_tuple_percent | 0
free_space | 447716392
free_percent | 48.1
Even after running an ANALYZE, pgstattuple provides the same results.
After a VACUUM FULL ANALYZE the world is as I would expect it to be:
testdb=# select n_live_tup, n_dead_tup, last_autovacuum,
last_autoanalyze from pg_stat_user_tables where relname = 'foo';
-[ RECORD 1 ]----+------------------------------
n_live_tup | 5000117
n_dead_tup | 0
last_autovacuum | 2019-09-27 12:05:36.072376+02
last_autoanalyze | 2019-09-27 12:06:16.538413+02
Time: 16,159 ms
testdb=# SELECT * FROM pgstattuple( 'foo' );
-[ RECORD 1 ]------+----------
table_len | 465453056
tuple_count | 5000000
tuple_len | 439595972
tuple_percent | 94.44
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 3870144
free_percent | 0.83
Am I missing something?
Thanks,
Luca
