PostgreSQL JOIN limit 优化器 成本计算 改进 – mergejoin startup cost 优化

PostgreSQL limit N的成本估算，是通过计算总成本A，以及估算得到的总记录数B得到：

对于单表查询，这种方法通常来说比较适用，但是如果数据分布有倾斜，实际上也并不一定适用，例如以下两种情况：

1、符合条件的数据占总记录数的50%，但是全部分布在表的末尾，那么limit 10000 条到底是走索引快还是走全表扫描快呢？

2、符合条件的数据占总记录数的50%，全部分布在表的头部，那么LIMIT 10000 条，肯定是全表扫描快了。

对于JOIN的情况，同样有类似的问题：

比如JOIN并且带条件时，LIMIT N，是走嵌套循环快，还是走MERGE 或 HASH JOIN快？

1、嵌套循环+where+LIMIT的成本计算方法，可以使用LIMIT占总估算记录数占比的方法得到，还算是比较合理。

2、MERGE JOIN+where+LIMIT的成本计算方法，必须考虑启动成本，例如WHERE条件在A表上（可以走索引直接从条件位置开始扫描），B表则需要从索引的开头开始扫描（到与A表的索引匹配时，也许需要扫描很多的索引ENTRY，这个启动成本可能会很高），启动成本，加上LIM免费云主机域名IT条数在剩余的所有成本中的一个占比，得到的成本是一个比较合理的成本。

3、hash join+where+limit的成本计算方法，使用启动成本+LIMIT占总估算记录数占比的方法得到，优化器目前就是这么做的，比较合理。

然而，对于MERGE JOIN，目前在使用LIMIT时，PG没有加上这个启动成本，使得最后得到的执行计划可能不准确。

改进方法建议可以加入merge join启动成本。

1、建表如下：

2、查询SQL如下：

该语句中表结构比较特殊，两个关联字段都是主键，并且存在外键约束关系，查询计划如下：

从执行计划上可以看出，根据test2表先查询出满足条件的10条记录，然后和test1表采用mergejoin关联，由于在估算的时候没有考虑到limit的影响，估算的行数非常大，是498340行，

实际采用nestloop效果会更好（关闭掉seqscan和megejoin）

但是从评估的成本来看，merge join+limit 比 nestloop+limit更低，原因是nestloop的总成本更高(189339 比 7929)。所以优化器根据比例算法（未参照merge join的启动成本），认为在LIMIT的情况下，也是merge join成本更低。

实际情况是，MERGE JOIN的没带查询条件的B表，需要从索引的头部开始扫，而不是从指定位置开始扫。 因此实际情况是merge join是更慢的。

目前优化器使用hash join时，已经算上了startup cost，例子

针对test1表，需要估算a

所以，索引扫描test1（where a > 500000）的merge join启动成本应该有 1702，加上这个成本后，成本远大于NEST LOOP JOIN的成本，所以不会选择merge join。

查询SQL如下：

Oracle 选择了nestloop join。

使用HINT，让Oracle使用merge join，看看成本是多少，是否与修正PostgreSQL merge join启动成本接近。

1、PostgreSQL 在计算merge join+limit的成本时，优化器有优化的空间，可以考虑把启动成本算进来，提高优化器选择带limit输出的SQL的JOIN方法的正确性。

2、如果是inner join，通过query rewrite可以对merge join进行优化，跳过不符合条件的头部INDEX SCAN。

《PostgreSQL 优化器案例之 – order by limit 索引选择问题》

src/backend/optimizer/path/costsize.c

原文地址：https://github.com/digoal/blog/blob/master/201810/20181004_03.md

相关推荐: oracle字段长度引起的思考 length()和lengthb()

‘小X，问个问题啊，我这里想往一个表里插入另一张表的值。都是vachar2()字段,用length()看，原表那些值的长度都在20以下，目标表是varchar2(22)，怎么会报错插入失败,超出最大值呢？‘主任，是同一个库吗？用什么判断值的长度呢？’‘是同一个…