PostgreSQL / 系统课时

校准代价参数

校准代价参数

代价参数决定规划器如何给候选路径打分,但它们不是“让某个查询走索引”的开关。本课用一套可回滚的校准方法,区分 I/O、缓存与 CPU 代价,并明确何时应该先修统计而不是改参数。

学习目标

  • 解释 seq_page_cost、random_page_cost、effective_cache_size 与 CPU 代价参数如何影响路径比较
  • 用会话级参数完成可回滚的校准实验,并知道该观察哪些计划变化
  • 识别“估算错了”和“代价模型不合适”的区别,避免用参数掩盖统计问题

先判断是不是代价模型的问题

PostgreSQL 的 cost 不是毫秒,而是一套相对单位。规划器先估算每条路径会读取多少页、处理多少行、执行多少运算,再按代价参数换算成总成本,最后选择成本最低的计划。因此,估算行数错误时,调 cost 往往只是在错误输入上重新打分。

开始校准前先看代表性慢查询的 EXPLAIN (ANALYZE, BUFFERS)。如果 rows 与 actual rows 相差一个或多个数量级,应优先刷新统计、提高统计目标或建立扩展统计;只有行数估算基本可信,而扫描方式仍与真实硬件特征明显不匹配时,才进入代价参数校准。

理解几个关键参数

参数 规划器中的含义 调低后的常见倾向
seq_page_cost 顺序读取一个数据页的相对成本基准 顺序扫描整体更便宜
random_page_cost 非顺序读取一个数据页的相对成本 索引扫描、位图路径更容易胜出
effective_cache_size 规划器估计单个查询可利用的共享缓冲区与操作系统缓存规模 值变大时,更愿意相信重复页访问能命中缓存
cpu_tuple_cost 处理一个表行的 CPU 成本 扫描大量行的 CPU 权重下降
cpu_index_tuple_cost 处理一个索引项的 CPU 成本 索引路径的 CPU 权重下降
cpu_operator_cost 执行一个操作符或函数的 CPU 成本 计算密集表达式的成本下降

effective_cache_size 不会分配内存,也不会改变缓存大小,它只是规划器的估算输入。类似地,page cost 与 CPU cost 都是相对权重;把所有成本按同一倍数同时放大或缩小,通常不会改变路径选择。

用会话级设置做可回滚实验

先在事务内使用 SET LOCAL,不要一开始就改全局配置。下面的 SQL 只验证本次事务内参数是否按预期生效,事务结束后自动恢复原值。

sql
BEGIN;

SET LOCAL random_page_cost = 1.10;
SET LOCAL effective_cache_size = '8GB';
SET LOCAL cpu_tuple_cost = 0.01;

SELECT current_setting('random_page_cost') AS random_page_cost,
       current_setting('effective_cache_size') AS effective_cache_size,
       current_setting('cpu_tuple_cost') AS cpu_tuple_cost;

ROLLBACK;

预期结果

text
 random_page_cost | effective_cache_size | cpu_tuple_cost
------------------+----------------------+----------------
 1.1              | 8GB                  | 0.01

确认参数生效后,在同一个会话、同一份数据、同一组代表性查询上分别执行 EXPLAIN (ANALYZE, BUFFERS),比较路径、估算成本、实际耗时与缓冲区命中情况。不要只看单条查询,也不要只看规划成本变小;真正要确认的是一组查询的执行表现是否更稳定。

按证据校准而不是凭感觉改值

校准时可以按下面的顺序缩小变量范围:

  1. 固定数据集与查询样本,记录原始计划、实际耗时、shared hit、shared read 与行数估算误差。
  2. 先处理过期统计、低统计目标、列相关性等基数估算问题,确保 cost 模型接收到的行数接近事实。
  3. 根据存储介质和缓存命中情况调整 I/O 相对成本。SSD 或高缓存命中环境通常不需要像机械磁盘那样强调随机 I/O 惩罚,但不要机械照搬别人的数值。
  4. 只有在计算明显成为主要成本时,再评估 CPU 参数;一次只改少量参数,观察计划是否按预期变化。
  5. 通过会话或测试环境验证多类查询后,再考虑系统级调整,并保留回滚值与前后基线。

当 EXPLAIN ANALYZE 显示估算行数本身就明显错误时,正确动作是治理统计信息;当估算可信、缓存与 I/O 特征长期稳定,却持续出现不合理的扫描路径时,才值得校准代价模型。

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

目录与阅读设置

记录一个想法

校准代价参数

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

登录哪都通