独立服务器上的PostgreSQL查询变慢,如何区分索引问题与磁盘瓶颈?
查询变慢时,先不要把“慢”直接等同于“缺索引”或“磁盘不够快”。同一条 SQL 可能因为执行计划不合适而扫描过多数据,也可能使用了合适的计划,却在高负载下等待存储;锁等待、连接池排队和网络传输也会拉长应用看到的耗时。区分索引问题与磁盘瓶颈,关键是把应用耗时、数据库执行计划、等待事件和同一时间段的系统 I/O 放在一起看。
一个实用的判断起点是:若执行计划显示扫描或过滤了远超预期的数据,而磁盘延迟没有同步升高,优先检查索引与统计信息;若计划合理,但数据库读 I/O 等待和设备延迟在慢查询发生时同时升高,则应进一步确认存储是否饱和。单看“用了顺序扫描”、磁盘利用率或缓存命中率,都不足以定因。
先确认慢发生在哪一段
先明确对比的是哪种耗时:应用端从发起请求到收到响应的总时间,还是 PostgreSQL 实际执行 SQL 的时间。它们可能差很多。应用端耗时还包含连接池等待、网络往返、结果传输与应用处理;数据库日志和执行计划反映的则是数据库内部的不同阶段。
建议先采集同一慢查询、同一时间窗口内的证据:
- 应用侧记录请求耗时,并区分连接池等待、数据库调用与结果处理。若应用耗时明显高于数据库端执行耗时,索引或磁盘未必是主要原因。
- 数据库侧确认具体 SQL、调用次数、平均耗时和等待事件。慢查询文本可能包含敏感数据,保存和分享时应做好脱敏。
- 系统侧记录磁盘延迟、队列、吞吐和 CPU、内存情况。指标要与慢查询发生时间对齐,避免拿不同时段的数据作比较。
- 查询是否只在高峰期变慢、只影响特定表或用户,还是所有请求都变慢。这有助于区分局部计划问题与整体资源压力。
在排查前,记录 PostgreSQL 版本、操作系统、数据目录所在设备、近期发布或配置变更,以及慢查询出现的时间范围。不要先清空统计信息或重启服务:这些操作会改变现场,可能导致关键证据消失。
建立原因树:从请求路径走到存储
慢查询的原因可以按应用、网络、数据库和系统四层梳理。下表用于决定下一步查什么,而不是把某一项指标当作结论。
| 层级 | 观察依据 | 常见方向 | 下一步 |
|---|---|---|---|
| 应用与连接池 | 应用端耗时高,数据库执行时间低;请求开始前存在等待 | 连接池排队、重复查询、返回结果处理慢 | 对比连接池等待、数据库调用次数和响应大小 |
| 网络 | 数据库执行已结束,但应用收到结果较晚;问题集中在远端调用 | 往返延迟、网络拥塞、结果传输量大 | 对比同一请求的数据库端时间与客户端时间 |
| PostgreSQL 服务 | SQL 执行时间高,活动会话出现锁等待或 I/O 等待 | 锁阻塞、执行计划不佳、临时文件读写、读 I/O 等待 | 查看活动会话、阻塞关系与执行计划 |
| 操作系统与存储 | 多个数据库查询在同一时间变慢,设备延迟或队列持续抬升 | 磁盘压力、内存压力、设备共享负载 | 采集系统指标,并与数据库读写情况对时 |
这个层次顺序能避免一种常见误判:应用请求变慢,就立即给表加索引。若请求实际上在连接池里排队,新增索引不会解决排队;若问题是锁等待,磁盘升级也不一定有帮助。
先看数据库在等什么
先检查当前活动会话,确认慢查询是在执行、等待锁,还是等待 I/O。以下查询适合用于观察当前数据库中的非空闲会话;具体等待事件名称会随 PostgreSQL 版本和操作场景有所不同。
SELECT
pid,
now() - query_start AS elapsed,
state,
wait_event_type,
wait_event,
left(query, 200) AS query_sample
FROM pg_stat_activity
WHERE datname = current_database()
AND state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY query_start;
如果等待类型是 Lock,先检查阻塞关系,而不是先改索引。索引有时能缩短持锁时间,但不能替代对事务为何长时间未提交的调查。使用 pg_blocking_pids 可以查看阻塞某个会话的进程:
SELECT
pid,
pg_blocking_pids(pid) AS blocking_pids,
wait_event_type,
wait_event,
left(query, 200) AS query_sample
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
如果应用端显示耗时很长,但数据库里没有对应的长时间活动语句,问题可能发生在查询发往数据库之前,例如连接池排队;也可能发生在数据库执行之后,例如结果传输或应用处理。此时应把应用侧分段计时补齐,不能仅凭数据库没有“长查询”就断言网络正常。
用语句统计找到影响最大的查询
若已配置 pg_stat_statements,可以先按累计执行时间找出值得关注的语句,再按平均耗时、调用次数和单次高延迟等维度细看。下面的字段适用于较新的 PostgreSQL 版本;旧版本的统计字段名可能不同。
SELECT
queryid,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 1) AS mean_ms,
rows,
shared_blks_read,
shared_blks_hit
FROM pg_stat_statements
WHERE dbid = (
SELECT oid
FROM pg_database
WHERE datname = current_database()
)
ORDER BY total_exec_time DESC
LIMIT 20;
累计时间高可能是因为调用次数很多,不一定代表单次很慢;平均时间高也不一定说明影响范围最大。要结合调用量、耗时分布和业务时段判断。统计通常是累计值,采样时应记录时间间隔并比较增量;不要只看历史总量,更不要为了获得“干净数字”而随意重置统计。
shared_blks_read 是 PostgreSQL 从操作系统请求读取的共享缓冲区块数量,不等同于设备实际读取量。数据可能已在操作系统页缓存中,因此这个值不能单独证明磁盘发生了物理读。
用执行计划判断索引是否可疑
对具体慢查询检查计划,重点看扫描方式、估算行数与实际行数、过滤掉的行数,以及缓冲区读写情况。先用不执行查询的 EXPLAIN 查看估算计划;需要实际执行统计时,才谨慎使用 EXPLAIN (ANALYZE, BUFFERS)。
ANALYZE 会真正执行查询。查询可能很重,甚至会触发函数副作用;不要对写入语句或来源不明的 SQL 直接运行。对有风险的查询,应先在测试环境或只读副本上验证;生产环境执行前评估查询范围、负载和超时,必要时限制执行时间并安排在低峰。即使只读查询,也可能消耗大量 CPU、内存和 I/O。
BEGIN READ ONLY;
SET LOCAL statement_timeout = '5s';
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, created_at
FROM orders
WHERE customer_id = 12345
AND created_at >= timestamp '2025-01-01';
ROLLBACK;
上例中的表、列和值仅用于演示。statement_timeout 是保护措施,不意味着查询一定能在五秒内完成;超时后语句会被取消,事务需要回滚。EXPLAIN ANALYZE 的实测结果也会受缓存状态、并发负载和数据分布影响,最好在相近的业务条件下比较。
阅读计划时,优先核对以下信号:
- 估算行数与实际行数差距很大。 这常提示统计信息不能代表当前数据分布,可能导致规划器选择不合适的计划。先看表的
ANALYZE时间和数据变更情况,再决定是否更新统计信息。 - 扫描了大量行,又只留下很少结果。 看
Rows Removed by Filter、扫描节点的actual rows和过滤条件。若条件具有选择性,却没有合适的索引路径,索引设计可能需要复核。 - 索引条件与过滤条件的区别。 计划中的
Index Cond表示条件用于索引访问;Filter表示取出候选行后再过滤。大量行先被取出、随后被过滤,可能说明索引无法有效缩小扫描范围,也可能是查询本身确实要处理大量数据。 - 出现顺序扫描。 顺序扫描不等于计划错误。表很小、查询要取出表中很大比例的数据,或随机访问成本较高时,顺序扫描可能更合适。不要只因看到
Seq Scan就认定缺索引。 - 出现大量
shared read。 这说明 PostgreSQL 的共享缓冲区发生了读入,但不证明读请求已经到达物理磁盘。要与操作系统设备指标、I/O 等待时间一起看。 - 出现临时文件读写或排序、哈希溢写。 这提示执行过程中可能有中间结果落盘,原因可能是工作集较大或内存配置不合适,并不等同于“缺少索引”。
计划中的 actual time、行数和 loops 要结合节点层级解释;有循环执行的节点,其时间与行数可能是多次执行的统计。BUFFERS 记录的是缓冲区块活动,不是磁盘延迟,也不能单独用来判断存储性能。
复核索引与统计信息
若执行计划显示估算行数偏离实际很多,可查看目标表的统计信息更新时间和变更量:
SELECT
schemaname,
relname,
n_live_tup,
n_mod_since_analyze,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
统计信息过旧时,可评估在合适时段对目标表执行 ANALYZE。它不会删除表数据,但会消耗资源;大表或繁忙实例上仍应考虑执行时机。更新后重新观察计划,确认估算与实际是否更接近,而不是预设更新统计信息一定能解决问题。
索引设计要匹配查询条件、排序方式、数据分布和实际访问频率。多列索引的列顺序很重要;查询条件对列使用函数、发生类型转换、使用前导通配符,或操作符与索引类型不匹配时,普通索引未必能直接派上用场。局部索引和表达式索引也要确保查询条件与索引定义相符。PostgreSQL 默认不提供通用的“强制使用某个索引”机制;调整规划器参数来压制顺序扫描,不应作为常规修复方案。
不要仅凭某个索引短期内 idx_scan 为零就删除它:统计窗口可能太短,也可能未覆盖月末、报表或其他低频业务。新增索引会占用磁盘,并增加写入、维护和缓存成本。若决定变更索引,应先评估磁盘空间、写入负载、执行影响和变更窗口;在生产环境创建索引时,按对应版本支持的在线方式与操作要求执行,并留意并发创建失败后可能留下无效索引。回退应按确认的索引对象执行删除或重建,不要用不明范围的清理命令。
再确认磁盘是否形成了瓶颈
只有在执行计划和数据库等待信息提示存在读写压力时,才把系统 I/O 作为重点调查。Linux 上可以用 iostat、vmstat 和 pidstat 采集同一时间段的指标;设备名应先与 PostgreSQL 数据目录所在文件系统核对。
lsblk -o NAME,TYPE,SIZE,FSTYPE,MOUNTPOINTS
df -hT
iostat -xz 1 10
vmstat 1 10
pidstat -dru 1 10
iostat 和 pidstat 通常由系统的 sysstat 工具集提供。若命令不可用,先确认工具是否已安装及发行版对应的包名;不要在生产环境排障时未经评估就执行安装或更改系统配置。对于 RAID、虚拟化存储或云盘,操作系统显示的设备未必对应底层物理设备,需结合服务器的存储拓扑解释。
观察指标时,重点看趋势、基线和与慢查询的时间关联:
await持续高于平时且队列增长。 表示 I/O 完成时间或排队时间上升,应继续确认是哪类读写、哪个设备,以及这是否与 PostgreSQL 活动同时出现。单次采样值不能证明瓶颈。- 吞吐量和 IOPS 已接近设备或存储层的实际能力。 若此时数据库读写等待也增加,磁盘压力的可能性上升。设备型号、RAID、虚拟化层和负载模式都会影响合理范围,不能用一个固定阈值适配所有机器。
%util很高。 这可以作为线索,但不是单独的定论。并行能力较高的设备上,%util与饱和程度不一定一一对应;低值也不能排除特定读写延迟或底层共享存储问题。vmstat中等待 I/O 的时间增加。 说明系统层存在 I/O 等待迹象,但不一定是 PostgreSQL 数据文件导致;应继续核对设备、进程和业务时间。- 内存压力、交换活动或 CPU 饱和同时出现。 这可能间接加剧查询延迟,不能把所有资源问题都归到磁盘。还应检查是否有其他进程大量读写、内存是否不足、CPU 是否被计算任务占满。
如需更细的 PostgreSQL I/O 统计,可依据版本查看可用视图。较新版本提供 pg_stat_io;其他版本可结合 pg_stat_database、执行计划缓冲区统计及系统工具。某些读写耗时统计依赖 I/O 计时配置,启用前要确认版本、权限、采集开销和实例规范。
pg_stat_database.blks_read、temp_bytes 等统计一般是累计值,应在固定间隔内取样后计算增量,并结合统计重置时间解释。若数据库读请求增加,但设备延迟和系统读写没有变化,数据可能由操作系统缓存提供,或负载主要在其他层;这时不能仅凭 PostgreSQL 的块读取计数判为磁盘饱和。
用证据区分根因
将执行计划与系统指标按同一时间窗口对照,通常可以把问题归到以下几类:

| 执行计划与数据库现象 | 系统 I/O 表现 | 优先调查方向 |
|---|---|---|
| 行数估算严重偏差,扫描和过滤行数远超预期 | 设备延迟正常或变化不明显 | 统计信息、谓词与索引匹配、数据倾斜 |
| 查询访问范围大,计划合理,但数据库 I/O 等待增加 | 同一设备延迟和队列持续升高 | 存储负载、其他进程争用、数据集与缓存容量 |
| 计划显示有大量临时数据读写 | 磁盘写入与临时空间活动增加 | 排序、哈希或中间结果溢写,检查工作集与查询形态 |
查询耗时长,等待事件为 Lock | 设备指标没有明显变化 | 事务、阻塞会话和锁持有时间 |
| 应用耗时高,数据库端执行很快 | 设备指标正常 | 连接池、网络往返、结果传输或应用处理 |
| 多条查询同时变慢,CPU、内存或 I/O 中至少一项持续承压 | 系统指标与慢查询时间一致 | 实例级资源压力、并发量变化或共享设备争用 |
例如,某查询的计划估算返回几十行,实际却扫描数十万行;同一时段磁盘延迟没有明显上升。这时先查统计信息、过滤条件及现有索引是否覆盖该查询,通常比先更换磁盘更有针对性。反过来,如果多个查询的计划和行数都接近预期,但慢查询发生时同一数据盘的延迟、队列和数据库 I/O 等待一起上升,就应检查存储负载、其他进程和数据缓存是否受到压力。以上数字仅为示例,不能替代读者环境中的采样。
面向 PostgreSQL 等数据库业务,A5idc数据提供香港、美国、日本、韩国、中国台湾、新加坡和马来西亚等地区的物理服务器资源,覆盖 Xeon Gold、AMD EPYC 平台以及 SSD、NVMe 和大内存配置,可为数据库、业务后台和接口服务提供独立的计算与存储基础。不同地区还配有相应的 CN2、GIA、国际或本地线路方案,适配不同访问区域、数据规模与网络路径需求。
验证修复,并为复发留下监控点
修复前后应使用相同的 SQL、参数范围和相近负载进行比较,至少记录:
- 应用端请求耗时与数据库端语句耗时,必要时分开记录连接池等待和结果处理时间。
- 执行计划中的扫描方式、估算与实际行数、过滤行数和缓冲区活动。
- 同一时间段的磁盘延迟、队列、读写吞吐,以及数据库等待事件。
- 业务侧的错误率、超时率和查询结果正确性。
若更新统计信息后计划发生变化,要确认查询确实受益;若新增索引后查询变快,也要观察写入吞吐、索引空间和其他查询是否受到影响。不能只在低负载、暖缓存状态下验证一次,就推断高峰期也会改善。若改动带来回退,应按变更方案恢复索引或配置;恢复操作同样需要评估锁、资源和业务影响。
长期监控可围绕查询延迟分位数、慢查询调用量、连接池等待、锁等待、I/O 等待、临时文件增量和设备延迟建立基线。告警不宜只依赖单一阈值:短时峰值、统计重置和批处理任务都可能造成误报。更有价值的信号,是关键查询的延迟持续偏离自身基线,并同时出现可解释的计划变化、等待事件或系统资源压力。这样下次变慢时,才能先定位问题发生在哪一层,再决定应该调整索引、查询、并发或存储。
本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/9701.html



