CTE vs 子查询：深入拆解PostgreSQL复杂SQL的隐藏性能差异

-- 典型CTE使用示例
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 1000000;

-- 等效子查询示例
SELECT region, total_sales
FROM (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
) AS regional_sales
WHERE total_sales > 1000000;

-- 物化特性验证（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 Scan on cte t2
CTE cte
  ->  Seq Scan on large_table
        Filter: (category = 'A')

-- 相关子查询示例
SELECT o.order_id, o.amount,
       (SELECT AVG(amount)
        FROM orders 
        WHERE customer_id = o.customer_id) AS avg_customer_order
FROM orders o;

-- 性能对比测试（100万行数据）
EXPLAIN ANALYZE
-- 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;

-- 索引失效示例
CREATE INDEX idx_orders_date ON orders(order_date);

-- CTE版本（索引失效）
WITH recent_orders AS (
    SELECT * FROM orders WHERE order_date > '2023-01-01'
)
SELECT * FROM recent_orders WHERE customer_id = 100; -- 无法使用customer_id索引

-- 子查询版本（索引生效）
SELECT * 
FROM (
    SELECT * FROM orders WHERE order_date > '2023-01-01'
) AS sub
WHERE customer_id = 100; -- 可使用(customer_id, order_date)复合索引

-- 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;

-- CTE递归实现
WITH RECURSIVE subordinates AS (
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id = 100 -- 指定上级
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;

-- 子查询无法实现递归查询

-- 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
WHERE u.signup_date < '2023-01-01';

-- 子查询方案
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
WHERE u.signup_date < '2023-01-01';

-- 禁用物化（PostgreSQL 12+）
WITH cte_name AS MATERIALIZED (...) -- 默认行为
WITH cte_name AS NOT MATERIALIZED (...) -- 不物化

-- 部分物化示例
WITH 
  materialized_cte AS MATERIALIZED (
    SELECT /*+ 复杂计算 */ ...
  ),
  non_materialized AS NOT MATERIALIZED (
    SELECT /*+ 简单过滤 */ ...
  )
SELECT ...;

-- 转换为LATERAL JOIN
SELECT u.name, latest_order.amount
FROM users u
CROSS JOIN LATERAL (
    SELECT amount
    FROM orders
    WHERE user_id = u.user_id
    ORDER BY order_date DESC
    LIMIT 1
) latest_order;

-- EXISTS代替IN
SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id
    AND o.total > 1000
);

/* 黄金实践组合 */
WITH config AS (
    SELECT '2023-01-01'::date AS start_date, 
           1000 AS min_amount
), -- 配置项CTE
filtered_orders AS NOT MATERIALIZED (
    SELECT * FROM orders
    WHERE order_date > (SELECT start_date FROM config)
    AND amount > (SELECT min_amount FROM config)
) -- 非物化CTE
SELECT o.order_id, c.name
FROM filtered_orders o
JOIN LATERAL (
    SELECT name FROM customers 
    WHERE customer_id = o.customer_id
    LIMIT 1
) c ON true;