优化托管PostgreSQL慢查询,如何依据执行计划判断索引是否有效?

发布人:A5数据发布时间:2026-11-15 10:55阅读量:25

判断索引是否有效,不能只看执行计划里出现了 Index Scan。更可靠的标准是:在相同查询参数和相近业务负载下,索引是否减少了扫描行数、数据块读取和排序工作,是否降低了 p95 或 p99 延迟,并且没有把 CPU、I/O、连接池等待或写入开销转移到其他位置。

托管 PostgreSQL 中,EXPLAIN 的 Execution Time 主要反映数据库执行器耗时,接口端到端延迟还可能包含连接池排队、锁等待、网络传输和应用处理时间。因此,判断索引是否真正解决慢查询,需要把执行计划与延迟、吞吐、并发、CPU、内存缓存和存储 I/O 放在同一组对照数据中分析。

先确定测试目标:索引要改善哪一段工作

一个索引是否有效,可以分为三个层次判断:

先确定测试目标:索引要改善哪一段工作配图

  1. 计划层有效:优化器选择了索引相关路径,或者索引帮助消除了排序、缩小了扫描范围。
  2. 执行层有效:实际扫描行数、访问数据块和执行时间明显下降。
  3. 业务层有效:在目标并发下,查询 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。若查询优化后吞吐提升,但平均响应时间没有下降,可能只是并发量同时增加,不能直接认定数据库容量扩大。

下面是一组用于说明分析方法的模拟对照数据:

指标未使用合适索引使用候选索引
数据库执行时间 p95410 ms4 ms
接口端到端延迟 p95520 ms28 ms
30 并发下吞吐量约 70 QPS约 240 QPS
数据库 CPU78%54%
存储读取延迟12 ms3 ms
连接池等待请求数180~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

相关推荐