PostgreSQL / 系统课时

用 BUFFERS 判断资源消耗

用 BUFFERS 判断资源消耗

执行时间只能告诉你查询慢,不能说明慢在读取、缓存还是计算。EXPLAIN (ANALYZE, BUFFERS) 把实际行数、节点时间和缓冲块访问放在同一份计划里,本课用可重复实验识别读取放大,并说明哪些现象只能作为线索、不能直接当成磁盘或 CPU 结论。

学习目标

  • 读懂执行计划中的实际时间、行数和 BUFFERS 计数
  • 区分 shared hit、shared read、临时块与本地块
  • 用前后计划验证索引是否真正减少了读取工作量

先生成一份可控的计划证据

下面示例应在实验数据库执行。它创建独立 schema 和 20 万行测试表,不建客户列索引,并关闭本会话的并行 Gather,便于稳定观察整表扫描。

sql
DROP SCHEMA IF EXISTS anystack_buffer_lab CASCADE;
CREATE SCHEMA anystack_buffer_lab;

CREATE TABLE anystack_buffer_lab.orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL,
    amount numeric(10, 2) NOT NULL
);

INSERT INTO anystack_buffer_lab.orders (customer_id, amount)
SELECT
    (g % 1000) + 1,
    ((g % 10000) / 100.0)::numeric(10, 2)
FROM generate_series(1, 200000) AS g;

ANALYZE anystack_buffer_lab.orders;
SET max_parallel_workers_per_gather = 0;

预期输出:

text
DROP SCHEMA
CREATE SCHEMA
CREATE TABLE
INSERT 0 200000
ANALYZE
SET

若 schema 原本不存在,DROP SCHEMA IF EXISTS 还可能先输出一条 NOTICE,不影响后续实验。接着执行目标查询并同时采集实际执行信息和缓冲块统计。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(amount)
FROM anystack_buffer_lab.orders
WHERE customer_id = 42;

预期输出:

text
Aggregate  (actual time=<VALUE>..<VALUE> rows=1 loops=1)
  Buffers: shared hit=<VALUE> read=<VALUE>
  ->  Seq Scan on orders  (actual time=<VALUE>..<VALUE> rows=200 loops=1)
        Filter: (customer_id = 42)
        Rows Removed by Filter: 199800
        Buffers: shared hit=<VALUE> read=<VALUE>
Planning:
  Buffers: shared hit=<VALUE>
Planning Time: <VALUE> ms
Execution Time: <VALUE> ms

缓存状态不同会让 hit 与 read 的具体数字变化,零值字段也可能被 PostgreSQL 省略,但扫描节点会检查 20 万行,只返回 200 行。这就是典型的读取放大:为得到很少的目标行,执行器触碰了远多于结果所需的数据页。

正确解释 hit、read 与临时 I/O

BUFFERS 记录的是 PostgreSQL 看到的块访问,不等同于“真实磁盘 I/O 次数”。尤其是 shared read,表示块需要读入 PostgreSQL 共享缓冲区,但底层数据仍可能来自操作系统页缓存。

指标 含义 判断重点
shared hit 数据块已在 shared_buffers 中 命中高只能说明没再次读入共享缓冲区,不代表计算便宜
shared read 数据块被读入共享缓冲区 可提示冷数据或工作集压力,但不能直接等同物理磁盘读取
temp read/written 排序、哈希等产生的临时文件 I/O 持续偏高时检查 work_mem、行数估算和算法选择
local hit/read 临时表等本地缓冲区访问 不要与普通表的共享缓冲区统计混为一谈

shared dirtied 和 shared written 说明执行期间存在脏页或写出活动,它们也不等价于业务语句写了多少行。判断资源瓶颈时应把它们与节点类型、行数和语句性质一起看。

把块访问和时间、行数放在一起

读取放大最值得看扫描节点:返回行数很少,却有大量 shared hit + shared read,通常说明访问路径不够选择性。若块访问并不高但执行时间仍长,应继续检查表达式计算、排序、哈希、函数调用以及高 loops 节点,而不是把所有慢查询都归因于 I/O。

如果希望看到更直接的 I/O 时间,还要确认实例是否启用了 track_io_timing。

sql
SHOW track_io_timing;

预期输出:

text
 track_io_timing
-----------------
 <VALUE>

实际值通常是 on 或 off。为 off 时,BUFFERS 仍然可用,但计划不会提供对应的 I/O timing 证据;是否启用应评估采样开销并遵循实例运维策略。

用索引前后计划验证结论

如果读取放大来自缺少选择性访问路径,增加合适索引后,计划应从整表扫描转向索引相关扫描,并显著减少扫描节点触碰的表块。不要只看到“用了索引”就宣布优化成功,仍要比较实际行数、块访问和总执行时间。

sql
CREATE INDEX orders_customer_id_idx
ON anystack_buffer_lab.orders (customer_id);

ANALYZE anystack_buffer_lab.orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(amount)
FROM anystack_buffer_lab.orders
WHERE customer_id = 42;

预期输出:

text
CREATE INDEX
ANALYZE
Aggregate  (actual time=<VALUE>..<VALUE> rows=1 loops=1)
  Buffers: shared hit=<VALUE> read=<VALUE>
  ->  <Index Scan 或 Bitmap Heap Scan> on orders  (actual time=<VALUE>..<VALUE> rows=200 loops=1)
        Buffers: shared hit=<VALUE> read=<VALUE>
Planning Time: <VALUE> ms
Execution Time: <VALUE> ms

具体节点可能是 Index Scan 或 Bitmap Heap Scan,由成本估算和缓存状态决定,零值缓冲字段仍可能省略;关键验收项是同样返回 200 行时,扫描节点访问的表块明显减少,执行时间也得到验证性下降。若块访问降了但总时间没有改善,就应继续寻找 CPU、锁等待或其他执行阶段的成本,而不是继续机械加索引。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

用 BUFFERS 判断资源消耗

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通