大多数PostgreSQL性能问题归结为少数几个索引错误。以下是关于什么重要什么不重要的实用指南。
PostgreSQL何时使用索引（以及何时不使用）
查询规划器通过估计顺序扫描还是索引扫描更便宜来决定是否使用索引。关键因素：选择性（在90%行共享相同值的列上的索引——比如大多数行为true的布尔”is_active”列——几乎没有好处）、表大小（在小表上，顺序扫描比索引查找更快；大概10,000行是索引开始提供可靠好处的临界点），以及查询结构（索引用于WHERE子句、JOIN条件和ORDER BY；如果列被包裹在函数中则不使用）。EXPLAIN ANALYZE命令：对于了解规划器实际做什么是必不可少的。`EXPLAIN (ANALYZE, BUFFERS) SELECT …`显示执行计划、估计vs实际行数、成本估计和缓冲区命中vs磁盘读取。从下往上读计划（最内层节点首先执行）。
索引类型及何时使用每种
B-树（默认）：适用于相等比较（=）、范围比较（<、>、BETWEEN）和排序。用于绝大多数索引。哈希索引：仅相等（无范围扫描），有时对于纯相等查找比B-树更快，但不太常见，在较旧的PostgreSQL版本中不被复制。GIN（广义倒排索引）：用于在值内搜索——全文搜索（tsvector）、JSONB包含（@>）和数组重叠（&&）。当你在文本文档或JSON blob内搜索时的正确选择。GiST：用于几何数据（PostGIS、多边形内点查询）、范围类型和全文搜索（类似GIN但GIN通常对文本搜索更快）。BRIN（块范围索引）：极小，适用于自然排序数据，如时间戳，你想缩小要扫描的磁盘块。错误选择：在大文本列上使用B-树索引用于LIKE ‘%keyword%’查询——开头的通配符防止索引使用；对于这种模式使用带pg_trgm的GIN。
复合索引和列顺序
（a, b）上的复合索引可用于仅过滤a的查询，或a AND b，但不能用于仅过滤b的查询。复合索引中的列顺序很重要：把最有选择性的列放在第一位（最能缩小结果集的列）。覆盖索引：如果查询中的所有列都包含在索引中，PostgreSQL可以仅从索引满足查询而无需访问表（仅索引扫描）——对于读密集工作负载速度大幅提升。示例：`CREATE INDEX idx_orders_covering ON orders (user_id, created_at) INCLUDE (status, total)` ——选择status和total、过滤user_id和created_at的查询永远不需要触碰主表。部分索引：只为你实际查询的行建立索引。`CREATE INDEX idx_pending ON orders (created_at) WHERE status = ‘pending’` ——索引更小，维护和查询更快，但只对带WHERE status = ‘pending’的查询有用。对于大型表，其中95%的查询只针对5%的行（例如，活跃行、待处理项目），部分索引通常是可用的最大性能收益。
索引维护和监控
索引会减慢写入速度（每个INSERT、UPDATE、DELETE都必须更新所有索引）。未使用的索引是纯开销。查找未使用索引的查询：`SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY schemaname, tablename;`。膨胀：在许多更新后，索引页面积累死元组——`REINDEX CONCURRENTLY`或`pg_repack`可以在不锁定表的情况下回收空间。关键监控查询：`SELECT tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan DESC;` ——显示哪些索引被使用以及它们正在做多少工作。pg_stat_statements（扩展，建议在生产中使用）：记录每个不同查询的执行统计信息，包括平均执行时间、总调用次数和行数——找到需要解决的慢查询的最直接方式。
