インデックスとテーブル利用統計の収集

データベースをでっかい倉庫だと思ってみて。インデックスはカタログやリストで、欲しいものをサクッと見つけるためのもの。テーブルは棚に並んでる商品そのもの。もしインデックスが全然使われてなかったら、それは倉庫の隅っこに放置されたカタログみたいなもんで、誰も開かない。逆にテーブルがめっちゃ使われてるのに構造がイマイチだったり、データが多すぎたりすると、倉庫（データベース）がしんどくなって動きが遅くなるんだ。

分析の主な目的:

1. インデックス利用効率の評価。 たとえば、めっちゃ高いインデックス作ったのに全然使われてない？捨てちゃおう！
2. 読み書き操作の頻度を把握。 どのテーブルがよく使われてるか分かるよ。
3. クエリの最適化。 統計を見れば、どこをインデックス追加・変更すればデータ処理が速くなるか分かる。

ビュー pg_stat_user_indexes と pg_stat_user_tables

PostgreSQLには統計を集めるのに超便利なビューが2つあるよ：pg_stat_user_indexes と pg_stat_user_tables。詳しく見てみよう。

pg_stat_user_indexes: インデックスはどう使われてる？

主なカラム:

• relname — インデックスが紐づいてるテーブル名。
• indexrelname — インデックス名。
• idx_scan — インデックスが検索に使われた回数。
• idx_tup_read — インデックス経由で読まれた行数。
• idx_tup_fetch — 実際に返された行数（フィルタ後）。

クエリ例:

SELECT relname AS テーブル名, 
       indexrelname AS インデックス名, 
       idx_scan AS インデックス検索回数, 
       idx_tup_read AS インデックスで読んだ行数, 
       idx_tup_fetch AS インデックスで返した行数
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

ここでやってること：

• インデックスの呼び出し回数（idx_scan）でソートして、どのインデックスが人気かチェック。
• ほとんど使われてないインデックス（idx_scan = 0）は、本当に必要か考えよう。

実践的な使い方:

新しいアプリのバージョンをデプロイして新しいインデックスを追加したとき、pg_stat_user_indexesで本当にそのクエリが新インデックスを使ってるか確認できる。もしPostgreSQLがまだ古い方法でやってたら、せっかくの最適化が無駄になっちゃうよ。

pg_stat_user_tables: テーブルのデータをチェック

主なカラム:

• relname — テーブル名。
• seq_scan — テーブルのシーケンシャルスキャン回数（インデックス使わないやつ）。
• seq_tup_read — シーケンシャルスキャンで返された行数。
• idx_scan — テーブルに対するインデックススキャン回数。
• n_tup_ins — 挿入された行数。
• n_tup_upd — 更新された行数。
• n_tup_del — 削除された行数。

クエリ例:

SELECT relname AS テーブル名, 
       seq_scan AS シーケンシャルスキャン回数, 
       idx_scan AS インデックススキャン回数, 
       n_tup_ins AS 挿入行数, 
       n_tup_upd AS 更新行数, 
       n_tup_del AS 削除行数
FROM pg_stat_user_tables
ORDER BY シーケンシャルスキャン回数 DESC;

ここで何が分かる？

• シーケンシャルスキャン（seq_scan）が多いテーブルは、インデックス追加を検討した方がいいかも。
• 挿入・更新・削除の回数で、そのテーブルのデータがどれだけ頻繁に変わってるか分かる。

実践的な使い方: たとえばusersテーブル（アプリの全ユーザー情報が入ってるやつ）で、pg_stat_user_tablesを見たらシーケンシャルスキャン（seq_scan）がめっちゃ多い。これは「よく使うカラムにインデックス作ろうぜ！」ってサインだよ。クエリが速くなる！

例: 実際のデータベースでインデックスとテーブルを分析

たとえば、orders（注文）とproducts（商品）テーブルがあるデータベースで、テーブルやインデックスがどれだけ効率よく使われてるか見てみたいとする。

インデックスの分析:

SELECT relname AS テーブル名, 
       indexrelname AS インデックス名, 
       idx_scan AS インデックス検索回数, 
       idx_tup_read AS 読み取った行数, 
       idx_tup_fetch AS 返した行数
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY インデックス検索回数 DESC;

orders_customer_id_idxインデックスは5万回呼ばれてるのに、orders_date_idxはたった5回しか使われてない。orders_date_idxは不要かも。

テーブルの分析:

SELECT relname AS テーブル名, 
       seq_scan AS シーケンシャルスキャン回数, 
       seq_tup_read AS 読み取った行数, 
       idx_scan AS インデックススキャン回数, 
       n_tup_ins AS 挿入行数, 
       n_tup_upd AS 更新行数, 
       n_tup_del AS 削除行数
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY シーケンシャルスキャン回数 DESC;

productsテーブルはシーケンシャルスキャンされまくってる。つまり、商品カタログにインデックスが足りてないってこと。

ありがちなミスとその回避法

初心者がよくハマる罠は、統計を無視しちゃうこと。たとえば「新しいインデックス作ったからクエリ爆速！」って思っても、PostgreSQLがそれを使ってくれないことがある。なぜなら統計が自動で更新されてないから。テーブルに大きな変更を加えたら、ANALYZEコマンドで手動で統計を更新するのを忘れずに！

もう一つのよくあるミスは、インデックスをやたらと追加しまくること。インデックスはディスク容量を食うし、挿入・更新・削除も遅くなる。pg_stat_user_indexesの統計を見て、本当に必要なインデックスだけ残そう。使われてないやつは削除！

この知識が役立つ場面は？

実際の開発現場: データベースが遅いとき、まず最初にテーブルやインデックスの問題を探すことになるよ。

面接: インデックス最適化の質問はSQL面接の定番。pg_stat_user_indexesを説明できたら、もう半分合格！

DB管理: モニタリングはDBAの日常。テーブルやインデックスの統計がなきゃ、何も改善できないよ。