现在，是时候聊聊CTE的黑暗面了——常见的错误。就算你写的SQL再牛逼，CTE用错了也能让一切崩掉。不过别慌，这里有一份排查和防坑指南，帮你轻松搞定！
1. 错误：CTE物化带来的坑
PostgreSQL用CTE时有个很重要的点——默认会物化。意思就是CTE的结果会先算出来，然后暂时存内存（或者数据太多就落盘）。如果你CTE用得多或者数据量大，这会让SQL慢得飞起。
举个例子：
WITH heavy_data AS (
SELECT * FROM large_table
)
SELECT * FROM heavy_data WHERE column_a > 100;
乍一看像是CTE帮你过滤数据了。但实际上，heavy_data会先全量加载并物化，然后才过滤。这很可能超级慢。
怎么避坑？
PostgreSQL 12开始，CTE可以当成inline表达式（就像子查询），这样就不会物化了。只要你的CTE只用一次、没必要保存中间结果，就可以这么写。
优化写法示例：
WITH inline_data AS MATERIALIZED (
SELECT * FROM large_table
)
SELECT * FROM inline_data WHERE column_a > 100;
小贴士：想让它物化就写MATERIALIZED，不想物化就用NOT MATERIALIZED。
2. 错误：递归CTE死循环
递归CTE很强大，但如果你不限制递归深度，很容易死循环。这不仅会拖慢SQL，还能把资源吃光。
举个例子：
WITH RECURSIVE endless_loop AS (
SELECT 1 AS value
UNION ALL
SELECT value + 1
FROM endless_loop
)
SELECT * FROM endless_loop;这个写法会生成无穷多行，因为没有终止递归的条件。
怎么避坑？
加个明确的终止条件，比如用WHERE：
WITH RECURSIVE limited_loop AS (
SELECT 1 AS value
UNION ALL
SELECT value + 1
FROM limited_loop
WHERE value < 10
)
SELECT * FROM limited_loop;小贴士：如果你用递归CTE查大树结构，记得用PostgreSQL的max_recursion_depth限制递归深度。
3. 错误：UNION和UNION ALL用错了
你在CTE里合并基础查询和递归查询时，选错UNION还是UNION ALL会出大问题。比如UNION会去重，计算量还大。
举个例子：
WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, manager_id
FROM employees
WHERE manager_id IS NULL
UNION -- 这里其实应该用UNION ALL
SELECT e.employee_id, e.manager_id
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;上面这个例子里，UNION可能会把重要的层级数据误删掉，还会拖慢SQL！
怎么改？
没必要去重就用UNION ALL：
UNION ALL
4. 错误：一个SQL里塞太多CTE
有些人为了让SQL结构清晰，疯狂加CTE，十几个二十个一起上。这样不仅代码乱，还会把PostgreSQL的查询优化器搞晕。
举个例子：
WITH cte1 AS (...),
cte2 AS (...),
cte3 AS (...),
...
cte20 AS (...)
SELECT ...
FROM cte20;
这简直是开发者的噩梦。
怎么改？
—— 把SQL拆成几个简单点的。别写那种几十个CTE的巨型SQL，分开写更清楚。
—— 还有一种办法：中间结果要用多次的话，直接存临时表。
5. 错误：复杂CTE没加索引
CTE查大表时，如果你忘了给表加索引，SQL会慢到怀疑人生。索引就是数据库的加速器。
举个例子：
WITH filtered_data AS (
SELECT * FROM large_table WHERE unindexed_column = 'value'
)
SELECT * FROM filtered_data;
怎么改？
用CTE前，先确保表已经优化过：
CREATE INDEX idx_large_table ON large_table(unindexed_column);
6. 错误：想用CTE多次复用数据
CTE是一次性算出来然后“冻结”的。如果你想在多个地方用它的结果，数据不会重新计算——有时候这会出bug。
举个例子：
WITH data AS (
SELECT x, y FROM some_table
)
SELECT x FROM data
WHERE y > 10;
-- 如果你还想再算一次data，其实不会重新算。
怎么改？
如果你需要动态或多次计算，CTE可能不是最佳选择。用子查询吧。
7. 错误：没写注释
CTE很强，但没人喜欢那种两周后自己都看不懂的SQL。
举个例子：
WITH data_filtered AS (
SELECT *
FROM large_table
WHERE some_column > 100
)
SELECT * FROM data_filtered;
一个月后你肯定忘了为啥要过滤这些数据！
所以一定要写注释，尤其是复杂或递归CTE：
WITH data_filtered AS (
-- 按some_column > 100过滤数据
SELECT *
FROM large_table
WHERE some_column > 100
)
SELECT * FROM data_filtered;
8. 错误：滥用CTE，明明该用临时表
有时候临时表更合适。比如你要在不同SQL里多次用同一份结果，或者数据量特别大。
举个例子：
WITH temp_data AS (
SELECT * FROM large_table
)
SELECT * FROM temp_data WHERE column_a > 100;
SELECT * FROM temp_data WHERE column_b < 50;
这种写法会让CTE执行两次，明明数据没变！
怎么改？
如果数据要多次用，直接建临时表：
CREATE TEMP TABLE temp_table AS
SELECT * FROM large_table;
SELECT * FROM temp_table WHERE column_a > 100;
SELECT * FROM temp_table WHERE column_b < 50;最后的建议
CTE很强，但不是万能钥匙。用之前想清楚你到底要干嘛。别觉得“CTE越多越牛”，其实这样只会让SQL又慢又难懂。记得做性能测试，及时优化你的SQL。
GO TO FULL VERSION