PostgreSQL Tuplestore：查询物化背后的临时存储核心机制

如果你平时用EXPLAIN ANALYZE看复杂查询的计划，大概率见过Materialize节点。很多人第一反应是：这东西是不是性能瓶颈？其实它只是个"临时货架"，真正的幕后正主是藏在它身后的Tuplestore。Tuplestore 是 PostgreSQL 查询执行器里最核心的临时 tuple 存储机制之一，负责把中间结果先攒在内存里、内存不够时掉头把数据写给临时文件，还要保证之后能按顺序原样读回来。这篇文章我就从执行器的物化角度，把这个机制掰开揉碎，看看它到底怎么工作、哪些节点在背后悄悄用它，以及做 SQL 优化时该怎么和它打交道。

1. 执行器为什么要"物化"：一次查询旅程中的三个转折点

1.1 从拉取模型到中间结果存档

PostgreSQL 的执行器是典型的"拉取模型"：执行计划是一棵节点树，上层节点从下层节点ExecProcNode一次拉一行，处理完再往上传。这种流水线设计的最大好处是内存占用低，理论上可以边算边丢，不需要把整个结果集攒下来。

但有些算子天然无法满足这种"来一行处理一行"的假设。典型的例子是排序：要输出第一行结果，必须等所有输入都拿到并且排好序。另一个例子是嵌套循环连接的内侧：如果优化器打算把内表反复扫描，在流水线模式下就要反复执行子节点，代价极高。这时候执行器就需要一个"存档点"——把已经算出来的中间结果全部暂存到一个容器里，后续需要时再顺序重放。

这个容器就是 Tuplestore。物化节点（Materialize）只是 Tuplestore 最直接的使用者，而它的设计理念其实贯穿了整个执行器：用空间换时间，用暂存打破流水线限制。

1.2 场景一：同一份数据要被扫两遍以上

想象一个简单的笛卡尔积查询：SELECT * FROM t1, t2。如果t2很小，优化器通常会在计划里加一个物化节点：先把t2的全部数据一次性读出并缓存，然后外层循环的每一行都从头重放这个缓存，而不是重复扫描t2的底层表。

这在执行层面是巨大的收益。表扫描背后有 Buffer Pool 的竞争、有锁、有 IO 调度，重复扫一遍代价不低；而 Tuplestore 缓存的数据如果在内存里，重放就是遍历一批指针；即使溢出到了磁盘临时文件，也只是一次顺序读的开销，比重复执行子节点划算得多。

注意，物化节点是"惰性"的。它并不是在计划开始时就一口气把子节点全部读空，而是边消费边存：第一次扫描时，上层要一行它就去子节点拉一行，同时存入 Tuplestore；等子节点全部读完，Tuplestore 里也就攒齐了完整结果。之后的重扫描，才完全从 Tuplestore 重放。

1.3 场景二：某些运算符先天需要"看全集"

并不是所有物化都发生在显式的Materialize节点上。像窗口函数，它的一个分区（partition）内所有行都要先拿到，才能计算row_number()、sum() over (...)这类需要帧（frame）移动的逻辑；CTE 物化同样如此，WITH子句一旦被决定物化，就会在查询真正开始前把子查询的全部结果灌进 Tuplestore，之后每一处 CTE Scan 都重放同一份缓存。

这种"必须看全集"的场景和缓存重放场景有本质区别：前者是不可回避的语义要求，后者则是优化器的成本选择。但不管是哪一种，Tuplestore 都是它们共同的基础设施。

1.4 物化不是免费午餐：成本模型里的双份代价

优化器在决定是否物化时，成本模型里其实已经把物化的代价算得很明白了：cost_material大致把子路径的总成本翻了一倍——一份花在"写入 Tuplestore"，一份花在"从 Tuplestore 读回"。也就是说，物化等于把一段子计划多跑一遍的代价，但如果这个子计划原本要被重复执行 N 次，物化依然能赚回来。

这也是我想强调的第一个经验：看到计划里有 Materialize 别急着删，先问一问它避免了什么。如果内侧子计划本身特别便宜，物化的收益就有限；如果内侧是个大表全扫描，物化往往很值。

2. Tuplestore 的核心设计：内存为池，磁盘为库

2.1 一个只允许顺序读写的容器：接口与行为

从使用者的视角看，Tuplestore 是再简单不过的容器：只能往里追加 tuple，只能从头部按顺序读取，支持重置回开头再读一遍。它不提供随机访问能力，更不是索引结构。

源码层面它有一组清晰的操作接口：

• tuplestore_begin_heap()：创建 Tuplestore，传入是否允许随机访问、是否跨事务等参数
• tuplestore_puttupleslot()/tuplestore_putvalues()：写一行
• tuplestore_gettupleslot()：按顺序读一行
• tuplestore_reset()：把读取位置重置到开头，准备重新扫描
• tuplestore_end()：释放所有内存和临时文件

物化节点的整个实现（execMaterial.c）几乎就是围绕这一组接口展开的：第一次扫描循环tuplestore_puttupleslot，后续扫描不停tuplestore_gettupleslot。顺着这条代码路径读下去，你会发现 Tuplestore 内部的状态管理比接口本身要复杂得多。

2.2 三级状态机：从内存态到文件态的切换

Tuplestore 内部维护了一个状态机，核心是三种状态：

初始状态一定是内存态。随着写入量增长，一旦超过阈值，就切换到写文件态。在写文件态下，tuplestore_gettupleslot同样可以工作，也就是说，即使数据已经开始溢出，物化节点依然可以边写边读。等到需要重新扫描并调用tuplestore_reset时，内部再切换到读文件态，或者如果数据量没有溢出、仍在内存态，则只是把数组游标归零。

这个状态机设计精妙的地方在于：调用方根本不需要关心数据到底在内存还是磁盘，Tuplestore 自己会完成所有切换。这为上层节点提供了完全一致的行为。

2.3 溢出阈值背后的计算逻辑

很多人以为 Tuplestore 的溢出阈值就是一个简单的"work_mem多大，内存就用到多大"。实际上，tuplestore_begin_common()里会根据work_mem估算一个"最多能存多少个 tuple"的数值maxTups，而不是等内存真的耗尽才切换。

粗略的估算逻辑是：

maxTups = (允许内存 - 固定预留) / (每个tuple的管理开销 + 平均tuple宽度)

这里的管理开销包括HeapTuple结构本身、指针数组的占用、palloc 分配带来的对齐和头部损耗等。也就是说，Tuplestore 是在"预期"内存快要不够用时提前开始向文件转移，而不是把内存撑爆了再被动响应。这个估算是启发式的，所以不同宽度、不同类型的 tuple，能否在work_mem内全部缓存下来会有细微差异。

和排序不同，Tuplestore 不做归并。排序在溢出时需要对多个归并段做外部归并，而 Tuplestore 只是把 tuple 按到达顺序原样写入文件即可，这也是它比 Tuplesort 简单的原因之一。

2.4 临时文件的产生与自动清理

当数据切到写文件态后，物理文件并不是直接散落在数据目录里，而是通过底层的BufFile抽象创建。默认情况下，这些文件会写到temp_tablespaces指定的表空间；如果没有配置，就落在数据目录下的base/pgsql_tmp目录。

临时文件的命名带有一段随机标识和进程标识，例如pgsql_tmp12345.0这种形式。用完即删，正常状态下你不用关心它的生命周期。真正要关心的是文件落在哪块磁盘上——如果数据目录在机械盘上，一个需要大量物化溢出的查询，就会把磁盘 IO 打得很难看。这个我们放到第 5 节踩坑部分详细说。

3. 哪些计划节点在背地里使用 Tuplestore

3.1 Materialize：最名正言顺的消费者

Materialize节点是整个执行器里唯一把 Tuplestore 写进名字的节点。它做的事情很直接：缓存子节点输出，供上层节点重复扫描。它的randomAccess参数通常为真，因为嵌套循环的每一轮外层行都可能需要重新扫描整个缓存。

什么时候优化器会生成 Materialize？常见触发条件包括：

• 嵌套循环连接的 inner 侧，尤其是 inner 侧代价高、且连接条件无法走索引
• 子查询结果需要复用多次
• 某些特殊连接操作中，inner 侧需要保留完整数据再匹配

经验上看，Materialize 最常出现在"小表驱动大表但内表没有索引"的嵌套循环里。此时如果小表只有几百行，内表几百万行，物化内表一次再内存扫描，往往比每次都全表扫要快几个量级。

3.2 CTE 物化与递归查询的工作表

WITH子句从 PostgreSQL 12 开始默认倾向于内联，但你可以显式写WITH x AS MATERIALIZED (...)强制物化。被物化的 CTE 会在执行器启动阶段把子计划结果全部灌入 Tuplestore，之后每个 CTE Scan 都重放这份缓存。物化的好处是子查询只执行一次，坏处是如果结果集巨大且work_mem不足，Tuplestore 会溢出到临时文件，产生额外的 IO。

递归查询（WITH RECURSIVE）是另一个重度使用者。RecursiveUnion节点需要维护两个集合：working table和result table。每次迭代，递归项以当前 working table 的内容为输入，生成下一批 tuple，然后把新 working table 和上一轮的结果做并集去重。这个过程中 working table 的暂存就使用了 Tuplestore。可以说，没有 Tuplestore 就没有递归查询的实现基础。

3.3 WindowAgg 的大分区缓存

窗口函数节点（WindowAgg）也是 Tuplestore 的常客。为了计算某个窗口分区内的lag()、lead()、sum() over (order by ...)这类需要访问相邻行的函数，执行器需要把当前分区的所有 tuple 先缓存下来，再在内存里前后移动读取。

如果某个分区特别大——比如按user_id分区但某个用户有上亿行——WindowAgg 的 Tuplestore 就会形成巨大的内存/临时文件压力。这个问题排查起来很隐蔽，因为计划里看不到明显的 Materialize 节点，你会看到的是 WindowAgg 节点本身在持续消耗临时文件读写。

3.4 容易混淆的邻居：Sort 用的其实是 Tuplesort

很多人会误以为排序也是 Tuplestore 干的活，其实不是。排序节点（Sort）用的是另一套更复杂的机制Tuplesort。Tuplesort 同样分内存态和文件态，但它要做真正的排序，因此在溢出到文件时必须维护多个归并段，最后还要做 K 路归并。Tuplestore 则保持插入顺序即可。

两者共享了很多底层设施，比如BufFile、内存上下文管理、资源所有者自动清理，但定位完全不同。如果你发现计划里 Sort 节点有大量 temp IO，那是 Tuplesort 的外部排序问题，和本文的物化缓存问题不要混为一谈。

4. 用 EXPLAIN 和系统统计看清 Tuplestore 的运作

4.1 复现一个真实的物化+溢出场景

理论讲再多，不如动手看一次现场。最简单的方式是用强制物化的 CTE，写出来的执行计划非常直观。

准备一张测试表：

CREATE TABLE demo_cte AS SELECT g AS id, md5(g::text) AS payload FROM generate_series(1, 2000000) g;

把work_mem压得很小，再执行强制物化：

SET work_mem = '1MB'; EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) WITH x AS MATERIALIZED (SELECT id, payload FROM demo_cte) SELECT count(*), sum(length(payload)) FROM x;

计划里会清楚出现类似这样的结构：

Aggregate -> CTE Scan on x -> CTE x -> Seq Scan on demo_cte

因为在EXPLAIN ANALYZE里开了BUFFERS，如果物化的结果超过1MB的 Tuplestore 内存阈值，你会看到Buffers: temp read=... written=...这样的统计。这两项数字就是 Tuplestore 溢出后实际读写临时文件的块数，每一块通常是 8KB。

这个实验的妙处在于执行代价可控：CTE 物化只需要把 200 万行数据扫一遍、暂存、再读一遍，不会出现嵌套循环那样指数级增长的消耗。我自己做对比测试时，通常先看默认work_mem下是否有 temp IO，再逐步上调，观察temp written何时归零。

4.2 从 Buffers 看临时读写

EXPLAIN (ANALYZE, BUFFERS)的输出中，temp read和temp written会出现在两类地方：一是顶层汇总，二是各个节点级。物化节点、Sort 节点、HashAgg 节点、CTE 节点都可能自带这些指标。

经验上，节点级出现temp written意味着这个节点自身产生了溢出；顶层汇总里出现而具体节点级没出现的情况，通常是因为临时读写发生在更深层的某个节点里。所以排查时我习惯先看整体计划的temp written总量，再顺着节点逐层往下定位究竟是谁在 spill。

4.3 系统级监控：temp_files 与 temp_bytes

如果查询已经跑在生产环境上，没法随便改 SQL，可以通过pg_stat_database观察整个实例的临时文件使用情况：

SELECT datname, temp_files, temp_bytes FROM pg_stat_database WHERE datname = current_database();

temp_files表示产生的临时文件个数，temp_bytes表示累计写入的字节数。这两个数字是实例级累计值，适合做基线对比：跑一次慢查询前后各查一次，差值就能判断这次查询产生了多少临时文件 IO。

还可以按时间抽样记录，结合pg_stat_statements找到 temp 消耗最大的 SQL。这是我从线上定位"临时文件暴涨"问题的通用套路：先找 SQL，再看计划，最后确认是哪个节点在溢出。

4.4 进一步验证：strace/perf 层面观察临时文件 IO

如果还需要更底层的证据，可以借助操作系统工具。Tuplestore 溢出到文件后，物理读写最终会落到BufFile的读写调用上。在 Linux 上，用strace跟踪写文件的系统调用：

strace -f -e trace=write -p <postgres进程PID>

会看到大量对临时文件描述符的write调用。结合打开文件列表，也能确认文件路径是否在预期表空间下。不过说实话，日常优化里用 EXPLAIN 和统计视图就已经够了，底层的验证更多是用来写文章或者排查异常 IO 的补充手段。

5. Tuplestore 实战中的五个坑（和它们的解法）

5.1 一调 work_mem 就上头：每操作配额与并发叠加

work_mem最常见的误解是把它当成全局内存池。实际上它是一个每操作的配额：每一个 Sort、每一个 Materialize、每一个 Hash Join 的哈希表构建，都有各自独立的work_mem上限。

一个复杂查询如果同时存在 3 个物化/排序节点，那它可能会占用 3 倍work_mem的内存；如果这样的连接有 20 个，内存占用又会乘以 20。很多人简单粗暴地把work_mem从 4MB 调成 1GB，结果在并发稍高时直接把内存打爆，系统开始疯狂 swap，查询反而变得更慢。

我偏向的做法是：保持默认值或一个适中的全局值，针对特定慢查询用SET LOCAL work_mem = '128MB'在事务级别抬升。既不影响整体并发，又能让瓶颈节点的内存充足。

5.2 物化并不总是划算：enable_material 的取舍

Materialize 节点虽然解决重放问题，但物化本身有成本：写入一份、读回一份。如果 inner 侧子节点的执行代价并不高，或者它的输出很大而真正需要的行很少，物化反而会让查询变慢。

PostgreSQL 提供了开关enable_material。我遇到"计划里有 Materialize 且性能异常"时，第一步不是盲改参数，而是对比两个计划：

SET enable_material = off; EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT ...;

SET enable_material = on; EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT ...;

通过两边计划形态和实际耗时的对比，才能判断物化在这里是帮手还是拖油瓶。很多时候需要结合统计信息是否准确、连接选择性预估是否偏差来综合判断。

5.3 临时表空间放错位置

Tuplestore 溢出产生的临时文件，写入位置由temp_tablespaces决定。如果该参数为空，文件会写到数据目录，也就是很可能和数据库主文件在同一块磁盘上。这块盘一旦 IO 被打满，不只是这个慢查询变慢，整个实例的性能都会被拖下水。

我的建议是：如果业务里确实存在大量排序、物化、哈希溢出的场景，准备一块独立的 SSD 作为临时表空间：

CREATE TABLESPACE temp_ts LOCATION '/data/temp'; SET temp_tablespaces = 'temp_ts';

设置之后，可以再通过EXPLAIN (ANALYZE, BUFFERS)验证 temp 读写落到了新位置。临时表空间无需做备份，数据随时可以重建，所以放在单独的廉价盘上非常合适。

5.4 CTE 物化是双刃剑

强制物化 CTE 在某些场景能让子查询只执行一次，但我见过更多的问题是：用户不知道 CTE 默认是否物化、结果集有多大，结果物化了一个超大结果集，Tuplestore 溢出到临时文件，出现大量 temp IO。

PostgreSQL 12 之后，普通WITH子句默认内联（除非被引用多次或有副作用），这在大多数情况下是好事。如果担心重复计算，可以先用WITH ... AS MATERIALIZED显式指定，再对比执行计划和耗时。如果数据量很大，我会选择"宁可不物化"，让优化器自行决定，或者改写为临时表。

5.5 千万别把 Tuplestore 当随机访问容器

Tuplestore 只支持顺序读。如果你发现某个物化缓存需要频繁跳到任意位置读取，说明设计上可能有问题。PostgreSQL 里真正支持随机访问的临时结构是哈希表或 btree。比如哈希连接的内侧构建用的是哈希表，而不是 Tuplestore；如果物化后仍需要按 key 查找，应当考虑改写 SQL，让优化器生成更合适的连接策略，而不是在物化缓存上做笨拙的全量扫描。

6. 从 Tuplestore 延伸出去的执行器设计思考

6.1 全量物化 vs 流式处理：为什么要保留老的物化方式

现代优化器越来越倾向流式执行，但 Tuplestore 这种"全量暂存"的机制并没有被淘汰。原因很简单：并不是所有数据都能在内存里装下，也不是所有场景都能用流式表达。物化是执行器最可靠的兜底方案——内存不够就优雅地退到磁盘，而不是直接报错。

这种"优雅降级"的思想值得品味。Tuplestore 对调用方隐藏了内存和磁盘的差异，使得上层节点可以只用一套代码逻辑处理任意大小的数据。对于数据库这种必须处理"超出内存的数据"的基础软件来说，这种抽象是必需品。

6.2 Tuplestore 与 Tuplesort 家族的关系图

从源码目录看，src/backend/utils/sort/下同时躺着tuplestore.c和tuplesort.c。它们都解决了"临时数据放哪里"的问题，但定位各有侧重：

理解了这两者的区别，再去看执行计划里 Sort 和 Materialize 节点，就不会再把它们的 IO 行为混为一谈。

6.3 想再深入一层，可以看哪些源码文件

如果你和我一样喜欢直接啃源码，建议按这条路径去读：

• src/backend/utils/sort/tuplestore.c：Tuplestore 的完整实现，重点读状态切换
• src/backend/executor/nodeMaterial.c：Materialize 节点如何消费 Tuplestore
• src/backend/executor/nodeRecursiveunion.c：递归查询里 working table 与 result table 的管理
• src/backend/executor/nodeWindowAgg.c：窗口函数分区的缓存逻辑
• src/backend/storage/file/buffile.c：临时文件底层读写抽象

读代码的时候重点关注几个函数的调用关系：tuplestore_puttupleslot到tuplestore_begin_common之间发生了多少次内存分配，tuplestore_gettupleslot在文件态和内存态分别走了哪些路径。把这些搞明白了，Tuplestore 在你的知识体系里就不再是一个黑盒。

我在实际排查调试中还有一个很受用的习惯：遇到临时 IO 暴涨的查询，先别急着调work_mem，而是先用EXPLAIN (ANALYZE, BUFFERS)确认到底哪个节点在 spill，预估那个节点的真实数据量有多大。如果一条 SQL 有五个排序物化点，把work_mem调大五倍远不如把 SQL 拆成两步、消掉两个物化点来得有效。Tuplestore 本身并不复杂，真正复杂的往往是我们对数据量和执行路径的预估——想通这一点，很多执行计划里的"隐形成本"都会变得一目了然。