使用 CTE 進行查詢優化

今天我們要潛進一個既刺激又有點嚇人的主題：用 CTE（Common Table Expressions）來優化查詢。如果你已經會寫 CTE（我們之前的講座有講過），那現在就是時候來聊聊它們的內部細節、那些「暗礁」還有怎麼把效能榨到極致。

乍看之下，CTE 超完美：語法乾淨、好寫，還能把 code 拆成有邏輯的區塊。但有個小（或其實不小）細節要注意。PostgreSQL 處理 CTE 的策略很特別，這會直接影響效能。

當 PostgreSQL 看到 WITH，它通常會把 CTE 的結果 materialize。意思就是 CTE 回傳的資料會先算出來，存成一個暫存 table，然後主查詢再用這個 table。這對重複利用很方便，但有時會出問題，像是：

1. CTE 裡的資料量超大，但實際只用到一小部分。
2. CTE 被呼叫太多次，導致 overhead 變大。
3. 我們寫了一堆複雜又沒必要的 CTE。

來認識一下 materialization

Materialization 就是 PostgreSQL 把 CTE 結果存到記憶體或硬碟（看資料量決定）。這代表資料只會被撈一次，但如果你只在一個地方用到這個 CTE，materialization 其實有點多餘。舉例：

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

這裡 PostgreSQL 會先做出一個包含所有 grade > 60 的暫存 table，然後再去過濾 grade > 90。這樣多了一個沒必要的中間步驟，效能就會被拖慢。

怎麼避免多餘的 materialization？

從 PostgreSQL 12 開始，你可以選擇不要 materialize CTE。用 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 說不要 materialize large_set，直接把查詢嵌進主查詢。這樣就不會產生中間 table，查詢更有效率。

什麼時候 materialization 反而有幫助？

別以為 materialization 一定不好！如果 CTE 在查詢裡會被用很多次，或是必須獨立計算，materialization 反而很有用。舉例：

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;

這裡 materialization 可以避免重複計算 grade > 60 的 filter。

用 index 來優化 CTE 查詢

想讓 CTE 跑更快，記得在底層 table 上加 index。像這樣：

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 欄位加 index，PostgreSQL 就能更快找到 grade > 90 的資料。這對大 table 超重要。

把大的 CTE 拆小一點

如果 CTE 回傳一堆資料，然後又要 filter 或 aggregate，最好拆成幾個步驟。不要寫一個超大 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 比較能優化查詢流程。

實戰例子：結構分析與優化

來看個複雜一點的例子。我們有學生、課程、成績三個 table。想找出平均分數很高的學生，然後列出他們跟對應的課程：

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 table 上加 index，filter 跟 join 都會快很多。

Monitoring：效能分析

想知道查詢效能怎樣，用 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. 忘記加 index。如果 CTE 裡有 filter，但底層 table 沒有 index，效能會很差。
2. CTE 太大太複雜。一個查詢做太多事，會導致 materialize 一堆資料。
3. 濫用 NOT MATERIALIZED。有時候 materialization 其實是必要的，這樣才能避免重複計算。
4. 沒做 monitoring。不用 EXPLAIN 看效能，你可能根本沒發現查詢很慢。

現在你已經準備好用 CTE 來優化查詢，避開陷阱、提升效能啦！記住，CTE 是工具，不是萬靈丹。用得巧，它會是你在 PostgreSQL 裡的好夥伴！