索引类型与选型

索引是 PostgreSQL 性能优化最重要的工具——也是最容易被滥用的工具。加错索引可能让插入/更新变慢，而没有索引则让查询慢到不可用。这一节系统梳理 PostgreSQL 的索引类型和选型逻辑。

PostgreSQL 的主要索引类型

选型原则： - 不知道选什么 → 先用 B-tree（90% 的场景都适合） - 搜索 JSONB / 数组 → GIN - 地理位置 / PostGIS → GiST - 时间序列大表（每分区数亿行）→ BRIN

B-tree 索引：日常主力

-- 单列索引
CREATE INDEX idx_users_email ON users (email);
-- 复合索引（列顺序很重要，见下文）
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- 降序索引
CREATE INDEX idx_orders_created_desc ON orders (created_at DESC);
-- 查看索引是否被使用
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

复合索引的列顺序规则：

查询条件：WHERE user_id = 123 AND status = 'paid'
最优索引：CREATE INDEX ON orders (user_id, status)
原则：等值查询的列放前面，范围/排序的列放后面
示例：
WHERE user_id = 123 AND created_at > '2024-01-01'
→ CREATE INDEX ON orders (user_id, created_at)
user_id 是等值，放前；created_at 是范围，放后

覆盖索引（Index-Only Scan）

当查询的所有列都在索引中时，PostgreSQL 可以完全跳过堆表访问：

-- 查询只需要 user_id 和 status，以及 total_amount
SELECT user_id, status, total_amount
FROM orders
WHERE user_id = 123;
-- 创建包含 total_amount 的覆盖索引（INCLUDE 语法，PostgreSQL 11+）
CREATE INDEX idx_orders_user_covering
ON orders (user_id, status)
INCLUDE (total_amount, created_at);
-- EXPLAIN 应该显示 "Index Only Scan"
EXPLAIN SELECT user_id, status, total_amount FROM orders WHERE user_id = 123;

部分索引（Partial Index）

只对满足条件的行建索引，让索引更小、更高效：

-- 只对活跃用户建索引（假设 90% 的查询只找活跃用户）
CREATE INDEX idx_users_active_email
ON users (email)
WHERE status = 'active';
-- 只对未处理的订单建索引（已完成的订单很少被查询）
CREATE INDEX idx_orders_pending
ON orders (created_at, user_id)
WHERE status IN ('pending', 'processing');
-- 条件唯一索引：只对未删除的记录保证 email 唯一
CREATE UNIQUE INDEX uq_active_email
ON users (email)
WHERE deleted_at IS NULL;

GIN 索引：数组和 JSONB 必备

-- 为 tags 数组创建 GIN 索引
CREATE INDEX idx_products_tags ON products USING GIN (tags);
-- GIN 加速的查询
SELECT * FROM products WHERE tags @> ARRAY['electronics'];  -- 包含
SELECT * FROM products WHERE tags && ARRAY['electronics', 'laptop'];  -- 有交集
-- 为 JSONB 字段创建 GIN 索引
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
-- 也可以用 jsonb_path_ops（更小、只支持 @> 操作符）
CREATE INDEX idx_products_metadata_ops
ON products USING GIN (metadata jsonb_path_ops);

BRIN 索引：超大时间序列表

-- 适合物理有序的超大表（如事件日志、传感器数据）
-- 每 128 个物理块存一个 min/max 值，极其紧凑
CREATE INDEX idx_events_created_brin
ON events USING BRIN (created_at)
WITH (pages_per_range = 128);
-- BRIN 在时间范围查询上远快于全表扫描，但比 B-tree 精度低
-- 适合：每天插入数百万行的日志表，查询主要是按时间范围
-- 不适合：随机分布的数据（BRIN min/max 范围会很宽，过滤性差）

索引选型速查表

查询场景                              推荐索引
──────────────────────────────────────────────────────
等值查询（WHERE id = 123）           B-tree（默认）
范围查询（WHERE age BETWEEN 18 AND 30）B-tree
模糊前缀（LIKE 'John%'）             B-tree（前缀匹配有效，后缀无效）
精确相等（大量重复值）               Hash（但 B-tree 通常也 OK）
数组包含（WHERE tags @> ARRAY[...]） GIN
JSONB 查询（WHERE data @> '{"key":"val"}'）GIN
全文搜索（WHERE to_tsvector @@ query）GIN
地理范围（PostGIS ST_Within）        GiST
时间序列大表（10 亿行+按日期范围）   BRIN

查看和管理现有索引

-- 查看某表的所有索引
\d+ orders
-- 查看索引大小
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = 'orders'
ORDER BY pg_relation_size(indexname::regclass) DESC;
-- 查找从未被使用的索引（维护成本浪费）
SELECT
schemaname,
tablename,
indexname,
idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY tablename;
-- 删除未使用的索引（谨慎！确认后再删）
DROP INDEX CONCURRENTLY idx_unused_index;  -- CONCURRENTLY 不锁表

本节记录清单

• [ ] 了解 B-tree、GIN、GiST、BRIN 的适用场景
• [ ] 审查现有项目：哪些 WHERE 条件没有索引支持？
• [ ] 用 pg_stat_user_indexes 检查是否有从未使用的索引
• [ ] 为 JSONB / 数组字段添加 GIN 索引

下一节：EXPLAIN ANALYZE 输出解读——有了索引，怎么知道它有没有生效？EXPLAIN ANALYZE 是查询优化最重要的工具。