好的 CTE 與壞的 CTE

背景

在資料庫開發中，通用資料表運算式（Common Table Expression，簡稱 CTE）常被開發者視為提升 SQL 程式碼可讀性與結構化的利器。然而，CTE 在 PostgreSQL 12 之前的版本中一直被視為「優化柵欄」，會強制資料庫先將結果實體化再進行後續處理，這往往導致索引失效並拖慢效能。本文探討了 PostgreSQL 12 之後引入的自動內聯機制，以及在何種特定情況下 CTE 仍會被實體化。

社群觀點

針對 CTE 的討論，社群首先關注到術語定義的問題。有讀者指出，文章標題與縮寫在不同領域可能引發完全不同的解讀，例如在醫學與體育界，CTE 指的是慢性創傷性腦病變，這種歧義讓不少跨領域的讀者在點進文章前感到困惑。不過回到技術層面，多數開發者達成了一項共識：CTE 的核心價值在於程式碼的組織與可讀性，而非效能優化。將 CTE 視為優化柵欄其實是歷史遺留的技術限制，而非一項功能。

在效能表現上，有留言提到 PostgreSQL 曾是主流關聯式資料庫中，最後一個移除 CTE 優化柵欄的系統。開發者過去常在開發階段使用 CTE 以維持邏輯清晰，但在正式上線前必須手動將其改寫為巢狀子查詢以確保效能，這種「工作流」隨著 PostgreSQL 12 的更新已逐漸成為過去。然而，也有資深工程師提醒，雖然內聯是好事，但開發者仍需理解實體化的運作機制，因為在處理昂貴的計算時，刻意讓 CTE 實體化反而能避免重複運算，達到節省資源的效果。

關於遞迴 CTE 的討論也相當熱烈。有觀點指出，儘管名稱中帶有「遞迴」二字，但 PostgreSQL 的實作本質上是迭代的運算機制，並非真正的遞迴堆疊。相比之下，Oracle 的特定語法在處理深度優先搜尋或具有早期終止條件的層級結構時，表現可能優於標準的遞迴 CTE。此外，針對開發體驗的痛點，社群也提到 CTE 雖然讓程式碼變整齊，卻也增加了除錯難度，因為中間結果在標準 SQL 編輯器中難以直接檢視，這促使了相關輔助工具的開發，試圖透過解析查詢語句來逐步呈現每個 CTE 的執行結果。

延伸閱讀

在討論過程中，有開發者分享了針對 CTE 除錯的開源工具專案 SQG（https://github.com/sqg-dev/sqg/），該工具旨在解析 CTE 查詢並逐步執行，幫助開發者更直觀地理解複雜查詢中的中間產物。此外，對於需要處理深度層級結構的讀者，留言也建議參考 Oracle 的 CONNECT BY 語法，以了解不同資料庫在處理樹狀結構時的設計差異。