场景 seed.sql 的 W6 段把库从 5 万冲到百万--当年是天书，今天是套路。

讲解 公式：insert into t select ... from generate_series(...)，配上 random() 造分布、setseed() 固定随机、日期锚定 current_date 平移出「过去两年」。百万行一条语句几十秒，学习成本最低的压测手段。

insert into orders(id, user_id, status, total_amount, created_at)
select gen_random_uuid(),
       (select id from users offset floor(random()*10000) limit 1),
       (array[1,2,2,2,3])[1 + floor(random()*5)],
       round((random()*990 + 10)::numeric, 2),
       current_date - (random()*730)::int
from generate_series(1, 1000000);
analyze orders;   -- 灌完必做，下一条讲为什么

易错 灌完不 ANALYZE，统计信息还停留在 5 万行时代，优化器按旧地图走路（D41 详述）。

场景 第一个索引建完，查询从 400ms 变 1ms--凭什么快 400 倍？

讲解 磁盘按页（PG 8KB）读取，一次 IO 拿一页。B+ 树把每个节点做成一整页、一个节点上百个分叉，所以矮胖：100 万行只要 3~4 层 = 3~4 次 IO。二叉树高瘦（20 层 = 20 次 IO）；哈希表虽是 O(1)，但不支持范围和排序。树高 = IO 次数，这就是快的全部本质。

-- 对比：按主键点查
select * from orders where user_id = '某个uuid';      -- 无索引：全表扫
create index on orders(user_id);
select * from orders where user_id = '某个uuid';      -- Index Scan：3~4 次 IO

易错 B+ 树 vs B 树的考点：数据全在叶子层、叶子间有链表--范围扫描时顺藤摸瓜，不用回到上层。

场景 面试官问：「PG 和 MySQL 的索引有什么区别？」--今天这个答案是加分位。

讲解 MySQL InnoDB：表就是主键索引（聚簇），数据按主键序物理存放，二级索引叶子存主键值，查完还要再走一遍主键树。PG：表是无序堆（heap），所有索引（含主键）一律是二级索引，叶子存 (键, ctid 物理地址)。后果：PG 的主键查询也要「回堆」，但插入没有聚簇键的排序负担。

select ctid, id from orders limit 3;   -- ctid = (页号, 行偏移)，行的物理地址
-- 索引叶子里存的就是它：查到键 -> 拿 ctid -> 回堆取整行

易错 PG 可以用 CLUSTER 命令按某索引重排物理顺序，但那是一次性操作、不是 MySQL 那种「聚簇索引」。

场景 「回表」这个词总在性能文章里出现，在 PG 里有确切对应物。

讲解 Index Scan 两步走：索引里查到键、拿到 ctid；再拿 ctid 去堆里取整行。第二步就是回表。查询需要的列全在索引里时，理论上可以不回表--Index Only Scan（D38 展开）。回表是随机 IO，取的行一多，索引反而不如全表扫。

explain select * from orders where user_id = '...';
-- Index Scan using ... -> Heap Fetches 发生在扫描节点内部
explain select user_id from orders where user_id = '...';
-- 只取索引里有的列：可能变成 Index Only Scan

易错 「建了索引反而更慢」的大多数场景，就是回表的随机 IO 输给了全表扫的顺序 IO（D40 展开）。

场景 想把每列都建上索引--先看账单。

讲解 ① 空间：索引本身占磁盘（能到表的一半大小）；② 写入放大：每次 insert / update 都要同步维护所有相关索引；③ 维护：膨胀、失效索引清理、优化器选择空间变大。索引是「读加速、读付费」的交易，不是白捡的。

select relname, pg_size_pretty(pg_relation_size(relname::regclass)) as 大小
from pg_class
where relkind in ('r', 'i') and relname like 'orders%'
order by pg_relation_size(relname::regclass) desc;

易错 写多读少的表，索引是纯负担；没人查询的索引是纯负债（pg_stat_user_indexes 的 idx_scan = 0 可以揪出来）。

场景 索引类型六兄弟，先认脸再认专长。

讲解 B-tree（默认）：等值 + 范围 + 排序，90% 场景；Hash：只等值、不支持范围（场景少）；GIN：倒排索引，管「一个字段里含什么」（数组、jsonb、全文）；GiST：几何、范围类型、近邻搜索；BRIN：块区间摘要，超大时序表；SP-GiST：前缀/四叉树类结构（IP、电话前缀）。选型看数据形态，不是看名字酷不酷。

create index on orders (user_id);                      -- B-tree（默认）
create index on orders using hash (user_id);           -- 只能等值
create index on products using gist (price_range);     -- GiST：范围/几何

易错 PG 的 Hash 索引在 PG 10 前不记 WAL（崩溃会丢），老资料直接说「别用 Hash」；PG 10+ 可用但场景依然窄。

场景 商品加了个 jsonb 属性列，老板要按属性筛选--B-tree 对 jsonb 束手无策。

讲解 GIN = 倒排索引：「值 -> 哪些行含有它」的映射表。jsonb 的 @> 包含查询、tsvector 全文检索、数组包含，全靠它。没有 GIN，这些查询全是全表扫。

-- jsonb：给扩展属性建 GIN
create index on products using gin (attrs jsonb_path_ops);
select * from products where attrs @> '{"color": "红"}';

-- 全文：先转 tsvector 再 GIN
create index on products using gin (to_tsvector('simple', name));
select * from products
where to_tsvector('simple', name) @@ to_tsquery('simple', '手机 & 配件');

易错 GIN 建得慢、更新代价高（倒排表维护复杂）--写多读少的表慎用；jsonb_path_ops 比默认 operator class 更小更快但只支持 @>。

场景 500 万行登录日志按时间查--B-tree 索引几百 MB，有没有更省的？

讲解 BRIN 只记录每个物理块区间的 min/max。数据按时间追加（物理有序）时，「login_at > 昨天」这种条件能直接跳过 99% 的块。索引小到 KB 级，是「时序大表 + 追加写」的完美搭档。

create index on user_logins using brin (login_at);
create index on user_logins (login_at);   -- 对照组：B-tree

select relname, pg_size_pretty(pg_relation_size(relname::regclass)) as 大小
from pg_class where relname like 'user_logins%';   -- BRIN 小几个数量级

易错 BRIN 的前提是物理有序：数据乱序写入时 min/max 全重叠，BRIN 完全失效。先确认写入模式再选它。

场景 「待支付」订单只占 1%，查询却总在全表里捞；搜索要不区分大小写。

讲解 部分索引：where 条件 只索引满足条件的行--索引小、写入省、命中率还高（专给高频查询用）。表达式索引：索引的不是列而是表达式的结果，如 lower(email)；查询里必须写一模一样的表达式才命中。MySQL 没有部分索引（8.0 才有函数索引），这两个是 PG 的招牌优势。

-- 部分索引：只索引待支付订单（后台高频轮询它）
create index idx_pending on orders (created_at)
where status = 1;
select * from orders where status = 1 and created_at > now() - interval '1 day';

-- 表达式索引：忽略大小写的登录
create index idx_email_lower on users (lower(email));
select * from users where lower(email) = 'a@b.com';   -- 命中

易错 表达式索引要求查询表达式一字不差：索引 lower(email)，查询写 upper(email) 或 email 不带函数，全都不命中。

场景 建了 (user_id, created_at) 复合索引，只按 created_at 查却不走索引--为什么？

讲解 复合索引的排序规则：先按第一列排，第一列相同时再按第二列排。像电话簿「先姓后名」：知道姓能二分定位；只知道名（=只用第二列），整本电话簿还是得翻一遍。所以 (user_id, created_at) 支持「只 user_id」「user_id + created_at」，不支持只 created_at。

create index on orders (user_id, created_at);

-- 命中
explain select * from orders where user_id = '...';
explain select * from orders where user_id = '...' and created_at >= current_date - 30;
-- 不命中（最左前缀断了）
explain select * from orders where created_at >= current_date - 30;

易错 「(a, b) 能不能服务 where b」是索引面试的必考送分/送命题，先讲清排序原理再给结论。

场景 同样两列，先建 (user_id, created_at) 还是 (created_at, user_id)？

讲解 ① 等值在前、范围在后：范围条件命中后，索引里它后面的列就没法继续精确定位了。(user_id = ? and created_at > ?) 用 (user_id, created_at)：user_id 等值定位后，created_at 在索引里还是有序的，范围顺藤摸瓜。反过来范围在前，user_id 就乱序了。② 多个等值列之间，高选择性（区分度大的）优先。

-- 报表标配：某用户的某时间段
create index on orders (user_id, created_at);   -- 对
-- create index on orders (created_at, user_id); -- created_at 的范围会挡住 user_id 的等值定位

易错 范围列一旦「挡路」，它后面的列在索引里就只剩过滤作用（Filter），不再参与定位（Index Cond）--看 EXPLAIN 的这两个字段能立刻诊断。

场景 「这列建索引值不值」要拿数字说话。

讲解 选择性 = 不同值数量 / 总行数，越接近 1 区分度越大、索引越值。status 列选择性 ≈ 0.000003（100 万行 3 个值），索引意义小；email 接近 1，必建。PG 里看 pg_stats.n_distinct（正数 = 绝对值、负数 = 占总行数的比例）。

select attname, n_distinct, null_frac
from pg_stats
where tablename = 'orders';
-- user_id 的 n_distinct ≈ -0.01（约 1% 行数即 1 万个不同用户）
-- status 的 n_distinct = 3

易错 「低选择性列单独建索引」= 白占空间：优化器预判命中太多行，照样全表扫。

场景 列表页只要 id、金额、时间--这些列全在索引里的话，连堆都不用回。

讲解 查询涉及的列全部包含在索引里时，扫描器理论上可以只读索引--Index Only Scan，省掉回表的随机 IO。include (列) 把额外列塞进索引叶子（不参与排序），是官方支持的「为 IOS 补货」写法。

create index on orders (user_id, created_at) include (total_amount);

explain (analyze, buffers)
select user_id, created_at, total_amount
from orders
where user_id = '...' and created_at >= current_date - 30;
-- 计划出现 Index Only Scan，Heap Fetches = 0

易错 include 会加大索引、拖慢写入--只为偶尔一条查询加它不值；高频接口查询才值得。

场景 明明列都在索引里，计划却时不时回堆几万次（Heap Fetches > 0）--为什么？

讲解 索引里没有行的可见性信息（MVCC 的 xmin/xmax 在堆上），IOS 必须确认「这行对当前事务可见」。靠 visibility map：每个堆页一个「全可见」位，VM 置位的页才敢直接用索引。VM 由 vacuum 维护--表刚大量更新过，VM 退化，IOS 又偷偷回表了。

explain (analyze, buffers) select user_id from orders where user_id = '...';
-- Heap Fetches: 12345   <- 回堆了，VM 不干净
vacuum orders;
explain (analyze, buffers) select user_id from orders where user_id = '...';
-- Heap Fetches: 0       <- VM 全可见，真·只读索引

易错 「IOS 时快时慢」的老大难，先想 VM 和 vacuum；autovacuum 跟不上就调参（D41 讲）。

场景 本周主力工具，先把每个开关是干什么的记牢。

讲解 裸 explain：只估算不执行；analyze：真执行并报告实际耗时行数（注意 UPDATE / DELETE 会真的执行）；buffers：显示读了多少页、命中缓存多少（IO 真相）；verbose：显示每个节点输出哪些列；format json：机器可读，程序分析用。

explain (analyze, buffers)
select * from orders where user_id = '...';

-- 对 UPDATE 想安全地 analyze：包在事务里回滚
begin;
explain (analyze, buffers) update orders set status = 3 where status = 1;
rollback;

易错 explain analyze 一条 DELETE 把数据真删了，是新手经典事故--DML 先包 rollback 事务。

场景 计划第一行的这串数字，多数人看了三年没看懂。

讲解 cost = 启动成本..总成本：启动成本 = 吐出第一行之前要干的活（排序节点几乎全部成本都在启动段）；总成本 = 整个节点干完的账。单位是成本单位不是秒（一次顺序页读 = 1.0 基准），只能用于计划内部比较，不能换算耗时。

explain select * from orders order by created_at limit 10;
-- Sort 节点：cost=....大数字....（启动贵：排完才有第一行）
-- Limit 把下游成本打了折：只需要排够 10 行的量

易错 「cost 100 万 = 多少毫秒」没有答案；真实耗时看 analyze 的 actual time。

场景 优化器是个做决策的会计师，账算错了路就选错。

讲解 估算 rows 来自统计信息（pg_stats）。偏差 10 倍以上就是警报：优化器按错误行数选 join 算法、选扫描方式，整棵计划树跟着错。常见原因：统计过期（大更新后没 analyze）、列间相关性强（单列统计不懂「上海的用户爱买手机」这种相关）。

explain (analyze)
select * from orders o join users u on u.id = o.user_id
where o.status = 2 and u.city = '上海';
-- 对比每个节点的 rows=估算 与 actual rows=实际

易错 排错性能问题的第一步永远是找「估算和实际差最远」的节点，它常常就是病根。

场景 一个 0.2ms 的节点，总耗时却是 2 秒--数字没错，你没乘 loops。

讲解 嵌套在循环里的节点（典型：Nested Loop 的内侧）会执行多次，actual time 是每次的平均值，真实总耗时 = actual time × loops。看计划不乘 loops，就像看单价不乘数量。

explain (analyze)
select * from users u
join orders o on o.user_id = u.id
where u.city = '上海';
-- Index Scan on orders ... (actual time=0.2..0.3 rows=100 loops=10000)
-- 0.3ms × 10000 loops = 3 秒 <- 瓶颈在这，不在顶上那个节点

易错 loops=1 时才可以直接读 actual time；大数字节点先检查它是不是被循环了。

场景 一份计划几十行，从哪读起？

讲解 数据流是自下而上的：缩进最深的节点最先执行，把行喂给上层。读法：先找最内层（叶子扫描节点，看走没走索引），再往外看连接怎么组织，顶上是排序 / 聚合。找瓶颈的口诀：看 analyze 里 actual time 最大的一枝，顺藤摸到它的叶子。

explain (analyze)
select u.city, count(*), sum(o.total_amount)
from orders o
join users u on u.id = o.user_id
where o.created_at >= current_date - 7
group by u.city;
-- 读的顺序：最深的 Seq/Index Scan -> Hash -> Hash Join -> GroupAggregate

易错 顶层节点的成本包含所有子节点（累计值），别把父子的时间相加重复计算。

场景 四种扫描方式像四档变速箱，优化器按「预计行数」换挡。

讲解 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 也能被它利用。

场景 老板：「建了索引为什么还不走？」--因为优化器比你算得明白。

讲解 取 30% 的行：走索引 = 30% 行的随机回表 IO；全表扫 = 100% 数据的顺序 IO 一遍读完。顺序读的吞吐是随机读的几十倍，全表扫赢。索引只在「取少」时是捷径，「取多」时是绕路。

explain (analyze, buffers)
select * from orders where total_amount > 0;   -- 命中 95% 行
-- 计划选 Seq Scan，buffers 里 read 少而整齐
-- 强行 enable_seqscan=off 再跑一次：通常更慢

易错 「加索引 = 快」是错觉；优化器放弃索引常常是正确决策，别用 enable 开关硬拗。

场景 三种 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) 灾难。

场景 同一个 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 会爆内存），只按会话调。

场景 索引明明在，查询就是不走--先过一遍六种「装死」写法。

讲解 ① 函数包列：where date(created_at) = ...（D2 埋的伏笔今天收）；② 隐式类型转换：varchar 列 = 整数值，列被套 cast；③ 前导 %：like '%手机'，B-tree 没法定位（后缀通配可以反转+表达式索引）；④ OR 连接：OR 的两半不是每列都有索引时退化；⑤ 低选择性：命中行太多，优化器主动放弃（D40 讲过，是正确决策）；⑥ 排序方向 / collation 不匹配：索引的排序和 ORDER BY 要的不一致。

-- ①② 的修法对照
explain select * from orders where date(created_at) = current_date - 1;   -- 失效
explain select * from orders
where created_at >= current_date - 1 and created_at < current_date;       -- 命中

易错 六类里只有①②④是「写法病」要治；⑤ 是优化器正确判断，治了反而慢。

场景 优化器的一切估算都来自一本「户口本」--谁在维护它？

讲解 PG 对每列维护统计（pg_stats：直方图、高频值、n_distinct、null 占比），analyze 命令就是采样刷新这本户口本（默认采样 3 万行）。优化器的 rows 估算、索引选择、join 算法全按它算。大批量写入后统计就是旧的，优化器拿着旧地图走新路。

-- 灌数后统计还是旧的（reltuples 停留在 5 万）
select relname, reltuples::bigint from pg_class where relname = 'orders';

analyze orders;   -- 刷新，reltuples 立即接近百万
select relname, reltuples::bigint from pg_class where relname = 'orders';

易错 「昨天好好的今天突然慢了」的排查第一步：想想昨晚是不是跑过大批量导入 / 更新。

场景 没人手动 analyze，统计怎么平时还算新鲜？--autovacuum 在后台干活。

讲解 autovacuum 三件事：清死元组（vacuum）、刷新 VM、更新统计（analyze）。触发的默认规则是「变更行数超过表大小的 10%」（analyze 部分），大表上 10% 是个很大的数字--刚导入完的窗口期统计就是旧的。可以按表调小：alter table t set (autovacuum_analyze_scale_factor = 0.02)。

-- 看各表 autovacuum 的最近活动
select relname, last_autoanalyze, last_autovacuum, n_live_tup, n_dead_tup
from pg_stat_user_tables
order by last_autoanalyze nulls first;

易错 「大批量导入后手动 analyze」不是玄学仪式，是把 autovacuum 的下一次触发提前到「现在」。

场景 「到底哪条 SQL 慢」不能靠猜--让数据库自己记账。

讲解 pg_stat_statements 扩展记录每条（归一化后）语句的调用次数、总耗时、平均耗时、返回行数。慢查询治理的第一入口：先看 TOP 10 总耗时（total_exec_time），再看平均耗时（mean_exec_time）和调用次数的组合。

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'，然后重启
create extension pg_stat_statements;

select calls,
       round(total_exec_time::numeric, 0) as 总ms,
       round(mean_exec_time::numeric, 1)  as 平均ms,
       rows,
       left(query, 70) as query
from pg_stat_statements
order by total_exec_time desc
limit 10;

易错 需要 preload + 重启才生效；归一化把字面量换成 $1，同一个模板的慢查询才会被聚合统计。