Postgres非均匀分布表索引异常切换致性能暴跌的技术咨询

背景：发件箱模式实现细节 #

架构设计 #

• 使用PostgreSQL存储消息
• Golang Worker负责消息发布流程：
  • 通过SELECT FOR UPDATE选取待发送的下一批消息
  • 将消息标记为“锁定”状态（设置locked_at为非空值）
  • 提交事务
  • 发布选中的消息
  • 根据消息id标记为已发送（设置sent_at）并重置locked_at字段
• 定时Cron任务负责清理历史消息

核心SQL语句 #

Worker待发送消息查询

WHERE sent_at IS NULL
AND available_at <= NOW() -- 排除未来才允许发送的消息
AND ( locked_at IS NULL OR locked_at <= [NOW - TIMEOUT]) -- 排除锁定超时的消息
AND retry_count < 30 -- 排除放弃重试的消息
ORDER BY available_at ASC
LIMIT 10
FOR UPDATE SKIP LOCKED

历史消息清理查询

WITH rows_to_delete AS (
    SELECT id
    FROM outbox
    WHERE sent_at <= [DATE_THRESHOLD]
    LIMIT [LIMIT_FOR_BATCHING]
)
DELETE FROM outbox
WHERE id IN (SELECT id FROM rows_to_delete)

表结构 #

索引配置 #

问题现象 #

表中99.99%的数据为已发布状态（sent_at IS NOT NULL），待发布消息（sent_at IS NULL）数量在0-10万之间波动，少量消息存在重试记录（retry_count > 0）。多数情况下Worker查询会正确使用messages_to_send索引，响应时间约4ms；但偶尔PostgreSQL会切换到messages_sent索引，导致查询性能骤降，一段时间后会自动切回正确索引（若系统未崩溃）。

经排查，问题源于查询规划器的统计数据偏差：正常状态下规划行数与实际行数接近，使用预期索引；异常状态下规划行数与实际行数偏差极大，错误选择索引。

问题解答 #

1. 如何阻止查询规划器切换索引？ #

有几种直接的方式强制查询使用指定索引：

• 索引强制指定：在查询中使用INDEX提示（PostgreSQL 11+支持），明确指定使用messages_to_send索引，示例：

  SELECT ...
  FROM outbox
  WHERE sent_at IS NULL
    AND available_at <= NOW()
    AND (locked_at IS NULL OR locked_at <= NOW() - INTERVAL '5 minutes')
    AND retry_count < 30
  ORDER BY available_at ASC
  LIMIT 10
  FOR UPDATE SKIP LOCKED
  -- 强制使用目标索引
  INDEX (messages_to_send);
  

• 过滤无效索引选择：通过更精确的条件让规划器自动排除messages_sent索引，但最直接可靠的还是使用索引提示锁定选择。
• 临时调整成本参数：将random_page_cost设为接近1.1（接近顺序扫描成本），让规划器更倾向于使用索引，但这种方式影响全局，不推荐作为长期方案。

2. 如何确保查询规划器拥有准确的统计数据？ #

针对这种数据分布极度倾斜的表，需要优化统计信息的采集策略：

• 提高倾斜列的统计采样率：针对sent_at这类分布极度不均的列，提高统计采样比例，确保PostgreSQL能准确捕获小众数据的分布：

  ALTER TABLE outbox ALTER COLUMN sent_at SET STATISTICS 1000;
  

  数值范围1-10000，数值越高采样越准确，倾斜列建议设为1000以上。
• 手动触发统计更新：在数据量发生较大变化后（比如批量清理消息后），手动执行ANALYZE outbox;，强制PostgreSQL更新统计数据，避免规划器使用过时结果。
• 监控索引统计：通过查询pg_stat_user_tables和pg_stat_user_indexes监控表和索引的统计更新情况，及时发现统计数据过时的问题。
• 依赖部分索引统计：PostgreSQL会单独统计部分索引的条目数，确保messages_to_send这类部分索引的统计信息是最新的，手动执行ANALYZE也会同步更新这些索引的统计数据。

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