PostgreSQL / 系统课时
用 BUFFERS 判断资源消耗
用 BUFFERS 判断资源消耗
执行时间只能告诉你查询慢,不能说明慢在读取、缓存还是计算。EXPLAIN (ANALYZE, BUFFERS) 把实际行数、节点时间和缓冲块访问放在同一份计划里,本课用可重复实验识别读取放大,并说明哪些现象只能作为线索、不能直接当成磁盘或 CPU 结论。
学习目标
- 读懂执行计划中的实际时间、行数和
BUFFERS计数 - 区分
shared hit、shared read、临时块与本地块 - 用前后计划验证索引是否真正减少了读取工作量
先生成一份可控的计划证据
下面示例应在实验数据库执行。它创建独立 schema 和 20 万行测试表,不建客户列索引,并关闭本会话的并行 Gather,便于稳定观察整表扫描。
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;预期输出:
DROP SCHEMA
CREATE SCHEMA
CREATE TABLE
INSERT 0 200000
ANALYZE
SET若 schema 原本不存在,DROP SCHEMA IF EXISTS 还可能先输出一条 NOTICE,不影响后续实验。接着执行目标查询并同时采集实际执行信息和缓冲块统计。
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(amount)
FROM anystack_buffer_lab.orders
WHERE customer_id = 42;预期输出:
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。
SHOW track_io_timing;预期输出:
track_io_timing
-----------------
<VALUE>实际值通常是 on 或 off。为 off 时,BUFFERS 仍然可用,但计划不会提供对应的 I/O timing 证据;是否启用应评估采样开销并遵循实例运维策略。
用索引前后计划验证结论
如果读取放大来自缺少选择性访问路径,增加合适索引后,计划应从整表扫描转向索引相关扫描,并显著减少扫描节点触碰的表块。不要只看到“用了索引”就宣布优化成功,仍要比较实际行数、块访问和总执行时间。
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;预期输出:
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、锁等待或其他执行阶段的成本,而不是继续机械加索引。