公用表表达式在一条 SQL 内为查询结果命名。它可以先说明数据总体和中间计算，再写最终 SELECT，让复杂报表更容易阅读。这个名字不是持久表，也不自动承诺缓存结果、固定扫描次数或按顺序执行的一组命令。理解 CTE 时，应把可读结构与执行方式分别检查。

先确定作用域，再讨论执行

WITH 子句只属于当前语句。后续语句不能继续引用它的名字，执行结束后也没有一个可供建立索引或查询的 schema 对象。如果计算结果或查询定义需要保留到后续语句继续使用，应选择临时表、视图或其他合适的数据库对象。CTE 适合命名结果与语句作用域相匹配的工作，不能靠命名制造跨请求缓存。

现代 PostgreSQL 可以把非递归、没有 volatile 函数的 SELECT CTE 折叠进外层查询。默认情况下，只引用一次时会进行这种合并，使外层限制参与共同优化。因此，外层按主键查找时，条件可以直接作用于底层表的索引，而不必先生成独立中间结果再过滤。

同一个符合条件的 CTE 被引用多次时，PostgreSQL 默认通常会物化它。这能减少重复昂贵计算，但也可能阻止某个引用位置上的选择性条件被推入共享计算。这个区别从 PostgreSQL 十二开始适用；认为所有 WITH 都是固定优化边界，描述的是旧行为，不能覆盖当前默认规则。应根据实际版本和引用次数检查，而不是依赖旧经验。

物化是一项取舍

MATERIALIZED 请求独立计算。NOT MATERIALIZED 允许多次引用时也进行折叠，可能重复一部分工作，但让每次使用只访问自己需要的数据。对于递归或不是无副作用 SELECT 的 CTE，PostgreSQL 会忽略 NOT MATERIALIZED；它不会因此允许任意复制写入或 volatile 操作。

物化的 SELECT CTE 也不一定完整算到末尾。消费方可能只请求部分结果，不过排序、关联等操作又可能要求读取更多。物化同样不等于为后续语句建立一张带索引的可复用表。决定强制边界之前，应查看计划并测量真实负载，不能因为“物化”听起来像缓存就默认收益更好。

真正需要比较的是总工作量：多次选择性很高的索引查找，可能比构建并扫描大型共享结果便宜；重复昂贵计算又可能更慢。名字清楚与执行高效可以同时实现，但两者不能互相证明。业务条件应保持单一权威定义，执行控制则应在计划和测量支持时使用，而不是为猜想的性能问题增加边界。

坏掉的样子

修改数据的 CTE 使用不同规则。WITH 内的 INSERT、UPDATE、DELETE，以及 PostgreSQL 18 的 MERGE，即使输出没有被读取，也会执行一次并完成。可被引用的命名关系来自 RETURNING 输出，而不是修改后重新扫描目标表得到的结果。没有 RETURNING 的修改仍会发生，只是不能向后面的引用提供行。

修改子语句和主语句使用同一快照，实际修改顺序没有按文本从上到下依次执行的保证。后续部分需要变更后的行时，应通过 RETURNING 传递。不要在一条语句里尝试修改同一行两次，再期待文本位置决定最终结果。外层看起来只是 SELECT，也不能证明整条带 WITH 的语句只读；执行陌生查询时，必须检查内部是否有写操作。

快速验证

把下面内容保存为 cte.sql，在可丢弃的 PostgreSQL 18 数据库中执行 psql -X -v ON_ERROR_STOP=1 -f cte.sql。脚本先比较四个只读计划，再用 DELETE RETURNING 与 INSERT 搬移两条临时数据。末尾回滚会撤销实验中的修改并清除对象。

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE items (id integer PRIMARY KEY, value text);
INSERT INTO items SELECT g,'item-'||g FROM generate_series(1,10000) AS g;
ANALYZE items;
EXPLAIN (COSTS OFF)
WITH source AS (SELECT * FROM items) SELECT * FROM source WHERE id=7;
EXPLAIN (COSTS OFF)
WITH source AS MATERIALIZED (SELECT * FROM items) SELECT * FROM source WHERE id=7;
EXPLAIN (COSTS OFF)
WITH source AS (SELECT * FROM items)
SELECT a.id FROM source a JOIN source b USING(id) WHERE a.id=7;
EXPLAIN (COSTS OFF)
WITH source AS NOT MATERIALIZED (SELECT * FROM items)
SELECT a.id FROM source a JOIN source b USING(id) WHERE a.id=7;
CREATE TEMP TABLE moved (id integer,value text);
WITH removed AS (DELETE FROM items WHERE id<=2 RETURNING *)
INSERT INTO moved SELECT * FROM removed;
SELECT (SELECT count(*) FROM items) AS remaining,(SELECT count(*) FROM moved) AS moved;
DO $$ BEGIN
  IF (SELECT array_agg(id ORDER BY id) FROM moved) IS DISTINCT FROM ARRAY[1,2] THEN
    RAISE EXCEPTION 'RETURNING handoff failed';
  END IF;
  IF (SELECT count(*) FROM items) <> 9998 THEN RAISE EXCEPTION 'DELETE did not execute'; END IF;
END $$;
ROLLBACK;

PostgreSQL 18.6 中，默认单次引用的计划直接使用 items_pkey。显式 MATERIALIZED 增加 CTE Scan，其输入计划为顺序扫描；默认双次引用也共享一个物化 CTE，而 NOT MATERIALIZED 则形成两次基于索引的访问。这几条普通 EXPLAIN 只展示计划结构，没有测量执行时间，也不能据此宣称生产性能已经改善。

后面的修改语句确实执行了，items 剩余九千九百九十八行，moved 中有两行。断言确认转移的标识恰好是一和二，并确认原表对应行已删除。这里通过 RETURNING 在操作之间交接数据，不依赖目标表重读或文本顺序。减少外层消费量不能安全地阻止修改型 CTE 执行；类似实验应保留事务边界，并在执行前明确所有写入对象。

来源