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

查询优化器内部机制与高级调参

前四期我们构建了完整的优化知识体系：执行计划、统计信息、连接算法、高级 SQL、物化视图与分区表。 本期我们钻进优化器的“黑盒”，剖析代价模型、参数开关、并行查询，甚至学会强制执行计划——当你比优化器更懂数据时，这些能力会让你如虎添翼。

一、优化器的工作流程回顾

PostgreSQL 优化器基于代价选择执行计划。流程如下：

1. 解析与重写：SQL → 解析树 → 基于规则的重写（视图展开、常量折叠）。
2. 生成路径：为每个表考虑不同的扫描路径（Seq Scan、Index Scan、Bitmap Scan），为每个 JOIN 考虑不同的连接方法（Nested Loop、Hash Join、Merge Join）和连接顺序。
3. 代价估算：利用统计信息和代价参数计算每个路径的启动成本（获取第一行的成本）和总成本。
4. 选择最优路径：选出成本最低的执行计划（默认是最低总成本，可以通过 cursor_tuple_fraction 调整偏好）。

优化器并不完美，因为它依赖于：

• 统计信息的准确性（第一期、第三期）。
• 代价参数的合理性（本期重点）。
• 查询复杂度限制（from_collapse_limit 等，避免指数爆炸）。

二、代价模型参数详解

代价公式（简化）： 总成本 = seq_page_cost <em> 顺序页数 + random_page_cost </em> 随机页数 + cpu_tuple_cost <em> 处理的元组数 + cpu_index_tuple_cost </em> 索引元组数 + cpu_operator_cost * 操作次数

这些参数定义在 postgresql.conf 中，可以在会话级动态修改。

2.1 核心 I/O 参数

原理：当 random_page_cost 远大于 seq_page_cost 时，优化器会偏向 Seq Scan；当两者接近时，Index Scan 更容易被选中。 实例：某系统用 SSD，但未调整 random_page_cost，许多本应走索引的查询走了全表扫描，导致响应时间从 50ms 飙升到 3s。调整到 1.1 后，计划恢复正常。

-- 会话级调整（测试后可以写入配置文件）
SET random_page_cost = 1.1;

2.2 CPU 相关参数

通常保持默认值。在极端 OLAP 场景（大量计算），可以适当调高 cpu_tuple_cost 和 cpu_operator_cost 来反映真实负载。

2.3 连接顺序与搜索限制

当查询涉及超过 8 张表时，优化器会放弃穷举所有连接顺序，采用贪心或启发式算法。对于极端复杂的查询，提高这两个值可以找到更好的计划，但会增加规划时间。

三、enable_* 开关 – 手动干预优化器

当优化器做出错误选择（例如应该用 Hash Join 却用了 Nested Loop，并且无法通过统计信息修正），你可以临时禁用某种扫描或连接方法，强迫优化器选择正确路径。

所有 enable_* 参数默认为 on，可以在会话级或事务级修改。

案例：强迫使用 Hash Join

一个查询中，优化器错误地选择了 Nested Loop（内层表很大且无索引），导致执行 30 分钟。我们已知 Hash Join 可秒出结果。

BEGIN;
SET enable_nestloop = off;
SET enable_mergejoin = off;
-- 执行查询
SELECT ...;
COMMIT; -- 或者 RESET 恢复

注意：这仅是临时手段，持久化解决方案应该是修正统计信息或索引。但在生产应急时非常有效。

四、使用 pg_hint_plan 强制执行计划

当 enable_* 开关太粗暴（比如想指定连接顺序但不想禁用所有其他可能性），可以使用第三方扩展 pg_hint_plan。它允许在 SQL 注释中嵌入提示，精确控制扫描方式、连接方法、连接顺序和并行度。

4.1 安装与启用

# 从 PostgreSQL 官方 yum/apt 源安装
yum install postgresql13-pg_hint_plan

在 postgresql.conf 中添加：

shared_preload_libraries = 'pg_hint_plan'

重启数据库后，在会话中启用：

LOAD 'pg_hint_plan';
SET pg_hint_plan.enable_hint = on;

4.2 常用提示语法

/*+
   SeqScan(t1)                     -- 强制 t1 走顺序扫描
   IndexScan(t2 idx_t2_col)        -- 强制 t2 使用指定索引
   BitmapScan(t3)                  -- 强制 t3 走位图扫描
   NestLoop(t1 t2)                 -- 指定连接顺序和方法
   HashJoin(t2 t3)
   Leading(t1 t2 t3)               -- 指定连接顺序（t1 join t2 再 join t3）
   Parallel(t1 4)                  -- 指定表 t1 的并行度
*/
SELECT ...

4.3 实战案例：修复错误连接顺序

原始 SQL（查询某区域近 30 天活跃用户的订单总额）：

SELECT u.id, sum(o.amount)
FROM users u
JOIN regions r ON u.region_id = r.id
JOIN orders o ON u.id = o.user_id
WHERE r.name = 'East'
  AND o.order_date > now() - interval '30 days'
GROUP BY u.id;

优化器错误地先连接 orders 和 users（产生大量中间结果），然后再过滤 region。使用提示强制执行：

/*+
   Leading(r u o)
   HashJoin(r u)
   HashJoin(u o)
   SeqScan(r)
   IndexScan(u idx_users_region)
   IndexScan(o idx_orders_date)
*/
SELECT ...

计划变为：先过滤 regions（只有几行），再与 users 索引连接，最后与 orders 索引连接。耗时从 45 秒降到 2 秒。

重要：pg_hint_plan 是强大的后援，但不要滥用。应优先通过索引、统计信息、参数调优解决问题，提示只用于优化器确实“犯傻”的极端场景。

五、并行查询 – 多核时代的选择

PostgreSQL 9.6 引入并行查询，10+ 持续增强。适当利用并行可以大幅降低复杂查询的响应时间。

5.1 并行查询的条件

• 表大小超过 min_parallel_table_scan_size（默认 8MB）。
• 查询计划中至少有一个可以并行的节点（Seq Scan、Partial Aggregate、Hash Join 等）。
• 并行度受 max_parallel_workers_per_gather 限制。

5.2 关键参数

调高 max_parallel_workers_per_gather 可以加快大表聚合，但会消耗更多 CPU 和内存。

5.3 并行查询的执行计划

对于查询：

SELECT count(*) FROM orders WHERE order_date > '2025-01-01';

并行计划示例：

Finalize Aggregate  (actual time=1200..1200 rows=1)
  ->  Gather  (actual time=200..1198 rows=4 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (actual time=950..950 rows=1)
              ->  Parallel Seq Scan on orders  (actual time=0.3..800 rows=500000)
                    Filter: (order_date > '2025-01-01')

• Gather：合并多个工作进程的结果。
• Partial Aggregate：每个工作进程执行部分聚合。
• Parallel Seq Scan：每个进程扫描表的一部分（通过 block range）。

5.4 分区表并行

PostgreSQL 11+ 支持并行 Append（同时扫描多个分区）。

SET enable_parallel_append = on; -- 默认 on

执行计划中会出现 Parallel Append 节点，多个分区被同时扫描。

5.5 并行查询的陷阱

六、实战案例：优化器参数误调导致全表扫描

背景

某系统管理员将所有 enable_* 开关均保持默认，但将 random_page_cost 调低到 0.1（认为 SSD 很快）。 结果：优化器对所有查询都倾向于使用索引扫描，甚至当索引选择性很差（例如列上有 90% 重复值）时，仍然选择 Index Scan 而非 Seq Scan，导致大量随机 I/O，性能反而下降。

监控发现一个查询：

SELECT * FROM event_log WHERE event_type = 'click' AND created_at > '2025-01-01';

event_type 列上有一个索引，但 ‘click’ 占所有事件的 80%。本应走 Seq Scan + 过滤（因为要读取大部分数据），但优化器选择了 Index Scan，回表次数惊人，耗时 12 秒。

解决

使用 EXPLAIN (ANALYZE, BUFFERS) 发现实际读取的堆页面数与全表扫描几乎相同，但多了随机 I/O。

恢复方案：

SET random_page_cost = 1.1;   -- 从 0.1 改回合理值

之后计划变为 Seq Scan + Filter，耗时 3 秒（因为 SSD 顺序读快，且避免了大量随机回表）。

教训：盲目调低 random_page_cost 可能适得其反。普通的 SSD 设为 1.1 ~ 1.5 即可；真正内存级别的极致性能才考虑 1.0。

七、诊断工具与流程

7.1 查看当前参数

SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name LIKE '%cost%' OR name LIKE 'enable_%' OR name LIKE '%parallel%' OR name LIKE '%collapse%'
ORDER BY name;

7.2 启用查询规划时间日志

SET log_planner_stats = on;
-- 执行查询后，在日志中会看到规划时间

7.3 分析优化器的选择

使用 EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE) 后，对照实际执行时间与成本估算。重点关注：

• 成本估算是否符合实际耗时（相差不大说明参数合理）。
• 实际行数与估算行数差异（统计信息问题）。
• 是否有 Sort 节点在内存不足时落盘（work_mem 问题）。

7.4 系统性调优流程

1. 基准测试：收集慢查询。
2. 分析执行计划：识别扫描类型、连接方法、并行度、排序操作。
3. 修正统计信息：ANALYZE，必要时增大统计目标或创建扩展统计。
4. 调整索引：创建、删除、修改索引。
5. 微调成本参数：特别是 random_page_cost 和 effective_cache_size。
6. 针对无法修改 SQL 的场景：使用 pg_hint_plan。
7. 考虑并行：为大型报表查询增加并行度。
8. 验证与监控：确保改进生效且没有负作用。

八、总结与下期预告

本期要点

• 理解了优化器的代价模型公式以及 seq_page_cost、random_page_cost、effective_cache_size 如何影响执行计划选择。
• 学会了使用 enable_* 开关临时干预优化器行为。
• 掌握了 pg_hint_plan 对复杂查询强制执行计划的方法。
• 理解了并行查询的原理、参数调优以及常见陷阱。
• 通过一个因 random_page_cost 设置过低导致索引误用的案例，生动展示了参数调优的边界。

下期预告

第六期：锁与并发控制 – 查询优化的另一维度

• 事务隔离级别与 MVCC 对查询性能的影响。
• 锁等待分析：pg_locks、pg_stat_activity，定位阻塞源头。
• 死锁检测与预防。
• 长事务与表膨胀的关系，以及 autovacuum 调优。
• 优化高并发写入场景：热点行更新、使用 FOR UPDATE SKIP LOCKED 实现队列。
• 实战案例：行锁冲突导致吞吐量下降 90% 的诊断与修复。

欢迎分享你在优化器参数调优或并行查询中遇到的有趣案例，我们会在后续内容中选取分析。

真正的优化高手，既尊重优化器的判断，也懂得在关键时刻帮它一把。 我们第六期见。