窗口函数关联场景下避免全表扫描的SQL性能优化咨询

问题背景 #

现有events表，包含字段event_id、correlation_id、username、create_timestamp，数据量超100万条。需求是：为特定用户的每一条事件，展示其同correlation_id（同关联组）的最新事件。

原查询及性能瓶颈 #

当前使用的查询语句结果正确，但性能不佳：

SELECT 
  "events"."event_id" AS "event_id", 
  "latest"."event_id" AS "latest_event_id" 
FROM 
  events "events" 
  JOIN (
    SELECT 
      "latest"."correlation_id" AS "correlation_id", 
      "latest"."event_id" AS "event_id", 
      ROW_NUMBER () OVER (
        PARTITION BY "latest"."correlation_id" 
        ORDER BY 
          "latest"."create_timestamp" ASC
      ) AS "rn" 
    FROM 
      events "latest"
  ) "latest" ON (
    "latest"."correlation_id" = "events"."correlation_id" 
    AND "latest"."rn" = 1
  ) 
WHERE 
  "events"."username" = 'user1'

从执行计划可以看出核心问题：子查询对全表所有事件执行窗口函数计算每个correlation_id的最新事件，这部分成本占比约80%，且即便目标用户无任何事件，全表扫描也会执行。

Hash Right Join  (cost=13538.03..15522.72 rows=1612 width=64)
  Hash Cond: (("latest".correlation_id)::text = ("events".correlation_id)::text)
  ->  Subquery Scan on "latest"  (cost=12031.35..13981.87 rows=300 width=70)
        Filter: ("latest".rn = 1)
        ->  WindowAgg  (cost=12031.35..13231.67 rows=60016 width=86)
              ->  Sort  (cost=12031.35..12181.39 rows=60016 width=78)
                    Sort Key: "latest_1".correlation_id, "latest_1".create_timestamp
                    ->  Seq Scan on events "latest_1"  (cost=0.00..7268.16 rows=60016 width=78)
  ->  Hash  (cost=1486.53..1486.53 rows=1612 width=70)
        ->  Index Scan using events_username on events "events" (cost=0.41..1486.53 rows=1612 width=70)
              Index Cond: ((username)::text = 'user1'::text)

优化方案 #

方案1：先过滤用户事件，再针对性查询同组最新事件 #

核心思路是先获取目标用户的所有事件，拿到涉及的correlation_id集合，再仅对这些correlation_id计算最新事件，避免全表扫描。

方法1.1：使用CTE缩小计算范围

WITH user_events AS (
    SELECT event_id, correlation_id
    FROM events
    WHERE username = 'user1'
)
SELECT 
    u.event_id,
    l.event_id AS latest_event_id
FROM user_events u
JOIN (
    SELECT 
        correlation_id,
        event_id,
        ROW_NUMBER() OVER (PARTITION BY correlation_id ORDER BY create_timestamp DESC) AS rn
    FROM events
    WHERE correlation_id IN (SELECT correlation_id FROM user_events)
) l ON u.correlation_id = l.correlation_id AND l.rn = 1;

方法1.2：使用LATERAL JOIN（推荐）

PostgreSQL的LATERAL连接可以让子查询引用外部表的字段，直接针对用户事件的每个correlation_id查询最新事件，效率更高：

SELECT 
    e.event_id,
    latest.event_id AS latest_event_id
FROM events e
LEFT JOIN LATERAL (
    SELECT event_id
    FROM events
    WHERE correlation_id = e.correlation_id
    ORDER BY create_timestamp DESC
    LIMIT 1
) latest ON true
WHERE e.username = 'user1';

这种方式只会扫描用户事件涉及到的correlation_id对应的行，完全避免全表操作。

方案2：优化索引（针对性覆盖索引） #

如果现有索引不足以支撑高效查询，建议创建以下覆盖索引：

-- 针对用户过滤的索引（若未存在）
CREATE INDEX idx_events_username ON events(username);

-- 针对correlation_id+时间排序的覆盖索引，直接返回所需字段无需回表
CREATE INDEX idx_events_correlation_ts ON events(correlation_id, create_timestamp DESC) INCLUDE (event_id);

这个覆盖索引可以让查询同组最新事件时，直接从索引中获取event_id，不需要访问主表数据。

方案3：预计算物化视图（高频查询场景） #

如果该查询是高频访问的，可以创建物化视图预计算每个correlation_id的最新事件，查询时直接关联即可：

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_latest_events AS
SELECT 
    correlation_id,
    event_id AS latest_event_id
FROM (
    SELECT 
        correlation_id,
        event_id,
        ROW_NUMBER() OVER (PARTITION BY correlation_id ORDER BY create_timestamp DESC) AS rn
    FROM events
) t
WHERE rn = 1;

-- 为物化视图创建索引
CREATE UNIQUE INDEX idx_mv_correlation ON mv_latest_events(correlation_id);

查询时直接关联物化视图：

SELECT 
    e.event_id,
    m.latest_event_id
FROM events e
JOIN mv_latest_events m ON e.correlation_id = m.correlation_id
WHERE e.username = 'user1';

注意：物化视图需要定期刷新以保证数据时效性，可通过REFRESH MATERIALIZED VIEW mv_latest_events;手动刷新，或结合定时任务自动刷新。

内容的提问来源于stack exchange，提问作者vbarashko