PostgreSQL里的子查询和CTE居然在性能上“掐架”？到底该站哪边？

一、子查询与CTE的基本概念

1.1 什么是子查询？

子查询是嵌套在其他查询中的查询语句，本质是“用一个查询的结果作为另一个查询的输入”。根据是否依赖外部查询，分为两类：

• 非相关子查询：可独立执行，不依赖外部查询的任何值（比如“先统计各地区销售额，再过滤超过100万的地区”）；
• 相关子查询：依赖外部查询的字段值（比如“计算每个订单对应的客户平均订单金额”）。

示例：非相关子查询

-- 统计销售额超100万的地区（非相关子查询）
SELECT region, total_sales
FROM (
    SELECT region, SUM(amount) AS total_sales  -- 子查询：统计各地区销售额
    FROM orders
    GROUP BY region
) AS regional_sales
WHERE total_sales > 1000000;

示例：相关子查询

-- 计算每个订单的客户平均订单金额（相关子查询）
SELECT o.order_id, o.amount,
       (SELECT AVG(amount)
        FROM orders 
        WHERE customer_id = o.customer_id) AS avg_customer_order  -- 依赖外部的o.customer_id
FROM orders o;

1.2 什么是CTE（公共表表达式）？

CTE（Common Table Expression）用WITH子句定义，是命名的临时结果集，用于简化复杂查询的逻辑结构。它的核心特性是物化（默认生成临时表），且只执行一次（即使多次引用）。

示例：基础CTE

-- 用CTE实现“销售额超100万的地区”
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales  -- CTE：定义临时结果集
    FROM orders
    GROUP BY region
)
SELECT region, total_sales
FROM regional_sales  -- 引用CTE
WHERE total_sales > 1000000;

二、底层执行机制：为什么性能不同？

2.1 CTE的物化特性与执行流程

CTE的关键是物化（Materialized）：执行时会先将CTE的结果写入临时表，再供主查询使用。这个过程类似“先把中间结果存到一张临时表，再查这张表”。

示例：CTE的执行计划（EXPLAIN ANALYZE）

EXPLAIN ANALYZE
WITH cte AS (
    SELECT * FROM large_table WHERE category = 'A'
)
SELECT * FROM cte t1 JOIN cte t2 ON t1.id = t2.parent_id;

执行计划结果：

CTE Scan on cte t1  -- 扫描CTE的临时表
CTE Scan on cte t2  -- 再次扫描同一临时表
CTE cte
  ->  Seq Scan on large_table  -- CTE的实际执行逻辑
        Filter: (category = 'A')

说明：CTE只执行一次（Seq Scan on large_table），生成的临时表被两次引用，避免了重复计算，但增加了临时表的I/O开销。

2.2 子查询的优化融合机制

子查询的优势在于优化器融合：PostgreSQL会尝试将子查询逻辑合并到主查询计划中，比如将非相关子查询转换为JOIN，或对相关子查询使用LATERAL JOIN优化。

示例：子查询的优化结果 对于非相关子查询：

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'China');

优化器会将其转换为JOIN：

SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.country = 'China';

这样避免了子查询的独立执行，直接利用JOIN的高效性。

三、性能差异的关键场景分析

3.1 物化带来的双刃剑：I/O vs 重复计算

CTE的物化是把“双刃剑”：

• 优势：多次引用同一CTE时，避免重复计算（比如上面的两次JOIN）；
• 劣势：生成临时表会增加I/O开销，尤其是当CTE结果集很大时。

实战测试（100万行数据）：

-- CTE版本：物化临时表
WITH cte AS (SELECT * FROM events WHERE event_time > NOW() - INTERVAL '1 day')
SELECT user_id, COUNT(*) FROM cte GROUP BY user_id;

-- 子查询版本：优化器融合
SELECT user_id, COUNT(*) 
FROM (SELECT * FROM events WHERE event_time > NOW() - INTERVAL '1 day') AS sub
GROUP BY user_id;

性能结果：

3.2 索引利用：CTE的“黑盒”限制vs子查询的谓词下推

CTE是黑盒：主查询的条件无法传递到CTE内部，导致索引无法被有效利用；而子查询的条件会被“下推”到内部，直接命中索引。

示例：索引失效的CTE

-- 创建索引（order_date）
CREATE INDEX idx_orders_date ON orders(order_date);

-- CTE版本：无法利用customer_id索引
WITH recent_orders AS (
    SELECT * FROM orders WHERE order_date > '2023-01-01'
)
SELECT * FROM recent_orders WHERE customer_id = 100;  -- 全表扫描recent_orders

-- 子查询版本：利用(customer_id, order_date)复合索引
SELECT * 
FROM (SELECT * FROM orders WHERE order_date > '2023-01-01') AS sub
WHERE customer_id = 100;  -- 索引扫描orders

说明：子查询的条件customer_id = 100会被下推到orders表的查询中，直接使用复合索引；而CTE的recent_orders是临时表，没有customer_id索引，只能全表扫描。

3.3 递归查询：CTE的独占场景

递归查询（比如“查找所有下级”“路径遍历”）是CTE的独占场景，子查询无法实现。

示例：递归CTE查询组织层级

WITH RECURSIVE subordinates AS (
    -- 锚点成员：初始上级（manager_id=100）
    SELECT employee_id, name, manager_id FROM employees WHERE manager_id = 100
    UNION ALL
    -- 递归成员：连接下级（e.manager_id = s.employee_id）
    SELECT e.employee_id, e.name, e.manager_id FROM employees e
    JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;

说明：递归CTE通过UNION ALL连接“锚点成员”（初始查询）和“递归成员”（下级查询），直到没有新结果为止。子查询无法实现这种层级迭代。

四、实战案例：从代码到性能对比

4.1 案例一：多层聚合查询

需求：计算每个地区销售额前10的产品。

CTE实现：

WITH regional_products AS (
    SELECT region, product_id, SUM(quantity*price) AS sales FROM orders GROUP BY region, product_id
),
ranked_products AS (
    SELECT region, product_id, sales,
           RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank
    FROM regional_products
)
SELECT region, product_id, sales FROM ranked_products WHERE rank <=10;

子查询实现：

SELECT region, product_id, sales FROM (
    SELECT region, product_id, sales,
           RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank
    FROM (
        SELECT region, product_id, SUM(quantity*price) AS sales FROM orders GROUP BY region, product_id
    ) AS agg
) AS ranked WHERE rank <=10;

性能对比（1GB数据集）：

结论：子查询的优化融合（将三层查询合并为单次聚合）避免了CTE的临时表I/O，性能更优。

4.2 案例二：多维度关联分析

需求：关联用户行为数据（events）和交易数据（orders），计算每个用户的行为次数和总消费。

CTE实现：

WITH user_events AS (
    SELECT user_id, COUNT(*) AS event_count FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31' GROUP BY user_id
),
user_orders AS (
    SELECT user_id, SUM(amount) AS total_spent FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' GROUP BY user_id
)
SELECT u.user_id, e.event_count, o.total_spent FROM users u
LEFT JOIN user_events e ON u.user_id = e.user_id
LEFT JOIN user_orders o ON u.user_id = o.user_id;

子查询实现：

SELECT u.user_id,
       (SELECT COUNT(*) FROM events e WHERE e.user_id = u.user_id AND e.event_date BETWEEN '2023-01-01' AND '2023-01-31') AS event_count,
       (SELECT SUM(amount) FROM orders o WHERE o.user_id = u.user_id AND o.order_date BETWEEN '2023-01-01' AND '2023-01-31') AS total_spent
FROM users u;

性能对比：

• 当users表较小时（<1000行）：子查询更优（避免CTE的临时表）；
• 当users表较大时（>10000行）：CTE更优（避免子查询的重复扫描）。

五、决策指南：何时选CTE，何时选子查询？

5.1 优先选CTE的场景

5.2 优先选子查询的场景

六、高级优化技巧：突破性能瓶颈

6.1 CTE的物化控制：NOT MATERIALIZED

PostgreSQL 12+支持NOT MATERIALIZED选项，让CTE不生成临时表，允许优化器将CTE逻辑融合到主查询中。

示例：

WITH cte AS NOT MATERIALIZED (
    SELECT * FROM large_table WHERE category = 'A'
)
SELECT * FROM cte WHERE id = 100;  -- 优化器会将条件下推到large_table

说明：NOT MATERIALIZED适合CTE结果集大，但主查询有过滤条件的场景，避免临时表的I/O开销。

6.2 子查询的LATERAL JOIN优化

对于相关子查询（依赖外部表的字段），可以用LATERAL JOIN替代，提高性能。

示例：查找每个用户的最新订单

-- 相关子查询（性能差）
SELECT u.name, (SELECT amount FROM orders WHERE user_id=u.id ORDER BY order_date DESC LIMIT 1) AS latest_amount
FROM users u;

-- LATERAL JOIN优化（性能优）
SELECT u.name, o.amount FROM users u
CROSS JOIN LATERAL (
    SELECT amount FROM orders WHERE user_id=u.id ORDER BY order_date DESC LIMIT 1
) AS o;

说明：LATERAL JOIN允许子查询引用外部表（u.id），且优化器会为每个用户高效查找最新订单。

七、PostgreSQL版本对性能的影响

不同版本的优化能力差异很大，建议使用12+版本以获得更好的CTE和子查询支持：

课后Quiz：巩固你的理解

1. 以下哪种场景必须使用CTE？
   A. 单层嵌套查询 B. 递归路径查询 C. 小结果集过滤 D. 索引利用
   答案：B。解析：递归查询需要RECURSIVE关键字，子查询无法实现。

2. PostgreSQL 12+中，如何让CTE不生成临时表？
   答案：使用WITH cte_name AS NOT MATERIALIZED (...)。解析：NOT MATERIALIZED允许优化器融合CTE逻辑到主查询。

3. 子查询相比CTE更易利用索引的原因是？
   答案：子查询参与整体优化，允许谓词下推；CTE是“黑盒”，外部条件无法传递到内部。

常见报错与解决

1. ERROR: recursive query without RECURSIVE keyword

原因：递归CTE忘记写RECURSIVE关键字。
解决：在WITH后添加RECURSIVE，如WITH RECURSIVE subordinates AS (...)。
预防：写递归CTE时检查是否包含RECURSIVE。

2. ERROR: relation "cte_name" does not exist

原因：CTE的引用顺序错误（比如在定义前引用）。
解决：按顺序定义CTE，先定义的CTE可以被后定义的引用，如WITH cte1 AS (...), cte2 AS (SELECT * FROM cte1 ...)。
预防：先定义基础CTE，再定义依赖它的CTE。

3. ERROR: subquery in FROM cannot refer to other relations of same query level

原因：FROM子句中的子查询引用了同一层级的表（如SELECT * FROM (SELECT * FROM t1 WHERE id=t2.id) AS sub, t2）。
解决：使用LATERAL JOIN，如SELECT * FROM t2 CROSS JOIN LATERAL (SELECT * FROM t1 WHERE id=t2.id) AS sub。
预防：FROM子句中的子查询如需引用外部表，使用LATERAL JOIN。

参考链接

1. PostgreSQL官方文档：www.postgresql.org/docs/17/que…