PostgreSQL 性能基础
慢查询是应用程序性能问题的首要原因。以下是如何系统性地诊断和修复它们。
EXPLAIN ANALYZE
-- 始终使用 EXPLAIN (ANALYZE, BUFFERS) 获取真实执行数据
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;
-- 需要关注的关键指标：
-- Seq Scan vs Index Scan
-- Actual rows vs estimated rows（差异大 = 统计信息过时）
-- Buffers: shared hit vs read（高 read = 磁盘 I/O）
-- Loops: 嵌套循环次数
索引类型
-- B-tree（默认）：等值、范围、ORDER BY
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created ON orders (created_at DESC);
-- 复合索引：从左到右前缀规则
CREATE INDEX idx_orders_user_status ON orders (user_id, status, created_at DESC);
-- 适用于：WHERE user_id = X AND status = 'pending'
-- 也适用于：WHERE user_id = X ORDER BY created_at DESC
-- 部分索引：索引行的子集
CREATE INDEX idx_orders_pending ON orders (user_id, created_at)
WHERE status = 'pending';
-- 体积更小，仅查询 pending 时更快
-- GIN 索引：全文搜索、JSONB、数组
CREATE INDEX idx_posts_search ON posts USING gin(to_tsvector('english', title || ' ' || content));
CREATE INDEX idx_products_tags ON products USING gin(tags); -- 数组列
-- BRIN：时间序列数据，物理相关性高
CREATE INDEX idx_events_timestamp ON events USING brin(timestamp);
-- 体积非常小，适用于仅追加的时间序列
查询优化模式
-- 使用 CTE 提高可读性，但需谨慎（PG < 12 中 CTE 是优化屏障）
WITH user_stats AS MATERIALIZED (
SELECT user_id, COUNT(*) as total, SUM(amount) as revenue
FROM orders
WHERE status = 'completed'
GROUP BY user_id
)
SELECT u.name, s.total, s.revenue
FROM users u
JOIN user_stats s ON s.user_id = u.id
ORDER BY s.revenue DESC
LIMIT 10;
-- 避免 N+1：使用窗口函数
SELECT
id, name,
COUNT(*) OVER (PARTITION BY department) AS dept_size,
AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary,
salary - AVG(salary) OVER (PARTITION BY department) AS salary_diff
FROM employees;
-- 高效分页（基于游标）
-- 不好：OFFSET 10000 会扫描 10000 行
SELECT * FROM posts ORDER BY created_at DESC OFFSET 10000 LIMIT 20;
-- 好：游标分页
SELECT * FROM posts
WHERE created_at < '2026-05-01T12:00:00'
ORDER BY created_at DESC
LIMIT 20;
关键配置参数
# postgresql.conf 调优
# 内存
shared_buffers = 25% of RAM # 例如 16GB 服务器设为 4GB
effective_cache_size = 75% of RAM # 用于查询规划器估算
work_mem = 64MB # 每个排序/哈希操作
maintenance_work_mem = 512MB # 用于 VACUUM、CREATE INDEX
# 检查点
checkpoint_completion_target = 0.9
wal_buffers = 64MB
# 并行
max_parallel_workers_per_gather = 4 # 并行查询工作进程
max_parallel_workers = 8
# 日志
log_min_duration_statement = 1000 # 记录超过 1 秒的查询
log_checkpoints = on
log_lock_waits = on
使用 PgBouncer 进行连接池管理
# pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp
[pgbouncer]
pool_mode = transaction # 最适合大多数 Web 应用
max_client_conn = 1000
default_pool_size = 25 # 实际 PG 连接数
reserve_pool_size = 5
server_idle_timeout = 600
自动清理调优
-- 检查表膨胀
SELECT
tablename,
n_dead_tup,
n_live_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS bloat_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
-- 针对高写入表的每表自动清理设置
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- 死元组占比 1% 时清理
autovacuum_analyze_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 2
);
监控查询
-- 查找慢查询（需要 pg_stat_statements 扩展）
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- 查找缺失的索引
SELECT relname, seq_scan, idx_scan,
seq_scan - idx_scan AS diff
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY diff DESC;
-- 查找索引使用情况
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0 -- 未使用的索引
AND NOT indisprimary
ORDER BY pg_relation_size(indexrelid) DESC;