优化托管PostgreSQL慢查询,如何依据执行计划判断索引是否有效?
判断索引是否有效,不能只看执行计划里出现了 Index Scan。更可靠的标准是:在相同查询参数和相近业务负载下,索引是否减少了扫描行数、数据块读取和排序工作,是否降低了 p95 或 p99 延迟,并且没有把 CPU、I/O、连接池等待或写入开销转移到其他位置。
托管 PostgreSQL 中,EXPLAIN 的 Execution Time 主要反映数据库执行器耗时,接口端到端延迟还可能包含连接池排队、锁等待、网络传输和应用处理时间。因此,判断索引是否真正解决慢查询,需要把执行计划与延迟、吞吐、并发、CPU、内存缓存和存储 I/O 放在同一组对照数据中分析。
先确定测试目标:索引要改善哪一段工作
一个索引是否有效,可以分为三个层次判断:

- 计划层有效:优化器选择了索引相关路径,或者索引帮助消除了排序、缩小了扫描范围。
- 执行层有效:实际扫描行数、访问数据块和执行时间明显下降。
- 业务层有效:在目标并发下,查询 p95、p99 延迟下降,吞吐提升,且数据库资源没有持续饱和。
只有第一层成立,不能直接说明索引值得保留。例如,计划从 Seq Scan 变成了 Index Scan,但索引仍然需要访问表中数百万行,或者每一行都要回表读取,最终耗时可能没有改善。
以订单列表查询为例:
SELECT id, created_at, amount
FROM public.orders
WHERE tenant_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
这个查询的目标不是扫描所有已支付订单,而是快速找到某个租户最近的 50 条订单。验证时应固定或分组记录以下条件:
- PostgreSQL 主版本和实例规格;
orders表总行数、索引数量和数据增长情况;- 具有代表性的
tenant_id,包括数据量较大和较小的租户; - 查询参数、返回行数和排序条件;
- 测试时是冷缓存还是热缓存;
- 并发数、连接池大小和测试时长;
- 数据库 CPU、内存压力、I/O 延迟、IOPS 以及连接池等待情况。
如果只使用一个数据量很小的租户进行验证,索引效果可能被高估;如果只在缓存已经预热的情况下测试,也可能掩盖存储读取压力。
执行计划中哪些指标最值得看
估算行数与实际行数
执行计划中的 rows 是优化器估算值,actual rows 是执行时实际返回的行数。两者长期偏差较大,通常意味着统计信息不足、数据分布倾斜或多个列之间存在相关性。
例如:
Index Scan using idx_orders_tenant_status_created_desc on orders
(cost=0.43..18.27 rows=50 width=32)
(actual time=0.052..0.318 rows=50 loops=1)
这里估算结果为 50 行,实际也返回 50 行,说明优化器对这个节点的判断较接近实际。
但如果计划显示:
Index Scan using idx_orders_tenant_status_created_desc on orders
(cost=0.43..8500.00 rows=50 width=32)
(actual time=0.060..260.800 rows=18500 loops=1)
实际行数远高于估算值,后续节点可能因此选择了不合适的连接方式、排序方式或并行度。此时不应急于修改索引,应该先检查统计信息和数据分布。
当 loops 大于 1 时,要注意 actual rows 通常是每次循环的行数,节点累计处理量需要结合循环次数判断。例如 actual rows=20 loops=100,实际处理规模不能只按 20 行理解。
Index Cond 和 Filter 的区别
索引真正用于缩小扫描范围的条件通常出现在 Index Cond 中:
Index Cond: ((tenant_id = 42) AND (status = 'paid'::text))
如果条件出现在 Filter,说明这些行可能已经通过索引路径或表扫描找到,随后才由执行器逐行过滤:
Filter: (status = 'paid'::text)
Rows Removed by Filter: 920000
这并不代表索引完全无效,但说明索引没有充分表达查询条件。Rows Removed by Filter 很大时,索引可能只是提供了一个入口,实际工作量仍然很高。

Buffers 反映访问了多少数据块
使用 BUFFERS 选项可以观察缓存命中和读取压力:
Buffers: shared hit=4210 read=68500
常见字段含义如下:
| 指标 | 含义 | 分析重点 |
|---|---|---|
shared hit | 从 PostgreSQL 缓冲区缓存中命中的数据块 | 命中多不代表查询一定快,还要看扫描总量 |
shared read | 需要从更底层存储读取的数据块 | 较高时关注存储延迟、缓存状态和扫描范围 |
shared dirtied | 执行过程中被修改的数据块 | 读查询通常不应明显增加 |
shared written | 被写回存储的数据块 | 可能与写入、脏页刷盘或检查点有关 |
temp read、temp written | 临时文件读取和写入 | 常见于排序或哈希操作超出内存 |
shared read 不一定等于物理磁盘读取,也可能受到操作系统缓存和托管平台存储层的影响,但它可以帮助判断查询是否在大量触碰数据库缓存之外的数据。
计划成本不是实际耗时
cost=0.43..8500.00 是供优化器比较方案使用的相对成本,不是毫秒,也不能直接当作性能指标。不同配置、统计信息和成本参数下,成本值不能简单横向比较。
真正需要对比的是:
Planning Time:生成计划所需时间;Execution Time:执行器完成本次查询所需时间;- 每个节点的
actual time; actual rows、loops;Buffers和临时文件;- 在并发环境下的 p50、p95、p99 延迟。
Index Scan、Bitmap Heap Scan 和 Index Only Scan
不同节点并不对应固定的好坏关系:
Index Scan适合通过索引找到少量行,尤其适合带有LIMIT的选择性查询。Bitmap Index Scan加Bitmap Heap Scan,常见于需要返回较多行,但仍比全表扫描更划算的场景。Index Only Scan只有在查询所需列都能从索引获取,并且表页面的可见性信息足够完善时,才可能减少回表访问。
看到 Index Only Scan 也要继续检查:
Heap Fetches: 185000
如果 Heap Fetches 很高,执行器仍然频繁访问表,索引覆盖带来的收益会被削弱。表的更新频率、自动清理进度和可见性映射都会影响这个指标。
把执行计划与资源指标关联起来
单次 EXPLAIN ANALYZE 适合分析查询内部工作,但不一定能解释线上接口为什么变慢。托管 PostgreSQL 中至少应同时观察以下指标。
| 指标 | 主要回答的问题 | 常见解释 |
|---|---|---|
| p50 | 大多数请求的基础延迟是多少 | 反映典型执行路径 |
| p95、p99 | 慢请求尾部是否改善 | 反映缓存未命中、I/O 抖动、锁等待和资源争用 |
| 吞吐量 | 单位时间内完成多少次查询 | 需要结合并发和错误率观察 |
| CPU 使用率 | 查询是否主要受计算能力限制 | 排序、表达式、函数和高并发回表都会增加 CPU |
| 内存与缓存命中 | 数据是否能稳定留在缓存中 | 热缓存下的结果不代表冷缓存表现 |
| 存储 IOPS 和延迟 | 查询是否被存储读取拖慢 | 大量随机回表通常比少量顺序读取更敏感 |
| 活跃连接与等待连接 | 请求是否在连接池或数据库前排队 | 连接池排队不等于索引执行慢 |
| 临时文件 | 排序或哈希是否溢出到存储 | 可能需要优化查询、索引或内存配置 |
并发和吞吐不能脱离延迟单独判断。可以用一个简单关系理解容量变化:
并发数约等于吞吐量乘以平均响应时间。
例如,吞吐量为 100 次/秒、平均响应时间为 0.2 秒时,平均活跃请求数约为 20。若查询优化后吞吐提升,但平均响应时间没有下降,可能只是并发量同时增加,不能直接认定数据库容量扩大。
下面是一组用于说明分析方法的模拟对照数据:
| 指标 | 未使用合适索引 | 使用候选索引 |
|---|---|---|
| 数据库执行时间 p95 | 410 ms | 4 ms |
| 接口端到端延迟 p95 | 520 ms | 28 ms |
| 30 并发下吞吐量 | 约 70 QPS | 约 240 QPS |
| 数据库 CPU | 78% | 54% |
| 存储读取延迟 | 12 ms | 3 ms |
| 连接池等待请求数 | 18 | 0~2 |
这个结果说明索引优化同时减少了数据库工作量和连接池排队。但如果数据库执行时间已经只有 4 ms,而接口 p95 仍然超过 500 ms,就应转向检查连接池、锁、网络或应用逻辑,而不是继续添加索引。
影响索引效果的关键变量
过滤选择性与返回结果规模
索引最适合帮助查询快速定位少量结果。若条件能够匹配表中很大比例的行,优化器选择顺序扫描可能是合理的,因为大量随机回表的成本可能高于顺序读取。
不要把“使用全表扫描”直接等同于“缺少索引”。以下情况中,顺序扫描可能更合适:
- 表本身很小;
- 查询需要返回表中相当大比例的数据;
- 条件选择性很低;
- 表数据已经大部分在缓存中;
- 使用索引会产生大量随机回表。
对于订单列表示例,tenant_id 和 status 是等值过滤条件,created_at 用于排序,因此可以评估如下索引:
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created_desc
ON public.orders (tenant_id, status, created_at DESC);
这只是候选方案,不代表所有订单表都应使用相同列顺序。索引设计还要结合其他查询、写入频率、列的分布以及是否存在相同或相近的索引。不能只套用“把区分度最高的列放在第一位”这一条经验。
排序和 LIMIT 是否被索引消除
如果查询需要:
ORDER BY created_at DESC
LIMIT 50
而执行计划仍然出现:
Sort
Sort Key: created_at DESC
说明索引没有完全支持当前过滤和排序路径,或者优化器判断排序成本仍然更低。
对只取前几十行的查询,合适的索引可以让执行器按索引顺序读取,拿到足够结果后尽早停止。此时不仅要比较总耗时,还要检查计划是否读取了大量候选行后才满足 LIMIT。
统计信息是否反映当前数据
大批量导入、数据归档、租户增长不均衡或状态比例变化后,旧统计信息可能导致错误估算。可以在低峰期或测试环境更新统计信息:
ANALYZE public.orders;
如果多个过滤列存在明显相关性,例如某些租户几乎全部是 paid,而另一些租户几乎没有 paid,可以进一步评估扩展统计信息:
CREATE STATISTICS st_orders_tenant_status
(dependencies, mcv)
ON tenant_id, status
FROM public.orders;
ANALYZE public.orders;
扩展统计信息只对相关查询有帮助,不应在没有估算偏差证据时批量创建。托管服务可能限制相关权限,也可能由平台负责维护部分参数,需要先确认实例权限和版本支持情况。
查询表达式、隐式类型转换和条件位置
以下写法可能使普通索引难以直接使用:
WHERE lower(email) = 'user@example.com'
或者查询参数与列类型不一致,导致额外转换。带函数的查询需要确认是否适合表达式索引,类型转换则应先统一应用参数和数据库列类型。
同时要区分:
WHERE tenant_id = 42
AND status = 'paid'
与只把 tenant_id 放在索引条件、再通过 Filter 过滤 status 的情况。后者可能仍然需要检查大量行。
缓存状态和存储性能
连续执行同一查询时,后续执行可能受益于缓存。建议至少分别记录:
- 缓存已经预热时的结果;
- 重新启动、数据规模变化或缓存不稳定时的结果;
- 业务高峰、其他大查询运行时的结果。
如果计划中 shared hit 很高但 CPU 接近实例上限,问题可能主要是计算和执行行数;如果 shared read 较高且存储延迟同步升高,问题更接近 I/O;如果两个指标都不高但接口延迟很高,则应检查锁等待、连接池或网络。
参数值与预编译语句
同一条参数化 SQL,不同参数可能得到不同选择性。一个租户只有几千行,另一个租户有几千万行,适合的计划可能不同。
可以使用代表性参数检查预编译语句的实际计划:
PREPARE orders_query(bigint, text) AS
SELECT id, created_at, amount
FROM public.orders
WHERE tenant_id = $1
AND status = $2
ORDER BY created_at DESC
LIMIT 50;
EXPLAIN (ANALYZE, BUFFERS)
EXECUTE orders_query(42, 'paid');
如果应用使用了通用计划,计划中的参数处理方式可能与直接写入常量时不同。应分别测试小租户、大租户和高频状态值,不能只依据一个参数的计划决定索引结构。
用一组计划判断索引到底有没有改善
下面是一个简化的无合适索引示例:
Limit (cost=182000.00..182000.12 rows=50 width=32)
(actual time=410.221..410.237 rows=50 loops=1)
-> Sort (cost=182000.00..182062.00 rows=24800 width=32)
(actual time=410.218..410.224 rows=50 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 36kB
-> Seq Scan on orders
(cost=0.00..181200.00 rows=24800 width=32)
(actual time=0.020..386.100 rows=24873 loops=1)
Filter: ((tenant_id = 42) AND (status = 'paid'::text))
Rows Removed by Filter: 7975127
Planning Time: 0.420 ms
Execution Time: 410.300 ms
Buffers: shared hit=2100 read=68500
这个计划暴露出几个问题:
- 扫描了约 800 万行;
- 过滤后只剩约 2.5 万行;
- 为了得到前 50 行仍然需要排序;
- 存储读取量较高;
- 查询耗时主要花在扫描和排序,而非返回结果。
添加候选索引后,可能得到类似结果:
Limit (cost=0.43..18.27 rows=50 width=32)
(actual time=0.045..0.210 rows=50 loops=1)
-> Index Scan using idx_orders_tenant_status_created_desc on orders
(cost=0.43..18.27 rows=50 width=32)
(actual time=0.043..0.201 rows=50 loops=1)
Index Cond: ((tenant_id = 42) AND (status = 'paid'::text))
Planning Time: 0.310 ms
Execution Time: 0.245 ms
Buffers: shared hit=48 read=6
这个结果具备较强的索引有效性证据:
Index Cond同时使用了主要过滤条件;- 不再出现独立的
Sort节点; - 返回 50 行前不需要扫描数百万行;
- 访问的数据块明显减少;
- 执行时间从数百毫秒降到毫秒级。
但这仍然只是单次计划层面的证据。最终还要在并发环境中确认 p95、p99、CPU 和存储指标是否同步改善。

常见结果如何解释
| 观察结果 | 可能含义 | 下一步判断 |
|---|---|---|
仍是 Seq Scan,但返回数据比例很高 | 顺序扫描可能更便宜 | 检查选择性,不要为了出现索引节点而强制改计划 |
使用 Index Scan,但 Rows Removed by Filter 很大 | 索引只缩小了部分范围 | 检查复合索引列顺序和条件是否进入 Index Cond |
Bitmap Heap Scan,读取块较多但耗时稳定 | 查询返回行数中等,位图路径可能合理 | 与顺序扫描比较实际时间和 I/O,不要仅凭节点名称判断 |
Index Only Scan 但 Heap Fetches 很高 | 仍有大量回表访问 | 检查更新频率、自动清理和可见性信息 |
| 执行计划很快,接口 p95 仍高 | 慢点可能在连接池、锁、网络或应用 | 检查等待连接、wait_event 和应用链路 |
| 估算 50 行,实际几十万行 | 统计信息或数据分布判断失真 | 更新统计信息,必要时增加扩展统计信息 |
出现 temp read/written | 排序或哈希使用了临时文件 | 检查排序路径、查询结果规模和内存配置 |
| 索引计划只在热缓存下有效 | 结果依赖缓存 | 单独记录冷缓存或高峰 I/O 场景 |
enable_seqscan = off 可以用于诊断“如果强行使用索引会怎样”,但不应作为生产修复方案。强制索引可能在数据增长或参数变化后变得更差,且会掩盖统计信息和成本估算问题。
何时新增索引,何时不要继续加索引
适合继续评估索引的条件通常包括:
- 查询调用频率高,且慢查询在总耗时中占比较大;
- 过滤条件具有较好的选择性;
- 执行计划显示大量无效扫描、排序或回表;
- 候选索引在不同代表性参数下都能降低工作量;
- 并发测试中 p95、p99 和吞吐改善;
- 写入 CPU、存储空间和维护成本仍在可接受范围内。
不适合只靠新增索引解决的情况包括:
- 查询本身需要返回大部分数据;
- 主要耗时来自锁等待、连接池排队或网络;
- 数据库 CPU、存储或连接数已经达到实例容量边界;
- 索引已经被使用,但实际回表和过滤量仍然很大;
- 查询只偶尔执行一次,建立和维护索引的成本高于收益。
每增加一个索引,写入和更新通常都需要维护更多索引页,索引还会占用存储、增加清理工作,并可能增加复制链路的写入压力。对于高写入表,应把新增索引的收益与插入、更新延迟一起评估。
如果某个查询始终只访问固定状态,例如始终查询 status = 'paid',可以评估部分索引:
CREATE INDEX CONCURRENTLY idx_orders_paid_tenant_created_desc
ON public.orders (tenant_id, created_at DESC)
WHERE status = 'paid';
部分索引只有在查询条件能够稳定匹配谓词时才有意义。若业务经常查询其他状态,或者查询条件无法被优化器确认包含 status = 'paid',收益可能不符合预期。
生产环境验证时的前提与回滚边界
在托管 PostgreSQL 上创建索引前,应先确认以下事项:
- 已有可用备份或恢复时间点,且恢复流程经过验证;
- 实例和存储有足够空间;
- 创建期间的 CPU、I/O 和复制延迟在可接受范围;
- 业务能够承受短暂的元数据锁等待;
- 服务商允许执行
CREATE INDEX CONCURRENTLY; - 新索引不会与现有索引形成明显重复。
CREATE INDEX CONCURRENTLY 通常可以减少对正常读写的阻塞,但仍会消耗 CPU 和 I/O,创建时间也可能较长,并且不能放在事务块中执行。创建失败时可能留下无效索引,需要根据系统目录和平台文档确认状态后处理。
验证阶段不要立刻删除旧索引。先观察一段包含高峰和低峰的业务周期,确认:
- 目标查询持续使用预期计划;
- 其他查询没有因为索引竞争而退化;
- 写入延迟和存储使用量没有异常增长;
- 托管实例没有出现明显复制延迟或 I/O 饱和。
如果候选索引导致回归,应先停止继续扩大使用范围,保留原有可用索引,再在确认没有其他查询依赖候选索引后,通过 DROP INDEX CONCURRENTLY 清理。删除前应再次检查依赖关系和备份状态,避免把本来服务其他查询的索引一并移除。
一套可复用的验证流程
1. 采集基线
先用代表性参数执行只读查询的计划分析:
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING)
SELECT id, created_at, amount
FROM public.orders
WHERE tenant_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
EXPLAIN ANALYZE 会实际执行语句。生产环境中应避免直接对有副作用的写入语句使用;写入类查询优先在测试库、只读副本或可回滚的隔离环境验证。记录计划文本、执行时间、缓冲区、参数、缓存状态和实例监控数据。
如果需要降低逐节点计时带来的额外开销,可以使用:
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)
SELECT id, created_at, amount
FROM public.orders
WHERE tenant_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
2. 观察聚合查询表现
如果托管平台允许使用 pg_stat_statements,可以查看一段时间内的累计表现:
SELECT
queryid,
calls,
total_exec_time,
total_exec_time / NULLIF(calls, 0) AS avg_exec_time,
rows,
shared_blks_hit,
shared_blks_read,
temp_blks_read,
temp_blks_written,
query
FROM pg_stat_statements
WHERE query ILIKE '%FROM public.orders%'
ORDER BY total_exec_time DESC
LIMIT 20;
这里的平均耗时只能作为整体参考,不能替代 p95 和 p99。相同指纹下,不同参数可能有完全不同的延迟分布。若扩展不可用,应使用托管平台的查询分析、慢查询日志或应用链路监控。
3. 创建候选索引并重新生成计划
在确认空间、权限和影响范围后,创建候选索引。完成后重新运行相同参数的 EXPLAIN,重点比较:
- 是否从全表扫描变为更小范围的索引路径;
- 是否减少了
Rows Removed by Filter; - 是否消除了不必要的排序;
actual rows与估算值是否更接近;shared read、临时文件和执行时间是否下降。
4. 在并发负载下复测
单次查询从 400 ms 降到 3 ms,不代表一定可以承受生产流量。应使用与业务相近的并发数和查询比例复测,并同时记录:
- p50、p95、p99;
- 每秒完成请求数;
- 数据库 CPU;
- 存储 IOPS、吞吐量和延迟;
- 活跃连接、等待连接和连接池排队;
- 锁等待、临时文件和错误率。
如果单次执行明显变快,但高并发下 CPU 或存储延迟上升,应检查是否因为更多请求在同一时间到达,或者索引导致大量随机读取。
5. 处理失败结果
常见失败结果及处理方向如下:

- 索引未被使用:先检查统计信息、参数选择性、数据规模和索引条件是否匹配,不要立即强制索引。
- 索引被使用但仍然慢:查看回表量、过滤行数、排序节点和
Buffers,判断索引是否只解决了部分条件。 - 数据库执行很快但接口很慢:检查连接池等待、锁等待、网络和应用序列化。
- 高峰期出现回归:对比 CPU、I/O、缓存命中和并发数,确认是否是实例容量而非索引结构问题。
- 创建索引期间业务抖动:停止扩大变更范围,检查索引构建进度、锁和存储压力,并按照平台支持的方式终止或回退。
用复测结果判断容量是否足够
索引优化的验收条件应提前量化,而不是只写“执行计划变好了”。例如,可以把目标设为:代表性参数下查询 p95 低于业务阈值,30 并发时 p99 不出现明显长尾,CPU 保留一定余量,存储读取延迟不超过历史高峰,连接池等待接近于零。
如果目标查询已经满足延迟要求,但高峰期数据库 CPU 长时间接近实例上限,说明索引解决了单条查询的低效扫描,却没有解决整体容量问题。此时应结合吞吐量和资源增长趋势判断是否需要扩容。
如果 CPU、I/O 和连接池都还有余量,而 p95 仍然偶发升高,应优先查找参数倾斜、锁等待、缓存抖动和后台任务。如果索引优化后查询执行时间、尾延迟、I/O 和连接池等待同时下降,且写入成本没有明显上升,才可以把它视为在托管 PostgreSQL 中有效且可持续的索引优化方案。
A5idc数据提供香港、美国、日本等地区的物理服务器租用,覆盖 Xeon Gold 与 AMD EPYC 等平台,并有搭配大内存、SSD或NVMe存储的不同配置,可为 PostgreSQL 数据库及业务接口提供计算、内存和存储资源基础。多地区服务器和不同网络线路方案,也为按业务部署区域规划数据库及配套服务提供了基础设施选择。
本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/9525.html



