PostgreSQL 性能调优

EXPLAIN ANALYZE

-- 基本 explain
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- 带实际执行统计
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '30 days';

需要关注的关键术语：

• Seq Scan = 全表扫描（大表上通常不好）
• Index Scan = 使用索引
• Nested Loop = 适合小数据集，大数据集可能慢
• Hash Join = 更适合大数据集连接
• actual rows vs estimated rows = 差异大意味着统计信息过时

索引类型

-- B-tree（默认） - 适合等值和范围查询
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- 部分索引 - 只索引匹配条件的行
CREATE INDEX idx_pending_orders ON orders(created_at)
WHERE status = 'pending';

-- 表达式索引
CREATE INDEX idx_lower_email ON users(LOWER(email));
-- 查询必须使用相同表达式：
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- GIN 索引用于全文搜索
CREATE INDEX idx_articles_search ON articles
USING gin(to_tsvector('english', title || ' ' || body));

SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('database performance');

-- GIN 索引用于 JSONB
CREATE INDEX idx_metadata ON products USING gin(metadata);
SELECT * FROM products WHERE metadata @> '{"color": "red"}';

常见慢查询模式

-- 坏：对索引列使用函数会阻止索引使用
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 好：范围查询使用索引
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 坏：前导通配符阻止索引使用
SELECT * FROM products WHERE name LIKE '%phone%';
-- 好：使用全文搜索进行包含查询
SELECT * FROM products WHERE name_search @@ plainto_tsquery('phone');

-- 坏：SELECT * 获取不必要的列
SELECT * FROM orders WHERE user_id = 123;
-- 好：只获取需要的列
SELECT id, total, status, created_at FROM orders WHERE user_id = 123;

-- 坏：N+1 查询
SELECT * FROM orders;  -- 然后对每个订单：
SELECT * FROM users WHERE id = $order.user_id;

-- 好：单个 JOIN
SELECT o.*, u.email, u.name
FROM orders o
JOIN users u ON u.id = o.user_id;

使用 PgBouncer 进行连接池管理

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

[pgbouncer]
pool_mode = transaction   ; 最高效
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
server_idle_timeout = 600

配置调优

-- 检查当前设置
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;

-- 16GB RAM 服务器的 postgresql.conf 调优：
-- shared_buffers = 4GB          （RAM 的 25%）
-- effective_cache_size = 12GB   （RAM 的 75%）
-- work_mem = 64MB               （用于复杂排序）
-- maintenance_work_mem = 1GB    （用于 VACUUM、CREATE INDEX）
-- max_connections = 200         （更多连接使用 PgBouncer）
-- wal_buffers = 64MB
-- checkpoint_completion_target = 0.9
-- random_page_cost = 1.1        （对于 SSD）

自动清理与统计信息

-- 检查表膨胀
SELECT schemaname, 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
ORDER BY n_dead_tup DESC;

-- 手动 vacuum analyze
VACUUM ANALYZE orders;

-- 更新统计信息
ANALYZE orders;

-- 检查索引使用情况
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

-- 查找未使用的索引
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%';

始终在类似生产的环境中对优化前后进行基准测试。