[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: incoherent dead tuples between pg_stat_user_tables and pgstattuple?
From:       Luca Ferrari <fluca1978 () gmail ! com>
Date:       2019-09-27 18:51:31
Message-ID: CAKoxK+6u=su_ib3O50Q-_fkE0fGrorjfWDd8aVmmVd6bSHoiwA () mail ! gmail ! com
[Download RAW message or body]

On Fri, Sep 27, 2019 at 4:43 PM Tom Lane <tgl@sss.pgh.pa.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


[prev in list] [next in list] [prev in thread] [next in thread]