SQL 窗口函数为何容易导致全表扫描？

窗口函数性能差主因是PARTITION BY和ORDER BY列缺失联合索引；需建include覆盖聚合字段的联合索引，控制ROWS BETWEEN范围，确保WHERE下推至分区字段，并避免ORDER BY中函数或隐式转换导致索引失效。

窗口函数没走索引？先看 PARTITION BY 和 ORDER BY 列有没有联合索引

postgresql（以及多数主流数据库）的窗口函数本身不直接“触发”全表扫描，但当 PARTITION BY 和 ORDER BY 涉及的列缺少合适索引时，优化器就只能靠全表扫描 + 内存排序来满足窗口计算需求——尤其是像 SUM() OVER (PARTITION BY user_id ORDER BY order_date) 这种带累积逻辑的场景。

典型表现是执行计划里出现 Index Scan 但 rows 值等于全表行数，或者更糟：直接 Seq Scan；同时 windowAgg 节点的 actual time 占比超 90%，说明瓶颈在数据组织阶段，而非计算本身。

• 必须建联合索引：CREATE INDEX idx_orders_user_date ON orders(user_id, order_date) INCLUDE(amount);
• INCLUDE 是关键：把 amount 放进索引，避免回表，让窗口聚合直接从索引页完成
• 别只建单列索引——user_id 单独有索引，order_date 单独有索引，对窗口函数几乎没用
• 验证是否生效：用 EXPLaiN (ANALYZE, BUFFERS) 看 key 是否命中该索引，且 Rows Removed by Filter 接近 0

ROWS BETWEEN 子句写得太宽，内存撑爆后自动落盘

窗口帧（frame）定义直接影响内存占用。比如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 看似合理，但在高基数分组（如千万级用户）下，每个 user_id 的中间状态都要缓存，极易超出 work_mem 限制，触发磁盘临时文件写入——I/O 一上来，耗时翻几倍都是常态。

• 查当前设置：SHOW work_mem;，默认通常只有 4MB，远不够处理百万行以上窗口
• 临时调大（会话级）：SET LOCAL work_mem = '256MB';，但别全局改，防内存争抢
• 更治本：缩小帧范围，例如用 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW 替代无界累积，适合移动平均类需求
• 如果真要无界累积，且数据按 user_id + order_date 严格递增入库，可考虑物化中间结果（如每日跑一次 INSERT INTO daily_running_total...）

WHERE 条件没过滤分区字段，窗口照样扫全量

很多人以为加了 WHERE order_date > '2025-01-01' 就能减少窗口计算量，但若这个条件没覆盖到 PARTITION BY 字段（比如 user_id），PostgreSQL 仍得为每个 user_id 构建完整窗口上下文——哪怕其中 99% 的用户在该时间范围内根本没订单。

• 务必让 WHERE 包含 PARTITION BY 列的约束，例如：WHERE user_id IN (SELECT id FROM active_users WHERE last_login > '2025-01-01')
• 避免在窗口函数外层套子查询过滤，应尽量把过滤下推到窗口源表扫描阶段
• 用 EXPLAIN 对比：加过滤前后，Index Scan 的 Rows Removed by Filter 是否显著下降；没降，说明过滤没生效或没下推

ORDER BY 表达式或函数导致索引失效

就算你建了 (user_id, order_date) 索引，只要 ORDER BY 里写了函数，比如 ORDER BY DATE(order_date) 或 ORDER BY order_date::date，索引就废了——B+树无法按转换后的值有序遍历，优化器只能退回到全表扫描+排序。

• 错误写法：ORDER BY EXTRACT(YEAR FROM order_date)、ORDER BY UPPER(product_name)
• 正确做法：保持 ORDER BY 列“裸露”，必要时提前物化派生列并建索引，例如：ALTER table orders ADD column order_date_date DATE GENERATED ALWAYS AS (order_date::date) STOred;，再建索引 (user_id, order_date_date)
• 特别注意隐式类型转换：如果 order_date 是 timestamptz，而你 WHERE order_date > '2025-01-01' 却没写时区，可能触发时区转换函数，间接导致索引跳过

窗口函数不是银弹，它的性能完全取决于你给优化器喂了什么样的数据结构和约束条件。最常被忽略的一点是：索引建了≠能用，能用≠用得对——EXPLAIN 里那行 Buffers: shared hit=xxx read=yyy 才是真相，别只盯着 Index Scan 四个字就放心。