Chapter 06

自测题库 + 跨章判别场景

前五章建立了从诊断到配置的完整因果链。这一章不再讲新知识,只做一件事:用题目把它逼出来。三层梯度——概念、原理、判别——最后一层是真实排查的预演:给一个症状,你得自己决定该动哪一块。

怎么用这一章

  • 三层梯度:概念层(回忆是什么)→ 原理层(解释为什么)→ 应用判别层(跨章选择该动哪块)
  • 所有答案集中在文末一个折叠块里。做完一层再对照,别边看题边看答案
  • 判别层是重点:它逼你在"统计 / 索引 / VACUUM / 配置"之间做选择,而不是套单一招式
  • 最后有一个"亲手画图"——合上教程,把整张调优地图重画一遍

难度自下而上递增。底层能脱口而出,中层要讲清机制,顶层要像处理线上工单一样在多个根因里定位、取舍。

综合度↑ 应用判别层 跨章选择,像真实排查 原理层 解释机制:为什么会这样 概念层 回忆定义:是什么
图 6自测的三层梯度。注意:顶层(红)是判别层,题目里没有"标准招式",必须先把症状归位到第 02–05 章的某一块——这正是这份教程想训练的能力,也是面试和线上排查真正考的东西。

6.1概念层(对应 01–03 章)

脱口而出级别。答不上来的,回对应章节补。

  1. EXPLAIN 输出里,cost 的单位是什么?它能不能换算成毫秒?→ 01 §1.4
  2. 一个计划节点写 actual time=0.01..0.04 ... loops=2000,这个节点的真实总耗时大约怎么算?→ 01 §1.4
  3. pg_stat_statements 排名,为什么用 total_exec_time 而不是 mean_exec_time?→ 01 §1.5
  4. 社区版 PostgreSQL 有没有 FORCE INDEX 这类 hint?它靠什么决定执行计划?→ 02 §2.1
  5. 四种扫描方式(Seq / Index / Index-Only / Bitmap)各自最适用的命中行比例(选择率)是什么区间?→ 02 §2.3
  6. 多列索引 (a, b) 在 PG 17 上能服务 WHERE b = ? 吗?PG 18 呢?为什么 PG 18 也有前提?→ 03 §3.2

6.2原理层(对应 02–05 章)

要讲清机制,不只给名词。

  1. 为什么命中行多到一定程度,优化器反而放弃索引、改走顺序扫描?用 random_page_cost 解释。→ 02 §2.3
  2. 一次 UPDATE 在 PostgreSQL 内部发生了什么?为什么它会产生死元组?→ 04 §4.1
  3. 普通 VACUUM 和 VACUUM FULL 在"是否把空间还给操作系统"上有什么区别?各自的代价是什么?→ 04 §4.3
  4. autovacuum 的触发阈值公式是什么?为什么默认的 scale_factor=0.2 对大表偏迟钝?→ 04 §4.6
  5. work_mem 为什么不能盲目调大?它和并发连接数、单查询节点数是什么关系?→ 05 §5.2
  6. HOT 更新成立的两个条件是什么?它和"该表上有几个索引"如何相互影响?→ 04 §4.4

6.3应用判别层(跨章场景,这是重点)

每个场景给一个症状。先写下你的判断和理由,再展开答案——答案里的"该动哪一块"比结论本身更重要。

  1. 统计 vs 索引:一条 JOIN 查询昨天 200ms,今天 40s。EXPLAIN ANALYZE 显示顶层 Nested Loop,rows=6 但 actual rows=480000,昨夜刚批量导入过数据。你的第一动作是加索引(03)还是别的(02)?为什么?
  2. 膨胀 vs 缺索引:一张高频 UPDATE 的表,顺序扫描读的页数(Buffers)一周内翻了三倍,但行数几乎没涨。该往缺索引(03)还是表膨胀(04)方向查?用哪个指标一锤定音?
  3. 选择率 vs 强行索引:一条报表查询命中全表约 35% 的行,走了 Seq Scan。有人要求"给它强制走索引"。该不该?如果这条查询只 SELECT 两列,有没有更好的办法(02 + 03)?
  4. index-only 退化:一个本该 Index Only Scan 的查询,EXPLAIN 里 Heap Fetches 很高、变慢了。根因更像是在覆盖索引设计(03)还是 VACUUM/可见性映射(04)?
  5. 内存 vs 连接:一台 64GB 机器,高并发下报 out of memory。max_connections=2000、work_mem=256MB。该先降 work_mem 还是先上连接池(05)?把两者的关系讲清楚。
亲手画一张图

合上教程,在纸上或 Excalidraw 里重画一遍 PostgreSQL 调优全景——只画六个东西:顶部的症状、中间四个根因(优化器 / 索引 / MVCC-VACUUM / 配置)、底部的统一框架(两个自动系统的估算 vs 现实)。画完回到 起点页的图 0 对照:你画的图里,02 优化器 和 04 MVCC 有没有被标成两个"核心"?如果当时想不起这四个根因,说明地图还没真正进脑子——回头重读那一章的章末综合段。

§全部答案

做完上面三层再展开。判别层的答案刻意先讲"该动哪一块、为什么",再给结论。

展开全部答案(先做完三层)

概念层

  1. cost 是抽象代价单位,基准为"顺序读一个 8KB 数据页 = 1.0",只用于比较计划相对贵贱,不能换算成毫秒。真实时间看 actual time / Execution Time。
  2. 约 0.04 × 2000 = 80 毫秒。actual time 是单次循环的耗时,要乘 loops 才是该节点的真实总贡献。
  3. 因为优化要花在累计影响最大的查询上。一条单次 5ms、每天调用 48 万次的查询(累计 240s),比单次 15s、每天 3 次(累计 45s)的更值得先优化。
  4. 没有。社区版不支持指定计划的 hint;它靠统计信息 + 代价模型自动选最便宜的计划。要影响它,改输入(统计 / 索引 / 代价参数)。
  5. Index Scan:命中极少行;Bitmap Heap Scan:命中中等;Seq Scan:命中大部分行或表很小;Index-Only Scan:查询列都在索引里且页 all-visible。边界由代价现算,非固定阈值。
  6. PG 17 不能(最左前缀规则,缺前导列 a 用不上)。PG 18 起有 B-tree skip scan,WHERE b 也有机会用上 (a,b),但前提是 a 为低基数列;a 高基数时仍退化。

原理层

  1. 索引扫描的回表是随机读,random_page_cost(默认 4.0)表示随机读一页约等于顺序读 4 页。命中行多时,海量随机读的总代价超过把整表顺序读一遍,于是 Seq Scan 更便宜。
  2. UPDATE 写入一个新的行版本(带新的 xmin),把旧版本的 xmax 标为本事务;旧版本对新事务不再可见,成为死元组。原地修改会破坏其他事务正在读的旧快照,所以 MVCC 选择写新版本。
  3. 普通 VACUUM 把死元组空间标记为该表可重用,不缩小文件、不还给 OS;VACUUM FULL 重写整表、把空间还给 OS,但持有 ACCESS EXCLUSIVE 锁会锁住整表。前者可常态运行,后者生产慎用(可用 pg_repack 在线替代)。
  4. 阈值 = autovacuum_vacuum_threshold(50) + autovacuum_vacuum_scale_factor(0.2) × 表行数。0.2 意味着大表要积累 20% 死元组才触发——十亿行表就是两亿死元组,太迟。大表应调小 scale_factor。PG 18 新增 autovacuum_vacuum_max_threshold 上限缓解这一点。
  5. work_mem 是每个排序/哈希节点、每个连接各自可用的内存。一条复杂查询有多个这类节点,会用上数倍 work_mem,再乘并发连接数,总用量 = work_mem × 节点数 × 连接数,容易放大到 OOM。
  6. HOT 成立需要:① 这次 UPDATE 没有修改任何被索引的列;② 当前堆页有空闲空间。表上索引越多,"没动任何被索引列"越难满足,UPDATE 越难走 HOT、越容易制造索引膨胀——这是"索引不是越多越好"的另一面。

应用判别层

  1. 动 02,不是 03。rows=6 vs actual=480000 的巨大偏差 + "昨夜批量导入" = 统计信息过期的典型剧本:优化器以为外层只有几行才选了 Nested Loop。第一动作是 ANALYZE 那张表,让它重新估、改选 Hash Join。加索引解决不了"估错行数"这个根因。
  2. 查膨胀(04)。"行数没涨但页数翻倍"正是死元组堆积、表膨胀的特征——顺序扫描要读更多空洞页。一锤定音的指标:pg_stat_user_tables 的 n_dead_tup(以及 last_autovacuum)。缺索引不会让页数无故膨胀。修法是让 autovacuum 跟上 / 必要时 repack。
  3. 不该强行走索引。命中 35% 时 Seq Scan 通常是优化器的正确选择(回表随机读太贵,见第 07 题)。强扭只会更慢。但若只 SELECT 两列,更好的办法是建一个覆盖索引(把那两列放进索引,走 Index-Only Scan 免回表)——这才是把"命中行多但列少"这个场景做快的正解,答案横跨 02(选择率)和 03(覆盖索引)。
  4. 更可能在 04。Index-Only Scan 需要页在可见性映射里标 all-visible,而 VM 由 VACUUM 维护。Heap Fetches 高 = 很多页未被标 all-visible = autovacuum 没跟上。先查该表的 vacuum 状态,而不是急着改索引。这是 03 和 04 的连接点。
  5. 先降 work_mem,并上连接池。潜在峰值内存 ≈ work_mem × 每查询节点数 × 活跃连接数;256MB × 多节点 × 上千连接,远超 64GB。先把 work_mem 降到合理值(如 16–32MB),再用 PgBouncer 把后端连接数压到几十,从两个方向同时止血。单纯调高 max_connections 只会加剧内存放大。
洞察 · 五个场景是同一句话

回看判别层:每个场景的正确第一步,都是先把症状归位到"哪个自动系统的估算偏离了现实"——是优化器估错了行数(13、15),还是 autovacuum 没跟上清理(14、16),还是资源配置撑不住机制(17)。这就是起点页那句"一句话本质"的落地。能稳定做这个归位,你就从"背调优招式"升级成了"按因果链定位",这正是这份教程的目标。