PostgreSQL 窗口函数与CTE优化

窗口函数优化

窗口函数概述

窗口函数允许在查询结果集中执行聚合操作，同时保留原始行数据。它们在数据分析、报表生成和复杂查询中非常有用。

基本语法：

sql

FUNCTION_NAME() OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC|DESC]]
    [ROWS frame_clause]
)

常见窗口函数

函数类型	示例	用途
排名函数	`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`	为行分配排名
聚合函数	`SUM()`, `AVG()`, `COUNT()`	计算窗口内的聚合值
偏移函数	`LAG()`, `LEAD()`, `FIRST_VALUE()`, `LAST_VALUE()`	访问窗口内其他行的数据
分布函数	`PERCENT_RANK()`, `CUME_DIST()`, `NTILE()`	计算行在分布中的位置

窗口函数优化策略

1. 合理使用 PARTITION BY

原理：PARTITION BY 将数据划分为多个窗口，减少每个窗口的处理数据量。

sql

-- 优化前：无 PARTITION BY，全表作为一个窗口
SELECT 
    order_id, customer_id, total_amount,
    AVG(total_amount) OVER () AS avg_total
FROM orders;

-- 优化后：按客户分区，每个客户作为一个窗口
SELECT 
    order_id, customer_id, total_amount,
    AVG(total_amount) OVER (PARTITION BY customer_id) AS avg_customer_total
FROM orders;

最佳实践：

根据业务需求选择合适的分区键
分区键应具有较高的选择性
避免过度分区导致性能下降

2. 优化 ORDER BY 子句

原理：确保 ORDER BY 列上有索引，避免额外排序。

sql

-- 优化前：ORDER BY 列无索引
SELECT 
    order_id, order_date, total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS cumulative_total
FROM orders;

-- 优化后：创建索引
CREATE INDEX idx_orders_order_date ON orders (order_date, total_amount);

SELECT 
    order_id, order_date, total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS cumulative_total
FROM orders;

最佳实践：

在 ORDER BY 列上创建索引
索引应包含窗口函数中使用的其他列（覆盖索引）
避免在 ORDER BY 中使用复杂表达式

3. 合理设置窗口框架

原理：窗口框架定义了窗口内的行范围，合理设置可以减少计算量。

sql

-- 优化前：无框架子句（默认 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）
SELECT 
    order_id, order_date, total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

-- 优化后：明确指定框架（最近30天）
SELECT 
    order_id, order_date, total_amount,
    SUM(total_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date 
        RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
    ) AS 30day_total
FROM orders;

最佳实践：

明确指定必要的窗口框架
避免使用过大的窗口框架
优先使用 ROWS 而非 RANGE（ROWS 计算更快）

4. 避免在窗口函数中使用复杂表达式

原理：复杂表达式会增加窗口函数的计算开销。

sql

-- 优化前：窗口函数中使用复杂表达式
SELECT 
    order_id, total_amount,
    SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) 
        OVER (PARTITION BY customer_id) AS completed_total
FROM orders;

-- 优化后：提前计算表达式结果
SELECT 
    order_id, total_amount, completed_amount,
    SUM(completed_amount) OVER (PARTITION BY customer_id) AS completed_total
FROM (
    SELECT 
        order_id, customer_id, total_amount, status,
        CASE WHEN status = 'completed' THEN total_amount ELSE 0 END AS completed_amount
    FROM orders
) sub;

5. 版本差异优化

PostgreSQL 11+：支持并行窗口函数执行
PostgreSQL 10+：支持在分区表上使用窗口函数
PostgreSQL 9.6+：支持窗口函数的查询优化

配置并行查询：

ini

max_parallel_workers = 4
max_parallel_workers_per_gather = 2

窗口函数性能分析

使用 EXPLAIN ANALYZE 分析窗口函数

sql

EXPLAIN ANALYZE
SELECT 
    order_id, customer_id, total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS row_num,
    SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

执行计划关键点：

WindowAgg：窗口函数执行节点
Sort：ORDER BY 导致的排序
Hash 或 Seq Scan：数据访问方式
Workers Planned/Workers Launched：并行执行情况

监控窗口函数性能

使用 pg_stat_statements 查看窗口函数查询的执行情况：

sql

SELECT 
    queryid,
    query,
    calls,
    total_exec_time,
    mean_exec_time
FROM pg_stat_statements 
WHERE query LIKE '%OVER (%' 
ORDER BY total_exec_time DESC 
LIMIT 10;

CTE（Common Table Expressions）优化

CTE 概述

CTE 是一种临时结果集，允许在查询中多次引用。它们提高了查询的可读性和可维护性，支持递归查询，并避免重复计算。

基本语法：

sql

WITH cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

CTE 优化策略

1. 利用 CTE 减少重复计算

原理：将复杂子查询封装为 CTE，避免多次执行相同的计算。

sql

-- 优化前：重复计算
SELECT 
    (SELECT SUM(total_amount) FROM orders WHERE customer_id = c.customer_id) AS total_orders,
    (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id AND status = 'completed') AS completed_count
FROM customers c;

-- 优化后：使用 CTE 减少重复计算
WITH customer_stats AS (
    SELECT 
        customer_id,
        SUM(total_amount) AS total_orders,
        COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count
    FROM orders
    GROUP BY customer_id
)
SELECT 
    c.*, cs.total_orders, cs.completed_count
FROM customers c
LEFT JOIN customer_stats cs ON c.customer_id = cs.customer_id;

2. 优化递归 CTE

原理：递归 CTE 用于处理层级数据，优化递归终止条件和递归逻辑可以提高性能。

sql

-- 优化前：可能导致无限递归
WITH RECURSIVE org_hierarchy AS (
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.employee_id, e.manager_id, e.name, oh.level + 1
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy;

-- 优化后：添加递归深度限制
WITH RECURSIVE org_hierarchy AS (
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.employee_id, e.manager_id, e.name, oh.level + 1
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
    WHERE oh.level < 10  -- 限制递归深度
)
SELECT * FROM org_hierarchy;

最佳实践：

总是添加递归深度限制
确保递归终止条件正确
避免在递归 CTE 中使用复杂逻辑

3. 利用 CTE 物化优化

原理：PostgreSQL 12+ 支持 CTE 物化，允许显式控制 CTE 是否被物化。

sql

-- 物化 CTE（PostgreSQL 12+）
WITH cte AS MATERIALIZED (
    SELECT * FROM large_table WHERE complex_condition
)
SELECT * FROM cte WHERE additional_condition;

-- 不物化 CTE（允许优化器内联）
WITH cte AS NOT MATERIALIZED (
    SELECT * FROM small_table WHERE simple_condition
)
SELECT * FROM cte WHERE additional_condition;

最佳实践：

对于大结果集的 CTE，使用 MATERIALIZED
对于小结果集的 CTE，使用 NOT MATERIALIZED 或不指定
测试物化和非物化的性能差异

4. 避免 CTE 作为优化屏障

原理：在 PostgreSQL 12 之前，CTE 被视为优化屏障，优化器不会将 CTE 与外部查询合并优化。

优化方案：

升级到 PostgreSQL 12+
对于旧版本，考虑将 CTE 重写为子查询
或手动优化 CTE 内部逻辑

5. 使用 CTE 简化复杂查询

原理：将复杂查询拆分为多个简单 CTE，提高可读性和可维护性。

sql

WITH 
-- 步骤1：获取最近30天的订单
recent_orders AS (
    SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL '30 days'
),
-- 步骤2：计算每个客户的订单统计
customer_stats AS (
    SELECT 
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_sales
    FROM recent_orders
    GROUP BY customer_id
),
-- 步骤3：获取高价值客户
high_value_customers AS (
    SELECT * FROM customer_stats WHERE total_sales > 10000
)
-- 最终查询
SELECT 
    u.username,
    hvc.order_count,
    hvc.total_sales
FROM high_value_customers hvc
JOIN users u ON hvc.customer_id = u.user_id
ORDER BY hvc.total_sales DESC;

CTE 性能分析

使用 EXPLAIN ANALYZE 分析 CTE

sql

EXPLAIN ANALYZE
WITH cte AS (
    SELECT customer_id, SUM(total_amount) AS total_sales
    FROM orders
    GROUP BY customer_id
)
SELECT u.username, cte.total_sales
FROM users u
JOIN cte ON u.user_id = cte.customer_id
WHERE cte.total_sales > 5000;

执行计划关键点：

CTE Scan：CTE 结果扫描
CTE cte：CTE 定义
Hash Join 或 Nested Loop：CTE 与其他表的连接方式
Materialize：CTE 物化节点

CTE 与子查询性能比较

场景：简单查询

sql

-- CTE 版本
WITH cte AS (
    SELECT * FROM users WHERE active = true
)
SELECT * FROM cte WHERE username LIKE 'a%';

-- 子查询版本
SELECT * FROM (
    SELECT * FROM users WHERE active = true
) sub WHERE username LIKE 'a%';

性能差异：

PostgreSQL 12+：性能相当，优化器会自动优化
PostgreSQL 11-：子查询可能更快，因为 CTE 是优化屏障

窗口函数与 CTE 结合使用

适用场景

复杂数据分析
报表生成
层级数据处理
多步骤数据转换

优化示例

sql

-- 优化前：复杂嵌套查询
SELECT 
    order_id, customer_id, total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS row_num
FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM orders 
    GROUP BY customer_id 
    HAVING SUM(total_amount) > 10000
);

-- 优化后：CTE + 窗口函数
WITH high_value_customers AS (
    SELECT customer_id 
    FROM orders 
    GROUP BY customer_id 
    HAVING SUM(total_amount) > 10000
)
SELECT 
    o.order_id, o.customer_id, o.total_amount,
    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) AS row_num
FROM orders o
JOIN high_value_customers hvc ON o.customer_id = hvc.customer_id;

最佳实践

1. 窗口函数最佳实践

合理设计窗口范围，避免过大的窗口
确保 ORDER BY 列上有索引
优先使用 ROWS 而非 RANGE 框架
避免在窗口函数中使用复杂表达式
考虑使用并行查询优化大窗口计算

2. CTE 最佳实践

利用 CTE 提高查询可读性
避免过度使用 CTE
对于 PostgreSQL 12+，合理使用 MATERIALIZED 关键字
递归 CTE 必须添加深度限制
监控 CTE 的执行性能

3. 综合最佳实践

根据数据量和复杂度选择合适的查询方式
总是使用 EXPLAIN ANALYZE 分析性能
定期更新表统计信息
调整 work_mem 参数优化窗口函数和排序
考虑使用物化视图缓存复杂查询结果

常见问题（FAQ）

Q1: 窗口函数为什么这么慢？

A: 可能的原因：

窗口函数需要排序，而 ORDER BY 列上没有索引
窗口范围过大
数据量过大
没有使用并行查询
work_mem 不足导致磁盘排序

解决方案：

在 ORDER BY 列上创建索引
缩小窗口范围
增加 work_mem 参数
启用并行查询
考虑使用物化视图

Q2: 如何选择 CTE 物化还是非物化？

对于大结果集（>10000行），使用 MATERIALIZED
对于小结果集，使用 NOT MATERIALIZED 或不指定
测试两种方式的性能差异
考虑 CTE 是否被多次引用

Q3: 窗口函数可以与 GROUP BY 一起使用吗？

A: 可以，但需要注意执行顺序：GROUP BY 先执行，然后是窗口函数。

sql

SELECT 
    customer_id,
    COUNT(*) AS order_count,
    AVG(total_amount) OVER () AS avg_total
FROM orders
GROUP BY customer_id;

Q4: CTE 可以递归引用自己吗？

A: 可以，但需要使用 RECURSIVE 关键字，并确保有正确的终止条件。

sql

WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 10
)
SELECT * FROM cte;

Q5: 如何优化包含多个窗口函数的查询？

尝试合并多个窗口函数的 OVER 子句
确保所有窗口函数使用相同的 PARTITION BY 和 ORDER BY
考虑使用 CTE 预处理数据
调整 work_mem 参数

Q6: PostgreSQL 版本对 CTE 和窗口函数性能有影响吗？

A: 是的，主要影响：

PostgreSQL 12+：支持 CTE 物化，消除优化屏障
PostgreSQL 11+：支持并行窗口函数
PostgreSQL 10+：支持在分区表上使用窗口函数
PostgreSQL 9.6+：支持窗口函数的查询优化

性能调优案例

案例1：电商平台订单分析

问题：计算每个客户的订单排名和累计销售额，查询执行缓慢。

原始查询：

sql

SELECT 
    order_id, customer_id, order_date, total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_rank,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_sales,
    AVG(total_amount) OVER (PARTITION BY customer_id) AS avg_order_amount
FROM orders;

优化方案：

创建复合索引：CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date, total_amount);
调整 work_mem：SET work_mem = '64MB';
启用并行查询

优化后性能：查询时间从 500ms 降低到 80ms。

案例2：销售报表生成

问题：生成包含多个 CTE 和窗口函数的复杂销售报表，执行时间过长。

原始查询：包含 5 个 CTE 和多个窗口函数

优化方案：

对大结果集的 CTE 使用 MATERIALIZED
优化每个 CTE 内部的查询
合并相同的窗口函数 OVER 子句
创建必要的索引
使用物化视图缓存报表数据

优化后性能：报表生成时间从 10 分钟降低到 30 秒。

总结

窗口函数和 CTE 是 PostgreSQL 中强大的功能，用于复杂查询和数据分析。通过合理使用优化策略，可以显著提高它们的性能。

关键优化点

窗口函数：
- 合理使用 PARTITION BY 和 ORDER BY
- 优化窗口框架
- 确保 ORDER BY 列上有索引
- 调整 work_mem 参数
- 启用并行查询
CTE：
- 利用 CTE 提高可读性
- 合理使用物化选项（PostgreSQL 12+）
- 避免 CTE 作为优化屏障
- 优化递归 CTE
- 监控 CTE 性能
综合优化：
- 使用 EXPLAIN ANALYZE 分析性能
- 定期更新统计信息
- 考虑使用物化视图
- 测试不同优化策略
- 升级到较新的 PostgreSQL 版本

通过遵循这些优化策略和最佳实践，可以充分发挥窗口函数和 CTE 的强大功能，同时保持良好的性能，满足生产环境的需求。

PostgreSQL 窗口函数与CTE优化 ​

窗口函数优化 ​

窗口函数概述 ​

常见窗口函数 ​

窗口函数优化策略 ​

1. 合理使用 PARTITION BY ​

2. 优化 ORDER BY 子句 ​

3. 合理设置窗口框架 ​

4. 避免在窗口函数中使用复杂表达式 ​

5. 版本差异优化 ​

窗口函数性能分析 ​

使用 EXPLAIN ANALYZE 分析窗口函数 ​

监控窗口函数性能 ​

CTE（Common Table Expressions）优化 ​

CTE 概述 ​

CTE 优化策略 ​

1. 利用 CTE 减少重复计算 ​

2. 优化递归 CTE ​

3. 利用 CTE 物化优化 ​

4. 避免 CTE 作为优化屏障 ​

5. 使用 CTE 简化复杂查询 ​

CTE 性能分析 ​

使用 EXPLAIN ANALYZE 分析 CTE ​

CTE 与子查询性能比较 ​

窗口函数与 CTE 结合使用 ​

适用场景 ​

优化示例 ​

最佳实践 ​

1. 窗口函数最佳实践 ​

2. CTE 最佳实践 ​

3. 综合最佳实践 ​

常见问题（FAQ） ​

Q1: 窗口函数为什么这么慢？ ​

Q2: 如何选择 CTE 物化还是非物化？ ​

Q3: 窗口函数可以与 GROUP BY 一起使用吗？ ​

Q4: CTE 可以递归引用自己吗？ ​

Q5: 如何优化包含多个窗口函数的查询？ ​

Q6: PostgreSQL 版本对 CTE 和窗口函数性能有影响吗？ ​

性能调优案例 ​

案例1：电商平台订单分析 ​

案例2：销售报表生成 ​

总结 ​

关键优化点 ​

PostgreSQL 窗口函数与CTE优化

窗口函数优化

窗口函数概述

常见窗口函数

窗口函数优化策略

1. 合理使用 PARTITION BY

2. 优化 ORDER BY 子句

3. 合理设置窗口框架

4. 避免在窗口函数中使用复杂表达式

5. 版本差异优化

窗口函数性能分析

使用 EXPLAIN ANALYZE 分析窗口函数

监控窗口函数性能

CTE（Common Table Expressions）优化

CTE 概述

CTE 优化策略

1. 利用 CTE 减少重复计算

2. 优化递归 CTE

3. 利用 CTE 物化优化

4. 避免 CTE 作为优化屏障

5. 使用 CTE 简化复杂查询

CTE 性能分析

使用 EXPLAIN ANALYZE 分析 CTE

CTE 与子查询性能比较

窗口函数与 CTE 结合使用

适用场景

优化示例

最佳实践

1. 窗口函数最佳实践

2. CTE 最佳实践

3. 综合最佳实践

常见问题（FAQ）

Q1: 窗口函数为什么这么慢？

Q2: 如何选择 CTE 物化还是非物化？

Q3: 窗口函数可以与 GROUP BY 一起使用吗？

Q4: CTE 可以递归引用自己吗？

Q5: 如何优化包含多个窗口函数的查询？

Q6: PostgreSQL 版本对 CTE 和窗口函数性能有影响吗？

性能调优案例

案例1：电商平台订单分析

案例2：销售报表生成

总结

关键优化点