PostgreSQL查询性能诊断与索引优化实战手册

PostgreSQL慢查询定位与分析

数据库运维中，慢查询是最常见的性能瓶颈。PostgreSQL提供了多层诊断工具，从全局统计到单条查询的执行计划，精准定位问题。开启慢查询日志是排查的第一步：

# postgresql.conf - 慢查询日志配置
log_min_duration_statement = 200    # 记录执行超过200ms的SQL
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on                 # 记录等锁超过1秒的会话

# 查看当前活跃查询与等待事件
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

# 查看累计慢查询统计（pg_stat_statements扩展）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls, round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       round((total_exec_time / SUM(total_exec_time) OVER() * 100)::numeric, 2) AS pct,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

EXPLAIN ANALYZE执行计划深度解读

EXPLAIN ANALYZE是数据库性能调优最重要的工具，实际执行SQL并输出每个节点的真实耗时：

# 执行计划关键字段解读
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'pending'
ORDER BY o.amount DESC
LIMIT 50;

# 输出示例：
# Limit (cost=0.42..125.38 rows=50 width=72) (actual time=0.05..1.23 rows=50 loops=1)
#   -> Nested Loop (cost=0.42..125.38 rows=50 width=72)
#      -> Index Scan using idx_orders_status_created on orders
#         (cost=0.42..100.20 rows=50 width=48) (actual time=0.03..0.85 rows=50)
#         Index Cond: (status = 'pending' AND created_at > '2026-01-01')
#         Buffers: shared hit=12
#      -> Index Scan using customers_pkey on customers
#         (cost=0.28..0.50 rows=1 width=28) (actual time=0.01..0.01 rows=1)
#         Buffers: shared hit=2

# 关键诊断指标
# 1. cost：规划器估算的代价，第一行是启动代价，第二行是总代价
# 2. actual time：真实耗时(ms)，启动时间..总时间
# 3. rows：实际返回行数 vs 规划器预估行数，差异大说明统计信息过时
# 4. Buffers：shared hit=缓存命中，shared read=磁盘读取
# 5. Seq Scan：全表扫描，大表出现Seq Scan通常需要加索引

索引类型选择与设计策略

# 1. B-tree索引（默认，适用等值/范围/排序查询）
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

# 2. 部分索引（只索引满足条件的数据，体积更小）
CREATE INDEX idx_orders_pending
ON orders (created_at DESC)
WHERE status = 'pending';    # 仅索引pending状态订单

# 3. 表达式索引（对计算列建索引）
CREATE INDEX idx_users_lower_email
ON users (LOWER(email));      # 忽略大小写查询

# 4. GIN索引（全文搜索、JSONB、数组）
CREATE INDEX idx_products_tags
ON products USING GIN (tags);  # 数组包含查询

# 5. BRIN索引（时序数据，按物理顺序存储）
CREATE INDEX idx_logs_created_brin
ON logs USING BRIN (created_at)
WITH (pages_per_range = 32);  # 时序表按时间顺序写入

# 复合索引字段顺序原则
# 等值查询字段在前，范围查询字段在后
# 高选择性字段在前（区分度高的字段）
# WHERE a = 1 AND b > 10 ORDER BY b → INDEX(a, b)

索引维护与统计信息更新

# 查看索引使用率
SELECT schemaname, tablename, indexname,
       idx_scan AS index_scans,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

# 未使用的索引（idx_scan=0）应考虑删除
# 大索引占用磁盘和写入成本

# 手动更新统计信息
ANALYZE orders;                # 单表分析
ANALYZE orders (status, created_at);  # 指定列

# 自动清理配置（大表需要调大触发阈值）
ALTER TABLE orders SET (
    autovacuum_analyze_scale_factor = 0.02,  # 2%数据变化后分析
    autovacuum_vacuum_scale_factor = 0.05     # 5%数据变化后清理
);

# 重建索引（在线不锁表）
REINDEX INDEX CONCURRENTLY idx_orders_status_created;

常见慢查询优化案例

# 案例1：隐式类型转换导致索引失效
# ❌ 错误：字段是varchar，传入的参数是integer
SELECT * FROM users WHERE phone = 13800138000;
# 规划器对phone做隐式转换，无法使用索引

# ✅ 修正：参数类型与字段类型一致
SELECT * FROM users WHERE phone = '13800138000';

# 案例2：OR条件导致索引选择困难
# ❌ 错误
SELECT * FROM orders
WHERE customer_id = 100 OR product_id = 200;

# ✅ 修正：UNION ALL各走各自索引
SELECT * FROM orders WHERE customer_id = 100
UNION ALL
SELECT * FROM orders WHERE product_id = 200
  AND customer_id != 100;

# 案例3：分页查询深翻页性能退化
# ❌ 错误：OFFSET 100000在大偏移量时性能极差
SELECT * FROM articles ORDER BY id LIMIT 20 OFFSET 100000;

# ✅ 修正：游标分页（keyset pagination）
SELECT * FROM articles
WHERE id > :last_id
ORDER BY id LIMIT 20;

索引优化不是加索引就完事，每个索引都会增加写入开销和存储空间。核心方法论：先用pg_stat_statements和EXPLAIN ANALYZE定位问题SQL，再根据查询模式选择合适的索引类型，定期清理未使用的索引，保持统计信息新鲜度。