関数とプロシージャのパフォーマンス分析：EXPLAIN ANALYZEの使い方

EXPLAIN ANALYZEは、PostgreSQLがクエリを実行するときに「どう考えてるか」を理解するのに役立つよ：

• データ処理のためにどんなステップが実行されてるか。
• 各ステップの実行にどれくらい時間がかかってるか。
• なんで特定のクエリが遅いのか ― 例えば全件スキャン（英語でSeq Scan）とか、インデックスが使われてないとか。

EXPLAIN ANALYZEコマンドは実際にクエリを実行して、PostgreSQLがどう最適化してるかを見せてくれる。時計を分解して中身の仕組みを理解するみたいな感じ。EXPLAIN ANALYZEはそれをSQLクエリでやってくれるんだ。

EXPLAIN ANALYZEの構文

まずはシンプルな例から。基本的なコマンドはこんな感じ：

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

このクエリはSELECTを実行して、PostgreSQLがどうデータを処理してるかを見せてくれる。

EXPLAIN ANALYZEの結果はクエリ実行プランのツリーになってる。ツリーの各レベルはPostgreSQLが実行するステップを説明してるよ：

• Operation Type — 操作の種類（例：Seq Scan、Index Scan）。
• Cost — PostgreSQLがこの操作をどれくらい「重い」と思ってるか。
• Rows — 予想される行数と実際に取得された行数。
• Time — 操作にかかった時間。

出力例：

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Seq Scan on studentsに注目してみて。これはPostgreSQLがstudentsテーブルの全行を見てるってこと。テーブルが大きいと、これがめっちゃ遅くなるんだ。

EXPLAIN ANALYZEの使い方例

いくつか実践的な例を見て、クエリの問題点を見つけて直す方法を学ぼう。

例1：テーブル全件スキャン

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

出力：

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

ここでの問題は、PostgreSQLがSeq Scan、つまりテーブルの全行をなめてること。もしテーブルに何百万行もあったら、これがパフォーマンスのボトルネックになるよ。

解決策：ageカラムにインデックスを作ろう。

CREATE INDEX idx_students_age ON students(age);

同じクエリをもう一度実行してみて：

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

出力：

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

今度はIndex Scanになってるね。やった！クエリがめっちゃ速くなった！

例2：JOINを使った複雑なクエリ

例えば、studentsとcoursesの2つのテーブルがあるとする。学生の名前と、彼らが登録してるコース名を知りたい場合：

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

出力例はこんな感じ：

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

見ての通り、PostgreSQLはちゃんとenrollmentsとcoursesのテーブルでインデックスを使ってるし、実行も速い。でも、どこかのインデックスがなかったらSeq Scanになって遅くなるよ。

関数のパフォーマンス最適化

今度は、特定の年齢より上の学生リストを返す関数があるとする：

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

この関数のパフォーマンスもEXPLAIN ANALYZEで分析できる：

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

関数の実行を速くするには

もし関数の実行が遅いなら、たぶん全件スキャンが原因かも。直すには：

1. フィルターに使ってるカラム（age）にインデックスがあるか確認しよう。
2. テーブルの行数が多すぎるなら、パーティショニングも検討してみて。

ボトルネックとその直し方

1. テーブル全件スキャン（Seq Scan）。 検索を速くするにはインデックスを使おう。でも、インデックスが多すぎるとデータ挿入が遅くなることもあるから注意。

2. 結果セットの行数が多すぎる。 クエリが何百万行も返すなら、WHEREやLIMITで絞ったり、OFFSETでページングしたりしよう。

3. 「重い」操作。 ソートや集約、大きなテーブルのJOINみたいな操作はリソースをたくさん使う。インデックスを使ったり、クエリをいくつかのステップに分けたりしよう。