PostgreSQLにおいて、デッドタプルの割合 や いつvacuum, analyzeが実行されたか を把握したい場面があります。
そういう時によく実行しているSQLのメモです。
SQL
SELECT relname AS relname, n_live_tup AS live_tupple, n_dead_tup AS dead_tupple, COALESCE(ROUND(COALESCE(n_dead_tup, 0) / NULLIF(n_live_tup, 0), 2), 0) AS ratio_of_dead_to_each_live, COALESCE(ROUND(COALESCE(n_dead_tup, 0) * 100 / NULLIF(COALESCE(n_live_tup, 0) + COALESCE(n_dead_tup, 0), 0), 2), 0) AS percent_of_dead, last_vacuum AS last_vacuum_at, last_autovacuum AS last_autovacuum_at, last_analyze AS last_analyze_at, last_autoanalyze AS last_autoanalyze_at FROM pg_stat_user_tables ORDER BY percent_of_dead DESC, dead_tupple DESC, relname;
実行結果の例
relname | live_tupple | dead_tupple | ratio_of_dead_to_each_live | percent_of_dead | last_vacuum_at | last_autovacuum_at | last_analyze_at | last_autoanalyze_at ------------------+-------------+-------------+----------------------------+-----------------+-------------------------------+-------------------------------+-------------------------------+------------------------------- table_data_0001 | 0 | 51 | 0 | 100.00 | | | | 2022-06-21 13:30:59.320351+00 table_data_0002 | 0 | 50 | 0 | 100.00 | | 2022-07-08 14:00:10.48406+00 | | 2022-07-08 14:00:10.51093+00 table_data_0003 | 1 | 37 | 37.00 | 97.00 | | 2022-06-16 21:31:32.664391+00 | | 2022-06-16 21:23:30.586964+00 table_data_0004 | 1 | 1 | 1.00 | 50.00 | | | | table_data_0005 | 1087500 | 2050 | 0.00 | 0.00 | 2022-06-19 09:34:04.391395+00 | 2022-06-16 17:05:46.724842+00 | 2022-06-19 09:34:04.873403+00 | 2022-06-18 22:10:32.936799+00 table_data_0006 | 0 | 0 | 0 | 0 | | | | table_data_0007 | 1 | 0 | 0.00 | 0.00 | | | | table_data_0008 | 0 | 0 | 0 | 0 | 2022-06-19 09:33:44.950117+00 | 2022-07-08 17:55:51.345513+00 | 2022-06-19 09:33:44.950612+00 | 2022-07-08 17:55:51.346155+00
特定のテーブルについてだけ調べたい場合は、上記SQL に WHERE relname = 'テーブル名'
を加えます。