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
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 |