SQL 与查询优化（PostgreSQL 篇）· 第三期

连接优化与统计信息深度调优

经过前两期，你已经能读懂执行计划、用好窗口函数与 CTE。本期进入多表连接优化的核心地带，并深挖优化器的“大脑”——统计信息。 我们将剖析三种连接算法背后的代价模型，学会用扩展统计信息解决多列关联的基数误判，最终通过真实案例把执行时间从分钟级降至毫秒级。

一、连接算法深度解析 – 优化器的三种武器

当查询涉及多个表时，PostgreSQL 优化器需要在 Nested Loop、Hash Join、Merge Join 中做出选择，并决定连接顺序。这个决策的基础是 基数估计（每步返回的行数）与 代价计算。

1.1 Nested Loop – 索引依赖之王

原理：对外层结果集的每一行，遍历内层表查找匹配行。 代价：cost = outer_rows * inner_scan_cost

• 如果内层走索引（Index Scan），inner_scan_cost ≈ O(log N)；如果内层全表扫描，代价爆炸。 适用场景：
• 外层表很小（例如只返回几十行）。
• 内层表有高效索引（通常是连接键）。
• 支持非等值连接（如 a.id > b.id），这是 Hash Join 做不到的。

执行计划识别：

Nested Loop  (cost=0.29..2150.30 rows=10)
  ->  Index Scan using idx_users_id on users  (rows=1)
  ->  Index Scan using idx_orders_user_id on orders  (rows=10)

1.2 Hash Join – 中等数据集的王者

原理：先构建一个表的哈希表（通常在内存中），然后扫描另一个表探测。 代价：cost = build_cost + probe_cost ≈ O(outer + inner)（无索引也可）。 适用场景：

• 两表较大，但连接键无索引或索引选择性不佳。
• 等值连接（= 或 IN）。
• 内存足够容纳哈希表（work_mem 参数控制，不足则会 spill 到磁盘，严重降低性能）。

执行计划识别：

Hash Join  (cost=5000..12000 rows=50000)
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o  (rows=1000000)
  ->  Hash  (cost=3000..3000 rows=100000)
        ->  Seq Scan on users u

调优点：

• 增大 work_mem 避免哈希表落盘（监控 temp_files）。
• 调整连接顺序：让行数较少的表作为 build 表（哈希表源）。

1.3 Merge Join – 排序的交换

原理：两表按连接键预先排序，然后像合并有序数组一样归并。 代价：cost = sort_cost(outer) + sort_cost(inner) + merge_cost。 适用场景：

• 连接键本身有序（例如已有索引，或子查询带 ORDER BY）。
• 连接条件为不等式（<, > 等）。
• 数据量非常大且 Hash Join 因内存不足会频繁落盘时。

执行计划识别：

Merge Join  (cost=15000..18000 rows=50000)
  Merge Cond: (o.user_id = u.id)
  ->  Index Scan using idx_orders_user_id on orders  (rows=1000000)
  ->  Sort  (cost=10000..10200 rows=80000)
        Sort Key: u.id
        ->  Seq Scan on users u

注意：如果两表都需要显式排序，额外开销可能超过 Hash Join。

1.4 连接顺序 – 基数的艺术

多表连接时（如 A JOIN B JOIN C），优化器会尝试不同的连接顺序。基数估计的准确性直接影响顺序选择。例如：

SELECT * FROM A JOIN B ON A.x = B.x JOIN C ON B.y = C.y;

• 若优化器认为 A JOIN B 只返回 10 行，会倾向于用那 10 行作为外层去连接 C（Nested Loop）。
• 若实际返回 10 万行，则 Hash Join 可能更优，但执行计划已固定，性能崩溃。

强制顺序的方法：

• SET join_collapse_limit = 1（不推荐，杀死优化器灵活性）。
• 使用 CTE 控制物化顺序（WITH t AS MATERIALIZED (SELECT ...)）或明确 OFFSET 0 技巧。
• 更推荐：修正统计信息，让优化器自动选择正确顺序。

二、统计信息的深层秘密

优化器的一切估计都源自 pg_statistic（视图 pg_stats）。不准确的统计信息是慢查询的首要元凶。

2.1 核心统计字段解读

以 pg_stats 为例（针对表 orders 的 status 列）：

SELECT * FROM pg_stats WHERE tablename = 'orders' AND attname = 'status';

重要：histogram_bounds 默认 100 个桶，可通过 ALTER TABLE SET STATISTICS 提高精度。

2.2 直方图与 MCV 的实战应用

假设查询：SELECT * FROM orders WHERE amount BETWEEN 100 AND 200。

优化器的估算过程：

1. 先看 amount 列的 MCV，若 100~200 区间内包含某些高频值，直接用对应频率。
2. 排除 MCV 后，剩下行的数据分布由 histogram_bounds 描述（例如边界为 [1, 50, 90, 150, 210, 300, …]）。
3. 计算落在 [100,200] 的桶的数量，估算行数 = 总行数 × 比例。

若数据分布不均匀（例如 90% 的行 amount 在 100~200 内，但只有 10% 的行在其他范围），默认直方图可能低估，导致优化器错误选择全表扫描而不是索引。解决办法：提高统计精度。

2.3 提高统计信息质量

命令：

ALTER TABLE orders ALTER COLUMN amount SET STATISTICS 1000;  -- 默认 100，最大 10000
ANALYZE orders;

• 更大的统计目标会增加 ANALYZE 时间和存储，但对关键列值得投入。

查看当前统计目标：

SELECT attname, attstattarget FROM pg_attribute WHERE attrelid = 'orders'::regclass;

2.4 扩展统计信息 – 解决多列相关性

这是 PostgreSQL 10+ 引入的杀手级功能。当查询条件涉及 多个列 并且它们之间存在相关性时，普通单列统计会导致严重误判。

经典问题：表 weather 有 city 和 month 列。查询：

SELECT * FROM weather WHERE city = 'Beijing' AND month = 'July';

• 单列统计：知道 Beijing 占 10% 的行，July 占 8% 的行，假设独立 → 0.8% 行。
• 实际：Beijing 在 July 可能占 Beijing 所有数据的 80%，总比例可能 8%。优化器低估，可能选择 Index Scan 当实际上更需 Seq Scan，或反之导致错误连接顺序。

解决方案 – 创建扩展统计信息：

CREATE STATISTICS stts_city_month ON city, month FROM weather;
ANALYZE weather;

• 这会在 pg_stats_ext 中生成 多列 MCV（最常用组合）和 函数依赖。
• 优化器使用扩展统计后，city = 'Beijing' AND month = 'July' 的行数估算准确。

查看扩展统计：

SELECT * FROM pg_stats_ext WHERE tablename = 'weather';

适用场景：

• 多列 GROUP BY 基数估算。
• 多列条件连接（例如 A.c1 = B.c1 AND A.c2 = B.c2）的基数估算。
• 带有多个过滤条件的 WHERE 子句。

注意：扩展统计不会自动创建，需要 DBA 主动分析查询模式后添加。

三、实战案例：连接顺序错误 + 统计信息陈旧

场景描述

电商系统，三张表：

-- 订单表 orders：1 亿行，order_date 有索引，status 只有 'paid'/'pending'/'canceled'
-- 用户表 users：1000 万行，id 主键
-- 用户标签表 user_tags：5000 万行，user_id, tag, created_at

慢查询（统计过去 30 天带 ‘VIP’ 标签的用户的已支付订单总额）：

SELECT u.id, u.name, sum(o.amount) AS total
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN user_tags t ON u.id = t.user_id
WHERE o.order_date >= now() - interval '30 days'
  AND o.status = 'paid'
  AND t.tag = 'VIP'
GROUP BY u.id, u.name;

原始执行时间：150 秒。

执行计划分析（简化）

HashAggregate  (actual time=150000ms)
  ->  Nested Loop  (actual rows=500 loops=1)
        ->  Nested Loop  (actual rows=50000)
              ->  Seq Scan on user_tags t  (rows=5000000)    Filter: tag = 'VIP'
              ->  Index Scan on users u (rows=1)
        ->  Index Scan on orders o  (rows=10) 
              Index Cond: (user_id = u.id)
              Filter: (order_date >= ...) AND (status = 'paid')

问题诊断：

1. 驱动表错误：优化器以 user_tags 作为最外层表，全表扫描所有 tag=’VIP’ 的行（500 万行）。实际上 tag=’VIP’ 并不多（50 万行），但因统计信息未及时更新，n_distinct 估算错误。
2. 连接顺序：5 万行与 orders 连接时，orders 使用 Index Scan，但因每个用户在过去 30 天有多个订单，导致回表次数过多。
3. 统计问题：user_tags 表的 tag 列 most_common_freqs 未包含 ‘VIP’（因为最近业务调整，VIP 比例从 5% 升至 20%，但未重新分析）。

优化步骤

Step 1：更新统计信息

ANALYZE user_tags;

再次执行，优化器发现 tag=’VIP’ 占 20%，选择其他连接顺序。

新计划（简化）：

Hash Join  (actual time=8000ms)
  Hash Cond: (o.user_id = u.id)
  ->  Index Scan using idx_orders_date_status on orders (rows=2000000)
        Index Cond: (order_date >= ...)
        Filter: (status = 'paid')
  ->  Hash
        ->  Hash Join (actual rows=2000000)
              Hash Cond: (t.user_id = u.id)
              ->  Index Scan using idx_user_tags_tag on user_tags (rows=500000)
                    Index Cond: (tag = 'VIP')
              ->  Hash (rows=10000000) -> Seq Scan on users

时间降至 8 秒，但仍可优化。

Step 2：索引优化

目前的 orders 需要同时过滤 order_date 和 status，现有单列索引不合理。创建复合索引：

CREATE INDEX idx_orders_date_status ON orders(order_date, status) INCLUDE (user_id, amount);
-- INCLUDE 使得 Index Only Scan 可直接获得 user_id 和 amount，无需回表

Step 3：重写查询，使用 CTE 先过滤 order 和 tag

WITH paid_orders AS MATERIALIZED (
  SELECT user_id, sum(amount) AS total
  FROM orders
  WHERE order_date >= now() - interval '30 days' AND status = 'paid'
  GROUP BY user_id
), vip_users AS MATERIALIZED (
  SELECT DISTINCT user_id FROM user_tags WHERE tag = 'VIP'
)
SELECT u.id, u.name, po.total
FROM users u
JOIN vip_users vu ON u.id = vu.user_id
JOIN paid_orders po ON u.id = po.user_id;

• MATERIALIZED 强制 CTE 物化，避免重复扫描（每个 CTE 只被使用一次，但物化后数据量不大）。
• 物化后分别建立哈希表，最终 Hash Join。

执行时间 1.2 秒。

Step 4：扩展统计信息的应用

注意到 orders 表上 order_date 和 status 有相关性：过去 30 天的订单中 95% 是 paid，而更早的订单 paid 比例较低。这导致优化器可能低估 paid 订单数量。创建扩展统计：

CREATE STATISTICS stts_order_date_status ON order_date, status FROM orders;
ANALYZE orders;

此后优化器在处理 WHERE order_date >= ... AND status = 'paid' 时能更准确估算行数，避免了因低估而错误选择 Nested Loop。

最终查询稳定在 300 毫秒 左右。

四、手把手诊断流程

当你遇到慢查询时，按以下步骤操作：

1. 获取真实执行计划 EXPLAIN (ANALYZE, BUFFERS, TIMING) <query>; 关注实际行数（actual rows）与估算行数（rows）的差异。
2. 检查统计信息新鲜度
   • pg_stat_user_tables 中的 last_analyze 时间。
   • 如果表有大量变更后未 analyze，执行 ANALYZE 再次测试。
3. 对比估算 vs 实际
   • 对于过滤条件，检查 pg_stats 中对应列的 most_common_freqs 和 histogram_bounds 是否合理。
   • 若多列条件误估，考虑创建扩展统计。
4. 检查连接算法是否合理
   • Nested Loop 外层行数是否真的很小？
   • Hash Join 是否因 work_mem 不足而落盘？查看执行计划中的 Disk: xxx blocks。
   • Merge Join 的排序代价是否过高？
5. 索引审计
   • 连接键、过滤条件、排序列是否都有合适索引？
   • 是否可以利用覆盖索引（Index Only Scan）减少回表？
6. 改写 SQL
   • 使用 CTE 调整连接顺序（物化关键中间结果）。
   • 使用窗口函数替代自连接/子查询（参考第二期）。

五、调优参数速查表

注意：修改 work_mem 是针对每个操作（节点），不是整个查询，可适当调高但注意总内存消耗。

六、总结与下期预告

本期要点

• 理解了三种连接算法的代价公式与适用场景，掌握了识别执行计划中的连接节点。
• 深入统计信息的内部结构（MCV、直方图、相关度）以及如何通过 ALTER STATISTICS 提高精度。
• 学习了扩展统计信息（多列 MCV、函数依赖）解决多列相关性的基数误判。
• 通过真实案例，演练了从统计信息刷新、索引创建、SQL 改写，到最终性能提升 500 倍的全过程。

下期预告

第四期：物化视图、分区表与批量数据优化

• 物化视图：何时以及如何刷新（REFRESH MATERIALIZED VIEW CONCURRENTLY），增量刷新技巧。
• 分区表（声明式分区）：分区裁剪、分区索引、与 BRIN 索引的联合使用。
• 批量数据处理：INSERT ... ON CONFLICT、COPY 优化、DELETE 与 TRUNCATE 的选择。
• 实战：千万级日志表的日分区 + 物化视图预聚合，查询从 30 秒到 0.1 秒。

欢迎在评论区分享你遇到的连接性能或统计信息相关的疑难案例，我们会在后续文章中选典型案例进行匿名诊断。

理解优化器的统计世界，你就掌握了性能调优的主动权。 我们第四期见。