CTEを使ったクエリ最適化

今日は、ちょっとワクワク（そしてちょっと怖い）CTE (Common Table Expressions) を使ったクエリ最適化の世界に飛び込むよ。CTEの作り方は前のレクチャーでやったから、今回はその中身の仕組みとか「落とし穴」、そして効率を最大限に引き出すテクニックについて話すね。

パッと見、CTEって理想的に見えるよね。コードがスッキリ書けるし、ロジックごとに分けられるし。でも、ちょっとした（いや、結構大きいかも）注意点があるんだ。PostgreSQLはCTEに独特な戦略を持ってて、それがパフォーマンスに影響するんだよ。

PostgreSQLがWITHを見つけると、普通はCTEの結果をマテリアライズ（materialize）する。つまり、CTEが返すデータを一度計算して、一時テーブルとして保存してからメインのクエリで使うってこと。再利用には便利だけど、次のような場合は問題になることもある：

1. CTEのデータ量がめっちゃ多いのに、結果は一部しか使わないとき。
2. CTEが何回も呼ばれて、オーバーヘッドが増えるとき。
3. 本当はいらない複雑なCTEを作っちゃってるとき。

マテリアライズって何？

マテリアライズは、PostgreSQLがCTEの結果をメモリやディスク（データ量による）に保存するプロセスだよ。つまり、データは一度だけ取り出されるけど、CTEを一箇所でしか使わないなら、マテリアライズは無駄になることも。例えば：

WITH large_set AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

この場合、PostgreSQLはまずgrade > 60の全結果を一時テーブルにして、その後でgrade > 90で絞り込む。これ、余計な中間ステップが増えてパフォーマンスに悪影響なんだ。

無駄なマテリアライズを避けるには？

PostgreSQL 12からは、不要なマテリアライズを避ける機能が追加されたよ。キーワードMATERIALIZED（デフォルト）かNOT MATERIALIZEDを使うんだ。例：

WITH large_set AS NOT MATERIALIZED (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

ここでは、PostgreSQLにlarge_setをマテリアライズしないで、クエリをそのままメインの式に組み込んでって指示してる。これで中間テーブルが作られず、より効率的になるよ。

マテリアライズが役立つときは？

マテリアライズが「悪」ってわけじゃない！CTEのデータをクエリ内で何度も使う場合や、独立して計算したい場合はマテリアライズが便利。例：

WITH materialized_example AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)

SELECT student_id
FROM materialized_example
WHERE grade > 90

UNION ALL

SELECT student_id
FROM materialized_example
WHERE grade < 70;

ここでは、grade > 60のフィルタを何度も計算しなくて済むから、マテリアライズが役立つんだ。

インデックスを使ったクエリ最適化

CTEを速くしたいなら、元になるテーブルにインデックスを作るのが大事。例えば：

CREATE INDEX idx_students_grades_grade ON students_grades(grade);

WITH filtered_students AS (
    SELECT student_id, grade
    FROM students_grades
    WHERE grade > 90
)
SELECT *
FROM filtered_students;

gradeカラムにインデックスがあると、PostgreSQLはgrade > 90の条件に合う行を速く取り出せる。大きなテーブルだと特に重要！

大きなCTEを小さく分ける

CTEが大量のデータを返して、それをさらにフィルタや集計するなら、いくつかのステップに分けた方がいい。一つの大きなCTEより、小さいCTEを複数作るのがオススメ：

ダメな例（大きなCTE）：

WITH large_query AS (
    SELECT s.student_id, AVG(g.grade) AS avg_grade
    FROM students s
    JOIN grades g ON s.student_id = g.student_id
    WHERE g.subject_id = 101 AND g.grade > 85
    GROUP BY s.student_id
)
SELECT *
FROM large_query
WHERE avg_grade > 90;

良い例（ステップに分割）：

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE subject_id = 101 AND grade > 85
),
average_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM filtered_grades
    GROUP BY student_id
)
SELECT *
FROM average_grades
WHERE avg_grade > 90;

こうすると、PostgreSQLがクエリの実行をもっと最適化しやすくなるよ。

実践例：構造分析と最適化

もうちょっと複雑な例を見てみよう。学生、コース、成績のテーブルがあって、成績が高い学生を探して、その学生が取ってるコースも一緒に出したいとする：

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)
SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

このクエリは、gradesやenrollmentsテーブルにインデックスを追加すると、フィルタやJOINが速くなるよ。

モニタリング：パフォーマンス分析

クエリがどれだけ効率的か知りたいときは、EXPLAINやEXPLAIN ANALYZEを使おう。例えば：

EXPLAIN ANALYZE
WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

このクエリで、各ステップにどれだけ時間がかかってるか分かるし、どこを改善すればいいか見えてくるよ。

EXPLAIN ANALYZEの詳しい使い方は、また次のレベルでやるから楽しみにしててね :P

CTE最適化でよくあるミス

1. インデックスを忘れる。CTEでデータを絞り込んでるのに、元テーブルにインデックスがないとパフォーマンスが落ちるよ。
2. でかすぎるCTEを使う。一つのクエリで色々やりすぎると、巨大なデータがマテリアライズされてしまう。
3. NOT MATERIALIZEDの乱用。場合によってはマテリアライズが必要なこともあるから、何でもかんでも避ければいいってもんじゃない。
4. モニタリングを無視する。EXPLAINで分析しないと、クエリが遅い原因に気づけないよ。

これでCTEを使ったクエリ最適化の準備はバッチリ！落とし穴を避けてパフォーマンスを上げよう。CTEはあくまでツールだから、うまく使えばPostgreSQLで最強の味方になるよ！