查询规划与执行

同一条 SQL，PG 往往有不止一种方式执行：全表顺序读、走某个索引、把几个索引的结果用位图合起来、两表连接时用嵌套循环还是哈希……规划器（planner / optimizer）的工作，就是在这些方式里挑一条预计最便宜的，交给执行器去跑。它不靠固定规则（「有索引就走索引」那种），而是给每个候选计划算一个代价数字、比大小。这一章把这个过程拆开：代价怎么算（§cost-model）、估算的原料从哪来（§statistics）、有哪几种扫描和连接可选（§scan-types / §bitmap / §joins）、以及怎么用 EXPLAIN 把它的决策和真实执行对照起来（§explain）。

§1代价模型：把每个计划折算成一个数字

规划器枚举候选计划，给每个估一个抽象 cost，选最小的那个。

一个计划节点的代价，由两类成本加权求和：I/O 成本（要读多少个 8KB Page）和 CPU 成本（要处理多少行、算多少表达式）。粗略写成：

cost ≈ 顺序读页数 × seq_page_cost      (默认 1.0)
     + 随机读页数 × random_page_cost   (默认 4.0)
     + 处理行数   × cpu_tuple_cost      (默认 0.01)
     + 索引项数   × cpu_index_tuple_cost(默认 0.005)
     + 表达式次数 × cpu_operator_cost   (默认 0.0025)

这几个权重都是 postgresql.conf 里的 planner cost constant，可在会话级用 SET 调整。它们的绝对值不重要，重要的是彼此的比值——比值决定规划器把「读盘」看得比「算 CPU」重多少，以及把「随机读」看得比「顺序读」重多少。

底层机制（比文档深一层）。 random_page_cost : seq_page_cost = 4 : 1 这个默认比值是机械盘时代的遗产：机械硬盘随机寻道要转磁头、等盘片转到位，比顺序读一连串相邻扇区慢得多，4 倍是当年测出来的经验值。这个比值有一个直接后果：随机读一个 Page 在规划器眼里相当于顺序读 4 个 Page，所以「走索引随机回堆取少量行」要和「顺序扫一大片堆」掰手腕时，规划器会把索引路径的每次回堆都按 4 倍计价。SSD 上随机和顺序的差距远没有 4 倍那么大，因此 SSD 部署常把 random_page_cost 调到约 1.1——这一调，索引路径的回堆代价骤降，规划器就更敢用 index scan 而非退回 seq scan。这是生产环境最常见也最有效的规划器调参之一，本章末尾的预测题会再回到它。

§2统计信息：行数估算的原料

ANALYZE 把每列的分布写进 pg_statistic，规划器据此估每个节点输出多少行。

ANALYZE 对表做随机采样，把每列的分布摘要写进系统目录 pg_statistic（可读视图是 pg_stats）。autovacuum 守护进程在表的改动量超过阈值时会自动触发 ANALYZE（与 第 3 章 §autovacuum 同一套触发机制），无需手动盯着。每列收集的关键摘要有四样：

估行数的逻辑大致是：等值条件先查 MCV，命中就用它的精确频率；没命中就用「(1 − MCV 总频率) ÷ 非 MCV 的不同值数」摊平估算。范围条件查直方图，看条件覆盖了几个等频桶。correlation 接近 1 表示该列的值在堆里几乎按序物理排列（如自增主键），此时索引范围扫描的回堆近似顺序读、代价低；接近 0 则回堆是满表乱跳的随机读、代价高。

底层机制（比文档深一层）。 行数估错是慢查询的头号根因之一，且错误会沿计划树向上放大：底层一个扫描节点把「命中 2 行」估成「命中 20000 行」，上层的连接节点就会基于这个错误数字选错连接算法（详见 §joins、§explain）。三种典型的统计失真：其一，统计过期——大批量导入后还没跑 ANALYZE，规划器拿着旧画像估新数据；其二，采样精度不足——默认 default_statistics_target=100（每列约采 300×100 行、保留 100 个 MCV 与 100 个直方图桶），对值分布极不均匀的大表未必够，调高它能让 MCV 和直方图更细，代价是 ANALYZE 更慢、pg_statistic 更大；其三，多列相关性丢失——规划器默认假设各列独立，于是 WHERE city='上海' AND area_code='021' 会把两个条件的选择性直接相乘，而现实中这俩高度相关，相乘后估值会严重偏低。这第三种要靠扩展统计（CREATE STATISTICS）显式声明列间的函数依赖或联合分布来纠正。识别「估算为何失真」，就是 §explain 里读 estimated vs actual 偏差的全部意义。

§3四种扫描：按选择性分段

seq scan、index scan、index-only scan、bitmap heap scan——命中比例不同，最优的扫描方式不同。

四种扫描，从「命中极少」到「命中大半」排开：

• seq scan（顺序扫描）：从头到尾顺序读整个堆文件，对每行就地判断条件。读的是连续 Page，是顺序 I/O（每页按 seq_page_cost 计价，便宜）。当条件命中比例高、几乎要碰大部分行时，它最优——反正都要读，顺序读比满表随机跳快。
• index scan（索引扫描）：先在索引里定位到满足条件的索引项，再顺着每一项的 ctid 回堆取整行。高选择性（命中极少行）时最优：只回堆少数几行，省掉了扫全表。代价里每次回堆按随机 I/O（random_page_cost）计，所以命中行一多就不划算。
• index-only scan（仅索引扫描）：查询要的列全在索引里，且目标行所在 Page 在可见性图里标记为 all-visible 时，直接从索引返回、免回堆。这把 index scan 最贵的「随机回堆」整段省掉，机制细节见 第 4 章 §index-only-scan。
• bitmap heap scan（位图堆扫描）：中等选择性的甜区，机制见 §bitmap。它先扫索引建一张按 Page 排序的位图，再按物理顺序成批访问堆，把随机 I/O 摊成顺序 I/O。

底层机制（比文档深一层）。 「为什么加了索引却走 seq scan」几乎总能用这张谱回答：当条件命中比例高（例如 WHERE active = true 而九成行都 active），走索引意味着对大半张表逐行随机回堆，按 random_page_cost=4 计价后，总代价反而高于「顺序读一遍全表」。规划器算出 seq scan 更便宜，于是无视那个索引——这是正确决策，不是 bug。反过来，命中极少时它自然会选 index scan。中间地带交给 bitmap，下一节专讲。

§4Bitmap heap scan：把随机 I/O 摊成顺序 I/O

先扫索引建一张按 Page 排序的 ctid 位图，再按物理顺序一次性访问堆，每个 Page 只碰一次。

它分两个阶段，对应 EXPLAIN 里成对出现的两个节点 Bitmap Index Scan 和 Bitmap Heap Scan：

1. 建位图（Bitmap Index Scan）：扫索引，把所有命中项的 ctid 收集进一张内存位图。位图按 Page 号组织——本质是「哪些 Page 里有命中行」的一张图，天然按物理顺序排好。
2. 按图取堆（Bitmap Heap Scan）：按 Page 号从小到大遍历位图，每个有命中的 Page 只访问一次、取出其中所有命中行。访问顺序与堆的物理布局一致——随机 I/O 就此摊成了近似顺序 I/O。

底层机制（比文档深一层）。 三件事让它卡在中段最优。其一，排序消除了随机性：index scan 按索引顺序（逻辑序）回堆，物理上满表乱跳；bitmap 先按 Page 号排好再取，访问顺序贴合磁盘布局，I/O 接近顺序。其二，去重消除了重复访问：一个 Page 里多行命中，index scan 会多次拉取该 Page，bitmap 因为按页组织，每页只访问一次。其三，可组合：多个条件各走一个索引，各建一张位图，再做 BitmapAnd / BitmapOr 合并成一张，最后只取一次堆——这让 PG 能同时利用多个单列索引，而不必为每个查询组合都建复合索引。代价是建位图要占内存：命中行太多、位图大到超过 work_mem 时，PG 会把位图降级为「按 Page 粗粒度」（lossy，只记得「这页有命中」而非「这页的哪几行」），回堆时该页要逐行重判条件——这也是为什么命中比例再高就不如直接 seq scan 了，谱的右端由此收束。

§5三种 join：按表形态选算法

nested loop、hash join、merge join——按表大小、有无序、有无索引、work_mem 取一种。

三种算法，各自的甜区：

• nested loop join（嵌套循环）：外表每取一行，就去内表找匹配。外表小、内表在连接键上有索引时最优——外表行数少，每行用内表索引精确定位，总工作量 ≈ 外表行数 × 一次索引探查。外表一大、或内表无索引退化成逐行全扫，工作量爆炸。
• hash join（哈希连接）：先把较小的一侧（build 端）按连接键建一张内存哈希表，再扫另一侧（probe 端）逐行探哈希表。大表等值连接、数据无序、且 build 端能放进 work_mem 时最优。只支持等值连接（=），不要求任何顺序。build 端放不下 work_mem 时分批落盘（batched），代价上升。
• merge join（归并连接）：要求两边都已按连接键有序（顺序可来自索引扫描，或一个显式 Sort 节点），然后像拉链一样线性归并。两表都大、又恰好都能有序提供时高效——它是线性扫一遍，不像 hash 要建表、也不像 nested loop 要重复探查。代价主要在「让两边有序」上。

底层机制（比文档深一层）。 三种算法的代价曲线随「外表行数」分叉，这解释了为什么行数估算精度对 join 这么致命。nested loop 的代价约等于 外表行数 × 单次内表探查代价——是外表行数的线性函数，斜率就是内表那次探查的代价。外表只有几行时斜率乘出来很小，所以它在「外表小」时碾压其他两种；可一旦规划器把外表行数估成 2、实际却是 20000，这条线性代价被放大一万倍，一个本该毫秒级的 nested loop 跑成几分钟。hash join 有一笔固定的「建哈希表」启动成本，但之后每行探查是 O(1)，总代价对行数近似线性且斜率极小——所以行数大时它稳。merge join 的主成本在排序（若需要），归并阶段是线性扫一遍。规划器选谁，本质是看这三条代价曲线在「估出来的外表行数」那一点谁最低。结论落到诊断上：join 慢，先去 §explain 比 nested loop 节点的 estimated rows 和 actual rows——偏差大就是它被错选并放大了。

§6读 EXPLAIN：把估算和实际对照起来

EXPLAIN (ANALYZE, BUFFERS) 给出一棵计划树，核心诊断是比 estimated rows 和 actual rows。

计划是一棵节点树，缩进表示父子，自底向上执行：最内层的扫描节点先出数据，往上喂给连接、排序、聚合节点。每个节点带几组数字，分两批来源：

• 规划器的估算（不加 ANALYZE 也有）：cost=启动代价..总代价（启动代价是「吐出第一行前」要花的，总代价是「全部吐完」的累计，单位是 §1 那个抽象量）；rows=估算输出行数；width=每行估算字节宽度。
• 实际执行（加 ANALYZE 才有，会真的跑一遍查询）：actual time=启动..结束（毫秒）；actual rows=实际输出行数；loops=该节点被执行了几次（nested loop 的内层会等于外表行数）。
• 缓冲区（加 BUFFERS）：shared hit=从 shared_buffers 缓存命中的 Page 数、read=从磁盘实读的 Page 数——命中多说明数据多在内存，read 多说明在打盘。

下面是一条两表 JOIN 的 EXPLAIN (ANALYZE, BUFFERS) 真实形态输出（未在本机执行，数字用于演示该看哪些字段）：

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, u.name
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

                                    QUERY PLAN
-------------------------------------------------------------------------------------
 Hash Join  (cost=33.50..1894.20 rows=4820 width=40)
            (actual time=0.412..18.633 rows=4790 loops=1)
   Hash Cond: (o.user_id = u.id)
   Buffers: shared hit=812 read=140
   ->  Seq Scan on orders o  (cost=0.00..1721.00 rows=4820 width=8)
                             (actual time=0.020..14.880 rows=4790 loops=1)
         Filter: (status = 'paid')
         Rows Removed by Filter: 70210
         Buffers: shared hit=700 read=140
   ->  Hash  (cost=21.00..21.00 rows=1000 width=36)
            (actual time=0.340..0.341 rows=1000 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 72kB
         ->  Index Scan using users_pkey on users u
                 (cost=0.29..21.00 rows=1000 width=36)
                 (actual time=0.012..0.180 rows=1000 loops=1)
 Planning Time: 0.230 ms
 Execution Time: 18.920 ms
              <- 未在本机执行

逐行解读，把每个数字映射回前几节：

• Hash Join 顶节点树根是 Hash Join（§joins 的哈希连接）。cost=33.50..1894.20：启动代价 33.50（要先把 users 哈希进内存才能出第一行），总代价 1894.20。rows=4820 估、actual rows=4790 实——两者贴近，说明这一层估得准。
• Seq Scan on orders左子节点：对 orders 走 seq scan，Filter: status='paid' 就地过滤。Rows Removed by Filter: 70210 说明扫了约 7.5 万行、只留下 4790——命中比例低，但 orders 上若无 status 索引，seq scan 是唯一选择。Buffers: shared hit=700 read=140：700 页命中缓存、140 页打了盘。
• Hash → Index Scan on users右子树先 Index Scan using users_pkey 取出 users 全部 1000 行，再由 Hash 节点建哈希表。Batches: 1 表示一批装下、没落盘；Memory Usage: 72kB 远小于 work_mem，所以 hash join 走的是内存路径。
• Planning vs Execution Time底部 Planning Time（规划耗时）与 Execution Time（执行耗时）分列。诊断时不要只盯顶层 Execution Time，要顺着树找 estimated 和 actual 偏差最大的那个节点。

上面是一个「估得准」的健康计划。下面这个相反——一个 estimated 严重低估的节点，看它如何把上层带进坑：

 Nested Loop  (cost=0.29..845.10 rows=1 width=40)
              (actual time=0.030..9123.400 rows=50000 loops=1)
   ->  Seq Scan on events e  (cost=0.00..210.00 rows=1 width=8)
                            (actual time=0.015..12.300 rows=50000 loops=1)
         Filter: ((kind = 'click') AND (region = 'east'))
   ->  Index Scan using users_pkey on users u
           (cost=0.29..0.63 rows=1 width=36)
           (actual time=0.001..0.180 rows=1 loops=50000)
                                                  <- 未在本机执行

关键在第一个扫描节点：rows=1（估）对 actual rows=50000（实）——两个相关条件 kind='click' AND region='east' 被规划器按独立假设（§2）相乘，选择性估得离谱地低，于是它以为只会出 1 行。基于「外表只有 1 行」，规划器选了 nested loop：对外表那「1 行」去 users 索引探一次，看起来极便宜。可外表实际有 50000 行，于是内层 Index Scan 的 loops=50000——那次索引探查被放大成五万次，actual time 飙到 9 秒。同样这条查询，若估对了 50000 行，规划器会改选 hash join，一次哈希探表搞定，毫秒级完成。这就是「行数估错 → 错选 nested loop → 被放大」的完整链条，也是 §5 那句「nested loop 在行数估错时最危险」的实证。