ANBOB™

在oracle多表关连中有SQL hint可以干预CBO产生的不合理的表join顺序，如ordered, leading等，但PostgreSQL和部分基于PG国产数据库如Highgo V9.5， 目前也还不支持SQL hint。当遇到SQL性能问题，明确某个join 顺序更好时，如何影响PG数据库优化器执行指定的执行计划呢？如果您真的需要SQL hint，在pg中可以安装 pg_hint_plan 扩展，但目前应该是因为highgo的oracle和pg的双兼容模式，如果实现pg_hint_plan在解析器上隔离上要复杂了些，所以暂未实现，又或者不想安装第三方扩展，在这种情况下，记录几个可以强制执行join ordered的替代方法。

PostgreSQL optimizer的查询重写或执行计划有时并不会按SQL编写的顺序执行，会对查询进行相当大的重新排列，会按照它认为最好的方式重新排列 Join Order，优化器找到的执行查询的方式越多，找到最快执行计划的机会就越大。要想干涉执行计划就要相办法阻止 PostgreSQL 重新排列。

1，使用OFFSET 0强制连接顺序

如同oracle中的rownum一样，增加一些不影响结果的列或选项，阻止子查询展开或merge，在POSTGRSQL中使用 OFFSET or LIMIT 在子查询中. 如PG自动选择join order 为a b c, 我们希望 b c a。

SELECT subq.b_id, a.value
FROM a JOIN
   (SELECT a_id, b.b_id, c.c_id
    FROM b
       JOIN c USING (a_id)
    WHERE c.c_id < 300
    OFFSET 0
   ) AS subq
      USING (a_id);

2, 使用公用表表达式 （CTE） 强制连接顺序

和上面的子查询差不多，可以像oracle中的materialze hint一样 雾化一个子查询.  在 PostgreSQL v12 之前，CTE 自动成为优化器屏障。从 v12 开始，PostgreSQL 可以将 CTE 拉取到主查询中，您必须使用MATERIALIZED关键字来防止这种情况

 WITH subq AS MATERIALIZED (
   SELECT a_id, b.b_id, c.c_id
   FROM b
      JOIN c USING (a_id)
   WHERE c.c_id < 300
)
SELECT subq.b_id, a.value
FROM a
   JOIN subq USING (a_id);

3,  使用join_collapse_limit = 1

优化器会重新排列查询的联接顺序,  PostgreSQL 对于两个表的内部连接，通常有七种选择。表越多，选项的数量就越多， 虽然尝试的执行计划越多，越能找到最优执行计划，但是SQL解析时间就更长，更何况在PG中SQL游标无法共享， 当大量的表JOIN时，为了提升SQL 解析时间，可以通过参数来减少join order限制次数。

$ psql -c 'show all;'|grep join_coll
 join_collapse_limit |                  8                 | Sets the FROM-list size beyond which JOIN constructs are not flattened.

优化器将仅考虑前 8 个表的所有可能组合, parameters join_collapse_limit 适用于a join b样式 和from_collapse_limit 适用于 from a,b 样式。 默认情况下，此变量的设置与from_collapse_limit相同，这适用于大多数用途。如果表数量超过了该参数PostgreSQL 使用完全不同的方法，它随机生成一多个查询计划，并通过在几个中重新组合最有希望的计划来进行进化。这种遗传查询优化器可能会导致不确定的查询计划（如果表Join 超过该参数优化器不佳时，可以考虑增加该参数），可以选择暂时将此变量设置为 1，然后明确指定他们想要的连接顺序，告诉优化器不要在 SQL 语句中重新排列连接顺序。如同oracle ordered hint.

SET join_collapse_limit = 1;
SELECT b.b_id, a.value
FROM b
   JOIN c USING (a_id)
   JOIN a USING (a_id)
WHERE c.c_id < 300;

在opengauss系的某些国产库也增强除了参数外，还增加了SQL HINT级参数，如/*+set（join_collapse_limit 40）*/， 如同oracle 的opt_parm hint. 当然国产库也好多实现了类似oracle leading hint更灵活的hint。 在参数上也有一些限制，如果参数是3， select t1  join t2 join t3 join t4 join t5 在一个joinlist是 t1 ,t2 t3, 可以自由换order顺序， t4,t5 自由换order顺序. 但不允许 t1和t4互换。由于参数from_collapse_limit和join_callapse_limit是基于逻辑优化后的连接树进行处理的，此时还没有考虑连接的代价因素，大体还保持初始时的连接树；所以连接语句中的()对参数from_collapse_limit和join_callapse_limit产生的连接段是有影响的；而最终会影响后续代价估算及产生计划中连接顺序的选择.

扩展: oracle 的join 优化器评估上限数

在oracle中， 参数optimizer_search_limit 优化器用来评估的最大的连接组合数量，如有五个表连接的查询将有120（5! = 5 * 4 * 3 * 2 * 1 = 120）种可能的连接组合， 参数OPTIMIZER_MAX_PERMUTATIONS控制 CBO在为 带有连接的 SQL 语句生成执行计划时考虑的最大排列数每个查询块。 值的范围是 4 到 80000。值 80000 表示没有限制。 将此参数设置为小于 1000 的值通常可确保解析时间在几 秒钟或更短。一般来说，设置非常高的 OPTIMIZER_MAX_PERMUTATIONS 值可能会导致优化器花费更多时间来解析每个SQL 语句。如果您有一个 OLTP 应用程序，这很容易导致Oracle 花费更多时间来解析语句。调小OPTIMIZER_MAX_PERMUTATIONS 参数可用于减少 连接大量表的复杂 SQL 语句的解析时间 。但是， 降低其值可能会导致优化器错过 最佳连接排列。目前不是很明确这两个参数的组合方式。

Tanelpoder

      I remember the old DSI optimizer material saying that _optimizer_search_limit = 5 means that CBO will evaluate up to 5 factorial (120) join permutations which may include cartesian joins and from then onwards only non-cartesian join orders (up to optimizer_max_permutations) per query block.

sys@ORA19C > @pd max_permutations
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
      3795   ED3 _optimizer_max_permutations                              2000                           optimizer maximum join permutations per query block

Elapsed: 00:00:00.00
sys@ORA19C _DATE> @pd search_lim
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
      3963   F7B _optimizer_search_limit                                  5                              optimizer search limit

但是像JonathanLewis的SQL baseline的案例8个表join 产生了5040个join 顺序，超过了_optimizer_max_permutations默认参数。在使用sql baseline无法减少尝试次数，这是预期行为，以防它可以生成比基线规定的计划更好的计划。 而sql profile可以可以使用leading hint提示只检查1个join order. 这也是在优化解析时间时sql profile 与sql baseline的区别。

References
https://www.cybertec-postgresql.com/en/forcing-a-join-order-in-postgresql/

打赏

微信扫一扫，打赏作者吧～