PostgreSQL 查询优化：从慢查询到高性能

分析查询性能

使用 EXPLAIN ANALYZE 分析查询执行计划：

EXPLAIN ANALYZE

SELECT * FROM orders

WHERE user_id = 123

AND created_at > '2024-01-01';

索引优化

创建合适的索引

-- 单列索引

CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 复合索引（注意列顺序）

CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 部分索引

CREATE INDEX idx_orders_pending ON orders(status)

WHERE status = 'pending';

-- 表达式索引

CREATE INDEX idx_users_email_lower ON users(LOWER(email));

索引使用原则

查询重写

避免 SELECT *

-- ❌ 不推荐

SELECT * FROM orders WHERE user_id = 123;

-- ✅ 推荐

SELECT id, status, total FROM orders WHERE user_id = 123;

使用 EXISTS 替代 IN

-- ❌ 子查询可能很慢

SELECT * FROM orders

WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

-- ✅ EXISTS 通常更快

SELECT * FROM orders o

WHERE EXISTS (

SELECT 1 FROM users u

WHERE u.id = o.user_id AND u.status = 'active'

);

分页优化

-- ❌ OFFSET 大时性能差

SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- ✅ 使用游标分页

SELECT * FROM orders

WHERE id > 10000

ORDER BY id

LIMIT 20;

连接优化

-- 确保连接列有索引

CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- 使用合适的连接类型

SELECT o.*, oi.*

FROM orders o

INNER JOIN order_items oi ON o.id = oi.order_id

WHERE o.user_id = 123;

配置优化

-- 查看当前配置

SHOW shared_buffers;

SHOW work_mem;

SHOW effective_cache_size;

-- 推荐配置（根据服务器内存调整）

-- shared_buffers = 25% of RAM

-- work_mem = 64MB (for complex queries)

-- effective_cache_size = 75% of RAM

监控慢查询

-- 启用慢查询日志

ALTER SYSTEM SET log_min_duration_statement = '1000'; -- 1秒

-- 查看当前运行的查询

SELECT pid, now() - pg_stat_activity.query_start AS duration, query

FROM pg_stat_activity

WHERE state = 'active'

ORDER BY duration DESC;

总结

PostgreSQL 性能优化是一个持续的过程。通过分析执行计划、创建合适的索引、重写低效查询，可以显著提升数据库性能。