PostgreSQL 性能优化

EXPLAIN 分析查询

-- 基本分析
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 详细分析（包含成本）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT * FROM users WHERE id = 1;

-- JSON 格式输出
EXPLAIN (ANALYZE, FORMAT JSON) 
SELECT * FROM users WHERE status = 'active';

-- 查看执行计划
EXPLAIN (ANALYZE) 
SELECT u.username, COUNT(o.id) 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
GROUP BY u.id;

执行计划解读

Seq Scan on users  (cost=0.00..100.00 rows=1000 width=50)
                 ↑           ↑       ↑     ↑
              扫描方式    启动成本   行数   行宽度

关键指标：

• cost=..：前值为启动成本，后值为总成本
• rows=N：估算返回行数
• actual time：实际执行时间（毫秒）
• loops：循环次数

索引优化

索引类型选择

-- B-tree 索引（默认，最常用）
CREATE INDEX idx_users_email ON users(email);

-- 多列 B-tree 索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 部分索引（索引满足条件的行）
CREATE INDEX idx_users_active_email ON users(email) WHERE status = 'active';

-- GiST 索引（几何数据、全文搜索）
CREATE INDEX idx_users_geometry ON users USING GIST (location);

-- GIN 索引（数组、JSON）
CREATE INDEX idx_users_tags ON users USING GIN (tags);

-- 条件索引示例
CREATE INDEX idx_orders_pending ON orders(created_at) 
WHERE status = 'pending';

索引使用分析

-- 查看索引使用统计
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan
FROM pg_stat_user_indexes;

-- 查看未使用的索引
SELECT 
    schemaname || '.' || tablename AS table,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查看索引大小
SELECT 
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    indexrelname AS index_name
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

索引维护

-- 重建索引
REINDEX INDEX idx_users_email;

-- 重建表的所有索引
REINDEX TABLE users;

-- 重建系统索引
REINDEX DATABASE mydb;

-- 删除未使用的索引
DROP INDEX idx_users_unused;

查询优化技巧

避免全表扫描

-- 不好：全表扫描
SELECT * FROM users WHERE age > 18;

-- 好：使用索引
CREATE INDEX idx_users_age ON users(age);
SELECT * FROM users WHERE age > 18;  -- 会使用索引

-- 使用覆盖索引
CREATE INDEX idx_users_covering ON users(age) INCLUDE (username, email);
SELECT age, username, email FROM users WHERE age > 18;

优化 JOIN

-- 确保连接列有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 小表驱动大表
SELECT /*+ HASH(t1 t2) */ t1.name, t2.total
FROM small_table t1
JOIN large_table t2 ON t1.id = t2.ref_id;

-- 尝试不同的连接方式
SET enable_hashjoin = off;
SET enable_nestloop = off;
SET enable_mergejoin = off;

分页优化

-- 低效：OFFSET 大值时很慢
SELECT * FROM users ORDER BY id LIMIT 100 OFFSET 10000;

-- 高效：使用游标分页
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 100;

-- 使用 Keyset 分页
SELECT * FROM users 
WHERE (id, created_at) > (10000, '2024-01-01')
ORDER BY id, created_at
LIMIT 100;

批量操作优化

-- 批量插入
INSERT INTO users (username, email) VALUES 
    ('u1', 'u1@e.com'),
    ('u2', 'u2@e.com'),
    ('u3', 'u3@e.com');

-- 使用 COPY 批量导入（最快）
COPY users(username, email) FROM STDIN WITH (FORMAT csv);

-- 批量更新
UPDATE users SET status = 'inactive' 
WHERE id IN (1, 2, 3, 4, 5);

-- 批量删除
DELETE FROM users WHERE id IN (1, 2, 3, 4, 5);

配置优化

内存配置

-- 查看当前配置
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW effective_cache_size;

-- 临时设置（会话级）
SET shared_buffers = '256MB';
SET work_mem = '64MB';

-- 建议值（根据服务器内存调整）
-- shared_buffers: 25% of RAM
-- work_mem: 25% of RAM / max_connections / 4
-- maintenance_work_mem: 10% of RAM
-- effective_cache_size: 75% of RAM

关键配置参数

-- postgresql.conf 中的重要配置

-- 连接配置
max_connections = 200

-- 内存配置
shared_buffers = 4GB
work_mem = 64MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB

-- 写性能
wal_buffers = 16MB
checkpoint_completion_target = 0.9

-- 并行查询
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

-- 日志
log_min_duration_statement = 1000  -- 记录超过1秒的查询

VACUUM 与维护

VACUUM 操作

-- 清理死亡元组
VACUUM users;

-- 清理并更新统计信息
VACUUM ANALYZE users;

-- 全表清理
VACUUM FULL users;

-- 标记清理（不锁表）
VACUUM (VACUUM) users;

-- 自动清理配置
ALTER TABLE users SET (autovacuum_vacuum_threshold = 50);
ALTER TABLE users SET (autovacuum_analyze_threshold = 50);
ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.1);

自动清理配置

-- postgresql.conf 中配置
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

统计信息

-- 查看表统计信息
SELECT 
    schemaname,
    tablename,
    n_tup_ins,      -- 插入行数
    n_tup_upd,      -- 更新行数
    n_tup_del,      -- 删除行数
    n_live_tup,     -- 活跃行数
    n_dead_tup,     -- 死亡元组数
    last_vacuum,
    last_autovacuum,
    last_analyze
FROM pg_stat_user_tables
WHERE tablename = 'users';

-- 查看查询统计
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 查看缓存命中率
SELECT 
    sum(heap_blks_read) AS heap_read,
    sum(heap_blks_hit) AS heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

连接池

pgBouncer 配置

; pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20

使用连接池

-- 安装
apt install pgbouncer

-- 启动
pgbouncer pgbouncer.ini

-- 连接
psql -h 127.0.0.1 -p 6432 -U postgres mydb

慢查询排查流程

1. 启用查询日志

ALTER SYSTEM SET log_min_duration_statement = 1000;
SELECT pg_reload_conf();

2. 分析慢查询

SELECT 
    query,
    calls,
    mean_exec_time,
    total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

3. 查看执行计划

EXPLAIN (ANALYZE, BUFFERS) your_slow_query;

4. 根据执行计划优化
   • 添加缺失的索引
   • 重写查询
   • 调整配置参数
   • 分区大表