PostgreSQL CTE语句与materialized_materialize pg

PostgreSQL with语句功能可谓十分强大，可以优化很多复杂的查询，实现递归等等。不过with虽好，可还是不要乱用。

因为在PG12之前，with语句都是通过将子查询先进行物化，这就导致了一个问题：with子查询外的条件无法内推到里面。

我们看看PG12之前：
从执行计划可以看到，先对t1表进行全表扫描了一遍，然后再去进行过滤。

postgres=# explain analyze with c1 as (select * from t1) select * from c1 where id = 100;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 CTE Scan on c1  (cost=184.00..409.00 rows=50 width=36) (actual time=0.096..5.711 rows=1 loops=1)
   Filter: (id = 100)
   Rows Removed by Filter: 9999
   CTE c1
     ->  Seq Scan on t1  (cost=0.00..184.00 rows=10000 width=37) (actual time=0.041..2.370 rows=10000 loops=1)
 Planning time: 0.122 ms
 Execution time: 5.959 ms
(7 rows)
1
2
3
4
5
6
7
8
9
10
11

而到了PG12，提供了两个选项：

• with NOT MATERIALIZED (不使用物化，允许外面条件推进去)
• with MATERIALIZED (使用物化)

我们可以看下：
可以直接将id = 100的条件内推到with子句里面，还使用了索引扫描，这便是not MATERIALIZED带来的好处。

postgres=# explain analyze with c1 as (select * from t1) select * from c1 where id = 100;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t1 on t1  (cost=0.29..8.30 rows=1 width=37) (actual time=0.035..0.036 rows=1 loops=1)
   Index Cond: (id = 100)
 Planning Time: 0.439 ms
 Execution Time: 0.063 ms
(4 rows)
1
2
3
4
5
6
7
8

当然，既然还保留了原先的MATERIALIZED选项，自然也有它的好处。
那么对子查询直接物化的好处是什么呢？使用物化的好处是：如果是在每一次父查询的执行中它们通常只被计算一次，即使它们被父查询或兄弟WITH查询引用了超过一次，那么对于子查询本身代价很高的情况下。
这种情况下显然性能更佳：

WITH w AS (
    SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
1
2
3
4

总结：

• with NOT MATERIALIZED：不使用物化，允许外面条件推进去，但是存在子查询被多次执行的情况。
• with MATERIALIZED：使用物化，不允许外面条件推进去，但是可以保证子查询只被执行一遍，适用于子查询本身成本很高的情况。

参考链接：
https://www.modb.pro/db/69161
http://www.postgres.cn/docs/12/queries-with.html