聊一聊 PostgreSQL 中的各种物化(文末有彩蛋)

前言

PostgreSQL 中有各种各样的"物化"操作，比如熟知的物化视图，物化算子等等，12 版本之后，针对 CTE，还引入了 not materialized 关键字，允许内联等等，让我们一探究竟。

物化视图

物化视图就不再过多描述了，目前社区还不支持增量刷新，pg_ivm^[1] 也存在着诸多限制，值得注意的是，物化视图也有自己的一些坑，稍不注意，就掉进去了：

•物化视图会膨胀吗？物化视图同样会膨胀•糟糕，物化视图刷新之后，数据不再有序？在定义的时候不使用 ORDER BY 子句，因为在刷新的时候，无法保证数据仍然有序。

物化节点

Materialize Node，由 enable_material 参数控制。参照官方文档

This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. 

这意味着 t2 索引扫描只需执行一次，即使嵌套循环连接节点需要读取该数据十次 (针对外表中的每一行读取一次)。Materialize 节点在读取数据时会将其保存到内存中，然后在每次后续读取时从内存中返回数据。

EXPLAIN SELECT *FROM tenk1 t1, tenk2 t2WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;
                                         QUERY PLAN--------------------------------------------------------------------------------------------- Nested Loop  (cost=4.65..49.36 rows=33 width=488)   Join Filter: (t1.hundred < t2.hundred)   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)         Recheck Cond: (unique1 < 10)         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)               Index Cond: (unique1 < 10)   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)               Index Cond: (unique2 < 10)

将结果缓存在内存中，其总大小受到 work_mem 的限制，超过就会溢出到临时文件中，如果再次访问，节点就可以读取所累积的行而无需再次调用子节点，以提升查询性能。

Memoize

在 14 的版本中，PostgreSQL 引入了 Memoize，由 enable_memoize 参数控制，对于 SQL 中的嵌套循环连接特别有用。

参照 14 internals 的解释：此类缓存由 Memoize 节点执行。与 Materialize 节点类似，旨在处理参数化连接，并且具有更复杂的实现：

•Materialize 节点只是简单地保存所有子节点返回的行，而 Memoize 则确保不同参数值返回的行分开保存。•在发生溢出的情况下，Materialize 存储开始将行溢出到磁盘，而 Memoize 会将所有行保留在内存中 (否则缓存就没有意义了)。

postgres=# CREATE TABLE t ASSELECT i, i % 5 AS j FROM generate_series(1, 100000) AS t(i);SELECT 100000postgres=# CREATE TABLE u ASSELECT i, i % 20000 as j FROM generate_series(1, 100000) AS t(i);SELECT 100000postgres=# CREATE INDEX uj ON u(j);CREATE INDEXpostgres=# analyze t,u;ANALYZEpostgres=# EXPLAINSELECT *FROM t JOIN u ON t.j = u.j;                               QUERY PLAN                               ------------------------------------------------------------------------ Nested Loop  (cost=0.30..8950.41 rows=497587 width=16)   ->  Seq Scan on t  (cost=0.00..1448.00 rows=100000 width=8)   ->  Memoize  (cost=0.30..0.41 rows=5 width=8)         Cache Key: t.j         Cache Mode: logical         ->  Index Scan using uj on u  (cost=0.29..0.40 rows=5 width=8)               Index Cond: (j = t.j)(7 rows)

此处表 t 与 u 同有 100 000 行，t.j 只有 5 个不同值，每个值出现 20 000 次，u.j 有 20 000 个不同值，每个值出现 5 次，如果表 t 中出现相同的 j 值，就 不再触发新的索引扫描，而是直接把缓存的结果“重放”给嵌套循环的上层，节省了 I/O，因此当查找的不同值较少且每个值的查找次数较大时，使用带有结果缓存的参数化嵌套循环的优势会更大。小结一下：

1.Nested Loop + Index Scan：每个外表行都要查一次内表。2.Nested Loop + Memoize：只对每个 distinct 参数值查一次，然后重用缓存，显著降低重复访问开销。

CTE 物化

另外一个值得一提的是 CTE 的物化，在 12 以前的版本中，CTE 总是会被先物化，无法内联，这样就可能导致一些性能问题，比如

即使 s 字段上有索引，也无法使用扫描，CTE 就像一个"黑盒"，单独处理。在 12 以后，默认不内联，除非显式指定 materialized 关键字

可以看到，在 12 以后，就可以走索引扫描了，将 CTE "展开"了，大幅提升查询效率，不用再先全表扫一遍 big_table。不过值得注意的是，物化有其好处，也有其坏处

1.可以避免重复计算昂贵的 WITH 查询2.充当优化器栅栏 — 对规划器来说是一个黑盒3.物化本身可能成本较高4.物化 CTE 后，索引无法有效使用

具体可以参照官网的例子：An example where NOT MATERIALIZED could be undesirable is

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;

Here, materialization of the WITH query ensures that very_expensive_function is evaluated only once per table row, not twice.

其次我们知道，CTE 是可以嵌套引用的，对于这种情况，默认为自动物化；我们可以通过 WITH … AS MATERIALIZED (…) 强制物化，或 NOT MATERIALIZED 强制内联，精细控制优化器行为。

小结

好了，就聊到这吧。最后，PostgreSQL DBA Daily 5.0 高清纸质大图新鲜出炉啦，不过就邮寄了 20 份当此次 IvorySQL 大会的伴手礼(一张也要十几块大洋，实属肉疼)，电子档目前还未开源，所以各位读者抓紧机会 😎。这是笔者最为满意的作品之一，另外一个当然是 PostgreSQL 14 internals^[2] 中文版了。后续会做一些活动送出，请关注公众号。

参考

https://postgres-internals.cn/docs/chapter21^[3]

https://www.postgresql.org/docs/current/using-explain.html^[4]

https://blog.jooq.org/postgresql-14s-enable_memoize-for-improved-performance-of-nested-loop-joins/^[5]

References

[1] pg_ivm: https://github.com/sraoss/pg_ivm
[2] PostgreSQL 14 internals: https://postgres-internals.cn/
[3]: https://postgres-internals.cn/docs/chapter21
[4]: https://www.postgresql.org/docs/current/using-explain.html
[5]: https://blog.jooq.org/postgresql-14s-enable_memoize-for-improved-performance-of-nested-loop-joins/