CodeGym /課程 /SQL SELF /使用CTE時常見錯誤與如何避免

使用CTE時常見錯誤與如何避免

SQL SELF

等級 28 , 課堂 4

開放

現在是時候來聊聊 CTE 的黑暗面啦——常見的錯誤。就算你寫的 query 再帥，這些強大工具用錯了還是會爆炸。不過別擔心，我們有一整套診斷跟預防的說明給你！

1. 錯誤：CTE materialization 及其後果

PostgreSQL 處理 CTE 的一個重點特色，就是它們預設會 materialize。意思是 CTE 的結果會先被算出來，然後暫時存到記憶體（如果資料太多就丟到硬碟）。如果 query 很多或資料量很大，這會讓執行速度變超慢。

範例：

WITH heavy_data AS (
    SELECT * FROM large_table
)
SELECT * FROM heavy_data WHERE column_a > 100;

乍看之下好像只是 CTE 在過濾資料。但其實heavy_data 會先整個載入並 materialize，然後才做過濾。這超級花時間。

怎麼避免？

從 PostgreSQL 12 開始，可以把 CTE 當作inline expression（就像 subquery 一樣），這樣就不會 materialize 了。只要你的 CTE 只用一次、又不需要存中間結果，就可以這樣寫。

優化後的寫法：

WITH inline_data AS MATERIALIZED (
    SELECT * FROM large_table
)
SELECT * FROM inline_data WHERE column_a > 100;

小建議：如果你真的想 materialize，就加上 MATERIALIZED。不想的話就用 NOT MATERIALIZED。

2. 錯誤：遞迴 CTE 進入無限迴圈

遞迴 CTE 超強，但如果沒有限制遞迴深度，很容易就進入無限迴圈。這不只會拖慢速度，還會把所有資源吃光光。

範例：

WITH RECURSIVE endless_loop AS (
    SELECT 1 AS value

    UNION ALL

    SELECT value + 1
    FROM endless_loop
)
SELECT * FROM endless_loop;

這個會一直產生無限多的 row，因為沒有停止遞迴的條件。

怎麼避免？

加一個明確的停止條件，用 WHERE。像這樣：

WITH RECURSIVE limited_loop AS (
    SELECT 1 AS value

    UNION ALL

    SELECT value + 1
    FROM limited_loop
    WHERE value < 10
)
SELECT * FROM limited_loop;

小建議：如果你用遞迴 CTE 處理很大的階層結構，可以用 PostgreSQL 的 max_recursion_depth 來限制遞迴深度。

3. 錯誤：`UNION` 跟 `UNION ALL` 用錯

當你在 CTE 裡合併 base query 跟 recursive query 時，選錯 UNION 或 UNION ALL 會讓結果很奇怪。像 UNION 會把重複的 row 移掉，還會多花計算資源。

範例：

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION  -- 這裡其實該用 UNION ALL

    SELECT e.employee_id, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

這個例子裡 UNION 可能會把重要的 row 刪掉，如果剛好有重複。而且還會拖慢 query！

怎麼修正？

如果你不是真的需要去重，就用 UNION ALL：

UNION ALL

4. 錯誤：一個 query 裡塞太多 CTE

有些人為了讓 query 看起來很有結構，會加一堆 CTE。這不只讓 code 超亂，也會讓 PostgreSQL 的 query planner 爆炸。

範例：

WITH cte1 AS (...),
     cte2 AS (...),
     cte3 AS (...),
     ...
     cte20 AS (...)
SELECT ...
FROM cte20;

這根本是開發者的惡夢。

怎麼修正？

— 把 query 拆成幾個簡單一點的。不要寫一個塞滿 CTE 的 mega-query，分成幾個獨立的 query 吧。

— 另一個方法：如果有中間結果要重複用，可以存成暫存表。

5. 錯誤：複雜 CTE 沒加 index

如果你的 CTE 處理很多資料，但忘了幫 table 加 index，query 會慢到爆。index 就像資料庫的加速器啦。

範例：

WITH filtered_data AS (
    SELECT * FROM large_table WHERE unindexed_column = 'value'
)
SELECT * FROM filtered_data;

怎麼修正？

用 CTE 前，先確定你的 table 有優化：

CREATE INDEX idx_large_table ON large_table(unindexed_column);

6. 錯誤：想用 CTE 多次呼叫資料

CTE 只會被算一次，然後「凍結」結果。如果你在多個地方用到它，資料不會重新計算——有時候這會出錯。

範例：

WITH data AS (
    SELECT x, y FROM some_table
)
SELECT x FROM data
WHERE y > 10;
-- 如果還要再算一次 data，其實不會再算了。

怎麼修正？

如果你需要動態或重新計算，CTE 可能不是好選擇。用 subquery 吧。

7. 錯誤：沒寫註解

CTE 超好用，但誰想看一個連自己兩週後都看不懂的 SQL query？

範例：

WITH data_filtered AS (
    SELECT *
    FROM large_table
    WHERE some_column > 100
)
SELECT * FROM data_filtered;

一個月後你一定忘記為什麼要這樣過濾！

所以記得寫註解，尤其是複雜或遞迴的 CTE：

WITH data_filtered AS (
    -- 根據 some_column > 100 過濾資料
    SELECT *
    FROM large_table
    WHERE some_column > 100
)
SELECT * FROM data_filtered;

8. 錯誤：濫用 CTE 取代暫存表

有時候暫存表更適合。例如你要在不同 query 多次用到結果，或是資料量超大時。

範例：

WITH temp_data AS (
    SELECT * FROM large_table
)
SELECT * FROM temp_data WHERE column_a > 100;
SELECT * FROM temp_data WHERE column_b < 50;

這種寫法 CTE 會被執行兩次，雖然資料沒變！

怎麼修正？

如果資料要用很多次，就建個暫存表：

CREATE TEMP TABLE temp_table AS
SELECT * FROM large_table;

SELECT * FROM temp_table WHERE column_a > 100;
SELECT * FROM temp_table WHERE column_b < 50;