如何评估当前表/库是否需要VACUUM

GaussDB提供了pg_autovac_status系统函数来查询某个表是否达到VACUUM阈值，结合pg_class系统表可以查出top N 死行率的表。例如，查询top 10死行表的SQL语句如下：

SELECT
C.oid AS relid,
N.nspname AS schemaname,
C.relname AS relname,
C.reltuples AS n_rel_tup,
pg_catalog.pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
pg_catalog.pg_autovac_status(C.oid).doVacuum as need_Vacuum,
CAST(n_dead_tup / C.reltuples AS decimal(10, 2)) AS dead_radio
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.relkind IN ('r') and C.reltuples > 0
GROUP BY C.oid, N.nspname, C.relname, C.reltuples, need_Vacuum
order by dead_radio desc limit 10;

执行结果如下：

 relid | schemaname |         relname         | n_rel_tup | n_dead_tup | need_vacuum | dead_radio
-------+------------+-------------------------+-----------+------------+-------------+------------
  2604 | pg_catalog | pg_attrdef              |         1 |         14 |             |      14.00
  2606 | pg_catalog | pg_constraint           |        12 |         15 |             |       1.25
  3118 | pg_catalog | pg_foreign_table        |         2 |          2 |             |       1.00
  3079 | pg_catalog | pg_extension            |        24 |         15 |             |        .63
  2601 | pg_catalog | pg_am                   |        11 |          4 |             |        .36
  1417 | pg_catalog | pg_foreign_server       |         4 |          1 |             |        .25
  2610 | pg_catalog | pg_index                |       421 |         82 |             |        .19
  2328 | pg_catalog | pg_foreign_data_wrapper |         6 |          1 |             |        .17
  2619 | pg_catalog | pg_statistic            |      1231 |        187 |             |        .15
  2609 | pg_catalog | pg_description          |      4748 |         25 |             |        .01
(10 rows)