05.3.2.2 执行计划、成本模型与统计信息
所属:全栈开发面试题库 / 05 数据库与存储 / 05.3 PostgreSQL / 05.3.2 索引优化与高可用运维
覆盖范围
- 成本模型与索引选择、Seq/Index/Bitmap/Index-Only Scan
- EXPLAIN 关键指标与计划节点
- 统计信息、ANALYZE、直方图/相关性/扩展统计
- 计划缓存、prepared statement 与计划稳定性
- 并行查询取舍、与 MySQL 优化器对照
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.3.2.2-001 | 成本模型如何影响索引选择 | 高级 | ★★★★★ |
| 05.3.2.2-002 | Seq Scan 是否一定差 | 高级 | ★★★★★ |
| 05.3.2.2-004 | Index Only Scan 依赖 VACUUM | 高级 | ★★★★☆ |
| 05.3.2.2-006 | 统计不准选错计划 | 高级 | ★★★★★ |
| 05.3.2.2-008 | 预编译语句计划变差 | 高级 | ★★★★☆ |
成本主线
总成本 ≈ 顺序页成本(seq_page_cost × 页数)
+ 随机页成本(random_page_cost × 随机读页)
+ CPU 成本(每行处理 × 行数) + 启动成本
估算依赖:pg_stats 唯一值/高频值/直方图/相关性 → rows 选择率
偏差大 → ANALYZE / 提高统计目标 / 扩展统计
05.3.2.2-001 PG 成本模型如何影响索引选择?
频率 ★★★★★ | 难度 高级
一句话结论
优化器比较全表顺序扫描与索引扫描 + 回表的总成本;索引随机读成本由 random_page_cost 加权,匹配行占比高时顺序扫描更便宜;effective_cache_size 影响缓存命中假设,列相关性影响随机读估算准确性。
概念解释
- 顺序/随机页成本:读页 IO 权重系数。
- CPU 成本:逐行比较过滤处理。
- 缓存假设:effective_cache_size 告知优化器可用缓存规模。
- 相关性:列值与物理顺序相关度影响索引扫描 IO 估算。
原理流程
- 按统计信息估算各路径行数与页数。
- 叠加 IO 与 CPU 成本比较候选计划。
- 选总成本最低计划。
代码 / 命令
SELECT attname, correlation FROM pg_stats
WHERE tablename='orders' AND attname='create_time';
SHOW random_page_cost;
SHOW effective_cache_size;
常见误区
- 误区:成本就是实际执行时间。正解:模型相对估算,用于计划间比较,不等于真实耗时。
- 误区:随机读成本系数对 SSD 也该保持默认 4。正解:SSD 随机读快,默认偏高会让优化器过度回避索引(待确认默认值)。
追问
- 问:effective_cache_size 是实际分配内存吗?答:不是,是告知优化器 OS 缓存规模的估算参数。
- 问:相关性高为什么利于索引扫描?答:索引顺序接近物理顺序,回表随机读退化为顺序读。
05.3.2.2-002 Seq Scan 一定比 Index Scan 差吗?
频率 ★★★★★ | 难度 高级
一句话结论
不一定。小表、返回大部分行、物理高度相关或索引选择性差时,顺序扫描连续 IO 可能比索引随机回表更便宜;判断依据是 ANALYZE 实际行数、缓冲读取与回表次数,而非节点名。
概念解释
- Seq Scan:顺序扫全表,连续 IO。
- Index Scan:索引定位 + 回表,随机 IO。
- 成本权衡:返回行占比高时索引随机读代价超过顺序扫。
原理流程
- EXPLAIN ANALYZE 看实际行数与缓冲读。
- 大比例过滤后仍 Seq Scan 且 Rows Removed 多 → 评估索引。
- 小表/高占比 Seq Scan 属合理选择。
代码 / 命令
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
常见误区
- 误区:出现 Seq Scan 就是缺索引。正解:小表或高匹配占比下顺序扫合理。
- 误区:Index Scan 一定更快。正解:大量回表随机读可能比顺序扫慢。
追问
- 问:大表 Seq Scan 只返回少量行说明什么?答:通常缺可用索引或条件失效。
- 问:如何验证索引计划?答:ANALYZE 更新统计、核对参数,必要时临时关 seqscan 对比(不作长期依赖)。
05.3.2.2-003 Bitmap Heap Scan 是什么?
频率 ★★★★☆ | 难度 高级
一句话结论
先扫索引构建匹配位置位图,再按页顺序回表;适合中等选择性:把大量随机回表聚合成按页顺序访问;位图可能 lossy(页级近似),回表后 Recheck Cond 重新校验条件。
概念解释
- Bitmap Index Scan:索引扫描构建位图。
- Bitmap Heap Scan:按位图顺序回表。
- Recheck:lossy 位图在堆上重新过滤。
原理流程
- 索引扫描收集匹配行/页位图。
- 位图排序按页聚合。
- 顺序回表并复核条件。
代码 / 命令
EXPLAIN SELECT * FROM orders
WHERE status='PAID' AND create_time > now() - interval '7 days';
常见误区
- 误区:Bitmap Scan 是低效计划。正解:中等选择性下它减少随机读,常优于纯 Index Scan。
- 误区:命中很少也走 Bitmap。正解:单行少量命中时构建位图开销不划算,优化器选 Index Scan。
追问
- 问:Bitmap 与 Index Scan 区别?答:前者先收集全部位置再顺序回表,后者边扫边回表。
- 问:Recheck Cond 说明什么?答:位图为页级近似,回表需重新检查条件。
05.3.2.2-004 Index Only Scan 为什么依赖 VACUUM?
频率 ★★★★☆ | 难度 高级
一句话结论
Index Only Scan 只读索引不回表,但需确认行对当前事务可见;可见性映射(VM)标记页全可见,只有全可见页才能跳过回表。VACUUM 更新 VM,长期不回收则 Heap Fetches 升高退化为普通索引扫描。
概念解释
- VM 可见性:页内元组全可见标记。
- Heap Fetches:实际回表确认次数。
- 覆盖前提:输出列都在索引内。
原理流程
- 索引扫描定位索引项。
- 查 VM 判断页是否全可见。
- 全可见免回表,否则 Heap Fetch 回堆确认。
代码 / 命令
EXPLAIN (ANALYZE) SELECT tenant_id, amount FROM orders WHERE tenant_id=1;
-- Heap Fetches: 0 理想
常见误区
- 误区:覆盖索引建完就免回表。正解:VM 未标记全可见时仍回表。
- 误区:Heap Fetches 高是索引列不够。正解:也可能是表更新频繁、VM 未及时更新,需 vacuum。
追问
- 问:VM 什么时候更新?答:VACUUM 回收死元组后更新,见 05.3.1.4。
- 问:怎么提高 index-only 比例?答:INCLUDE 覆盖列 + 保障 vacuum 更新 VM。
05.3.2.2-005 EXPLAIN 怎么读?关键指标有哪些?
频率 ★★★★★ | 难度 进阶
一句话结论
看成本区间、估算 rows、实际 time/rows、loops、Buffers 命中/磁盘读、Filter 过滤量与 Heap Fetches;对照估算与实际偏差判断统计问题,按节点类型识别扫描/连接/聚合/排序代价。
概念解释
- cost:估算启动/总成本。
- rows:估算输出行数。
- actual time/rows/loops:实际耗时、行数与执行次数。
- Buffers:共享缓存命中与磁盘读。
原理流程
EXPLAIN (ANALYZE, BUFFERS)实际执行取真实指标。- 自上而下读节点树,定位高耗时/大偏差节点。
- 关联过滤量、回表与缓存判断根因。
代码 / 命令
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;
常见误区
- 误区:只看 cost 数值高低。正解:需结合实际时间、rows 偏差与 Buffers 判断。
- 误区:EXPLAIN 不加 ANALYZE 也能看到实际行数。正解:无 ANALYZE 只有估算,实际值需执行计划。
追问
- 问:常见节点类型?答:Seq/Index/Bitmap Scan、Nested Loop/Hash/Merge Join、Sort/HashAggregate/GroupAggregate。
- 问:Rows Removed by Filter 高说明什么?答:扫描大量行才过滤出少量,访问路径或索引需优化。
05.3.2.2-006 统计信息不准导致选错计划怎么办?
频率 ★★★★★ | 难度 高级
一句话结论
估算 rows 与实际差几倍即统计偏差:先 ANALYZE 更新统计;倾斜列调高统计目标;多列相关性用扩展统计;检查列相关性;临时关闭某连接算法验证,不长期依赖开关。
概念解释
- 采样偏差:直方图/高频值未覆盖真实分布。
- 多列相关:假设列独立导致选择率误估。
- 扩展统计:建模列依赖、多列 distinct 与 MCV。
原理流程
- 对比估算与实际 rows 定位偏差节点。
- ANALYZE 复测计划。
- 仍偏差则调统计目标/建扩展统计。
代码 / 命令
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
CREATE STATISTICS st_city_status (dependencies, ndistinct, mcv)
ON city, status FROM orders;
ANALYZE orders;
常见误区
- 误区:统计偏差靠加索引解决。正解:先修统计与分布建模,否则新索引也可能被误判。
- 误区:rows 偏大就是结果集大。正解:需区分估算错误与实际结果集大。
追问
- 问:为什么 rows 会差上千倍?答:模型假设列独立,实际强相关/倾斜,选择率估算失真。
- 问:扩展统计解决什么?答:多列依赖、多列 distinct 组合与高频值组合。
05.3.2.2-007 统计信息与 ANALYZE 的关系?
频率 ★★★★☆ | 难度 进阶
一句话结论
ANALYZE 采样收集列唯一值、高频值、直方图与相关性写入 pg_statistic,供优化器估算选择率;autovacuum 变更超阈值自动 analyze,大表/倾斜列可单列调高统计目标。
概念解释
- pg_statistic/pg_stats:统计数据存储与视图。
- 采样:只读采样不锁表。
- 自动 analyze:随 autovacuum 阈值触发。
原理流程
- 数据变更超阈值触发采样。
- 分布信息写入统计目录。
- 优化器据此估算行数与成本。
代码 / 命令
ANALYZE orders;
SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename='orders';
常见误区
- 误区:ANALYZE 锁表阻塞写。正解:只读采样,不长时间阻塞。
- 误区:统计实时反映最新数据。正解:采样滞后,大批量变更后需手动 analyze。
追问
- 问:default_statistics_target 默认多少?答:默认 100(待确认以版本为准)。
- 问:ANALYZE 会改数据吗?答:只更新统计目录,不改业务数据。
05.3.2.2-008 预编译语句为什么会导致计划变差?
频率 ★★★★☆ | 难度 高级
一句话结论
prepared statement 前期用自定义计划,达到次数后可能切通用计划复用同一计划;参数分布差异大时通用计划不适合部分参数。可用 plan_cache_mode 强制自定义/通用计划,按参数特征拆分查询。
概念解释
- 自定义计划:按当前参数值生成。
- 通用计划:不绑定参数值,复用计划。
- 计划缓存:减少解析规划开销,但通用计划可能次优。
原理流程
- 预编译语句前几次按参数生成自定义计划。
- 评估后切换通用计划复用。
- 极端参数下通用计划偏差导致慢。
代码 / 命令
SET plan_cache_mode = force_custom_plan;
常见误区
- 误区:预编译一定更快。正解:规划省了,但次优通用计划执行可能更慢。
- 误区:通用计划对所有参数都优。正解:高/低选择性参数需要不同访问路径。
追问
- 问:通用计划好处?答:减少重复解析规划开销,适合参数分布均匀语句。
- 问:驱动层预编译要注意什么?答:确认缓存模式与参数化方式,必要时 DEALLOCATE 重建。
05.3.2.2-009 成本参数 seq/random_page_cost 如何调?
频率 ★★★☆☆ | 难度 高级
一句话结论
seq_page_cost 加权顺序读,random_page_cost 加权随机读;机械盘默认随机成本高,SSD 随机读快应下调 random_page_cost,避免优化器回避索引;调整基于硬件实测,不照搬模板。
概念解释
- 顺序读成本:连续页扫描权重。
- 随机读成本:离散页访问权重。
- 硬件差异:SSD 随机/顺序差距远小于机械盘。
原理流程
- 按存储介质评估随机/顺序读比值。
- 调整随机成本系数。
- 观测计划选择与实测耗时回归。
代码 / 命令
SHOW seq_page_cost;
SHOW random_page_cost;
常见误区
- 误区:random_page_cost 越低越好。正解:过低可能让优化器在该顺序扫时误选索引。
- 误区:参数全局一刀切。正解:不同实例硬件与负载不同,需分别基线。
追问
- 问:effective_cache_size 怎么设?答:按物理内存与 OS cache 预留估算,告知优化器缓存规模,非实际分配。
- 问:work_mem 是成本参数吗?答:它控制排序/哈希内存,影响节点是否落盘,见 05.3.2.3。
05.3.2.2-010 计划节点:Scan / Bitmap / Join / Aggregate / Sort?
频率 ★★★☆☆ | 难度 进阶
一句话结论
Scan 节点负责取数路径;Join 节点实现嵌套循环/哈希/归并;Aggregate 分组聚合;Sort 排序。逐层估算输出行数,上层节点成本依赖下层输出,底层偏差逐级放大。
概念解释
- Scan:Seq/Index/Bitmap 访问路径。
- Join:三类连接算法。
- Aggregate:HashAggregate/GroupAggregate。
- Sort:内存或外部归并排序。
原理流程
- 读计划树各节点类型与行数。
- 定位大成本/大偏差节点。
- 向上追溯其输入路径与估算依据。
代码 / 命令
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
常见误区
- 误区:只看最顶层节点成本。正解:需下钻到具体 Scan/Join 找根因。
- 误区:聚合节点慢就是聚合算法差。正解:常是输入行数过大或统计偏差导致。
追问
- 问:Nested Loop 节点 loops 高说明什么?答:外层行数多,内层反复执行,需内层索引或减外层。
- 问:Sort 节点落盘怎么看?答:Sort Method 显示 external merge Disk。
05.3.2.2-011 rows 估算偏差与直方图/相关性?
频率 ★★★☆☆ | 难度 高级
一句话结论
选择率由列唯一值、高频值、直方图与列相关性估算;等值靠 MCV/唯一值,范围靠直方图分布,索引扫描 IO 靠相关性;样本不足或强相关未建模即偏差。
概念解释
- MCV:高频值及出现频率。
- 直方图:列值区间分布。
- 相关性:列值与物理行顺序相关度。
原理流程
- 条件类型匹配对应统计信息。
- 估算选择率乘总行数得 rows。
- 偏差大说明统计未覆盖分布。
代码 / 命令
SELECT attname, n_distinct, most_common_vals, histogram_bounds, correlation
FROM pg_stats WHERE tablename='orders';
常见误区
- 误区:唯一值多就估算准。正解:倾斜分布与多列相关仍会误估。
- 误区:相关性只影响排序。正解:它也影响索引扫描随机读成本估算。
追问
- 问:范围条件估算靠什么?答:直方图区间频次,区间跨桶时线性估算。
- 问:倾斜列怎么改善?答:提高该列统计目标,收集更细直方图/MCV。
05.3.2.2-012 计划缓存与 prepared 自定义/通用计划?
频率 ★★☆☆☆ | 难度 高级
一句话结论
PG 对预编译语句在自定义计划与通用计划间权衡:自定义计划按参数定制,通用计划一次规划复用;缓存模式可配置;应用侧避免无限制预编译,参数差异大时拆分语句。
概念解释
- 计划权衡:前期自定义,评估后可能通用。
- 缓存模式:auto/force_custom/force_generic。
- 适用判断:参数分布均匀适合通用,差异大适合自定义。
原理流程
- 语句首次规划记录成本对比。
- 达到阈值后选计划策略。
- 参数分布变化时复核计划。
代码 / 命令
SHOW plan_cache_mode;
常见误区
- 误区:通用计划一定复用到底不变。正解:按成本权衡切换,策略受配置控制。
- 误区:应用预编译行为数据库无法感知。正解:驱动预编译对应服务端 prepared statement,可查计划与缓存。
追问
- 问:强制自定义计划有代价吗?答:每次规划开销增加,适合参数差异大的语句。
- 问:怎么复现计划问题?答:用相同参数分别 EXPLAIN 自定义与通用计划对比。
05.3.2.2-013 扩展统计信息解决什么?
频率 ★★★☆☆ | 难度 高级
一句话结论
扩展统计建模多列间依赖、多列 distinct 组合与多列 MCV,弥补单列统计假设独立的不足;对强相关多列条件查询,显著改善行数估算,需在 ANALYZE 后生效。
概念解释
- dependencies:列间函数依赖。
- ndistinct:多列组合唯一数。
- mcv:多列高频值组合。
原理流程
- 识别强相关多列条件。
- 创建扩展统计对象。
- ANALYZE 收集,优化器估算时引用。
代码 / 命令
CREATE STATISTICS st (dependencies, ndistinct, mcv)
ON city, status FROM orders;
ANALYZE orders;
常见误区
- 误区:扩展统计自动创建。正解:需显式定义并 analyze 才收集。
- 误区:扩展统计能修复缺索引。正解:它改善估算,不替代访问路径。
追问
- 问:哪些列适合建扩展统计?答:经常同时 WHERE、取值强相关的列。
- 问:扩展统计占用空间吗?答:存目录中,量小,按列组合评估。
05.3.2.2-014 并行查询何时该开/关?
频率 ★★★★☆ | 难度 高级
一句话结论
并行适合大表扫描、大聚合、大 JOIN 且 CPU 空闲;OLTP 小查询启动成本高于收益,IO 瓶颈或 CPU 饱和、高并发时应限制或关闭;每个 worker 独立 work_mem,内存占用需折算。
概念解释
- 并行 worker:扫描/聚合/连接并行执行。
- 启动成本:worker 协调与调度开销。
- 资源折算:每 worker 独立排序哈希内存。
原理流程
- 优化器按表大小、成本参数判断是否并行。
- 大查询分裂给多 worker。
- 汇聚节点合并结果。
代码 / 命令
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
min_parallel_table_scan_size = 8MB
常见误区
- 误区:并行 always 更快。正解:小查询/IO 瓶颈/CPU 争用下并行反而降吞吐。
- 误区:并行只占一份 work_mem。正解:每个 worker 独立分配,并发并行查询内存翻倍增长。
追问
- 问:OLTP 要开并行吗?答:通常限制 worker 数或关闭,避免小查询抢占资源。
- 问:并行参数怎么调?答:按 CPU 核数与大查询比例压测,结合 max_worker_processes。
05.3.2.2-015 与 MySQL EXPLAIN/优化器对照?
频率 ★★★☆☆ | 难度 进阶
一句话结论
两边都基于统计做代价优化:PG 输出节点树 + ANALYZE 实际计时/Buffers;MySQL EXPLAIN 行式 type/key/rows/Extra。成本系数、统计对象与计划形态不同,解读思路相通。
概念解释
- 计划形态:PG 节点树;MySQL 行式表。
- 统计对象:PG pg_stats 直方图/相关性;MySQL 采样基数/直方图。
- 实测能力:PG ANALYZE 出实际计时与缓冲。
原理流程
- 各自统计 → 估算选择率行数 → 选计划。
- 计划偏差先更新统计再评估索引。
- 对照时不混用指标名。
代码 / 命令
-- PG
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- MySQL
EXPLAIN SELECT ...;
常见误区
- 误区:rows 字段含义两边完全相同。正解:都是估算行数,但统计模型与来源不同。
- 误区:PG Seq Scan 对应 MySQL ALL。正解:概念相近,但成本系数与判定模型不同。
追问
- 问:MySQL 统计滞后怎么处理?答:ANALYZE TABLE 更新,见 05.2.2.2。
- 问:哪边计划实测信息更细?答:PG ANALYZE 提供实际时间/行数/loops/Buffers,便于定位。
05.3.2.2-016 计划异常排查步骤?
频率 ★★★★☆ | 难度 高级
一句话结论
慢 SQL 先 EXPLAIN ANALYZE 对比估算与实际行数/耗时;区分统计偏差、缺索引、JOIN 算法错、排序落盘、锁等待与膨胀;逐项验证改动后回归对比计划与指标。
概念解释
- 偏差分类:估算错 vs 路径错 vs 运行时等待。
- 运行时因素:锁等待、缓存未命中、膨胀。
- 回归验证:改动前后计划与耗时对比。
原理流程
- 采集实际计划、Buffers 与活动会话状态。
- 定位高成本节点与偏差根因。
- 改统计/索引/SQL/参数后复测。
代码 / 命令
EXPLAIN (ANALYZE, BUFFERS) <SQL>;
SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle';
常见误区
- 误区:计划异常直接加索引。正解:先确认是统计、连接算法还是运行时等待。
- 误区:估算好就不会慢。正解:缓存未命中、锁等待、膨胀都可能使实际慢于估算。
追问
- 问:怎么区分统计偏差与数据倾斜?答:偏差节点结合 pg_stats 分布与实际值,倾斜列补统计目标。
- 问:排查先做什么?答:复现并出实际计划,再看运行时等待与资源指标。
05.3.2.2-017 版本间成本默认值变化需注意什么?
频率 ★★☆☆☆ | 难度 高级
一句话结论
不同 PG 大版本成本系数默认值、并行与统计行为可能调整;升级前核对目标版本文档与 release note,升级后重建计划基线,不能照搬旧版本参数经验(待确认)。
概念解释
- 默认值演进:成本系数、并行阈值随版本调整。
- 行为变化:规划策略与统计口径可能不同。
- 基线重建:升级后重新观测计划与慢查询。
原理流程
- 升级前查目标版本成本/并行参数默认值。
- 复核现有配置是否需调整。
- 升级后对比计划与慢查询基线。
代码 / 命令
SHOW server_version;
常见误区
- 误区:参数默认值跨版本不变。正解:部分默认值随版本演进,需核对文档。
- 误区:升级后旧计划基线仍适用。正解:行为变化后应重新采集慢查询基线。
追问
- 问:怎么确认版本行为?答:查官方文档对应版本章节与 release note,标注待确认点。
- 问:升级后计划变差怎么办?答:对比成本参数默认值与统计状态,再调索引/统计。
05.3.2.2-018 计划稳定性与基线如何维护?
频率 ★★☆☆☆ | 难度 高级
一句话结论
核心 SQL 监控计划指纹与耗时,统计更新/数据演进可能引发计划漂移;通过稳定统计、合理索引、必要时固定计划策略维护稳定性;变更前后对比计划,异常计划纳入复盘。
概念解释
- 计划漂移:统计/数据变化后优化器切换路径。
- 计划指纹:语句模板与计划形态标识。
- 复盘回归:慢查询计划纳入监控对比。
原理流程
- 采集核心语句计划与耗时基线。
- 监控计划形态突变与耗时上升。
- 定位统计/数据/结构变化后修复。
代码 / 命令
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC;
常见误区
- 误区:固定计划能替代统计治理。正解:它是兜底,根因仍需修复。
- 误区:计划一次最优永久不变。正解:数据增长与分布变化后需复核。
追问
- 问:哪些 SQL 纳入基线?答:核心高频、曾漂移、对延迟敏感的语句。
- 问:通用计划变差怎么临时缓解?答:plan_cache_mode 强制自定义,再评估语句拆分。