PgSQL · 源码分析 · PG优化器浅析

在使用PostgreSQL数据库过程中，对SQL调优最常用的手段是使用explain查看执行计划，很多时候我们只关注了执行计划的结果而未深入了解执行计划是如何生成的。优化器作为数据库核心功能之一，也是数据库的“大脑”，理解优化器将有助于我们更好地优化SQL，下面将会为大家解开PostgreSQL优化器神秘的面纱。

在PG数据库中，对于DDL语句无需进行优化，到utility模块处理，对于DML语句需要到优化器中处理，一个用户连接从接收SQL到执行的流程如下:

主要目的是为了消除view、rule等，如下示例，视图v_t_1_2在执行计划里面已经被t1、t2替换。

目标是将IN和exists子句递归提升。
select * from t1 where t1.a1 in (select t2.a2 from t2 where t2.b2=10); 假设t2.a2为unique
转化为:
select t1.a1,t1,a2 from t1 join t2 where t1.a1=t2.a2 and t2.b2=10;

in子链接执行计划如下：

explain select * from t1 where exists (select t2.a2 from t2 where t2.a2=t1.a1) ; 假设t2.a2为unique
转化为:
select t1.a1, t1.b1 from t1, t2 where t1.a1=t2.a1;

exists子链接执行计划如下：

子查询和子链接区别：子查询不在表达式中子句，子链接在in/exists表达式中的子句。
select * from t1, (select * from t2) as c where t1.a1=c.a2;
转化为：
select * from t1, t2 where t1.a1=t2.a2;

并不是所有的子查询都能提升，含有集合操作、聚合操作、sort/limit/with/group、易失函数、from为空等是不支持提升的。
如下：

包含逻辑推理、表达式计算等

以left join为例，left join(左连接) 返回包括左表中的所有记录和右表中连接字段相等的记录 ，如果右表没有匹配的记录，那么右表将会以NULL值代替，例如：

存在外连接left join

消除外连接需要where和join条件保证右表不会有NULL值的行产生。

条件下推的目的为了连接前，元组数组尽量少，如下示例，条件已经下推到每个表上面了。

当表中字段存在约束键时，PostgreSQL将会对其进行语义优化，因为查询条件有可能已经隐含满足或者不满足，例如：

表tt1的id字段已经隐含了不为NULL，所以id=null这种条件可以直接返回false，PostgreSQL数据库默认并没有开启约束优化，需要设置constraint_exclusion这个参数。

min/max函数在应用的使用中是非常广泛的，数据库有必要对其进行特殊优化，比如索引中已经将数据排好序了，最大最小值可以直接获取到，所以PostgreSQL对min/max函数做了一步转化。
select min(a1) from t1 转化为 select a1 from t1 order by a1 limit 1;
如果a1没有索引，那么将会是顺序扫描，不进行转化。

如果不对group by优化，那么将会需要对结果进行Sort或者Hash，但是如果表中数据已经是排序好的，那么将可以对其进行优化。

1. 利用索引消除order by

2. order by下推，利用merge join实现更快的连接

类似于group by优化，distinct将会从Sort和Hash中选择最优的，如果字段中有索引，Sort代价可能会更低。

集合操作union被转换成Append方式。

以上介绍了几种常见的PostgreSQL优化器对SQL优化的方法，这些方法更着重于SQL逻辑优化，也就是尽量对SQL进行等价或者推倒变换，以达到更有效率的执行计划。PostgreSQL优化器原理远不止这些，比如表的扫描方式选择、多表组合方式、多表组合顺序等，这些内容将会在后续的月报中继续呈现。