Database Reference
In-Depth Information
N.nspname, C.relname,
pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,
pg_stat_get_tuples_hot_updated(C.oid)::real /
pg_stat_get_tuples_updated(C.oid) AS HOT_update_ratio,
pg_stat_get_live_tuples(C.oid) AS n_live_tup,
pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
C.reltuples AS reltuples,round(
current_setting('autovacuum_vacuum_threshold')::integer
+current_setting('autovacuum_vacuum_scale_factor')::numeric
* C.reltuples)
AS av_threshold, date_trunc('minute',greatest(pg_stat_get_last_
vacuum_time(C.oid),pg_stat_get_last_autovacuum_time(C.oid))) AS last_
vacuum, date_trunc('minute',greatest(pg_stat_get_last_analyze_time(C.
oid),pg_stat_get_last_analyze_time(C.oid))) AS last_analyze
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', 't')
AND N.nspname NOT IN ('pg_catalog', 'information_schema') AND
N.nspname !~ '^pg_toast'
) AS av
ORDER BY av_needed DESC,n_dead_tup DESC;
which we can then use to look at individual tables as follows:
postgres=# \x
postgres=# SELECT * FROM av_needed
WHERE relation = 'public.pgbench_accounts';
-[ RECORD 1 ]----+------------------------
nspname | public
relname | pgbench_accounts
n_tup_ins | 100001
n_tup_upd | 117201
n_tup_del | 1
hot_update_ratio | 0.123454578032611
n_live_tup | 100000
n_dead_tup | 0
reltuples | 100000
av_threshold | 20050
last_vacuum | 2010-04-29 01:33:00+01
last_analyze | 2010-04-28 15:21:00+01
av_needed | f
pct_dead | 0
