聊一聊 PostgreSQL 中的各种物化

数据库原理

Word count: 1.5kReading time: 6 min

 2025/06/23 

前言

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

物化视图

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

物化视图会膨胀吗？物化视图同样会膨胀
糟糕，物化视图刷新之后，数据不再有序？在定义的时候不使用 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. The Materialize node saves the data in memory as it’s read, and then returns the data from memory on each subsequent pass.

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

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE 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 AS
SELECT i, i % 5 AS j 
FROM generate_series(1, 100000) AS t(i);
SELECT 100000
postgres=# CREATE TABLE u AS
SELECT i, i % 20000 as j 
FROM generate_series(1, 100000) AS t(i);
SELECT 100000
postgres=# CREATE INDEX uj ON u(j);
CREATE INDEX
postgres=# analyze t,u;
ANALYZE
postgres=# EXPLAIN
SELECT *
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，因此当查找的不同值较少且每个值的查找次数较大时，使用带有结果缓存的参数化嵌套循环的优势会更大。小结一下：

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

CTE 物化

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

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

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

可以避免重复计算昂贵的 WITH 查询
充当优化器栅栏 — 对规划器来说是一个黑盒
物化本身可能成本较高
物化 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 中文版了。后续会做一些活动送出，请关注公众号。

参考

https://postgres-internals.cn/docs/chapter21

https://www.postgresql.org/docs/current/using-explain.html

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

Previous Post

告别 SubPlan 循环！PostgreSQL 17 相关子查询性能直线飙升

CATALOG

1. 前言
2. 物化视图
3. 物化节点
4. Memoize
5. CTE 物化
6. 小结
7. 参考