实用编译项：OPTIMIZER_DEBUG

前言

在 PostgreSQL 中，一般遇到慢 SQL 都是三板斧

1. 观察执行计划
2. 分析高消耗算子
3. 调整与改写相关 SQL

由于 PostgreSQL 并不支持原生的 hint 功能，因此我们都是手动调整诸如 enable_xxx 相关参数，对比成本再去分析为何优化器走了"糟糕"的执行计划，费时费力，那么有没有其他便捷的方式呢？没错，还真有！OPTIMIZER_DEBUG

OPTIMIZER_DEBUG

前阵子其实我就看到了这一编译选项，其作用是 print other candidate plans，由于 PostgreSQL 也是 CBO 模型，会在众多执行计划中选择出代价最低的一条进行执行，但是我们通过 explain 观察到的执行计划是优化器已经筛选出来的 cheapest plan，而 OPTIMIZER_DEBUG 的作用则是将其它潜在的候选执行计划打印出来，方便我们分析，代码位于 planner.c 中：

/*****************************************************************************
 *   DEBUG SUPPORT
 *****************************************************************************/
#ifdef OPTIMIZER_DEBUG
static void
print_relids(PlannerInfo *root, Relids relids)
{
 int   x;
 bool  first = true;
 x = -1;
 while ((x = bms_next_member(relids, x)) >= 0)
 {
  if (!first)
   printf(" ");
  if (x < root->simple_rel_array_size &&
   root->simple_rte_array[x])
   printf("%s", root->simple_rte_array[x]->eref->aliasname);
  else
   printf("%d", x);
  first = false;
 }
}
...
...

可以看到，在代码中会将这些信息打印到日志中。那让我们小试牛刀一下 ~

[postgres@xiongcc log]$ pg_config | grep CONFIGURE
CONFIGURE =  '--prefix=/usr/pgsql-16' '--enable-debug' 'CFLAGS=-ggdb -O0 -D OPTIMIZER_DEBUG=1' '--without-icu' '--enable-dtrace'

假设现在有这么一条 SQL，优化器选择了索引扫描，让我们执行一下

postgres=# \d test1
               Table "public.test1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 info   | text    |           |          | 
Indexes:
    "test1_info_idx" btree (info)
postgres=# explain select * from test1 order by info limit 10;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Limit  (cost=0.29..0.77 rows=10 width=9)
   ->  Index Scan using test1_info_idx on test1  (cost=0.29..486.28 rows=10000 width=9)
(2 rows)
postgres=# explain analyze select * from test1 order by info limit 10;
                                                            QUERY PLAN                                                     
        ---------------------------------------------------------------------------------------------------------------------------
--------
 Limit  (cost=0.29..0.77 rows=10 width=9) (actual time=0.031..0.046 rows=10 loops=1)
   ->  Index Scan using test1_info_idx on test1  (cost=0.29..486.28 rows=10000 width=9) (actual time=0.030..0.044 rows=10 l
oops=1)
 Planning Time: 0.126 ms
 Execution Time: 0.064 ms
(4 rows)

同时观察日志

RELOPTINFO (test1): rows=10000 width=9
        path list:
        SeqScan(test1) rows=10000 cost=0.00..155.00
        IdxScan(test1) rows=10000 cost=0.29..486.28
          pathkeys: ((test1.info))
        cheapest parameterized paths:
        SeqScan(test1) rows=10000 cost=0.00..155.00
        cheapest startup path:
        SeqScan(test1) rows=10000 cost=0.00..155.00
        cheapest total path:
        SeqScan(test1) rows=10000 cost=0.00..155.00

一目了然，并且通过这个例子可以发现，PostgreSQL 采用动态规划的方法来实现路径的搜索，一种自底向上的方法，之前我也说过：首先全表扫描肯定可以，然后去获取统计信息估算出相应的代价；现在 info 列有索引，那么再去估算索引扫描的代价；先为基表确定扫描路径，估计扫描路径的代价和大小。

也就是说会先建立筛选扫描路径，然后用筛选后的扫描路径再去形成连接路径。但是在筛选扫描路径的时候，是不知道它的上层有没有 LIMIT 的，这便是启动代价的作用，假如 A> 1 的选择率高的话会选择顺序扫描，索引扫描的总成本由于随机 IO，会很高，但是假如有了 limit 1，就无需扫描完全部数据即可返回，但是 SeqScan + Sort 的话就不一样了，必须要先全部扫描完排序之后才可以。

执行路径1：LIMIT 1

-> SORT(a)

-> SeqScan WHERE A > 1;

执行路径2：LIMIT 1

-> IndexScan WHERE A > 1;

这个打印的结果也证实了这一点，优化器并不知道上层是 LIMIT 1。

小结

简而言之，该编译选项会打出一些潜在的计划，不多对于 join 我测了一下，效果并不是太理想，有能力的读者不妨改造一下，将所有 JOIN 的候选计划全部打印出来，方便我们排查。或许这也是为什么社区邮件中说到这个参数许久没用的原因吧，但是聊胜于无，改造一下，变成我们诊断慢 SQL 的瑞士军刀。

参考

PostgreSQL查询优化器详解（物理优化篇）

https://www.postgresql.org/message-id/flat/CAApHDvoMLP3ZpAE4_zivkT1GJU293BT7Yy-Kmo-kxU%2BNc_-EWg%40mail.gmail.com#0bb04d01305df841719ea5b14f97d127

 

推荐阅读

📙 PostgreSQL优化器解析

📙 深入剖析PostgreSQL优化器

Feel free to contact me 

• 微信公众号：PostgreSQL学徒
• Github：https://github.com/xiongcccc
• 微信：_xiongcc
  
• 知乎：xiongcc
• 墨天轮：https://www.modb.pro/u/39588