PostgreSQL 索引与优化器

索引不是“建了就快”，优化器也不是“看到索引就一定用”。PostgreSQL 的索引学习要围绕一个问题展开：

一条 SQL 到底要读多少页、读哪些 tuple、是否需要排序、是否需要回表检查可见性，优化器为什么认为某个计划成本最低。

学习目标

学完这一页，你要能说清楚：

1. B-tree、GIN、GiST、BRIN、Hash 分别解决什么问题。
2. 为什么 PostgreSQL 默认索引叫 B-tree，和 MySQL 常说的 B+Tree 有什么表达差异。
3. Index Scan、Index Only Scan、Bitmap Heap Scan、Seq Scan 分别是什么意思。
4. 为什么有索引也可能不用，为什么用了索引也可能慢。
5. EXPLAIN (ANALYZE, BUFFERS) 里每个关键信息从哪里来。
6. 商业项目里如何给列表页、详情页、JSONB 查询、历史流水表设计索引。

索引到底解决什么问题

表里数据最终放在 heap page 中。没有索引时，数据库只能从很多页里逐行判断条件。

flowchart TD
    A["没有索引"] --> B["读取大量 heap page"]
    B --> C["逐个 tuple 判断 where 条件"]
    C --> D["过滤出目标行"]
    D --> E["如果需要 order by 再排序"]

索引相当于额外维护一份“按某种 key 组织的定位结构”。

flowchart TD
    A["where owner_id = 1001"] --> B["访问 owner_id 索引"]
    B --> C["定位满足条件的索引项"]
    C --> D["根据 TID 找 heap tuple"]
    D --> E["做 MVCC 可见性判断"]
    E --> F["返回结果"]

代价也很明确：

收益	代价
减少扫描页数	插入、更新、删除要维护索引
支持排序和范围查询	索引占磁盘和缓存
支持唯一约束	索引太多会拖慢写入
支持 JSONB、全文等特殊查询	错误索引会误导优化器或没有收益

所以索引设计不是“字段都建”，而是“围绕高频 SQL 的过滤、排序、返回列、数据分布去建”。

常见索引类型

索引	适合场景	不适合场景
B-tree	等值、范围、排序、唯一约束、前缀匹配	JSONB 包含、数组包含、全文检索
Hash	单列等值查询	范围、排序、多列组合
GIN	JSONB、数组、全文检索，一个字段里有多个可搜索元素	高频更新大字段、普通范围排序
GiST	空间、范围、相似性、PostGIS	普通等值查询通常不优先
SP-GiST	特定空间分区、前缀树类场景	通用业务列表
BRIN	超大表，字段值和物理写入顺序强相关，例如时间序列表	随机分布、高精度点查

为什么 PostgreSQL 叫 B-tree

PostgreSQL 官方默认索引类型叫 B-tree。很多数据库教材或 MySQL 场景里会强调 B+Tree。工程实践里不要把重点放在名词抬杠上，应该看数据库索引是否具备下面这些性质：

flowchart TD
    A["数据库页式索引"] --> B["多路分叉，高扇出"]
    B --> C["树高低，减少随机 IO"]
    A --> D["key 有序"]
    D --> E["支持范围扫描"]
    D --> F["支持 order by"]
    A --> G["叶子项能定位数据行"]
    G --> H["PostgreSQL 通过 TID 找 heap tuple"]

PostgreSQL B-tree 索引项通常保存 key 和指向 heap tuple 的 TID。查询命中索引后，还要根据 TID 去 heap 取行，并做 MVCC 可见性判断。

B-tree 联合索引怎么设计

医疗资产列表页：

select id, asset_no, asset_name, status
from biz.medical_asset
where hospital_id = 1001
  and status = 'USED'
order by created_at desc
limit 20;

推荐索引：

create index idx_asset_hospital_status_created
on biz.medical_asset(hospital_id, status, created_at desc);

为什么这个顺序合理：

1. hospital_id 是等值过滤，先定位医院范围。
2. status 也是等值过滤，继续缩小结果。
3. created_at desc 用来满足排序和 limit。
4. 如果把 created_at 放第一，数据库要先按全表时间顺序找，再过滤医院和状态，命中范围会大很多。

不合理索引示例：

create index idx_bad_asset_created_hospital
on biz.medical_asset(created_at desc, hospital_id);

它对“某医院某状态列表”不够友好，因为第一列不是主要等值过滤条件。

GIN 和 JSONB

JSONB 适合保存低频、变化多、但仍需要查询的扩展属性。核心高频字段不要全部塞进 JSONB。

create table biz.medical_asset (
  id bigserial primary key,
  asset_no varchar(64) not null,
  hospital_id bigint not null,
  status varchar(20) not null,
  attrs jsonb not null default '{}'
);

create index idx_asset_attrs_gin
on biz.medical_asset using gin(attrs);

查询：

select id, asset_no
from biz.medical_asset
where attrs @> '{"deviceType":"CT"}';

为什么 GIN 适合：

flowchart TD
    A["JSONB 字段"] --> B["拆成多个可检索 key/value 元素"]
    B --> C["GIN 记录元素到行的映射"]
    C --> D["查询包含条件时快速找候选行"]
    D --> E["再回表确认完整条件和可见性"]

常见坑：

坑	后果	建议
高频字段全放 JSONB	约束弱，统计和索引困难	核心字段列化
JSONB 很大且频繁更新	tuple 和索引维护成本高	拆表或减少更新范围
建了 GIN 但查询表达式不匹配	索引用不上	统一查询写法并看计划

BRIN 适合超大时间表

BRIN 不像 B-tree 那样精确保存每个 key，它保存一段物理页范围的摘要信息。适合“数据按时间不断追加”的场景。

create table biz.asset_event (
  id bigserial primary key,
  asset_no varchar(64) not null,
  event_time timestamp not null,
  event_type varchar(32) not null,
  payload jsonb not null
);

create index idx_asset_event_time_brin
on biz.asset_event using brin(event_time);

适合查询：

select count(*)
from biz.asset_event
where event_time >= now() - interval '1 day';

BRIN 为什么小：

flowchart TD
    A["连续 heap page 范围"] --> B["记录 min/max 摘要"]
    B --> C["查询时间范围"]
    C --> D["排除明显不相关的页范围"]
    D --> E["扫描候选页再过滤"]

如果数据按时间随机写入，BRIN 摘要无法有效排除页范围，效果会很差。

一条 SQL 的计划怎么生成

flowchart TD
    A["SQL 文本"] --> B["Parser 解析语法"]
    B --> C["Rewriter 重写"]
    C --> D["Planner 枚举访问路径"]
    D --> E["读取统计信息"]
    E --> F["估算 rows 和 cost"]
    F --> G["选择成本最低计划"]
    G --> H["Executor 执行"]
    H --> I["返回实际耗时和行数"]

优化器不会真实执行所有方案再选最快，它根据统计信息估算：

信息	来源	用途
表行数	pg_class、统计信息	判断扫描规模
列基数	ANALYZE 采样统计	估算等值过滤后剩多少
直方图	统计信息	估算范围条件
常见值 MCV	统计信息	处理数据倾斜
相关性 correlation	统计信息	判断索引扫描和物理顺序关系

更新统计信息：

analyze biz.medical_asset;

如果统计信息过期，优化器可能把“实际 50 万行”估算成“50 行”，然后选出错误的 Nested Loop 或索引扫描计划。

EXPLAIN ANALYZE 怎么看

推荐排查格式：

explain (analyze, buffers, verbose)
select id, asset_no, asset_name, status
from biz.medical_asset
where hospital_id = 1001
  and status = 'USED'
order by created_at desc
limit 20;

重点字段：

字段	含义	怎么用
cost	优化器估算成本，不是毫秒	看计划选择依据
rows	估算行数	和实际行数对比
actual time	实际启动和结束耗时	找耗时节点
actual rows	实际返回行数	判断估算偏差
loops	节点执行次数	Nested Loop 内层可能被反复执行
Buffers: shared hit	共享缓存命中页	判断逻辑读压力
Buffers: read	从磁盘读页	判断 IO 压力
Heap Fetches	Index Only Scan 回 heap 次数	判断可见性地图效果

执行计划节点：

节点	说明	常见问题
Seq Scan	顺序扫描 heap	大表高频查询慢
Index Scan	走索引再访问 heap	返回行太多时仍慢
Index Only Scan	尽量只读索引	受 Visibility Map 影响
Bitmap Index Scan	先从索引生成位图	多条件或返回较多行常见
Bitmap Heap Scan	按位图访问 heap page	需要 recheck 条件
Sort	排序	数据大可能占内存或落盘
Nested Loop	外层驱动内层	外层行数大时灾难
Hash Join	构建哈希表连接	内存不足可能慢

为什么 Index Only Scan 不一定只读索引

PostgreSQL 要保证 MVCC 可见性。索引项不一定包含足够信息判断 heap tuple 对当前快照是否可见。

flowchart TD
    A["Index Only Scan"] --> B["字段都在索引里"]
    B --> C{"heap page 是否 all-visible"}
    C -- "是" --> D["直接用索引返回"]
    C -- "否" --> E["访问 heap 检查可见性"]
    E --> F["Heap Fetches 增加"]

Visibility Map 由 VACUUM 等过程维护。如果表频繁更新、长事务阻止 VACUUM、dead tuple 很多，Index Only Scan 可能仍然大量回 heap。

有索引为什么还是慢

现象	原因	处理
走了索引但慢	返回行太多，随机回表多	调整索引、减少返回、覆盖更多列或接受顺序扫描
不走索引	过滤性差、统计信息认为扫表更便宜	看行数和 Buffers，不要强行迷信索引
估算和实际差很多	统计信息过期或数据倾斜	ANALYZE、提高统计目标、改 SQL
排序慢	索引无法满足排序	联合索引包含排序列
JSONB 慢	GIN 未命中或字段过大	检查操作符、拆核心字段
Index Only Scan 回表多	Visibility Map 不充分	查 VACUUM、长事务和膨胀

商业项目索引设计清单

以医疗数据采集与资产平台为例：

页面/场景	SQL 特点	索引建议
资产详情	asset_no = ?	唯一 B-tree
医院资产列表	hospital_id + status + created_at desc	联合 B-tree
设备属性筛选	attrs @> ...	JSONB GIN，核心字段列化
事件流水按时间查	event_time between ...	分区 + BRIN/B-tree
采集任务状态列表	tenant_id + status + next_time	联合 B-tree
报表统计	大范围扫描和聚合	分区、物化视图、离线数仓

可运行 Demo

create schema if not exists biz;

drop table if exists biz.medical_asset;

create table biz.medical_asset (
  id bigserial primary key,
  asset_no varchar(64) not null,
  hospital_id bigint not null,
  status varchar(20) not null,
  asset_name varchar(128) not null,
  attrs jsonb not null default '{}',
  created_at timestamp not null default now(),
  constraint uk_asset_no unique(asset_no)
);

create index idx_asset_hospital_status_created
on biz.medical_asset(hospital_id, status, created_at desc);

create index idx_asset_attrs_gin
on biz.medical_asset using gin(attrs);

insert into biz.medical_asset(asset_no, hospital_id, status, asset_name, attrs)
select 'A' || g,
       1000 + (g % 10),
       case when g % 3 = 0 then 'USED' else 'IDLE' end,
       'asset-' || g,
       jsonb_build_object('deviceType', case when g % 2 = 0 then 'CT' else 'MRI' end)
from generate_series(1, 10000) g;

analyze biz.medical_asset;

explain (analyze, buffers)
select id, asset_no, asset_name
from biz.medical_asset
where hospital_id = 1001
  and status = 'USED'
order by created_at desc
limit 20;

explain (analyze, buffers)
select id, asset_no
from biz.medical_asset
where attrs @> '{"deviceType":"CT"}'
limit 20;

观察重点：

1. 列表页是否使用联合索引。
2. JSONB 查询是否使用 GIN。
3. 估算行数和实际行数是否接近。
4. Buffers 是否明显减少。

面试标准回答

PostgreSQL 默认索引类型是 B-tree，适合等值、范围、排序和唯一约束；GIN 适合 JSONB、数组、全文检索；GiST 适合空间、范围和相似性查询；BRIN 适合按时间追加的超大表。优化器会根据统计信息估算不同计划的成本，而不是看到索引就一定用。排查慢 SQL 要看 EXPLAIN (ANALYZE, BUFFERS)，关注扫描方式、估算行数和实际行数差异、Buffers、Sort、Join、Heap Fetches。Index Only Scan 也可能访问 heap，因为 PostgreSQL 要做 MVCC 可见性判断，Visibility Map 和 VACUUM 会影响它的效果。

追问：

1. 为什么有索引还会走 Seq Scan？
2. GIN 为什么适合 JSONB？
3. Index Only Scan 为什么还有 Heap Fetches？
4. 统计信息不准会导致什么问题？

关联知识点

知识点	说明
PostgreSQL 从零到生产级掌握	学习主线
核心全过程原理	SQL 执行、MVCC、WAL、VACUUM 全链路
事务与锁	快照、锁、长事务
MVCC、VACUUM 与 WAL 原理	Index Only Scan 和可见性相关背景
性能排查	慢 SQL、统计信息、膨胀排查