Postgres 12 的 NOT MATERIALIZED 指令有副作用嗎？

我正在對我公司的一些 SQL 進行一些性能基準測試，比較 PG10 和 PG12。我們在程式碼中使用了很多CTE，而 PG12 並沒有對 CTE 進行原生優化，所以 PG10 和 PG12 的性能是一樣的。

我的下一個實驗是將NOT MATERIALIZED指令添加到 CTE，結果令人震驚：它大大縮短了查詢時間（在某些情況下減半）。

我在這裡讀到這MATERIALIZED是 PG12 之前的預設功能。該功能會將 CTE 的所有內容寫入臨時位置。

所以我的問題主要是NOT MATERIALIZED：

1. NOT MATERIALIZED相比之下，功能對幕後數據有什麼作用MATERIALIZED？
2. NOT MATERIALIZED在重構我們的程式碼庫之前，我應該注意哪些副作用？

文件中對此進行了很好的解釋。

WITH 查詢的一個有用屬性是，它們通常在每次執行父查詢時只評估一次，即使它們被父查詢或同級 WITH 查詢多次​​引用。因此，可以將在多個地方需要的昂貴計算放在 WITH 查詢中以避免冗餘工作。另一個可能的應用是防止對具有副作用的函式進行不必要的多次評估。

到目前為止，一切都很好，但是：

然而，這個硬幣的另一面是優化器無法將父查詢的限制下推到多重引用的 WITH 查詢中，因為這可能會影響 WITH 查詢輸出的所有使用，而它應該只影響一個。多次引用的 WITH 查詢將被評估為已寫入，而不會抑制父查詢之後可能丟棄的行。

因此，正如給出的範例中所指出的，如果您有這樣的查詢：

WITH w AS (
   SELECT * FROM big_table  -- big_table has an INDEX on a field called key!
)
SELECT * FROM w AS w1 
 JOIN w AS w2 ON w1.key = w2.ref  -- w is called TWICE, so DEFAULT is MATERIALIZED
                                  -- PostgreSQL can't take advantage of big_table.key
WHERE w2.key = 123;

所以，在這種情況下：

WITH 查詢將被具體化，生成 big_table 的臨時副本 > 然後與自身連接 - 沒有任何索引的好處

最好有：

WITH w AS NOT MATERIALIZED (
   SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

這樣優化器就可以將 CTE 查詢“折疊”到主查詢中，並INDEX利用!key``big_table

關於。的：DEFAULT_NOT MATERIALIZED

但是，如果 WITH 查詢是非遞歸且無副作用的（即，它是一個不包含 volatile 函式的 SELECT），則可以將其折疊到父查詢中，從而允許對兩個查詢級別進行聯合優化。預設情況下，如果父查詢僅引用一次 WITH 查詢，則會發生這種情況，但如果它多次引用 WITH 查詢則不會。

所以DEFAULT如果NOT MATERIALIZED：

   the_query IS NOT recursive 
AND the_query is_side_effect_free 
AND the_query is_referenced_only_once

否則你必須告訴 PostgreSQL 使用NOT MATERIALIZED.

我看到的唯一小問題是需要進行測試以查看是否 NOT MATERIALIZED有所改進？我可以看到兩者之間的平衡取決於表大小、選擇的欄位以及 CTE 中使用的欄位和表的索引的情況——換句話說，沒有什麼可以替代知識和經驗。DBA 還沒有死去！:-)

引用自：https://dba.stackexchange.com/questions/257014