PostgreSQL 查询优化技巧

查询性能问题是后端开发的常见瓶颈，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。复合索引要注意列的顺序（区分度高的列在前）。

最佳实践

定期分析慢查询日志，关注 pg_stat_statements 的 mean_exec_time，大表用 CONCURRENTLY 建索引避免锁表。