PostgreSQL / 系统课时

建立查询性能基线

建立查询性能基线

索引优化前先记录基线,才能回答“到底快了多少”和“代价是否可接受”。本课用固定查询测量执行时间、实际处理行数、缓冲区命中与读取情况,并保留执行计划,形成一份可以和加索引结果逐项对比的性能证据。

学习目标

  • 用 EXPLAIN (ANALYZE, BUFFERS) 建立可重复的查询基线
  • 区分实际行数、过滤开销和共享缓冲区命中情况
  • 记录无索引与有索引两次计划,避免只看执行时间下结论

先记录无索引基线

沿用同类实验中的 orders 表,但不要假设当前数据库已经存在正确的客户索引。先删除实验索引,再固定执行参数,确保本次测量只观察全表扫描。

sql
DROP INDEX IF EXISTS idx_orders_customer_id;

SET max_parallel_workers_per_gather = 0;
SET jit = off;

ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT order_id, amount
FROM orders
WHERE customer_id = 42;

预期结果

text
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Seq Scan on orders
   Filter: (customer_id = 42)
   Rows Removed by Filter: <接近180000>
   Buffers: shared hit=<较多>
 Planning Time: <TIME> ms
 Execution Time: <TIME> ms

这里重点记录四类信息,而不是抄一遍整棵计划树。Seq Scan 表明当前路径需要扫描整张表;Rows Removed by Filter 反映大量无关行被访问后才被筛掉;Buffers 中的 shared hit 表示页面直接从共享缓冲区命中,若出现 shared read,则说明部分页面需要从存储读取;Execution Time 则是本次实际执行耗时。具体数值会随硬件、缓存状态和 PostgreSQL 版本而变化。

固定一份可比较的基线表

把第一次测量压缩成少量字段,后续新增索引后继续记录同样的字段。对于稳定比较,至少保留扫描节点、返回行数、被过滤行数、命中页、读取页和执行时间。

实际返回行数可以直接从执行计划中的 rows 观察。由于本实验每个客户对应固定 20 条订单,正常情况下 customer_id = 42 应返回 20 行;这也是检查数据集是否被意外改变的一个简单信号。

创建索引并重复同一查询

现在只改变一个变量:增加 customer_id 的 B-tree 索引。不要同时修改 SQL、数据量或会话参数,否则即使计划发生变化,也无法证明原因来自这个索引。

sql
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT order_id, amount
FROM orders
WHERE customer_id = 42;

预期结果

text
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders
   Recheck Cond: (customer_id = 42)
   Buffers: shared hit=<少量>
   ->  Bitmap Index Scan on idx_orders_customer_id
         Index Cond: (customer_id = 42)
         Buffers: shared hit=<少量>
 Planning Time: <TIME> ms
 Execution Time: <TIME> ms

具体环境也可能得到 Index Scan 而不是 Bitmap Heap Scan,两者都说明优化器已经利用 idx_orders_customer_id。因此基线不能写成“必须出现某一种节点”,而应记录访问路径是否从全表扫描转为索引驱动,以及实际代价是否下降。

这样读取基线才有意义

最值得比较的是同一 SQL 前后的趋势,而不是单次毫秒数。执行时间明显下降通常说明访问范围变小,但还要同时检查共享缓冲区访问量;如果数据已经全部缓存在内存中,shared hit 很高并不代表查询异常,它只说明页面来自共享缓冲区而非磁盘读取。

对于索引优化,还应保留完整执行计划文本,因为计划节点能够解释“为什么变快”。例如从 Seq Scan 变成 Index Scan 或 Bitmap Heap Scan,比单纯记录“从 30 ms 变成 2 ms”更适合后续复盘和回归验证。

基线的目标不是追求一个看起来漂亮的数字,而是让优化前后使用完全相同的查询、数据和会话条件,并同时保存计划、实际行数和缓冲区访问。只有这些证据能够互相对上,后面的索引收益判断才具有可复现性。

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

目录与阅读设置

记录一个想法

建立查询性能基线

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

登录哪都通