查询性能问题是后端开发的常见瓶颈，PostgreSQL 提供了丰富的诊断工具和优化手段，合理利用可实现数量级性能提升。
执行计划诊断
使用 EXPLAIN (ANALYZE, BUFFERS) 获取真实执行时间和缓冲区命中，重点关注全表扫描、Rows 估算偏差、Sort/Hash 溢出磁盘等信号。
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, o.created_at, u.username, sum(oi.quantity * oi.price) AS total
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY o.order_id, o.created_at, u.username
HAVING sum(oi.quantity * oi.price) > 1000
ORDER BY total DESC
LIMIT 100;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_user
ON orders (created_at, user_id) INCLUDE (order_id);
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.01);
ANALYZE orders (order_id, user_id, created_at);
SET enable_hashjoin = off; SET enable_mergejoin = on;
EXPLAIN ANALYZE SELECT * FROM orders o JOIN users u ON o.user_id = u.user_id;
RESET enable_hashjoin; RESET enable_mergejoin;
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname IN ('orders','order_items','users') ORDER BY idx_scan DESC;
索引设计与调优
根据查询模式选择索引类型：等值查询用 B-tree、全文/数组用 GIN、时序有序数据用 BRIN。复合索引要注意列的顺序（区分度高的列在前）。
| 场景 | 推荐索引类型 | 命中率 | 维护成本 |
|---|---|---|---|
| 主键/外键等值 | B-tree | 极高 | 低 |
| JSONB/数组包含 | GIN | 高 | 中 |
| 时序范围查询 | BRIN | 中高 | 极低 |
| 模糊查询前缀 | B-tree varchar_pattern_ops | 中 | 低 |
| 多列过滤排序 | 复合B-tree | 高 | 中高 |
最佳实践
定期分析慢查询日志，关注 pg_stat_statements 的 mean_exec_time，大表用 CONCURRENTLY 建索引避免锁表。