筆記 | PostgreSQL v12 CTEs 行為改變

前段時間，工作環境將 PostgreSQL v10 升級到 v15，升級後發現報表的執行效率從 1 秒變成了 2 分多鐘，也剛好變因太多，排查了很多項後才開始面對 SQL 執行計畫，同一段 SQL v10 跟 v15 有很大的差別，許多人花了一番功夫調整，速度才回到水準，接著進一步從調整方向的線索，再爬網路文章，才發現原來在 PostgreSQL v12 有一項對我們來說蠻重要的改變：CTEs 行為改變。

過去經驗裡，SQL 使用 CTE (Common Table Expression) 能將一段查詢的結果暫存起來，在主查詢語句中使用，能有提升查詢效率的效果

例如：

WITH temp AS (
SELECT col1, col2, col3 FROM big_table
WHERE col1 = ‘xxx’
)
SELECT a.col1, a.col4, b.col2, b.col3
FROM table a
INNER JOIN temp b ON b.col1=a.col1

從 PostgreSQL v12 起，CTE 的行為改變了
如果該 WITH 語句符合以下條件 (全部符合)，預設會併入主查詢成為子查詢

• 不是遞迴
• 未使用 Volatile Function
• 在 SQL 主查詢語句中只被引用一次

等同於以下結果

SELECT a.col1, a.col4, b.col2, b.col3
FROM table a
INNER JOIN (
SELECT col1, col2, col3 FROM big_table
WHERE col1 = ‘xxx’
) b ON b.col1=a.col1

若不符合上述條件，則會像之前一樣暫存結果
如果想要強制 CTE 暫存結果，就需在 WITH AS 語法加上 MATERIALIZED 語句，例如：

WITH temp AS MATERIALIZED(
SELECT col1, col2, col3 FROM big_table
WHERE col1 = ‘xxx’
)
SELECT a.col1, a.col4, b.col2, b.col3
FROM table a
INNER JOIN temp b ON b.col1=a.col1

反之，如果當時使用 CTE 的目的是為了讓 SQL 看起來簡化，其實可以併入子查詢
就加 NOT MATERIALIZED 的語句

如果沒有加，就代表交給 PostgreSQL 的查詢優化器來決定

延伸問題

• 那是不是全部的 WITH 語句都加 MATERIALIZED 就可以了？
  如果過去的使用習慣都是希望將結果暫存，這麼想沒有太大問題，但建議還是認真解析 SQL 來決定。
  使用 MATERIALIZED 將結果暫存，再在主查詢中使用，主查詢的條件過濾不會影響 WITH 查詢語句的結果 (也就是筆數不會改變)，加上暫存臨時表沒有index，單純把所有的 WITH 語句都加上 MATERIALIZED 有可能衍生另外的效能瓶頸，我想這是 PostgreSQL v12 針對 CTE 作行為調整的原因之一。
• 如果SQL 語法裡有很多個 WITH 語句，加第一個就好，還是全部都加？
  MATERIALIZED 語句是跟著每一個 WITH 語句的，所以如果全部都要暫存，就全部都要加。
  進一步的理解，其實要看 WITH 語句資料量、引用次數來決定，有些 WITH 語句的運算資料量少，但是被其他的 WITH 語句引用，而引用它的 WITH 語句資料量很大，此時運算量就會隨著引用者的資料量增加，這時將被引用者加上 MATERIALIZED 就可以達到很好的效能提升效果，需要 by case 解析。

相關文章：

• CTEs & Optimization
• WITH Queries (Common Table Expressions)
• PostgreSQL Release Notes (見 E.20.3.1.3. Optimizer 第 2 點)