《Postgres DBA: 9.查看表中被删除的元组个数》

1. SQL语句

SELECT 
    schemaname
    ,relname
    ,n_live_tup
    ,n_dead_tup
    ,last_autovacuum
FROM pg_stat_all_tables
WHERE schemaname='public'
ORDER BY n_dead_tup

2.结果

testdb=#
 schemaname |   relname   | n_live_tup | n_dead_tup | last_autovacuum
------------+-------------+------------+------------+-----------------
 public     | superperson |          0 |          0 |
 public     | person      |          8 |          4 |
(2 rows)

3.比率

SELECT 
    schemaname
    ,relname
    ,n_live_tup
    ,n_dead_tup
    ,last_autovacuum
 ,n_dead_tup
 ,current_setting('autovacuum_vacuum_scale_factor')::float8 as fac
 ,current_setting('autovacuum_vacuum_threshold')::float8 as thred
    ,n_dead_tup
    /(n_live_tup
      * current_setting('autovacuum_vacuum_scale_factor')::float8
      + current_setting('autovacuum_vacuum_threshold')::float8)   as ratio
      
FROM pg_stat_all_tables
WHERE schemaname='public'
ORDER BY n_dead_tup
    /(n_live_tup
      * current_setting('autovacuum_vacuum_scale_factor')::float8
      + current_setting('autovacuum_vacuum_threshold')::float8)
     DESC

4.结果

testdb-#  
 schemaname |   relname   | n_live_tup | n_dead_tup | last_autovacuum | n_dead_tup | fac | thred |
       ratio
------------+-------------+------------+------------+-----------------+------------+-----+-------+
--------------------
 public     | person      |          8 |          4 |                 |          4 | 0.2 |    50 |
 0.0775193798449612
 public     | superperson |          0 |          0 |                 |          0 | 0.2 |    50 |
                  0
(2 rows)