PostgreSQL 教程: 使用 EXPLAIN BUFFERS 进行查询调优

九月 1, 2025

摘要：在本教程中，你将学习如何在 PostgreSQL 中使用 EXPLAIN BUFFERS 对查询进行调优。

介绍

在进行查询优化工作时，人们自然会将注意力集中在时间数据上。若想提升查询速度，就需要明确查询中哪些部分执行较慢。

但时间数据存在以下几个缺陷：

每次运行的结果都可能不同
依赖缓存状态
仅靠时间数据可能掩盖效率问题（例如通过并行执行掩盖）

当然，时间数据在查询调优中非常重要。但在本文中，我们将探讨如何使用 EXPLAIN BUFFERS，结合查询读取和写入的数据量，来补充时间数据的不足。

当人们分享 “查询速度提升 1000 倍” 的案例时，这类优化通常源于整体数据读取量的大幅减少，这通常是通过添加索引来实现的。

若想查看查询读取或写入的数据量，可使用 buffers 参数，例如在查询前添加以下前缀：

explain (analyze, buffers)

什么是缓冲区统计数据？

每个缓冲区统计数据都由 “前缀” 和 “后缀” 两部分组成。

三种前缀：

Shared（共享块）：包含普通表和索引的数据。
Temp（临时块）：包含用于计算哈希、排序、物化等操作的短期临时数据。
Local（本地块）：包含临时表和临时索引的数据（需要注意的是，它与 “Temp” 前缀的含义不同，这点很容易混淆）。

四种后缀：

Hit（命中）：表示该数据块在 PostgreSQL 缓冲区缓存中被找到。
Read（读取）：表示该数据块在 PostgreSQL 缓冲区缓存中未命中，需从磁盘或操作系统缓存中读取。
Dirtied（修改）：表示该数据块被当前查询修改（变为 “脏块”）。
Written（写入）：表示该数据块从缓存中被淘汰并写入磁盘。

默认情况下，这些数据块是大小为 8KB 的页，几乎没有人会修改这个设置。若想查看您的数据库的块大小，可执行下面查询：

show block_size;

我们来看一个包含缓冲区信息的简单查询执行计划示例：

explain (analyze, buffers, costs off)
  select id, email from people where email = 'redrock@outlook.com';

Index Scan using people_email on people (actual time=1.066..1.071 rows=1 loops=1)
  Index Cond: (email = 'redrock@outlook.com'::text)
  Buffers: shared hit=3 read=1
Planning Time: 0.179 ms
Execution Time: 1.108 ms

从以 “Buffers:” 开头的行中，我们可以看出：在这次索引扫描中，有 3 个数据块从 PostgreSQL 缓冲区缓存中读取（shared hit），1 个数据块从操作系统缓存或磁盘中读取（shared read）。

若要获取 “I/O 写入时间” 和 “I/O 读取时间”，也可通过 buffers 实现，但需先设置一个配置选项来启用该功能。如果您是超级用户（superuser），可在当前会话中执行 SET track_io_timing = on; 来收集这两项数据。

缓冲区数据在哪些场景下有用？

缓冲区统计数据的用途有很多，以下是常见的几种主要场景：

发现执行 I/O 操作远超预期的节点
了解查询的总 I/O 量
发现节点发生磁盘溢出
识别缓存性能问题的迹象

下面我们逐一分析这些场景。

发现执行 I/O 操作远超预期的节点

如果某个索引仅返回几千行数据，但却读取了数万甚至数十万的数据块，这可能会让您感到意外。假设每个数据块为 8KB，那么读取 10 万个数据块就相当于读取了近 1GB 的数据！

看到一个耗时 2 秒的扫描操作读取了 5GB 数据，无疑能帮助我们理解它为何执行缓慢。当然，即使不使用buffers，您也可能发现索引扫描过滤了大量行，但这类情况有时难以察觉，因为过滤行数是以 “每轮循环平均值” 的形式上报的。

了解查询的总 I/O 量

由于父节点的缓冲区数据会包含其子节点的缓冲区数据，因此通常只需查看查询计划中的顶层节点，就能快速了解查询的总缓冲区使用量，即总 I/O 量。

我们在上面的简单示例中添加limit子句，就能看到这一点：

explain (analyze, buffers, costs off)
  select id, email from people where email = 'redrock@outlook.com' limit 1;

Limit (actual time=0.146..0.146 rows=1 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using people_email on people (actual time=0.145..0.145 rows=1 loops=1)
        Index Cond: (email = 'redrock@outlook.com'::text)
        Buffers: shared hit=4
Planning Time: 0.230 ms
Execution Time: 0.305 ms

注意，Limit节点显示读取了 4 个数据块（全部来自 PostgreSQL 缓存）。这是一个包含子节点（索引扫描）的总量，显然，所有数据读取都由索引扫描完成。对于更复杂的查询，通过顶层节点的缓冲区数据，可快速判断查询的总 I/O 情况。

发现节点发生磁盘溢出

另一个常见的性能问题是：节点发生 “磁盘溢出”，操作所需内存不足，需借助磁盘存储临时数据。

对于排序操作（Sort），PostgreSQL 会通过 “Sort Method” 和 “Disk” 统计数据，明确告知我们是否发生磁盘溢出。但对于其他类型的操作，若不借助缓冲区数据，可能难以甚至无法发现其磁盘溢出情况。

以下是一个排序操作发生磁盘溢出的简单示例：

explain (analyze, buffers, costs off)
  select id, email from people order by email;

Sort (actual time=2501.340..3314.259 rows=1000000 loops=1)
  Sort Key: email
  Sort Method: external merge  Disk: 34144kB
  Buffers: shared hit=7255, temp read=4268 written=4274
  ->  Seq Scan on people (actual time=0.016..35.445 rows=1000000 loops=1)
        Buffers: shared hit=7255
Planning Time: 0.101 ms
Execution Time: 3335.237 ms

由于我们启用了buffers参数，在排序节点的缓冲区统计数据中，还能看到temp read/written（临时块读取 / 写入）的数值，这证实该操作确实向磁盘写入了临时数据，之后又从磁盘读取了这些数据。

对于那些不像排序操作那样会明确报告磁盘溢出的节点，关注临时缓冲区（temp前缀相关数据）是发现其磁盘溢出的有效方法！

识别缓存性能问题的迹象

通过对比 “共享命中块（shared hit）” 和 “共享读取块（shared read）” 的数量，我们可以大致了解缓存命中率。遗憾的是，这种方法并不完美，因为 “共享读取块” 还包含了操作系统缓存命中的情况，但它至少能为我们的问题排查提供线索。

需要注意的是，在执行计划的默认文本格式中，只会显示非零的缓冲区统计数据。因此，若您只看到 “shared hit”（共享命中），那么对于分析的查询，所有数据块都来自 PostgreSQL 缓冲区缓存！

总结

我们探讨了在查询优化过程中使用缓冲区数据的几种主要方式：

通过单个节点的缓冲区数据，发现执行 I/O 操作远超预期的节点。
通过总缓冲区数据，了解查询的总 I/O 量。
通过临时缓冲区数据，发现节点发生磁盘溢出。
通过对比共享命中块与共享读取块，识别潜在缓存性能问题的线索。

了解更多

PostgreSQL 优化

介绍