PostgreSQL 查询优化实践：执行计划、统计信息与索引策略

PostgreSQL 是功能非常丰富的关系型数据库。它支持复杂 SQL、JSON、全文搜索、窗口函数、多种索引和扩展生态。能力越强，越需要理解优化器。很多 PostgreSQL 慢查询不是数据库“不够快”，而是统计信息过期、索引不匹配、连接顺序错误或返回数据量超出预期。

优化 PostgreSQL 查询的第一步，是用执行计划解释数据库实际做了什么。

EXPLAIN ANALYZE

EXPLAIN 给出计划，EXPLAIN ANALYZE 会真实执行并返回实际耗时。生产使用时要谨慎，因为它会执行 SQL。

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 100
ORDER BY created_at DESC
LIMIT 20;

重点关注：

• Seq Scan 还是 Index Scan。
• 估算行数和实际行数差异。
• Buffers 命中和读取。
• Sort 是否溢出。
• Join 类型是否合理。
• 最耗时节点在哪里。

如果估算行数和实际行数差异巨大，优化器可能基于错误统计信息选择了错误计划。

统计信息

PostgreSQL 依赖统计信息估算选择率。数据分布变化后，需要 autovacuum/analyze 更新统计。对倾斜字段，可以提高统计目标。

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

例如 status 字段大部分是 paid，少量是 failed，如果统计信息不准确，优化器可能低估或高估扫描范围。

索引类型

PostgreSQL 支持多种索引：

• B-tree：通用等值、范围、排序。
• GIN：数组、JSONB、全文搜索。
• GiST：地理、范围类型。
• BRIN：大表顺序数据，如时间序列。
• Hash：等值场景，使用较少。

不要所有场景都用 B-tree。日志或时间序列表按时间范围查询，BRIN 可能以很低成本提供足够性能。JSONB 查询可以考虑 GIN。

部分索引

部分索引适合只查询某类数据的场景。例如订单表中只有少量未处理订单：

CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';

这比为全表创建 (status, created_at) 索引更小，维护成本更低。适合状态分布高度倾斜的业务。

表膨胀与 VACUUM

PostgreSQL 使用 MVCC，更新和删除会产生旧版本。VACUUM 负责清理死元组。如果 autovacuum 配置不合理，表和索引会膨胀，查询和写入都变慢。

需要监控：

• dead tuples。
• autovacuum 运行频率。
• 表和索引膨胀。
• 长事务。
• xid wraparound 风险。

长事务会阻止旧版本清理，是生产 PostgreSQL 常见隐患。

排序与内存

复杂查询中 Sort 和 Hash Join 可能使用 work_mem。如果内存不足，会写临时文件到磁盘，性能明显下降。

不要全局盲目调大 work_mem，因为它是每个操作节点、每个连接都可能使用。更稳妥的方式是针对批处理或报表会话设置，或优化查询减少大排序。

JSONB 查询

PostgreSQL 的 JSONB 很方便，但不要把它当成逃避建模的工具。高频过滤字段应考虑独立列或表达式索引。

CREATE INDEX idx_users_profile_region
ON users ((profile->>'region'));

如果 JSONB 字段结构不可控，索引和统计都会变复杂。核心业务字段尽量结构化。

总结

PostgreSQL 查询优化的关键是理解优化器证据：执行计划、统计信息、索引、表膨胀和内存使用。它能处理非常复杂的数据访问，但前提是让优化器拥有准确统计和合适访问路径。优化不是猜参数，而是读计划、验证假设、逐步收敛。