美国服务器上的PostgreSQL慢查询,如何判断是否需要调整索引?
接口 P95 延迟从 300 毫秒涨到 900 毫秒,数据库 CPU 却只有 40%,这并不能直接说明 PostgreSQL 缺少索引。请求可能在连接池排队,也可能花在跨境网络、锁等待或磁盘读取上。对于部署在美国服务器上的 PostgreSQL,判断是否需要调整索引,首先要确认:慢的是数据库执行,还是数据库之外的等待。
只有当执行计划显示大量无效扫描、回表或排序,并且这些工作与查询条件、排序方式及现有索引不匹配时,调整索引才有明确依据。反过来,如果数据库只执行了几毫秒,而接口耗时数百毫秒,新增索引通常无法解决主要问题。
一、测试目标:把“接口慢”缩小到一条查询的执行成本
索引优化的测试目标,不是让服务器 CPU 看起来更低,也不是让所有查询都出现 Index Scan,而是在相同业务负载下,减少目标查询的执行时间与资源消耗,同时不明显损害写入性能。
先把一次请求拆成几个阶段:
- 应用等待数据库连接的时间。
- 应用与 PostgreSQL 之间的网络往返。
- 数据库内部的执行及等待时间。
- 结果传输、应用处理和响应返回时间。
美国服务器的地理位置主要影响网络路径,不会改变 PostgreSQL 的索引机制。如果应用与数据库位于同一美国机房,跨境延迟可能主要发生在用户访问应用这一段;如果应用在国内、数据库在美国,多次串行 SQL 往返就可能放大请求耗时。
例如,示例链路单次往返约为 150 毫秒,一个接口顺序发送四次数据库请求,仅网络往返就可能累计约 600 毫秒。即使每条 SQL 从 20 毫秒降至 5 毫秒,整体收益仍然有限。这里更值得检查的是查询次数、批量读取方式和应用部署位置,而不是继续堆索引。

为了让前后结果可比较,测试至少需要固定以下条件:
| 测试条件 | 需要固定的内容 | 不固定可能造成的误判 |
|---|---|---|
| 数据 | 表规模、数据分布、查询参数 | 小账户快,不代表大账户也快 |
| 负载 | 请求速率、并发数、读写比例 | 低负载优化无法代表高峰表现 |
| 环境 | PostgreSQL 版本、实例资源、存储类型 | 把资源变化误认为索引收益 |
| 缓存 | 是否预热、是否存在应用缓存 | 把缓存命中误认为执行计划改善 |
| 业务行为 | 返回行数、排序、分页方式 | 实际比较的是不同查询 |
测试应同时关注目标 SQL 和整个数据库。某条查询变快,如果以写入延迟显著上升为代价,就不能直接判断为优化成功。
二、指标含义:平均耗时找对象,执行计划找原因
查询统计负责筛选,不直接给出索引结论
已启用 pg_stat_statements 的环境,可以先找累计执行时间高、调用频繁或平均执行时间偏大的语句。下面示例使用 PostgreSQL 13 及以后版本中的常见字段;执行前应确认扩展已安装,并使用具备相应统计查看权限的账号。
SELECT
queryid,
calls,
round(total_exec_time::numeric, 1) AS total_exec_ms,
round(mean_exec_time::numeric, 2) AS mean_exec_ms,
round(rows::numeric / NULLIF(calls, 0), 2) AS rows_per_call,
round(shared_blks_hit::numeric / NULLIF(calls, 0), 1)
AS hit_blocks_per_call,
round(shared_blks_read::numeric / NULLIF(calls, 0), 1)
AS read_blocks_per_call,
query
FROM pg_stat_statements
WHERE calls > 0
ORDER BY total_exec_time DESC
LIMIT 10;
这些数据需要结合统计时间窗口解释。一次执行很慢但每天只调用一次的报表,与平均耗时不高却每秒调用数百次的接口,优化优先级可能完全不同。
另外,mean_exec_time 是平均执行时间,不是 P95;pg_stat_statements 也不能直接提供完整的执行耗时分布。P95、P99 应从应用链路监控、查询日志或压测采样中获取。
缓存块命中和读取同样不能直接等同于物理磁盘读写。shared_blks_read 表示 PostgreSQL 需要把块读入共享缓冲区,其中部分读取可能由操作系统缓存满足。因此,要把它与操作系统的存储延迟、吞吐量和等待指标一起看。
执行计划负责判断有没有“多做工作”
以下查询作为后续分析对象:订单列表按账户和状态筛选,再按创建时间倒序取前 50 条。
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_amount
FROM orders
WHERE account_id = 12345
AND status = 'pending'
ORDER BY created_at DESC, id DESC
LIMIT 50;
EXPLAIN ANALYZE 会真实执行语句。这里仅针对只读查询,仍可能消耗大量 CPU、I/O 和执行时间,应优先在具有代表性数据的测试环境进行,或在生产低峰设置会话级超时后执行。不要把同样的方式直接用于有副作用的函数或写入语句。
阅读结果时,重点不是有没有索引扫描,而是:
- 返回 50 行之前,实际检查了多少行。
Rows Removed by Filter是否很大。- 是否存在代价较高的排序,是否使用临时文件。
- 预计行数与实际行数是否严重偏离。
- 哪个节点消耗了主要时间,缓冲区访问集中在哪里。
对于重复执行的节点,实际行数通常是每次循环的平均值,必须结合 loops 理解总工作量。节点时间还存在包含关系,不能把所有节点耗时直接相加。
返回行数少但扫描范围大,是调整索引的重要线索;出现顺序扫描本身,不是缺少索引的充分证据。
三、影响变量:为什么已有索引,查询仍然慢
索引是否同时支持过滤和排序
对于前面的订单查询,仅有 account_id 索引,可能仍需读取该账户的大量订单,过滤状态后再排序。如果某个账户有几十万条订单,这种执行方式即使使用了索引,也可能很慢。
一个值得验证的候选是:
CREATE INDEX CONCURRENTLY idx_orders_account_status_created_id
ON orders (account_id, status, created_at DESC, id DESC);
该 B-tree 索引把等值过滤字段放在前面,再按查询需要的顺序组织数据。在条件匹配时,数据库有机会较早找到所需的 50 行,而不是读取全部候选记录再排序。

这条建索引语句仅是候选设计,不应直接复制到生产执行。上线前需要备份或确认可恢复能力,并核对现有索引、可用磁盘空间及复制延迟。并发建索引通常允许正常读写继续,但仍会扫描数据、增加 I/O 和 CPU 消耗,也可能等待长事务;它不能放在事务块内执行。
如果构建失败,应检查是否留下无效索引。若验证后决定撤销,下面的操作仅适用于本次新建、未用于约束的索引:
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_account_status_created_id;
删除会使相关查询失去该索引路径,也不能放在事务块内执行。索引删除后只能重新构建,不能像事务回滚一样立即恢复。因此,测试阶段应保留原有索引,不要在收益尚未确认时一并删除旧索引。
数据分布比字段数量更重要
如果业务几乎只查询待处理订单,可以考虑以 status = 'pending' 为条件的部分索引,减少索引体积。但它只适用于查询条件能够证明满足索引谓词的场景。
预备语句采用通用计划时,参数化状态条件未必能匹配固定状态的部分索引。因此,不能只测试一次字面量 SQL 就判断它适合应用实际使用方式。
数据倾斜也会改变结果:一个账户只有 200 条订单,另一个账户有 80 万条,二者可能适合不同执行计划。如果预计只返回几十行,实际却匹配数十万行,应先检查统计信息是否陈旧,以及账户、状态等字段是否存在相关性。必要时评估扩展统计信息,而不是立即强制使用索引。
资源压力可能改变计划收益
工作集能否进入内存,会影响同一个索引的表现。频繁回表、数据分散时,索引扫描可能产生较多随机读取;查询需要读取表中很大比例的数据时,顺序扫描反而可能更合理。
判断资源影响时,应建立关联:
- 查询缓冲区访问量很大、CPU 升高,可能是扫描和过滤工作过多。
- 数据文件读取等待增加、存储延迟上升,可能是缓存不足或读取范围过大。
- 临时文件读写增加,可能是排序或哈希操作溢出。
- 锁等待突出,应该先定位阻塞事务,不能只归因于索引。
- 虚拟机 CPU steal 较高,可能存在宿主机争用,需与 SQL 执行成本分开判断。
看到排序落盘也不应直接全局调大 work_mem。它可能被同一查询的多个操作、多个并发会话分别使用,总内存占用远大于单个配置值。
围绕美国服务器上的PostgreSQL数据库负载,A5数据提供Xeon及AMD EPYC物理服务器,涵盖不同计算、内存与存储配置。其中,美国AMD系列提供64GB DDR5、128GB内存及NVMe存储等配置组合,可为热点数据缓存、索引与表数据访问、并发查询处理提供硬件基础;另有双路EPYC方案,承载数据库与接口服务、多任务计算等业务负载。
四、结果解释:索引有效,需要形成完整证据链
下面是一组用于解释判断方法的示例对照数据:约 2000 万行订单,应用与数据库位于同一美国机房,固定每秒 120 次请求,并发上限和读写比例保持一致。两组均在预热后采样。
| 指标 | 原执行计划 | 候选索引启用后 |
|---|---|---|
| 目标 SQL P95 | 420 毫秒 | 28 毫秒 |
| 接口 P95 | 680 毫秒 | 290 毫秒 |
| 单次查询缓冲区访问量 | 约 34000 块 | 约 90 块 |
| 数据库 CPU 平均占用 | 78% | 43% |
| 排序临时文件 | 存在 | 未出现 |
| 写入事务 P95 | 18 毫秒 | 23 毫秒 |
这组结果支持索引调整的理由,不只是“SQL 变快了”,而是查询访问范围明显缩小、排序工作减少、CPU 消耗随之下降,机制与指标相互吻合。
但接口 P95 仍有 290 毫秒,说明还有其他延迟来源。下一步应检查连接等待、应用处理和网络路径,而不是继续给这条查询加索引。不同阶段的 P95 通常不能直接相加或相减,上表也不能用来精确推算网络耗时。
写入 P95 从 18 毫秒升到 23 毫秒,则提醒我们:新增索引需要维护,收益必须与写入成本一起验收。如果高峰以写入为主,还应复测更新状态、批量导入和复制延迟。
另一种结果是:新增索引后目标 SQL 只从 35 毫秒降至 30 毫秒,接口仍接近 800 毫秒;同时连接池等待 P95 达到 500 毫秒。此时主要瓶颈在排队。扩大连接池也未必正确,因为更多活动连接可能进一步增加 CPU、锁和内存压力,应先确认数据库是否有剩余处理能力。
还有一种情况是首次测试很慢,重复执行明显变快。若两次执行计划和访问块数基本一致,差异更可能来自缓存预热,而不是索引设计变化。应分别保留预热后和低缓存命中场景的结果,不要在生产服务器上主动清空缓存来制造对照。
五、决策边界:什么时候调整,什么时候停止加索引
可以把判断压缩为以下几类:
| 观察结果 | 优先判断 | 后续动作 |
|---|---|---|
| 少量结果对应大量扫描、过滤或排序 | 索引匹配不足 | 验证复合索引或部分索引 |
| 预计行数与实际行数严重偏离 | 统计信息或数据相关性问题 | 检查统计信息,再比较计划 |
| 返回表中较大比例的数据 | 顺序扫描可能合理 | 检查查询范围、聚合方式和返回量 |
| SQL 很快,接口很慢 | 数据库外部延迟 | 检查网络、连接等待和应用处理 |
| 锁等待占主要时间 | 事务阻塞 | 处理长事务和访问顺序 |
| 大量读取伴随存储延迟上升 | 扫描范围或资源瓶颈 | 同时验证索引收益与存储能力 |
值得调整索引的条件,是主要耗时确实来自可减少的数据访问或排序工作,候选索引能在代表性参数下稳定降低这些成本,并且写入、空间和运维代价可接受。
不必为了追求 Index Only Scan 把所有输出列都塞进索引。覆盖列会增加索引体积和维护成本;即使包含了查询所需列,是否还需访问表页,也与可见性映射和清理状态有关。高频更新表不一定能持续获得预期收益。
同样,如果慢查询来自深分页,大量 OFFSET 行仍需被处理,索引通常只能缓解,不能消除这类成本。应进一步验证基于排序键的游标分页,而不是认为“再加一个索引”就足够。
六、复测条件:在高峰负载下确认容量收益
索引上线后,应使用相同数据规模、参数分布和读写比例复测,并覆盖高频账户、大账户、不同状态及不同时间范围。对美国服务器上的数据库,还要保持应用访问位置和网络路径一致,避免把链路变化混入数据库收益。
容量判断可以采用分级增加请求速率的方法:每档保持足够采样时间,记录吞吐量、P95/P99、连接池等待、CPU、内存、存储延迟和复制延迟。当请求速率继续增加,而吞吐量不再明显增长、尾延迟持续上升时,系统开始接近当前负载下的处理边界。

这时应回到执行计划核对:如果访问量已经显著下降,但瓶颈转移到 CPU、写入或连接排队,就应停止围绕同一条 SQL 继续加索引。一次有价值的索引调整,最终要证明的不是空载下查询更快,而是在业务高峰仍能满足延迟目标,并保留可接受的资源余量和写入能力。
本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/4148.html



