一、索引是解决慢查询的手段，但不是没有代价的手段
给 PostgreSQL 加索引，往往是数据库优化里最立竿见影的操作——一条 CREATE INDEX 命令下去，原本几秒钟的查询可能眨眼间变成几毫秒。但别忘了，索引不是免费的午餐。在写入密集的场景下，代价会成倍放大：每次 INSERT、UPDATE、DELETE，数据库不光要改数据，还得同步更新所有相关索引。索引越多，写入越慢；索引设计不合理，查询也不见得变快，甚至可能拖后腿。
负责任的索引策略，本质上是在“查询性能”和“写入性能”之间找平衡点。这个平衡没有放之四海而皆准的公式，全靠对具体业务的理解。举个例子：电商平台的订单表，查询和写入都密集，索引设计必须克制；日志系统的日志表，写入量大但查询简单，索引可以少而精；数据分析系统的宽表，查询复杂但数据不要求实时写入，索引可以大胆一些。
但无论什么业务，索引策略的第一步永远是“用数据说话”：哪几条查询最慢？它们访问了哪些表和列？过滤条件是什么？排序条件是什么？没有 pg_stat_statements 的数据支撑，任何索引建议都只是猜测。
二、索引类型选择：B-Tree 不是唯一答案，但通常是第一个答案
flowchart TD A[查询性能问题] --> B{查询模式?} B -- 精确匹配/范围查询 --> C[B-Tree 索引] B -- 全文搜索 --> D[GIN 索引 + tsvector] B -- 地理空间查询 --> E[GiST / SP-GiST 索引] B -- 数组包含关系 --> F[GIN 索引] B -- 模糊前缀匹配 --> G[GiST 索引 + pg_trgm] B -- 布隆过滤 --> H[Bloom 索引] C --> I[适用大部分场景] D --> J[适用搜索场景] E --> K[适用 PostGIS] F --> L[适用标签/分类] G --> M[适用模糊搜索]B-Tree 索引是 PostgreSQL 的默认选择，能应付等值查询、范围查询、ORDER BY 和 GROUP BY。如果你不确定该用什么索引，从 B-Tree 开始准没错。但它不是万能的：LIKE '%keyword%' 这种前后模糊的查询，B-Tree 无能为力；数组字段的“包含”查询，它也不行；全文搜索更不是它的菜。
GIN（Generalized Inverted Index）索引是 PostgreSQL 里第二常用的类型，特别适合多值类型，比如 jsonb、数组、tsvector。假设一个文章表有 tags 数组字段，要查询“包含所有这些标签的文章”，GIN 索引就能高效胜任。但代价是写入成本比 B-Tree 高，更新操作会导致大量随机 I/O。
再来说 jsonb 字段：PostgreSQL 支持两种索引。GIN 索引可以加速“包含”查询（@>、?、?&），而 B-Tree 索引只能加速字段的整体比较。如果你的查询是“找到所有 meta 字段里包含 {"premium": true} 的记录”，那 GIN 索引才是正确选择。
三、复合索引设计：列顺序决定索引能否被使用
复合索引（多列索引）的列顺序，是索引设计里最容易被忽略、但影响最大的细节。PostgreSQL 的 B-Tree 索引支持从左到右的前缀查询，但不能跳过前面的列。比如一个 (user_id, created_at) 的复合索引，可以加速 WHERE user_id = 1 的查询，也能加速 WHERE user_id = 1 AND created_at > '2024-01-01'，但无法加速 WHERE created_at > '2024-01-01'——因为 created_at 不是最左前缀。
-- 假设有索引：CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);-- 能用到索引SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC;-- 能用到索引（user_id 是前缀）SELECT * FROM orders WHERE user_id = 1 AND created_at > '2024-01-01';-- 用不到索引（跳过了 user_id）SELECT * FROM orders WHERE created_at > '2024-01-01';-- 能用到索引，但效率较低（索引扫描后过滤）SELECT * FROM orders WHERE user_id IN (1, 2, 3) AND created_at > '2024-01-01';
列顺序的选择原则其实很清晰：把 = 条件的列放前面，范围 条件的列放后面。因为范围条件（>、<、BETWEEN、LIKE 'prefix%'）会终止索引的连续使用，范围条件后面的列就无法再利用索引了。所以如果 user_id 是等值查询、created_at 是范围查询，(user_id, created_at) 顺序正确；反过来就错了。
另一个要考虑的因素是“选择性”：选择性高的列（唯一值多的列）放前面，能让索引在早期过滤掉更多行。但这条规则有时会和“等值条件放前面”冲突，具体怎么权衡，还得看实际的查询模式。
四、生产环境索引管理：创建、监控与清理
在生产环境的数据库上创建索引，最危险的操作就是“阻塞写入”。PostgreSQL 的 CREATE INDEX 会锁表，阻止写入，直到索引创建完成。对于大表，这可能意味着几分钟甚至几小时的写入不可用。生产环境必须使用 CREATE INDEX CONCURRENTLY（并发创建索引），它不会阻塞写入，但创建时间更长，而且有可能失败——失败后会留下一个无效索引，需要手动清理。
索引创建之后，还需要持续监控两个指标：索引是否被使用，以及它的维护成本。pg_stat_user_indexes 视图提供了每个索引的扫描次数和使用情况。如果一个索引从来没有被扫描过，那它就在白白消耗写入性能和存储空间。
-- 找出从未被使用的索引SELECT schemaname, tablename, indexname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) as index_sizeFROM pg_stat_user_indexesJOIN pg_index ON pg_stat_user_indexes.indexrelid = pg_index.indexrelidWHERE idx_scan = 0 AND NOT indisprimary AND NOT indisunique;
不过，idx_scan = 0 不一定意味着索引没用——它可能是为那些不常执行的查询准备的，或者是为了灾难恢复场景留的后手。删除索引前，最好先记下它的定义，在低流量时段观察一段时间再决定。
另一个容易被忽略的问题是“索引膨胀”。PostgreSQL 的 MVCC 机制会导致索引产生死页，随着时间推移，索引文件会变得比实际需要的大，查询性能也会下降。REINDEX 或 REINDEX CONCURRENTLY 可以重建索引，回收空间并提升性能。对于大表，可以考虑使用 pg_repack 工具，它能在不持锁的情况下重建表和索引。
五、总结
PostgreSQL 索引策略远不止“加索引让查询变快”这么简单。索引类型的选择、复合索引的列顺序、并发创建索引的生产安全、以及持续监控和清理无用索引——每一个环节都需要结合具体业务的查询模式和写入负载来做决策。没有万能的索引方案，只有不断测量、调整、再测量的工程循环。
