优化器的心思：扫描方式与连接算法
同一条查询，优化器有时走索引有时全表扫，有时 Hash Join 有时 Nested Loop。你要能看懂它的选择逻辑。
学 · 45 min
01Seq Scan / Index Scan / Index Only Scan / Bitmap Heap Scan 的触发条件
场景四种扫描方式像四档变速箱，优化器按「预计行数」换挡。
Seq Scan：全表顺序读，返回行多时的本命；Index Scan：索引定位 + 回表，点查和极少行数；Index Only Scan：列全在索引（D38）；Bitmap Heap Scan：先在索引里攒一张「命中页位图」，再按页顺序批量回表--把零散回表的随机 IO 拼成顺序 IO，返回行数中等时的最优解。
-- 放宽条件看换挡过程 explain select count(*) from orders where user_id = '...'; -- Index explain select count(*) from orders where created_at > current_date - 1; -- Bitmap explain select count(*) from orders where created_at > current_date - 365; -- Seq易错Bitmap 不等于「一次一页只取一行」：位图去重后整页整页地读，_LOS 也能被它利用。
02为什么选择率高时全表扫反而更快
场景老板：「建了索引为什么还不走？」--因为优化器比你算得明白。
取 30% 的行：走索引 = 30% 行的随机回表 IO；全表扫 = 100% 数据的顺序 IO 一遍读完。顺序读的吞吐是随机读的几十倍，全表扫赢。索引只在「取少」时是捷径，「取多」时是绕路。
explain (analyze, buffers) select * from orders where total_amount > 0; -- 命中 95% 行 -- 计划选 Seq Scan，buffers 里 read 少而整齐 -- 强行 enable_seqscan=off 再跑一次：通常更慢易错「加索引 = 快」是错觉；优化器放弃索引常常是正确决策，别用 enable 开关硬拗。
03Nested Loop / Hash Join / Merge Join 的原理、复杂度与前提
场景三种 join 算法，面试必考的三张牌。
① Nested Loop：外层每行去内层找一次，内层有索引时每次 O(logM)，总 O(N·logM)--外层小 + 内层有索引时无敌；② Hash Join：小表建哈希表、大表扫一遍逐行探测，O(N+M)，不要求有序、不要求索引--大表无序连接的本命；③ Merge Join：两边按连接键有序（有索引或先排序）才能归并，O(N+M)--大结果集 + 已有序时最快。
explain select * from users u join orders o on o.user_id = u.id; -- 1 万用户 × 100 万订单：Hash Join（users 建哈希表） explain select * from orders o join orders o2 on o.id = o2.id where o.created_at >= current_date - 1 and o2.paid_at is not null; -- 两边都能走主键索引有序输入时，可能出 Merge Join易错「NL 一定慢 / HJ 一定快」都是错的：NL 的前提是内层有索引，没有索引的 NL 是 O(N·M) 灾难。
04work_mem 不足时 Hash Join 会落盘
场景同一个 Hash Join 昨天快今天慢，计划一模一样--差别在内存。
哈希表装不进
work_mem（默认 4MB）时，PG 把数据分批写盘（计划里Batches: N，N > 1 就是落盘了），慢一个量级。会话级调大再跑：set work_mem = '256MB'只影响当前连接。排序节点同理（external merge Disk）。explain (analyze, buffers) select count(*) from orders o join order_items i on i.order_id = o.id; -- Hash Join ... Batches: 24 <- 落盘了 set work_mem = '256MB'; explain (analyze, buffers) select count(*) from orders o join order_items i on i.order_id = o.id; -- Batches: 1，耗时骤降易错work_mem 是每个排序/哈希节点各一份，全局调大是危险操作（连接数 × 并发节点数 × work_mem 会爆内存），只按会话调。
练 · 60 min
- 逐步放宽 WHERE 的选择率，观察计划从 Index Scan -> Bitmap -> Seq Scan 的切换点
参考答案
切换点不固定（取决于统计和成本模型），看的是趋势：命中少 -> Index Scan；中等 -> Bitmap Heap Scan（位图攒页、按页顺序批量回表）；多 -> Seq Scan。orders 散布在近两年，-365 大约命中一半。
create index on orders (created_at); -- 先有单列索引才看得到换挡 explain select count(*) from orders where created_at >= current_date - 1; -- Index Scan explain select count(*) from orders where created_at >= current_date - 30; -- Bitmap explain select count(*) from orders where created_at >= current_date - 365; -- Bitmap / Seq explain select count(*) from orders where created_at >= current_date - 730; -- Seq Scan - 用
SET enable_hashjoin = off强制换算法，对比耗时参考答案
强制换挡后通常慢一个量级--这恰好证明优化器原来的选择是对的。enable 开关只配做实验，永远别写进生产配置。
explain (analyze) select count(*) from orders o join order_items i on i.order_id = o.id; set enable_hashjoin = off; explain (analyze) select count(*) from orders o join order_items i on i.order_id = o.id; -- 换成 Merge Join / Nested Loop，对比 Execution Time reset enable_hashjoin; - 小表连大表，观察优化器选谁做驱动表（hash 表建在哪边）
参考答案
计划里 Hash 节点挂在 users 一侧：小表（1 万行）建哈希表，大表（orders 100 万）做探测端扫一遍。「哈希表建在小表上」是 Hash Join 的铁律：建表 O(M)、探测 O(N)。
explain (analyze) select count(*) from orders o join users u on u.user_id = u.id; - 把
work_mem调到 64kB，观察 Hash Join 落盘（计划里出现 Batches > 1）参考答案
work_mem 默认才 4MB，哈希表 / 排序装不下就分批写盘。它是会话级参数，全局调大会乘以连接数爆内存，只在需要的会话里 set。
set work_mem = '64kB'; explain (analyze, buffers) select count(*) from orders o join order_items i on i.order_id = o.id; -- Hash Join ... Batches: N（N > 1 即落盘），慢一个量级 set work_mem = '256MB'; explain (analyze, buffers) select count(*) from orders o join order_items i on i.order_id = o.id; -- Batches: 1，耗时骤降 reset work_mem; - 整理一张「三种 join 算法 × 适用条件 × 复杂度」对比表
参考答案
表格进 notes.md，骨架五行：Nested Loop--O(N·logM)--内层有索引、外层行数少（外层小 + 内层点查无敌）；Hash Join--O(N+M)--等值连接、不要求有序不要索引、内存装得下（大表无序连接的本命）；Merge Join--O(N+M)--两边按连接键有序（索引或先排序）。各补一行风险：NL 内层没索引变 O(N·M) 灾难、HJ 落盘慢一个量级、MJ 要为排序预付成本。