pg_stat_statementsを使ったインデックスとフィルター利用の分析

インデックスって、本のしおりみたいなもんだよね。必要なデータをサクッと見つけるのに役立つ。でも、しおりをいっぱい挟んだのに誰も使ってなかったら意味ないし、逆に変なとこにしおり挟んでたら、結局最初から最後まで本をめくる羽目になる。だからこそ、インデックスがちゃんと使われてるか分析する必要があるんだ。

イケてないクエリはインデックスを無視して、コストの高いシーケンシャルスキャン（Seq Scan）をやっちゃう。これだとクエリが遅くなるし、サーバーの負荷も上がる。僕らのゴールは、「どのクエリがインデックスを使ってないのか、なぜなのか」を理解すること。

インデックスが使われてるかどうか、どうやって分かる？

ポイントは2つ：

1. 作ったインデックスが本当に使われてる？
2. 使われてるなら、それって効率いいの？

このために、pg_stat_statementsのクエリ統計を見て、いくつかのカラムに注目しよう：

• rows: クエリで処理された行数。
• shared_blks_hit: メモリ（ディスクじゃなくて）から読まれたページ数。
• shared_blks_read: 実際にディスクから読まれたページ数。

クエリで処理される行数が少なくて、shared_blks_hitの割合が高いほど、インデックスがうまく効いてるってこと。

インデックス分析の例

例えば、学生テーブルがあるとしよう：

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- grade_levelにインデックス追加
CREATE INDEX idx_grade_level ON students(grade_level);

じゃあ、実験用にデータを入れてみる：

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    '学生 ' || generate_series(1, 100000),
    'ラストネーム',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

特定の学年の学生を探すクエリを実行：

SELECT *
FROM students
WHERE grade_level = 10;

pg_stat_statementsでチェック

何回かクエリを実行したら、統計を確認しよう：

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

結果の読み方：

• rows: クエリがめっちゃ多くの行を返してたら、その条件でインデックスって意味ある？セレクティビティが低い場合はインデックスいらないかも。
• shared_blks_hitとshared_blks_read: ディスクからたくさんページを読んでたら（shared_blks_read）、インデックスが効いてないか、データがバッファプール外にあるかも。

インデックス最適化

インデックスを作るだけじゃ半分しか終わってない。PostgreSQLが本当に使ってくれるかが大事。頑張って作ったのに、なぜかシーケンシャルスキャンを選ばれることもある。なんで？ちょっと見てみよう。

まず、インデックスが明らかに役立ちそうなのに無視される理由を見てみる。そのあと、どうやったらDBに「インデックスあるよ！使って！」って思い出させられるかのテクニックも紹介。

もしインデックスが使われてなかったら？

たまにPostgreSQLはインデックスを無視してシーケンシャルスキャン（Seq Scan）をやる。理由はいくつかある：

1. 条件のセレクティビティが低い。 クエリがテーブルの半分以上の行を返すなら、シーケンシャルスキャンの方が速いこともある。
2. データ型や関数。 インデックス対象カラムに関数を使うと、インデックスが無視されることがある。例えば：

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- インデックス使われない

こういう時はクエリを書き換えよう：

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- インデックス使う

3. インデックスタイプが合ってない。 例えば全文検索ならGINやGiSTインデックスが良くて、B-TREEは向いてない。

4. 統計情報が古い。 統計が古いとオプティマイザが変な判断をする。ANALYZEを使おう：

   ANALYZE students;
   

クエリの改善

さっきの例に戻ろう。インデックスが効いてないなら、こんなことを試してみて：

1. インデックスが使えるフィルターをクエリで使ってるか確認。関数や型変換は避けよう。
2. フィルターで大量の行が返るなら、インデックスが本当に必要か考えよう。よく使うクエリなら、テーブル構造を変えたり、マテリアライズドビューを追加するのもアリ。
3. データ量が多くてSeq Scanになるなら、テーブルをパーティション分割（PARTITION BY）するのも手。

インデックス効率のチェック

最適化したら、もう一度クエリを実行して統計を見てみよう：

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

最適化前後でメトリクスを比べてみて。ディスク読み込み（shared_blks_read）が減って、ヒット数（shared_blks_hit）が増えてたらOK！

リアルなケース

1. インデックスの間違った使い方

例えば、descriptionってテキストカラムがあるproductsテーブル：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- 全文検索用インデックス
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

こんなクエリを実行した場合：

SELECT *
FROM products
WHERE description ILIKE '%スマートフォン%';

インデックスは使われない！理由はILIKEはGINと互換性がないから。インデックスを使うには、クエリを書き換えよう：

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('スマートフォン');

2. 必要なインデックスがない

例えば、こんなクエリ：

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

がシーケンシャルスキャン（Seq Scan）になってたら、birth_dateにインデックスがないのが原因かも。インデックスを作って：

CREATE INDEX idx_birth_date ON students(birth_date);

統計も更新（ANALYZE students）すれば、このクエリはかなり速くなるはず！