如果你平时用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 内部维护了一个状态机核心是三种状态状态含义行为TSS_INMEM全部数据在内存写入就是往内存上下文里分配 tuple读取就是遍历内存数组TSS_WRITEFILE正在向文件写内存占用达到阈值把已有内存数据逐步转存到临时文件之后新数据直接追加写文件TSS_READFILE从文件读回读取位置在文件中移动按块批量读入缓冲后再逐 tuple 返回初始状态一定是内存态。随着写入量增长一旦超过阈值就切换到写文件态。在写文件态下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. 哪些计划节点在背地里使用 Tuplestore3.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 tracewrite -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。它们都解决了临时数据放哪里的问题但定位各有侧重组件排序随机访问典型使用者Tuplestore否保持插入序否只支持顺序回放Materialize、CTE、RecursiveUnion、WindowAggTuplesort是完整外部排序部分场景支持Sort 节点、归并连接前的排序理解了这两者的区别再去看执行计划里 Sort 和 Materialize 节点就不会再把它们的 IO 行为混为一谈。6.3 想再深入一层可以看哪些源码文件如果你和我一样喜欢直接啃源码建议按这条路径去读src/backend/utils/sort/tuplestore.cTuplestore 的完整实现重点读状态切换src/backend/executor/nodeMaterial.cMaterialize 节点如何消费 Tuplestoresrc/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 本身并不复杂真正复杂的往往是我们对数据量和执行路径的预估——想通这一点很多执行计划里的隐形成本都会变得一目了然。