索引与 EXPLAIN
用执行计划、真实耗时与缓冲区访问验证索引是否有效
先获取计划
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, customer_id, placed_at
FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC
LIMIT 20;EXPLAIN只展示估算，不执行语句。ANALYZE会真实执行并给出实际行数和耗时。对写语句使用时，应包在事务中并回滚。BUFFERS展示 shared/local/temp block 的命中与读取。- 重点比较
rows与actual rows、循环次数、最贵节点和是否发生磁盘排序。
ANALYZE 会执行
EXPLAIN ANALYZE DELETE ... 真的会删除。需要检查写语句时使用 BEGIN; EXPLAIN (ANALYZE, BUFFERS) ...; ROLLBACK;，并确认没有不可回滚的外部副作用。
为查询形状建索引
上面的过滤与排序可以使用：
CREATE INDEX CONCURRENTLY orders_customer_placed_idx
ON orders (customer_id, placed_at DESC)
INCLUDE (id);复合 B-tree 通常从最左列开始匹配。列顺序由实际谓词、范围条件与排序共同决定，不是简单地把“区分度最高”放最前。
INCLUDE 列不参与搜索顺序，但可能允许 index-only scan；是否真正只读索引还取决于可见性图。
常用索引类型
| 类型 | 适合 |
|---|---|
| B-tree | 等值、范围、排序；默认选择 |
| GIN | jsonb 包含、数组成员、全文检索 |
| GiST | 几何、范围、某些扩展运算符 |
| SP-GiST | trie、quad-tree、k-d tree 等可分区搜索结构 |
| BRIN | 物理顺序与值强相关的超大表，如按时间追加日志 |
| Hash | 仅等值；通常 B-tree 更通用 |
| Bloom extension | 多字段任意组合的等值过滤；lossy 且需要 recheck；自带 operator class 仅有 int4 与 text |
这些名称仍不足以决定索引是否可用：operator class 决定具体 operator 与数据类型。Table Access Method、HNSW/IVFFlat 和更完整的选择图见 索引与存储访问方法。
两种高价值索引
部分索引只覆盖相关行：
CREATE INDEX orders_unfinished_idx
ON orders (placed_at)
WHERE status IN ('pending', 'paid');表达式索引加速规范化查找：
CREATE UNIQUE INDEX customers_email_ci_idx
ON customers (lower(email));查询谓词需要与表达式或部分条件相匹配，优化器才能使用它们。
为什么没有走索引
- 表很小，顺序扫描更便宜。
- 查询要返回很大比例的行。
- 统计信息陈旧或列之间相关性未被描述。
- 对列套了与索引不匹配的函数或隐式转换。
- 复合索引的最左列不适用。
- 成本参数与真实存储特征不匹配。
先运行 ANALYZE orders; 并检查估算偏差，不要第一反应关闭顺序扫描。
生产创建与清理
CREATE INDEX CONCURRENTLY 减少对写入的阻塞，但耗时更长、不能放在事务块内，失败时可能留下 invalid index。使用：
SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index
WHERE indrelid = 'orders'::regclass;每个索引都会增加写放大、WAL、缓存压力和 vacuum 工作量。定期结合 pg_stat_user_indexes 与业务周期审查未使用索引。
Last updated on