incoherent dead tuples between pg_stat_user_tables and pgstattuple?

From: Luca Ferrari <fluca1978(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: incoherent dead tuples between pg_stat_user_tables and pgstattuple?
Date: 2019-09-27 10:37:08
Message-ID: CAKoxK+4guWzkhxgzrr5G5WfB+sHwbKc8DJdVaOsO9mVgU92f2Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marco Ippolito 2019-09-27 10:37:23 Re: "Failed to connect to Postgres database"
Previous Message Daulat Ram 2019-09-27 05:25:28 RE: Monitor Postgres database status on Docker