PostgreSQL是最强大的开源关系型数据库，也是大多数新应用程序的默认选择，但需要理解其内部原理才能避免常见的性能陷阱。以下是导致大多数PostgreSQL性能问题的模式以及如何修复它们。
理解EXPLAIN ANALYZE
最重要的性能工具：`EXPLAIN ANALYZE SELECT …;`显示实际查询执行计划及时间。需要注意的关键事项：顺序扫描vs索引扫描：在你通过特定值过滤的大表上进行顺序扫描（读取每一行）是最常见的慢速原因。修复：确保你过滤的列上存在索引。索引扫描vs仅索引扫描：仅索引扫描更高效——这意味着PostgreSQL可以完全从索引满足查询，而无需读取堆（表数据）。当你选择的所有列都包含在索引中时发生这种情况。嵌套循环vs哈希连接vs合并连接：对于连接，PostgreSQL选择连接算法。嵌套循环对小结果集有效；哈希连接对较大的连接是典型的；合并连接需要排序输入。如果规划器在大表连接上选择嵌套循环，它可能使用不正确的统计数据——运行ANALYZE。行估计：规划器估计每个步骤将产生多少行。如果估计严重错误（例如，估计1行，实际处理50,000行），计划将是错误的。修复：运行VACUUM ANALYZE更新统计数据。成本数字：EXPLAIN以抽象单位显示成本。计划的总成本是你想要最小化的，但EXPLAIN ANALYZE的实际时间更具可操作性。
索引类型及何时使用
B-tree（默认）：适用于等值（`=`）、范围（`>`、`<`、`BETWEEN`）和`ORDER BY`。默认索引类型——大多数目的使用它。Hash：比B-tree更快用于仅等值查找，但在PostgreSQL 10之前没有WAL日志记录且不那么常用。GIN（广义倒排索引）：用于全文搜索、JSONB包含（`@>`）和数组操作。如果你经常在JSONB文档或文本列中搜索，GIN是正确的索引类型。GiST（广义搜索树）：用于几何类型、范围类型和全文搜索。BRIN（块范围索引）：用于自然排序的大表（时间序列、仅追加日志）。非常小的索引，对时间戳等单调递增列的范围扫描有效。部分索引：只对行的子集建索引。示例：`CREATE INDEX ON orders (status) WHERE status = ‘pending’;`——如果99%的订单已完成，你只查询待处理的订单，此索引比状态的完整索引小得多且更快。覆盖索引：在索引中包含额外的列以启用仅索引扫描。`CREATE INDEX ON users (email) INCLUDE (name, role);`——通过电子邮件选择姓名和角色的查询可以不读取表即可回答。
常见瓶颈和修复
N+1查询问题：执行一个查询获取列表，然后对列表中每个项目执行一个查询。修复：使用JOIN或IN子句在一个查询中获取，或使用自动执行此操作的库。外键上缺少索引：当你在没有索引的外键列上JOIN时，PostgreSQL进行顺序扫描。在PostgreSQL中，外键约束不会自动创建索引（与MySQL不同）。始终在外键列上创建索引。`SELECT *`在宽表上：在有很多列或大型JSONB/文本列的表上选择所有列会传输不必要的数据。只选择你需要的列。`LIKE ‘%pattern%’`不可索引：前导通配符阻止B-tree索引使用。解决方案：带GIN索引的pg_trgm扩展启用子字符串搜索；文本内容的GIN全文搜索。连接开销：PostgreSQL用单独的进程处理每个连接。数百个短暂连接（在无服务器或自动扩展环境中典型）产生显著开销。修复：PgBouncer连接池器位于应用程序和PostgreSQL之间，维护数据库连接池。锁竞争：长时间运行的事务持有锁。`SELECT * FROM pg_locks JOIN pg_stat_activity USING (pid)`显示什么持有锁。自动清理滞后：PostgreSQL的MVCC模型积累死行版本（元组），自动清理必须清理。在高写入表上，检查`pg_stat_user_tables`中的`n_dead_tup`——如果这很大，可能需要调整自动清理（增加`autovacuum_vacuum_scale_factor`或`autovacuum_vacuum_cost_delay`）。
