PostgreSQL 12 preview - CTE 增強，支援使用者文法層控制 materialized 優化

标簽

PostgreSQL , CTE , materialized , not materialized , push down

https://github.com/digoal/blog/blob/master/201903/20190309_04.md#%E8%83%8C%E6%99%AF 背景

PostgreSQL with 文法，能跑非常複雜的SQL邏輯，包括遞歸，多語句物化計算等。

在12以前的版本中，WITH中的每一個CTE（common table express），都是直接進行物化的，也就是說外層的條件不會推到CTE（物化節點）裡面去。

這麼做對于insert,update,delete的CTE以及遞歸WITH語句，都是稀疏平常的。但是對于select CTE，外面的條件推到CTE裡面，可能能夠大幅降低掃描。

是以PG 12開始，提供了使用者選擇

with NOT MATERIALIZED (不使用物化，允許外面條件推進去)  
  
with MATERIALIZED (使用物化)  
           

Allow user control of CTE materialization, and change the default behavior.  
  
Historically we've always materialized the full output of a CTE query,  
treating WITH as an optimization fence (so that, for example, restrictions  
from the outer query cannot be pushed into it).  This is appropriate when  
the CTE query is INSERT/UPDATE/DELETE, or is recursive; but when the CTE  
query is non-recursive and side-effect-free, there's no hazard of changing  
the query results by pushing restrictions down.  
  
Another argument for materialization is that it can avoid duplicate  
computation of an expensive WITH query --- but that only applies if  
the WITH query is called more than once in the outer query.  Even then  
it could still be a net loss, if each call has restrictions that  
would allow just a small part of the WITH query to be computed.  
  
Hence, let's change the behavior for WITH queries that are non-recursive  
and side-effect-free.  By default, we will inline them into the outer  
query (removing the optimization fence) if they are called just once.  
If they are called more than once, we will keep the old behavior by  
default, but the user can override this and force inlining by specifying  
NOT MATERIALIZED.  Lastly, the user can force the old behavior by  
specifying MATERIALIZED; this would mainly be useful when the query had  
deliberately been employing WITH as an optimization fence to prevent a  
poor choice of plan.  
  
Andreas Karlsson, Andrew Gierth, David Fetter  
  
Discussion: https://postgr.es/m/[email protected]  
           

https://github.com/digoal/blog/blob/master/201903/20190309_04.md#%E4%BE%8B%E5%AD%90 例子

在CTE中使用NOT MATERIALIZED，表示這個CTE不使用物化，外面的條件可以推到CTE中。

In particular, if there's an index on key, it will probably be used to fetch just the rows having key = 123. On the other hand, in

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

the WITH query will be materialized, producing a temporary copy of big_table that is then joined with itself — without benefit of any index.

This query will be executed much more efficiently if written as：

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;  
           

https://github.com/digoal/blog/blob/master/201903/20190309_04.md#%E5%8F%82%E8%80%83 參考

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=608b167f9f9c4553c35bb1ec0eab9ddae643989b https://www.postgresql.org/docs/devel/queries-with.html

https://github.com/digoal/blog/blob/master/201903/20190309_04.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機