PostgreSQL中CTE之间JOIN慢，因默认物化导致重复扫描与落盘；PG12起可用NOT MATERIALIZED内联优化，配合原表索引使Hash Join替代Nested Loop。
CTE之间JOIN为什么慢得明显
PostgreSQL默认把每个WITH子句的结果物化成临时结构，哪怕你只是想把两个CTE简单JOIN，它也可能执行两次全表扫描、写入临时磁盘、再读取——尤其是当CTE里有GROUP BY或大表WHERE过滤时。这不是“写法错”，而是PostgreSQL 12+之前默认的物化行为导致的。你看到EXPLAIN ANALYZE里出现重复的Seq Scan或Materialize节点，基本就是这个原因。
用MATERIALIZED和NOT MATERIALIZED控制物化行为
从PostgreSQL 12起，你可以显式告诉优化器是否物化某个CTE：
-
WITH cte_name AS MATERIALIZED (SELECT ...)：强制物化，适合结果集小、后续多次引用的场景 -
WITH cte_name AS NOT MATERIALIZED (SELECT ...)：禁止物化，让优化器尝试将其内联为子查询，更可能触发Hash Join或Index Scan
例如两个CTE做JOIN时，如果都默认物化，性能可能断崖下跌；改成NOT MATERIALIZED后，优化器常能将整个逻辑重写为单层JOIN，跳过中间落盘。
避免在CTE中提前聚合再JOIN
常见误区：先用CTE算好SUM、COUNT，再和其他CTEJOIN。这会切断优化器对原始表连接条件的下推能力。比如：
WITH sales_by_prod AS (SELECT product_id, SUM(amount) FROM sales GROUP BY product_id),
prod_info AS (SELECT id, name FROM products WHERE category = 'A')
SELECT * FROM sales_by_prod s JOIN prod_info p ON s.product_id = p.id;不如直接写成：
SELECT p.id, p.name, SUM(s.amount) FROM products p JOIN sales s ON p.id = s.product_id WHERE p.category = 'A' GROUP BY p.id, p.name;
后者更容易命中product_id上的索引，也允许WHERE下推到JOIN前过滤。
多个CTE JOIN时的索引关键点
CTE本身不继承索引，但它的底层表有。所以真正起作用的是JOIN列在原表上的索引：
- 确保
JOIN条件字段（如orders.customer_id、customers.id）在各自表上有单列或组合索引 - 如果CTE里有
WHERE过滤，且该条件和JOIN列一起高频出现，优先建组合索引，顺序按“等值条件列在前、范围条件列在后” - 避免在CTE定义中对JOIN列用函数，比如
UPPER(email)——这会让索引失效，即使底层表有索引也没用
复杂点在于：CTE的物化与否、底层表索引质量、以及JOIN列的选择性三者必须同时对齐，少一个环节，Hash Join就可能退化成Nested Loop甚至Seq Scan。
