PostgreSQL 查询性能优化实战

PostgreSQL 性能优化的重要性

数据库性能是应用系统的核心。本文通过实战案例，展示如何系统地优化 PostgreSQL 查询性能。

1. 查询计划分析

使用 EXPLAIN ANALYZE 是优化的第一步：

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at > '2024-01-01'
AND status = 'completed';

理解查询计划中的关键指标：

Seq Scan vs Index Scan
Cost 估算
Actual Time 实际执行时间
Rows 行数估算准确性

2. 索引策略

创建合适的索引是性能优化的关键：

-- B-tree 索引（最常用）
CREATE INDEX idx_orders_status ON orders(status);

-- 复合索引
CREATE INDEX idx_orders_date_status 
ON orders(created_at, status);

-- 部分索引
CREATE INDEX idx_active_orders 
ON orders(created_at) 
WHERE status = 'active';

3. 分区表设计

对于大表，分区可以显著提升性能：

CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

4. 统计信息维护

定期更新统计信息确保查询计划准确：

ANALYZE orders;
VACUUM ANALYZE orders;

5. 连接池配置

合理配置连接池参数：

max_connections: 根据并发需求设置
shared_buffers: 建议设置为总内存的 25%
effective_cache_size: 设置为可用内存的 50-75%

实战案例

我们优化了一个电商系统的订单查询，通过添加合适的索引和分区，查询时间从 8 秒降低到 200ms，性能提升了 40 倍。

性能监控

使用 pg_stat_statements 扩展监控慢查询，及时发现性能问题。