PostgreSQL慢查询优化诉求：300万+事件记录查询性能提升

针对你这个突然变慢的SQL查询，结合300万+的events表数据量，咱们从查询语句调整和索引优化两个方向来拆解优化方案：

一、查询语句调整 #

1. 替换JSONB字段的直接引用，改用生成列 #

你当前查询里多次用到events.metadata->?和events.metadata->>?来过滤和分组，JSONB字段的直接操作效率远不如普通列。建议给events表新增一个生成列来存储这个常用的JSONB属性：

ALTER TABLE events 
ADD COLUMN metadata_event_kind TEXT 
GENERATED ALWAYS AS (metadata->>'event_kind') STORED;

之后把查询里的events.metadata->?和events.metadata->>?全替换成events.metadata_event_kind，这样后续的过滤、分组操作都能直接用普通列，效率提升明显。

2. 提前过滤events表，减少JOIN数据量 #

因为events表是数据量最大的表，先通过子查询过滤出符合条件的events记录，再和其他表JOIN，能大幅减少后续关联的数据量：

SELECT 
  COUNT(*) AS count_all, 
  flows.user_id AS flows_user_id, 
  filtered_events.metadata_event_kind 
FROM (
  SELECT 
    eventable_id, 
    metadata_event_kind 
  FROM events 
  WHERE deleted_at IS NULL 
    AND eventable_type = $? 
    AND type = $? 
    AND created_at BETWEEN $? AND $? 
    AND metadata_event_kind IN (?, ?, ?)
) AS filtered_events
INNER JOIN flow_recipients 
  ON filtered_events.eventable_id = flow_recipients.id 
  AND flow_recipients.deleted_at IS NULL
INNER JOIN flows 
  ON flow_recipients.flow_id = flows.id 
  AND flows.deleted_at IS NULL 
  AND flows.company_id = $?
INNER JOIN users 
  ON flows.user_id = users.id 
  AND users.deleted_by_user_at IS NULL
GROUP BY flows.user_id, filtered_events.metadata_event_kind;

二、索引优化 #

1. 给events表建覆盖过滤+JOIN的复合索引 #

针对events表的过滤条件（created_at区间、type、eventable_type、deleted_at IS NULL），加上JOIN和分组需要的字段，建一个部分覆盖索引：

CREATE INDEX idx_events_filter_join ON events 
(created_at, type, eventable_type) 
WHERE deleted_at IS NULL 
INCLUDE (eventable_id, metadata_event_kind);

如果你用的是PostgreSQL 10及以下版本（不支持INCLUDE），可以把eventable_id和metadata_event_kind放到索引列里：

CREATE INDEX idx_events_filter_join ON events 
(created_at, type, eventable_type, eventable_id, metadata_event_kind) 
WHERE deleted_at IS NULL;

这个索引能让数据库直接从索引里拿到所有需要的数据，不需要回表查询events的主表。

2. 优化flow_recipients表的JOIN索引 #

因为查询里用events.eventable_id = flow_recipients.id关联，且要求flow_recipients.deleted_at IS NULL，建一个带过滤条件的索引：

CREATE INDEX idx_flow_recipients_id_flow ON flow_recipients 
(id, flow_id) 
WHERE deleted_at IS NULL;

这个索引能快速匹配关联条件，同时直接拿到flow_id用于后续和flows表的JOIN，避免回表。

3. 优化flows表的索引 #

针对flows表的过滤条件（deleted_at IS NULL、company_id = ?）和需要的user_id，建复合索引：

CREATE INDEX idx_flows_id_user_company ON flows 
(id, company_id, user_id) 
WHERE deleted_at IS NULL;

这个索引能快速定位符合条件的flows记录，同时直接获取user_id用于分组和JOIN users表。

三、其他辅助优化 #

• 更新统计信息：运行ANALYZE events; ANALYZE flow_recipients; ANALYZE flows; ANALYZE users;，让PostgreSQL获取最新的表数据分布，生成更准确的执行计划。
• 检查执行计划的行数预估：如果执行计划里的rows=1是明显的预估错误，说明统计信息过时，上面的ANALYZE操作能解决这个问题。

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