PostgreSQL 性能优化：从查询计划到硬件调优的完整方法论

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2025-01-01'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 100;

-- 慢查询排查
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC
LIMIT 10;

-- 终止慢查询（危险操作）
SELECT pg_terminate_backend(12345); -- pid

-- 查看表扫描频率（排序找出缺索引的表）
SELECT schemaname, relname,
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

-- 复合索引（最左前缀：WHERE user_id=? AND created_at > ?）
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- 部分索引（只索引热点数据，减小索引体积）
CREATE INDEX idx_orders_unpaid ON orders(status) WHERE status = 'unpaid';

-- 函数索引（匹配查询条件 UPPER(email) = ?）
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 覆盖索引（Index Only Scan）
CREATE INDEX idx_orders_cover ON orders(user_id, status) INCLUDE (total);
-- 好处：查询 SELECT user_id, status, total 时无需回表

-- GIN 索引（JSONB 包含 @> 查询）
CREATE INDEX idx_products_attrs ON products USING GIN(attributes jsonb_path_ops);

-- BRIN 索引（时序大数据）
CREATE INDEX idx_logs_time ON logs USING BRIN(created_at)
  WITH (pages_per_range = 128);

-- 找出从未被使用的索引
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_toast%'
ORDER BY indexrelname;

-- 删除前先用 pg_index 确认不是约束索引
-- 删除：DROP INDEX CONCURRENTLY idx_name; （不锁表）

-- 查看表膨胀率
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       ROUND(n_dead_tup::NUMERIC / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 20;

-- 查看表实际物理大小
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(oid) DESC LIMIT 20;

# postgresql.conf 关键参数
autovacuum_max_workers = 3          # 并行 VACUUM 进程数
autovacuum_naptime = 10s            # 检查间隔
autovacuum_vacuum_threshold = 50    # 触发 VACUUM 的最小死元组数
autovacuum_vacuum_scale_factor = 0.1 # 触发阈值 = 阈值 + 行数 × 比例
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05
vacuum_cost_delay = 2ms             # VACUUM 的限速（避免影响正常业务）
vacuum_cost_limit = 1000            # VACUUM 的速限成本

-- 为频繁写入的日志表单独降低 scale_factor（避免每次更新全表 VACUUM）
ALTER TABLE logs SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE logs SET (autovacuum_vacuum_threshold = 500);

# postgresql.conf
max_connections = 200              # 根据 CPU 和内存决定
shared_buffers = 256MB             # 内存的 25%，但也可用更多（DB 专用机器可到 40%）
effective_cache_size = 1GB         # OS + DB 可用缓存总大小（预估，非实际分配）
work_mem = 16MB                    # 每个操作（排序/哈希）的内存，有 N 个连接同时排序则 N × work_mem
maintenance_work_mem = 128MB       # VACUUM/CREATE INDEX 内存
wal_buffers = 16MB
random_page_cost = 1.1             # SSD 设为 1.1，HDD 设为 4（影响索引选择决策）
effective_io_concurrency = 200     # SSD 设为 200，HDD 设为 2（影响位图索引扫描并行度）

# postgresql.conf
log_min_duration_statement = 500   # 记录 > 500ms 的查询
log_statement = 'mod'              # 记录 INSERT/UPDATE/DELETE（可调为 'all'）
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '  # 每条日志前缀
log_duration = on                  # 记录查询耗时
log_slow_queries = on              # 慢查询统计

-- 原始慢查询
SELECT * FROM logs WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20;
-- EXPLAIN 显示 Seq Scan + Sort

-- 优化：加复合索引匹配排序
CREATE INDEX idx_logs_user_created ON logs(user_id, created_at DESC);
-- 结果：Index Only Scan，从数秒降到 <1ms

-- 原始：无索引，大表扫描
SELECT * FROM products WHERE attributes @> '{"brand": "Apple"}';

-- 优化：GIN 索引
CREATE INDEX idx_products_attrs ON products USING GIN(attributes jsonb_path_ops);
-- 结果：GIN Index Scan，查询提速 100x

-- 原始慢查询
SELECT o.*, u.email FROM orders o JOIN users u ON o.user_id = u.id;
-- EXPLAIN 显示 Nested Loop 上 users 做 Seq Scan

-- 优化：确保 JOIN 列有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);  -- 通常主键外键已有
-- 结果：Index Scan + Loop，避免用户表全表扫描