当 PostgreSQL 规划器放弃索引：如何控制失控查询的爆炸半径

2026-07-13 23 预计阅读时间: 1 分钟
来源: planetscale.com AI 摘要 Original link

Disclaimer: This article is an AI-assisted summary. Read it together with the original source when precision matters. The summary may omit context, version differences, or edge cases and is not official documentation.

预计阅读时间：10 分钟

一条长期稳定的 PostgreSQL 查询，可能在数据分布、统计信息或参数发生变化后突然换掉执行计划。来源案例中，查询规划器放弃了原本可用的索引，导致查询失控；PlanetScale 的 Database Traffic Control 则在问题进一步扩散前限制了它。这个案例值得关注的地方，不只是“规划器为什么选错”，更是数据库入口能否在错误计划出现时守住延迟、连接数和整体吞吐量。

索引存在，不代表规划器一定会使用

PostgreSQL 不会机械地选择索引。规划器会比较顺序扫描、索引扫描、位图扫描以及不同连接顺序的估算成本，再选择它认为最便宜的方案。

因此，“表上明明有索引”并不能证明执行计划一定正确。常见影响因素包括：

  • 统计信息没有及时反映当前数据分布；
  • 某个字段高度倾斜，但规划器按平均分布估算；
  • 查询参数改变了过滤条件的选择性；
  • 表持续增长后，随机读取与顺序扫描的成本关系发生变化；
  • 预备语句选择了通用计划，而实际参数更适合定制计划；
  • 多列条件存在相关性，单列统计信息无法准确估算结果行数。

危险之处在于，计划变化可能没有任何应用代码变更。昨天只读取几百行的请求，今天可能扫描整张大表，占用 CPU、I/O 和数据库连接，并让后续正常请求排队。

真正需要控制的是资源放大效应

一次慢查询通常不是孤立事件。应用超时后可能自动重试，负载均衡器可能把更多请求转发到仍然存活的实例，连接池也可能不断补充连接。单条错误计划由此被放大成并发查询风暴。

Database Traffic Control 这类入口层能力的价值，是把异常查询与数据库的其余工作负载隔离开来。即使无法立刻修正规划器，也可以通过并发上限、队列、超时或拒绝策略限制爆炸半径。

可以把防线分成三个层次：

  1. PostgreSQL 层：用 statement_timeout、角色权限和资源监控阻止查询无限运行。
  2. 连接池或代理层：限制特定应用、租户或查询类别的并发量。
  3. 应用层：设置明确的请求截止时间，避免无上限重试，并为降级路径预留容量。

这些机制不能让错误计划变快，但能防止它拖垮所有正常查询。

可以这样实践：复现计划变化并设置止损线

下面的示例可以在测试数据库中运行。它创建一张订单表和索引，然后使用 EXPLAIN (ANALYZE, BUFFERS) 查看 PostgreSQL 实际读取了多少行。请不要直接在生产大表上运行带 ANALYZE 的诊断语句，因为它会真正执行查询。

DROP TABLE IF EXISTS orders_demo;

CREATE TABLE orders_demo (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   bigint NOT NULL,
    status      text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders_demo (tenant_id, status, created_at)
SELECT
    CASE WHEN n <= 190000 THEN 1 ELSE 2 + (n % 1000) END,
    CASE WHEN n % 20 = 0 THEN 'pending' ELSE 'complete' END,
    now() - (n || ' seconds')::interval
FROM generate_series(1, 200000) AS n;

CREATE INDEX orders_demo_tenant_status_created_idx
    ON orders_demo (tenant_id, status, created_at DESC);

ANALYZE orders_demo;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, created_at
FROM orders_demo
WHERE tenant_id = 1
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

检查输出时，不要只看 Index Scan 或 Seq Scan 的名称。更重要的是比较：

  • estimated rows 与 actual rows 是否相差几个数量级；
  • Rows Removed by Filter 是否异常高；
  • shared read 和 shared hit 是否突然上升；
  • 排序是否落盘；
  • 执行时间是否主要消耗在某个扫描或连接节点。

如果估算偏差明显，可以先刷新统计信息，再比较计划：

ANALYZE VERBOSE orders_demo;

SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders_demo';

对于相关性较强的多列条件，可以在测试环境评估扩展统计信息。下面的配置只是可改造示例，是否有效必须通过真实数据和执行计划验证：

CREATE STATISTICS orders_demo_tenant_status_stats
    (dependencies, mcv)
    ON tenant_id, status
    FROM orders_demo;

ANALYZE orders_demo;

接下来设置查询止损线。与其给整个数据库使用同一个宽松超时，更实用的做法是按应用角色配置：

-- 将 app_reader 替换为实际的只读应用角色。
ALTER ROLE app_reader SET statement_timeout = '5s';
ALTER ROLE app_reader SET lock_timeout = '1s';
ALTER ROLE app_reader SET idle_in_transaction_session_timeout = '30s';

对于已失控的查询，可以先定位，再取消。以下命令需要相应监控和取消权限：

SELECT pid,
       usename,
       application_name,
       now() - query_start AS runtime,
       wait_event_type,
       wait_event,
       left(query, 160) AS query_sample
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

-- 优先请求取消查询；只有会话无法恢复时才考虑 pg_terminate_backend。
SELECT pg_cancel_backend(12345);

其中 12345 必须替换为实际 PID。自动化取消规则还应校验数据库、角色、应用名称和查询持续时间，避免误伤迁移、备份或维护任务。

把执行计划纳入持续监控

只监控平均查询延迟很容易错过计划突变。平均值可能仍然正常，但尾延迟、读取块数和并发占用已经快速上升。更可靠的观测组合包括：

  • 使用 pg_stat_statements 按查询指纹跟踪调用次数、总耗时和平均耗时；
  • 监控 P95、P99 延迟，而不只是平均值；
  • 记录超时、取消和连接池排队次数；
  • 对关键查询保存基准执行计划，并在发布或统计信息变化后重新检查；
  • 同时观察数据库 CPU、磁盘读取、临时文件和活跃连接数。

可以这样启用并查询 pg_stat_statements，前提是服务允许修改 shared_preload_libraries，并在修改后按平台要求重启 PostgreSQL：

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT queryid,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       left(query, 120) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

需要注意，查询指纹监控能发现“哪类查询变贵了”，但不一定保存每次运行时的具体计划。若要捕获计划变化，还需要结合慢查询日志、受控的自动 EXPLAIN，或数据库平台提供的查询分析能力。

上线前的控制清单

规划器偶尔做出不理想的选择并不意外，真正危险的是系统默认允许一次错误选择无限占用资源。落地时可以检查以下事项：

  • 为面向用户的数据库角色设置有限的 statement_timeout；
  • 让应用请求截止时间、数据库超时和代理超时保持一致；
  • 禁止无退避、无次数上限的自动重试；
  • 按工作负载隔离连接池，避免批处理挤占在线请求；
  • 为高风险查询建立查询指纹、尾延迟和读取量告警；
  • 在真实数据分布上验证索引与扩展统计信息；
  • 准备取消查询、限制并发和应用降级的操作手册。

索引和统计信息负责降低查询成本，流量控制负责限制失败成本。两者不能相互替代：前者改善正常路径，后者保证执行计划突然改变时，数据库仍有空间处理健康流量和修复操作。

# AI # Go # MySQL # 后端 # 数据库

相关推荐