Re: incoherent dead tuples between pg_stat_user_tables and pgstattuple?

On Fri, Sep 27, 2019 at 4:43 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> I'm not suggesting any specific scenario, because you haven't presented
> any evidence as to when those counts became off. I'm just saying that
> there are plenty of possible mechanisms for them to become so.

So far autovacuum seems the main reason for pg_stattuple to be _ahead_
of pg_stats (I stand correct).
In fact, turning off autovacuum the pg_stattuple shows the exact
result as I would have expected, and probably the pg_stats I was
referring to with n_dead_tup > 0 was due to a previous UPDATE ran
against the same table.

Apparently this produces always the same results:

testdb=# CREATE TABLE foo( bar text );
CREATE TABLE
testdb=# INSERT INTO foo
SELECT generate_series( 1, 5000000 )::text;
INSERT 0 5000000
testdb=# ANALYZE foo;
ANALYZE

testdb=# show autovacuum;
-[ RECORD 1 ]---
autovacuum | off

testdb=# select current_timestamp;
-[ RECORD 1 ]-----+------------------------------
current_timestamp | 2019-09-27 20:44:39.277217+02

Time: 0,839 ms
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 | 5000000
n_dead_tup | 0
last_autovacuum | 2019-09-27 20:29:18.571858+02
last_autoanalyze | 2019-09-27 20:29:40.889657+02

testdb=# UPDATE foo SET bar = lower( bar );
UPDATE 5000000

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 | 5000000
n_dead_tup | 5000000
last_autovacuum | 2019-09-27 20:29:18.571858+02
last_autoanalyze | 2019-09-27 20:29:40.889657+02

testdb=# SELECT * FROM pgstattuple( 'foo' );
-[ RECORD 1 ]------+----------
table_len | 362479616
tuple_count | 5000000
tuple_len | 158888896
tuple_percent | 43.83
dead_tuple_count | 5000000
dead_tuple_len | 158888896
dead_tuple_percent | 43.83
free_space | 1240672
free_percent | 0.34