【PostgreSQL】pg_stat_user_tablesでSeq ScanとIndex Scanの比率を確認する

pg_stat_user_tablesとは

pg_stat_user_tablesは、テーブルごとのアクセス状況を集計するビューである。Seq Scan(全件走査)とIndex Scanのそれぞれの回数を確認できるため、インデックスが不足しているテーブルの発見に使える。

SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_live_tup
FROM pg_stat_user_tables
ORDER BY relname;

 schemaname |   relname   | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_live_tup
------------+-------------+----------+--------------+----------+---------------+------------
 public     | accounts    |       42 |     12600000 |        8 |             8 |     300000
 public     | prefectures |      120 |         5640 |        0 |             0 |         47

seq_scan: テーブルに対してSeq Scanを実行した回数
seq_tup_read: Seq Scanで読み取った行数の累計
idx_scan: テーブルのインデックスを使った検索の回数
idx_tup_fetch: インデックス経由で取得した行数の累計
n_live_tup: テーブルの推定行数

accountsテーブルはSeq Scanが42回に対してIndex Scanが8回であり、Seq Scanが優勢である。seq_tup_readは1260万行に達しており、42回のSeq Scanで毎回30万行すべてを読んでいる。

Seq ScanとIndex Scanの比率を計算する

全体に占めるIndex Scanの割合を計算すると、テーブルごとの傾向を比較できる。

SELECT
  relname,
  seq_scan,
  idx_scan,
  round(100.0 * idx_scan / nullif(seq_scan + idx_scan, 0), 1) AS idx_scan_ratio
FROM pg_stat_user_tables
ORDER BY idx_scan_ratio;

   relname   | seq_scan | idx_scan | idx_scan_ratio
-------------+----------+----------+----------------
 prefectures |      120 |        0 |            0.0
 accounts    |       42 |        8 |           16.0

nullifは分母が0のときにNULLを返す。計算結果もNULLになるため、一度もアクセスされていないテーブルでのゼロ除算エラーを防げる。該当する行は除外されず、idx_scan_ratioが空欄で出力される。

idx_scan_ratioが低いテーブルほどSeq Scanに頼っている。ただし比率が低いだけでは問題と断定できない。

比率だけで判断しない

prefecturesテーブルはidx_scan_ratioが0.0%だが、47行しかない小さなテーブルである。数ページに収まるテーブルではインデックスをたどるより全件読むほうが速いため、プランナは主キーがあってもSeq Scanを選ぶ。

EXPLAIN SELECT * FROM prefectures WHERE id = 5;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on prefectures  (cost=0.00..1.59 rows=1 width=10)
   Filter: (id = 5)
(2 rows)

実際に問題となるのは、大きなテーブルへのSeq Scanである。1回のSeq Scanあたりの平均読み取り行数を計算すると、両者を区別できる。

SELECT
  relname,
  seq_scan,
  seq_tup_read,
  seq_tup_read / nullif(seq_scan, 0) AS avg_seq_tup_read,
  n_live_tup,
  pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;

   relname   | seq_scan | seq_tup_read | avg_seq_tup_read | n_live_tup | table_size
-------------+----------+--------------+------------------+------------+-------------
 accounts    |       42 |     12600000 |           300000 |     300000 | 22 MB
 prefectures |      120 |         5640 |               47 |         47 | 8192 bytes

prefecturesは1回あたり47行しか読んでおらず、Seq Scanが120回でも負荷はごく小さい。一方のaccountsは1回あたり30万行を読んでおり、インデックス追加の候補になる。seq_tup_readはWHERE句で捨てられた行も含めた読み取り行数であり、クエリが返した行数とは異なる。

avg_seq_tup_readに閾値を設けると、調査対象のテーブルを絞り込める。

SELECT
  relname,
  seq_scan,
  idx_scan,
  seq_tup_read / nullif(seq_scan, 0) AS avg_seq_tup_read,
  pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
WHERE seq_tup_read / nullif(seq_scan, 0) > 1000
ORDER BY seq_tup_read DESC;

 relname  | seq_scan | idx_scan | avg_seq_tup_read | table_size
----------+----------+----------+------------------+------------
 accounts |       42 |        8 |           300000 | 22 MB

最後にスキャンされた時刻を確認する

PostgreSQL 16以降ではlast_seq_scanとlast_idx_scanが追加され、最後にSeq Scan・Index Scanが実行された時刻を確認できる。

SELECT relname, seq_scan, last_seq_scan, idx_scan, last_idx_scan
FROM pg_stat_user_tables
ORDER BY relname;

   relname   | seq_scan |         last_seq_scan         | idx_scan |         last_idx_scan
-------------+----------+-------------------------------+----------+-------------------------------
 accounts    |       42 | 2026-07-26 07:55:52.764846+00 |        8 | 2026-07-26 07:55:52.764846+00
 prefectures |      120 | 2026-07-26 07:55:52.764846+00 |        0 |

累計値だけでは、Seq Scanが過去の一時的なバッチ処理で発生したのか現在も続いているのかを判別できない。last_seq_scanを見ると、直近でもSeq Scanが発生しているかが分かる。

インデックス追加の効果を確認する

accountsテーブルのSeq Scanはbalance列を条件にした検索で発生していた。該当列にインデックスを作成し、統計をリセットしてから同じ処理を実行する。

CREATE INDEX idx_accounts_balance ON accounts (balance);
SELECT pg_stat_reset();

pg_stat_reset()は現在のデータベースの統計をすべて消去する。本番環境で特定テーブルの効果だけを測る場合はpg_stat_reset_single_table_countersを使う。また統計をリセットするとn_live_tupも0になり、次のVACUUMまたはANALYZEまで復帰しない。

統計はトランザクションの終了時にまとめて反映されるため、処理の直後には値が増えていない場合がある。少し待ってから確認する。

参考: 【PostgreSQL】CREATE INDEX CONCURRENTLYでサービスを止めずにインデックスを作成する

SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE relname = 'accounts';

 relname  | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch
----------+----------+--------------+----------+---------------
 accounts |        0 |            0 |       51 |        124077

seq_scanが0になり、すべての検索がインデックス経由に変わった。どのインデックスが使われているかはpg_stat_user_indexesで確認する。

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'accounts'
ORDER BY indexrelname;

     indexrelname     | idx_scan
----------------------+----------
 accounts_pkey        |        0
 idx_accounts_balance |       43
 idx_accounts_email   |        8

参考: 【PostgreSQL】pg_stat_user_indexesで使われていないインデックスを見つける

統計値を読むときの注意点

idx_scanはインデックスごとの値の合計

pg_stat_user_tablesのidx_scanは、テーブル自体のカウンタではなく、テーブルに属する全インデックスのidx_scanを合計した値である。前述の例でも、idx_accounts_balanceの43とidx_accounts_emailの8を足した51がpg_stat_user_tables側のidx_scanと一致する。

そのためpg_stat_reset_single_table_countersでテーブルのカウンタをリセットしても、インデックス側のカウンタが残るためidx_scanは0にならない。インデックスの統計もリセットする場合は、インデックスのOIDを指定して個別に実行する。

SELECT pg_stat_reset_single_table_counters('idx_accounts_balance'::regclass);

一方でidx_tup_fetchは単純な合計ではない。ビュー定義ではインデックス側の合計にテーブル自身のカウンタを加算しており、Bitmap Heap Scanによるヒープ読み取りはテーブル側に計上される。そのためpg_stat_user_indexesのidx_tup_fetchの合計とは一致しない。

並列Seq Scanはリーダーとワーカーの数だけ加算される

並列クエリでSeq Scanが実行されると、リーダーと各ワーカーがそれぞれ1回としてseq_scanを加算する。ワーカーが1つ起動した以下のクエリは、1回の実行でseq_scanが2増える。

EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM accounts WHERE status = 'active';

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=18.199..19.512 rows=1 loops=1)
   ->  Gather (actual time=18.065..19.508 rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate (actual time=17.099..17.099 rows=1 loops=2)
               ->  Parallel Seq Scan on accounts (actual time=0.007..12.324 rows=135000 loops=2)
                     Filter: (status = 'active'::text)
                     Rows Removed by Filter: 15000
 Planning Time: 0.186 ms
 Execution Time: 19.546 ms
(10 rows)

 relname  | seq_scan | seq_tup_read
----------+----------+--------------
 accounts |        2 |       300000

seq_scanはクエリの実行回数より多くなる場合がある。一方でseq_tup_readは読み取った行数の合計であり、並列実行でも二重に数えられない。実際の負荷を見るにはseq_tup_readのほうが信頼できる。

統計はリセットされる

pg_stat_user_tablesの値は、統計情報のリセット以降の累計である。pg_stat_reset()の実行時に加えて、クラッシュからの復帰やベースバックアップからの起動時にもリセットされる。集計の起点はstats_resetカラムで確認する。

SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

pg_stat_reset_single_table_countersによるテーブル単位のリセットではstats_resetが更新されないため、テーブルごとに起点がずれる点に注意する。

短時間の観測では、たまたま実行されたバッチ処理の影響を受けて比率が偏る場合もある。十分な期間の統計を蓄積してから判断する。

該当クエリを特定する

pg_stat_user_tablesで分かるのはテーブル単位の傾向までであり、どのクエリがSeq Scanを発生させたかまでは分からない。原因のクエリを特定するにはpg_stat_statementsとEXPLAINを併用する。なおpg_stat_user_tablesが対象とするのは接続中のデータベースのみである。

参考: 【PostgreSQL】pg_stat_statementsで重いクエリを特定する

参考: 【PostgreSQL】EXPLAIN ANALYZEの読み方の基本

参考

PostgreSQL: The Cumulative Statistics System