PostgreSQL窗口函数避坑指南：如何让复杂分析查询提速300%？

-- 问题案例：全表排序导致内存溢出
EXPLAIN ANALYZE
SELECT 
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;  -- 10亿行订单表

->  WindowAgg  (cost=0.00..154793452.34 rows=1000000000 width=24) 
               (actual time=0.036..**1265432.671** ms)
   ->  Index Scan using orders_user_id_idx on orders  
         (cost=0.00..104793452.34 rows=1000000000 width=16) 
         (actual time=0.020..**874329.458** ms)

-- 创建针对性索引
CREATE INDEX orders_user_date_idx ON orders (user_id, order_date) INCLUDE (amount);

-- 优化后执行计划
->  WindowAgg  (cost=0.56..**783452.18** rows=10000000 width=24) 
               (actual time=0.132..**458.21** ms)
   ->  Index Only Scan using orders_user_date_idx on orders  
        (cost=0.56..**283452.18** rows=10000000 width=16) 
        (actual time=0.095..**112.74** ms)

-- 添加分区条件
SELECT ...
FROM orders
WHERE user_id IN (SELECT target_id FROM premium_users)  -- 仅处理高价值用户

-- 创建分区表
CREATE TABLE orders_partitioned (
  user_id BIGINT,
  order_date DATE,
  amount NUMERIC
) PARTITION BY LIST (user_id);

-- 查询自动路由到特定分区
SELECT ... 
FROM orders_partitioned 
WHERE user_id = 10086;

-- 动态调整内存设置
SET work_mem = '256MB';

-- 监控溢出警告
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... 

WARNING:  temporary file size exceeds 1GB
Buffers: shared hit=45, temp read=34210 written=34210

-- 原始慢查询
SELECT 
  user_id,
  AVG(amount) OVER (PARTITION BY city_id ORDER BY order_date 
                    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW)
FROM orders;

-- 优化版本：使用CTE分步计算
WITH daily_summary AS (
  SELECT 
    city_id,
    order_date,
    AVG(amount) AS daily_avg
  FROM orders
  GROUP BY city_id, order_date
)
SELECT
  city_id,
  order_date,
  AVG(daily_avg) OVER (PARTITION BY city_id ORDER BY order_date
                      ROWS 6 PRECEDING) AS rolling_avg
FROM daily_summary;

-- 启用并行查询
SET max_parallel_workers_per_gather = 8;

-- 确保窗口函数可并行化
EXPLAIN (ANALYZE)
SELECT 
  user_id,
  RANK() OVER (PARTITION BY dept ORDER BY sales DESC)
FROM sales_records;

->  Gather  (cost=10432.18..25432.68 rows=100000 width=12)
            Workers Planned: 8
   ->  Parallel WindowAgg  
        (cost=10432.18..**14332.68** rows=12500 width=12)

-- 原始查询：用户会话内行为序列分析
SELECT
  user_id,
  session_id,
  event_time,
  event_type,
  LEAD(event_time) OVER (PARTITION BY session_id ORDER BY event_time) AS next_time,
  NTILE(5) OVER (PARTITION BY session_id ORDER BY event_time) AS behavior_quintile
FROM user_events;  -- 当日20亿条行为记录

CREATE INDEX events_session_time_idx ON user_events (session_id, event_time)
  WITH (fillfactor=95);

CREATE TABLE sessions_partitioned (
  session_id UUID PRIMARY KEY,
  user_id BIGINT
) PARTITION BY HASH (session_id);

-- 行为表继承分区
CREATE TABLE user_events_partitioned (
  session_id UUID REFERENCES sessions_partitioned,
  event_time TIMESTAMPTZ,
  event_type TEXT
) PARTITION BY HASH (session_id);

WITH session_events AS (
  SELECT * 
  FROM user_events_partitioned
  WHERE session_id IN (
    SELECT session_id 
    FROM active_sessions  -- 仅查询活跃会话
    WHERE created_at >= NOW() - INTERVAL '1 day'
  )
)
SELECT 
  session_id,
  event_time,
  event_type,
  LEAD(event_time) OVER w AS next_time,
  NTILE(5) OVER w AS behavior_quintile
FROM session_events
WINDOW w AS (PARTITION BY session_id ORDER BY event_time);

-- 创建窗口物化视图
CREATE MATERIALIZED VIEW session_behavior_mv
AS
SELECT 
  session_id,
  event_time,
  LAST_VALUE(event_type) OVER w AS last_event,
  AVG(EXTRACT(EPOCH FROM duration)) OVER w AS avg_duration
FROM user_events
WINDOW w AS (
  PARTITION BY session_id 
  ORDER BY event_time
  RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW
)
WITH DATA;

-- 增量刷新（PG15）
REFRESH MATERIALIZED VIEW CONCURRENTLY session_behavior_mv;