4. 索引与查询计划：使用 EXPLAIN ANALYZE 识别低效查询

在报表导出服务中，Redis 热点往往不是 Redis 本身配置不当，而是 PostgreSQL 查询低效 导致大量重复查询或长时间锁等待，最终将压力传导至缓存层。本章的核心目标是：通过 EXPLAIN ANALYZE 精准定位低效查询，并反向推导索引设计的缺陷。

4.1 理解查询计划的基础结构

PostgreSQL 的查询计划是一棵由多个节点组成的执行树。每个节点代表一个操作（如顺序扫描、索引扫描、连接、排序等）。EXPLAIN ANALYZE 不仅展示计划树，还会实际执行 SQL 并报告每个节点的真实耗时与行数。

4.2 使用 EXPLAIN ANALYZE 的标准化流程

不要直接在生产库上执行 EXPLAIN ANALYZE 写操作（INSERT/UPDATE/DELETE），它会真实提交数据。对于报表导出场景，我们主要分析 SELECT 查询。推荐以下流程：

1. 捕获慢查询：从 pg_stat_statements 或应用日志中提取耗时最长的 SQL。
2. 在只读副本或压测环境执行：避免影响主库业务。
3. 执行 EXPLAIN (ANALYZE, BUFFERS, TIMING)：获取实际执行时间、缓冲区命中率、行数估计偏差。
4. 对比预估行数 vs 实际行数：偏差超过 10 倍说明统计信息过时。
5. 检查每个节点的实际耗时占比：找到最耗时的节点，优先优化。

-- 标准分析命令
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT o.order_id, o.created_at, u.user_name
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE o.created_at >= '2024-01-01'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 1000;

4.3 识别低效查询的四个关键指标

在分析输出中，重点关注以下四个指标，它们直接反映索引设计的健康度：

4.4 从查询计划反推索引设计缺陷

以下是一张典型的低效查询计划与索引缺陷对照表，帮助你在分析时快速定位问题：

4.5 实战：通过 EXPLAIN ANALYZE 优化报表导出查询

假设报表导出服务执行以下查询，导致 Redis 缓存频繁过期重建：

SELECT p.product_name, s.sales_amount, s.sale_date
FROM sales s
JOIN products p ON p.product_id = s.product_id
WHERE s.sale_date BETWEEN '2024-06-01' AND '2024-06-30'
  AND s.region_id = 5
ORDER BY s.sale_date DESC;

执行 EXPLAIN ANALYZE 后得到以下关键输出片段：

Sort (actual time=1250.34..1255.67 rows=23400 loops=1)
  Sort Key: s.sale_date DESC
  Sort Method: external merge  Disk: 1024kB
  -> Hash Join (actual time=200.12..1100.45 rows=23400 loops=1)
        Hash Cond: (p.product_id = s.product_id)
        -> Seq Scan on products p (actual time=0.01..0.02 rows=1000 loops=1)
        -> Hash (actual time=200.00..200.01 rows=23400 loops=1)
              -> Seq Scan on sales s (actual time=0.12..150.34 rows=23400 loops=1)
                    Filter: (sale_date BETWEEN ... AND region_id = 5)

问题诊断：

• sales 表全表扫描（Seq Scan），尽管有过滤条件，但无复合索引支撑。
• Sort 使用磁盘排序（external merge），说明 work_mem 不足且无索引辅助排序。
• Hash Join 虽然比 Nested Loop 好，但 products 表扫描方式尚可，主要瓶颈在 sales 表。

索引优化方案：

-- 创建复合索引，覆盖过滤、排序、连接列
CREATE INDEX idx_sales_region_date_product
ON sales (region_id, sale_date DESC, product_id)
INCLUDE (sales_amount);

优化后再次执行 EXPLAIN ANALYZE，计划变为：

Index Only Scan using idx_sales_region_date_product on sales
  (actual time=0.05..12.34 rows=23400 loops=1)
  Index Cond: ((region_id = 5) AND (sale_date BETWEEN ...))
  Heap Fetches: 0

查询耗时从 1255ms 降至 12ms，Redis 缓存压力随之消失。

4.6 查询计划可视化分析

下图展示了从低效查询到索引优化的完整决策路径，帮助你在实际工作中快速建立分析框架：

4.7 常见陷阱与避坑指南

• 陷阱1：只看总耗时，忽略节点分布。 总耗时可能被某个子节点放大，必须逐层展开分析。
• 陷阱2：在生产库直接执行 EXPLAIN ANALYZE 写操作。 务必使用只读副本或 EXPLAIN (ANALYZE, FORMAT JSON) 结合事务回滚。
• 陷阱3：忽略缓冲区命中率。 即使耗时低，如果缓冲区命中率低于 90%，说明索引未覆盖，回表压力大，最终会拖垮共享缓冲区。
• 陷阱4：行数偏差大时不更新统计信息。 偏差超过 10 倍时，优化器可能选择全表扫描而非索引扫描，此时应执行 ANALYZE 或提高 default_statistics_target。