## 背景知识
数据库优化器需要具备逻辑推理能力，而且越强越好，为什么呢？
举一些例子，
通过已知的一个人讲的是真话，推理另一个人讲的一定是真话或一定是假话。
例子1：
假设预先提供了 a > 10 是真话
可以推理出 a < 1 一定是假话
例子2：
假设预先提供了 a > 10 是真话
无法推理出 a < 100 一定是真话或假话
例子3：
假设预先提供了 a 是空 是真话
可以推理出 a 不是空 一定是假话
例子4：
假设预先提供了 a <>100 是真话
可以推理出 a =100 一定是假话
例子5：
假设预先提供了 a >100 是真话
可以推理出 a >1 一定是真话
例子6：
假设预先提供了 a 的坐标位置在中国 是真话
可以推理出 a 的坐标位置在浙江杭州 一定是真话
例子7：
假设预先提供了 平面中 坐标A和坐标（1,100）的距离小于100 是真话
是否推理出 坐标A和坐标（100,100）的距离小于1000 一定是真话或假话？
总结一下以上逻辑推理，首先要提供已知真假的一个表达式，然后推理另一个表达式的真假。推理可以得出的结论是真、或者假、或者不知道真假。
对于推理出来的结果一定是真或者一定是假的情况，数据库可以利用它来减少后期的处理。
## PostgreSQL 逻辑推理的例子
逻辑推理能力体现在优化器生成查询树之前。例如：
~~~
create table tab(id int check (id >=0), info text, crt_time timestamp);
select * from tab where id<0;
~~~
以上已知为真的表达式是id>=0，通过这个表达式能推理出SQL中给出的表达式 id<0 一定是假。
优化器在执行这条SQL时，可以省去扫描表然后再过滤id<0的行，而是构造结构，并直接返回0条记录。
执行计划如下：
~~~
digoal=# create table ta(id int check (id >=0), info text, crt_time timestamp);
CREATE TABLE
digoal=# explain select * from ta where id=-1;
QUERY PLAN
----------------------------------------------------
Seq Scan on ta (cost=0.00..24.12 rows=6 width=44)
Filter: (id = '-1'::integer)
(2 rows)
~~~
以上查询貌似并没有优化，还是扫描了表，原因是constraint_exclusion参数默认值对UNION ALL和分区表开启这种逻辑推理检查。
将constraint_exclusion 改为ON即可对所有表进行逻辑推理检查。
~~~
digoal=# set constraint_exclusion =on;
SET
digoal=# explain select * from ta where id=-1; -- 现在不需要扫描表了
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)
digoal=# explain select * from ta where id<-1; -- 现在不需要扫描表了
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)
~~~
对于无法推理出一定为假的条件，还是需要扫描表的，例如 id<>0。
~~~
postgres=# explain select * from ta where id<>0;
QUERY PLAN
-------------------------------------------------------
Seq Scan on ta (cost=0.00..24.12 rows=1124 width=44)
Filter: (id <> 0)
(2 rows)
~~~
对于提供的表达式与已知的表达式操作符左侧不一致的，目前PG的优化器没有做到这么智能，例如 id+1<10，id+1<0，优化器不会对这种表达式进行逻辑推理，后面我会在代码中分析这块。
ps: 这里给PG内核爱好者一个题目, 让PG支持以上这种情况的逻辑推理。
~~~
postgres=# explain select * from ta where id+1<10; -- 未推理成功
QUERY PLAN
------------------------------------------------------
Seq Scan on ta (cost=0.00..26.95 rows=377 width=44)
Filter: ((id + 1) < 10)
(2 rows)
postgres=# explain select * from ta where id+1<0; -- 未推理成功
QUERY PLAN
------------------------------------------------------
Seq Scan on ta (cost=0.00..26.95 rows=377 width=44)
Filter: ((id + 1) < 0)
(2 rows)
~~~
id+1<0 是可以转换为 id< 0-1的 ，对于以下表达式，PG进行了推理，原因是-操作符是一个immutable操作符，0-1可以转为常数-1从而可以进行推理。
~~~
postgres=# explain select * from ta where id<0-1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)
~~~
## PostgreSQL 支持哪些逻辑推理
目前PostgreSQL数据库支持哪些逻辑推理呢？
1. 约束中包含的表达式的操作符必须是B-tree-indexable operators(或者is null, or , is not null)，也就是可以被btree索引用于检索操作符，例如,>=以及不能直接被索引使用，但是可以转换为来使用索引)；
2. SQL语句where字句中提供的表达式，同样操作符必须是B-tree-indexable operators；
3. SQL语句where字句中提供的表达式，操作符左侧的操作数必须与约束中的操作数完全一致。
例如约束为(check mod(id,4) = 0)，SQL where字句提供的表达式则必须为 mod(id,4) op? ? 这种形式才会进行推理。
又如约束为(check id*100 > 1000)，SQL where字句提供的表达式则必须为 id*100 op? ? 这种形式才会进行推理。
又如约束为(check id+10 between 1000 and 10000)，SQL where字句提供的表达式则必须为 id+10 op? ? 这种形式才会进行推理。( PostgreSQL 的 between and 会转换为>= and <=，属于B-tree-indexable operators )
又如约束为(check id between 1000 and 10000)，SQL where字句提供的表达式则必须为 id op? ? 这种形式才会进行推理。
重要的事情说三遍，btree, btree, btree。
例子：
约束为is [not] null类型
~~~
postgres=# create table tt1(id int check (id is null));
CREATE TABLE
postgres=# explain select * from tt1 where id=1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)
postgres=# explain select * from tt1 where id is null;
QUERY PLAN
-----------------------------------------------------
Seq Scan on tt1 (cost=0.00..35.50 rows=13 width=4)
Filter: (id IS NULL)
(2 rows)
postgres=# explain select * from tt1 where id is not null;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)
~~~
约束为 mod(id,4) = 0，=为B-tree-indexable operators
~~~
postgres=# create table tt2( id int check(mod(id,4) = 0));
CREATE TABLE
postgres=# explain select * from tt2 where id=1;
QUERY PLAN
-----------------------------------------------------
Seq Scan on tt2 (cost=0.00..41.88 rows=13 width=4)
Filter: (id = 1)
(2 rows)
-- 要让PG进行逻辑推理，WHERE中必须包含mod(id,4)表达式，并且由于mod是immutable函数，mod(1,4)可以转换为常数，因此以下SQL相当于
explain select * from tt2 where mod(id,4)=1 and id=1; 这样才可以被逻辑推理。
postgres=# explain select * from tt2 where mod(id,4)=mod(1,4) and id=1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)
~~~
约束为 id*100 > 1000，>为B-tree-indexable operators
~~~
postgres=# create table tt3( id int check(id*100 > 1000));
CREATE TABLE
postgres=# explain select * from tt3 where id=1;
QUERY PLAN
-----------------------------------------------------
Seq Scan on tt3 (cost=0.00..41.88 rows=13 width=4)
Filter: (id = 1)
(2 rows)
-- 要让PG进行逻辑推理，WHERE中必须包含id*100表达式，并且*是immutable操作符,所以1*100可以替换为常数。从而进行逻辑推理。
postgres=# explain select * from tt3 where id=1 and id*100=1*100;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)
~~~
约束为 id+10 between 1000 and 10000，between and 自动转换为>=和and =或<=是B-tree-indexable operators。
~~~
postgres=# create table tt4( id int check(id+10 between 1000 and 10000));
CREATE TABLE
postgres=# explain select * from tt4 where id=1;
QUERY PLAN
-----------------------------------------------------
Seq Scan on tt4 (cost=0.00..41.88 rows=13 width=4)
Filter: (id = 1)
(2 rows)
postgres=# explain select * from tt4 where id=1 and id+10=1+10; -- +是immutable操作符1+10将转换为11常数。
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)
~~~
约束为 check id between 1000 and 10000
~~~
postgres=# create table tt5( id int check(id between 1000 and 10000));
CREATE TABLE
postgres=# explain select * from tt5 where id=1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)
postgres=# explain select * from tt5 where id+1=1;
QUERY PLAN
-----------------------------------------------------
Seq Scan on tt5 (cost=0.00..48.25 rows=13 width=4)
Filter: ((id + 1) = 1)
(2 rows)
postgres=# explain select * from tt5 where 1=id;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)
postgres=# explain select * from tt5 where 1>id;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)
postgres=# explain select * from tt5 where 1<id;
QUERY PLAN
------------------------------------------------------
Seq Scan on tt5 (cost=0.00..41.88 rows=850 width=4)
Filter: (1 < id)
(2 rows)
~~~
## PostgreSQL 是如何实现逻辑推理的
PostgreSQL数据库是如何实现这些逻辑推理的呢？
上面的例子，都转换成了?1 op ?2，其中 ?1 是一个表达式或字段，?2是一个常数。但是，数据库是怎么通过一个条件的真伪判断另一个条件的真伪呢？还是回到一个例子：
check id > 100，推理 id > 1 是真是假？可以通过比较两个常数来决定，100 >= 1 为真则说明 id>1为真。
为什么要比较这两个常数呢？因为这是优化器排除对表的扫描的一种手段，这时还没有到需要用到id值的阶段。所以此时优化器只能通过常数来推理。
目前PG只实现了对btree索引可以用到的操作符的逻辑推理，使用了两张映射表来描述推理关系。
一张表BT_implic_table 用来推理一定为真，另一张表BT_refute_table 用来推理一定为假。
例如：
已知 ATTR given_op CONST1 为真
如果 CONST2 test_op CONST1 为真
则推理得出 ATTR target_op CONST2 一定为真
其中 test_op = BT_implic_table[given_op-1][target_op-1] 就是通过BT_implic_table 映射表取出的操作符。
已知 ATTR given_op CONST1 为真
如果 CONST2 test_op CONST1 为假
则推理得出 ATTR target_op CONST2 一定为假
其中 test_op = BT_refute_table[given_op-1][target_op-1] 就是通过BT_refute_table 映射表取出的操作符。
代码：
~~~
/*
* Define an "operator implication table" for btree operators ("strategies"),
* and a similar table for refutation.
*
* The strategy numbers defined by btree indexes (see access/skey.h) are:
* (1) < (2) <= (3) = (4) >= (5) >
* and in addition we use (6) to represent <>. <> is not a btree-indexable
* operator, but we assume here that if an equality operator of a btree
* opfamily has a negator operator, the negator behaves as <> for the opfamily.
* (This convention is also known to get_op_btree_interpretation().)
*
* The interpretation of:
*
* test_op = BT_implic_table[given_op-1][target_op-1]
*
* where test_op, given_op and target_op are strategy numbers (from 1 to 6)
* of btree operators, is as follows:
*
* If you know, for some ATTR, that "ATTR given_op CONST1" is true, and you
* want to determine whether "ATTR target_op CONST2" must also be true, then
* you can use "CONST2 test_op CONST1" as a test. If this test returns true,
* then the target expression must be true; if the test returns false, then
* the target expression may be false.
*
* For example, if clause is "Quantity > 10" and pred is "Quantity > 5"
* then we test "5 <= 10" which evals to true, so clause implies pred.
*
* Similarly, the interpretation of a BT_refute_table entry is:
*
* If you know, for some ATTR, that "ATTR given_op CONST1" is true, and you
* want to determine whether "ATTR target_op CONST2" must be false, then
* you can use "CONST2 test_op CONST1" as a test. If this test returns true,
* then the target expression must be false; if the test returns false, then
* the target expression may be true.
*
* For example, if clause is "Quantity > 10" and pred is "Quantity < 5"
* then we test "5 <= 10" which evals to true, so clause refutes pred.
*
* An entry where test_op == 0 means the implication cannot be determined.
*/
#define BTLT BTLessStrategyNumber
#define BTLE BTLessEqualStrategyNumber
#define BTEQ BTEqualStrategyNumber
#define BTGE BTGreaterEqualStrategyNumber
#define BTGT BTGreaterStrategyNumber
#define BTNE ROWCOMPARE_NE
static const StrategyNumber BT_implic_table[6][6] = {
/*
* The target operator:
*
* LT LE EQ GE GT NE
*/
{BTGE, BTGE, 0, 0, 0, BTGE}, /* LT */
{BTGT, BTGE, 0, 0, 0, BTGT}, /* LE */
{BTGT, BTGE, BTEQ, BTLE, BTLT, BTNE}, /* EQ */
{0, 0, 0, BTLE, BTLT, BTLT}, /* GE */
{0, 0, 0, BTLE, BTLE, BTLE}, /* GT */
{0, 0, 0, 0, 0, BTEQ} /* NE */
};
static const StrategyNumber BT_refute_table[6][6] = {
/*
* The target operator:
*
* LT LE EQ GE GT NE
*/
{0, 0, BTGE, BTGE, BTGE, 0}, /* LT */
{0, 0, BTGT, BTGT, BTGE, 0}, /* LE */
{BTLE, BTLT, BTNE, BTGT, BTGE, BTEQ}, /* EQ */
{BTLE, BTLT, BTLT, 0, 0, 0}, /* GE */
{BTLE, BTLE, BTLE, 0, 0, 0}, /* GT */
{0, 0, BTEQ, 0, 0, 0} /* NE */
};
~~~
这两个表里面的0，表示无法推断真或假的情况。例如通过 a>100 无法推断 a>? 一定为假， 只能推断 a>? 一定为真。
通过100, ?, 以及 test_op 来推断，而test_op就是从BT_implic_table表中取出的BTLE即? 一定为真。
PostgreSQL通过`get_btree_test_op` 获得test_op，代码如下：
~~~
get_btree_test_op
/*
* Look up the "test" strategy number in the implication table
*/
if (refute_it)
test_strategy = BT_refute_table[clause_strategy - 1][pred_strategy - 1];
else
test_strategy = BT_implic_table[clause_strategy - 1][pred_strategy - 1];
if (test_strategy == 0)
{
/* Can't determine implication using this interpretation */
continue;
}
/*
* See if opfamily has an operator for the test strategy and the
* datatypes.
*/
if (test_strategy == BTNE)
{
test_op = get_opfamily_member(opfamily_id,
pred_op_info->oprighttype,
clause_op_info->oprighttype,
BTEqualStrategyNumber);
if (OidIsValid(test_op))
test_op = get_negator(test_op);
}
else
{
test_op = get_opfamily_member(opfamily_id,
pred_op_info->oprighttype,
clause_op_info->oprighttype,
test_strategy);
}
if (!OidIsValid(test_op))
continue;
return test_op;
~~~
## PostgreSQL 逻辑推理的用处
那么PostgreSQL可以利用这些逻辑推理来做什么呢？
通过推断 “一定为假” 来排除哪些表不需要参与到执行计划。直接排除掉。
通过推断 “一定对真” ，可以用在建立执行计划的过程中。
以一定为假为例，我们看看PostgreSQL优化器如何排除哪些表是不需要参与执行计划的。
constraint_exclusion参数控制的逻辑推理应用，可以看到调用栈如下：
`relation_excluded_by_constraints` 返回 true 表示不需要扫描这个表，返回 false 表示需要扫描这个表。简单分析一下这个函数的代码，未开启constraint_exclusion时，不进行逻辑推理。
~~~
/* Skip the test if constraint exclusion is disabled for the rel */
if (constraint_exclusion == CONSTRAINT_EXCLUSION_OFF ||
(constraint_exclusion == CONSTRAINT_EXCLUSION_PARTITION &&
!(rel->reloptkind == RELOPT_OTHER_MEMBER_REL ||
(root->hasInheritedTarget &&
rel->reloptkind == RELOPT_BASEREL &&
rel->relid == root->parse->resultRelation))))
return false;
~~~
在检查表自身的约束和SQL提供的where条件前，先检查where 条件是否有自相矛盾的。例如：
~~~
id <> mod(4,3) and id = mod(4,3)
postgres=# \d+ tt11
Table "public.tt11"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | | plain | |
postgres=# explain (analyze,verbose) select * from tt11 where id<>mod(4,3) and id=mod(4,3);
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Output: id
One-Time Filter: false
Planning time: 0.051 ms
Execution time: 0.012 ms
(5 rows)
~~~
代码如下：
~~~
/*
* Check for self-contradictory restriction clauses. We dare not make
* deductions with non-immutable functions, but any immutable clauses that
* are self-contradictory allow us to conclude the scan is unnecessary.
*
* Note: strip off RestrictInfo because predicate_refuted_by() isn't
* expecting to see any in its predicate argument.
*/
safe_restrictions = NIL;
foreach(lc, rel->baserestrictinfo)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
if (!contain_mutable_functions((Node *) rinfo->clause))
safe_restrictions = lappend(safe_restrictions, rinfo->clause);
}
if (predicate_refuted_by(safe_restrictions, safe_restrictions))
return true;
// 从SQL涉及的表，以及继承表中获取约束
/* Only plain relations have constraints */
if (rte->rtekind != RTE_RELATION || rte->inh)
return false;
/*
* OK to fetch the constraint expressions. Include "col IS NOT NULL"
* expressions for attnotnull columns, in case we can refute those.
*/
constraint_pred = get_relation_constraints(root, rte->relid, rel, true);
/*
* We do not currently enforce that CHECK constraints contain only
* immutable functions, so it's necessary to check here. We daren't draw
* conclusions from plan-time evaluation of non-immutable functions. Since
* they're ANDed, we can just ignore any mutable constraints in the list,
* and reason about the rest.
*/
safe_constraints = NIL;
foreach(lc, constraint_pred)
{
Node *pred = (Node *) lfirst(lc);
// 包含非immutable函数的表达式不加入推理判断，因为非immutable函数存在变数，不能转常量
if (!contain_mutable_functions(pred))
safe_constraints = lappend(safe_constraints, pred);
}
/*
* The constraints are effectively ANDed together, so we can just try to
* refute the entire collection at once. This may allow us to make proofs
* that would fail if we took them individually.
*
* Note: we use rel->baserestrictinfo, not safe_restrictions as might seem
* an obvious optimization. Some of the clauses might be OR clauses that
* have volatile and nonvolatile subclauses, and it's OK to make
* deductions with the nonvolatile parts.
*/
// 检测是否一定为假，如果一定为假，则不需要扫描这个表。
if (predicate_refuted_by(safe_constraints, rel->baserestrictinfo))
return true;
~~~
调用栈如下：
~~~
predicate_refuted_by
predicate_refuted_by_recurse
predicate_refuted_by_simple_clause
return btree_predicate_proof(predicate, clause, true)
btree_predicate_proof@src/backend/optimizer/util/predtest.c
/*
* Lookup the comparison operator using the system catalogs and the
* operator implication tables.
*/
test_op = get_btree_test_op(pred_op, clause_op, refute_it);
~~~
## PostgreSQL 支持逻辑推理的操作符汇总
目前PostgreSQL仅仅支持有限操作符的逻辑推理，这些操作符必须是btree-indexable operator。
~~~
postgres=# select oprname,oprcode from pg_operator where oid in (select amopopr from pg_amop where amopmethod=(select oid from pg_am where amname='btree'));
oprname | oprcode
---------+--------------------------
= | int48eq
< | int48lt
> | int48gt
<= | int48le
>= | int48ge
< | boollt
> | boolgt
= | booleq
<= | boolle
>= | boolge
= | chareq
= | nameeq
= | int2eq
< | int2lt
= | int4eq
< | int4lt
= | texteq
= | tideq
< | tidlt
> | tidgt
<= | tidle
>= | tidge
= | int8eq
< | int8lt
> | int8gt
<= | int8le
>= | int8ge
= | int84eq
< | int84lt
> | int84gt
<= | int84le
>= | int84ge
> | int2gt
> | int4gt
<= | int2le
<= | int4le
>= | int2ge
>= | int4ge
= | int24eq
= | int42eq
< | int24lt
< | int42lt
> | int24gt
> | int42gt
<= | int24le
<= | int42le
>= | int24ge
>= | int42ge
= | abstimeeq
< | abstimelt
> | abstimegt
<= | abstimele
>= | abstimege
= | reltimeeq
< | reltimelt
> | reltimegt
<= | reltimele
>= | reltimege
= | oideq
< | oidlt
> | oidgt
<= | oidle
>= | oidge
< | oidvectorlt
> | oidvectorgt
<= | oidvectorle
>= | oidvectorge
= | oidvectoreq
= | float4eq
< | float4lt
> | float4gt
<= | float4le
>= | float4ge
< | charlt
<= | charle
> | chargt
>= | charge
< | namelt
<= | namele
> | namegt
>= | namege
< | text_lt
<= | text_le
> | text_gt
>= | text_ge
= | float8eq
< | float8lt
<= | float8le
> | float8gt
>= | float8ge
= | tintervaleq
< | tintervallt
> | tintervalgt
<= | tintervalle
>= | tintervalge
= | cash_eq
< | cash_lt
> | cash_gt
<= | cash_le
>= | cash_ge
= | bpchareq
< | bpcharlt
<= | bpcharle
> | bpchargt
>= | bpcharge
= | array_eq
< | array_lt
> | array_gt
<= | array_le
>= | array_ge
= | date_eq
< | date_lt
<= | date_le
> | date_gt
>= | date_ge
= | time_eq
< | time_lt
<= | time_le
> | time_gt
>= | time_ge
= | timetz_eq
< | timetz_lt
<= | timetz_le
> | timetz_gt
>= | timetz_ge
= | float48eq
< | float48lt
> | float48gt
<= | float48le
>= | float48ge
= | float84eq
< | float84lt
> | float84gt
<= | float84le
>= | float84ge
= | timestamptz_eq
< | timestamptz_lt
<= | timestamptz_le
> | timestamptz_gt
>= | timestamptz_ge
= | interval_eq
< | interval_lt
<= | interval_le
> | interval_gt
>= | interval_ge
= | macaddr_eq
< | macaddr_lt
<= | macaddr_le
> | macaddr_gt
>= | macaddr_ge
= | network_eq
< | network_lt
<= | network_le
> | network_gt
>= | network_ge
= | numeric_eq
< | numeric_lt
<= | numeric_le
> | numeric_gt
>= | numeric_ge
= | biteq
< | bitlt
> | bitgt
<= | bitle
>= | bitge
= | varbiteq
< | varbitlt
> | varbitgt
<= | varbitle
>= | varbitge
= | int28eq
< | int28lt
> | int28gt
<= | int28le
>= | int28ge
= | int82eq
< | int82lt
> | int82gt
<= | int82le
>= | int82ge
= | byteaeq
< | bytealt
<= | byteale
> | byteagt
>= | byteage
= | timestamp_eq
< | timestamp_lt
<= | timestamp_le
> | timestamp_gt
>= | timestamp_ge
~<~ | text_pattern_lt
~<=~ | text_pattern_le
~>=~ | text_pattern_ge
~>~ | text_pattern_gt
~<~ | bpchar_pattern_lt
~<=~ | bpchar_pattern_le
~>=~ | bpchar_pattern_ge
~>~ | bpchar_pattern_gt
< | date_lt_timestamp
<= | date_le_timestamp
= | date_eq_timestamp
>= | date_ge_timestamp
> | date_gt_timestamp
< | date_lt_timestamptz
<= | date_le_timestamptz
= | date_eq_timestamptz
>= | date_ge_timestamptz
> | date_gt_timestamptz
< | timestamp_lt_date
<= | timestamp_le_date
= | timestamp_eq_date
>= | timestamp_ge_date
> | timestamp_gt_date
< | timestamptz_lt_date
<= | timestamptz_le_date
= | timestamptz_eq_date
>= | timestamptz_ge_date
> | timestamptz_gt_date
< | timestamp_lt_timestamptz
<= | timestamp_le_timestamptz
= | timestamp_eq_timestamptz
>= | timestamp_ge_timestamptz
> | timestamp_gt_timestamptz
< | timestamptz_lt_timestamp
<= | timestamptz_le_timestamp
= | timestamptz_eq_timestamp
>= | timestamptz_ge_timestamp
> | timestamptz_gt_timestamp
= | uuid_eq
< | uuid_lt
> | uuid_gt
<= | uuid_le
>= | uuid_ge
= | pg_lsn_eq
< | pg_lsn_lt
> | pg_lsn_gt
<= | pg_lsn_le
>= | pg_lsn_ge
= | enum_eq
< | enum_lt
> | enum_gt
<= | enum_le
>= | enum_ge
< | tsvector_lt
<= | tsvector_le
= | tsvector_eq
>= | tsvector_ge
> | tsvector_gt
< | tsquery_lt
<= | tsquery_le
= | tsquery_eq
>= | tsquery_ge
> | tsquery_gt
= | record_eq
< | record_lt
> | record_gt
<= | record_le
>= | record_ge
*= | record_image_eq
*< | record_image_lt
*> | record_image_gt
*<= | record_image_le
*>= | record_image_ge
= | range_eq
< | range_lt
<= | range_le
>= | range_ge
> | range_gt
= | jsonb_eq
< | jsonb_lt
> | jsonb_gt
<= | jsonb_le
>= | jsonb_ge
(273 rows)
~~~
## PostgreSQL 不能进行逻辑推理的场景及优化思路
除此以外的操作符，不参与逻辑推理。
例如：我们知道geo严格在坐标10,0的左边，肯定能推理出它不可能在11,0的右边，正常情况下是可以排除对这个表的扫描的。但是由于«,»不是btree operator，所以不参与推理。
~~~
postgres=# create table tt13(id int, geo point check(geo << point '(10,0)'));
CREATE TABLE
postgres=# explain select * from tt13 where geo >> point '(11,0)';
QUERY PLAN
--------------------------------------------------------
Seq Scan on tt13 (cost=0.00..31.25 rows=170 width=20)
Filter: (geo >> '(11,0)'::point)
(2 rows)
~~~
这种逻辑推理在分区表的应用中尤为突出，例如：
用户规划了一批分区表，按照ID取模分区。
~~~
postgres=# create table p(id int, info text);
CREATE TABLE
postgres=# create table t0(id int check(abs(mod(id,4))=0), info text);
CREATE TABLE
postgres=# create table t1(id int check(abs(mod(id,4))=1), info text);
CREATE TABLE
postgres=# create table t2(id int check(abs(mod(id,4))=2), info text);
CREATE TABLE
postgres=# create table t3(id int check(abs(mod(id,4))=3), info text);
CREATE TABLE
postgres=# alter table t0 inherit p;
ALTER TABLE
postgres=# alter table t1 inherit p;
ALTER TABLE
postgres=# alter table t2 inherit p;
ALTER TABLE
postgres=# alter table t3 inherit p;
ALTER TABLE
postgres=# explain select * from p where id=0; -- id=0 和 abs(mod(id,4)) = 0,1,2,3由于操作数不一致，不会进行推理。
QUERY PLAN
----------------------------------------------------------
Append (cost=0.00..103.50 rows=25 width=36)
-> Seq Scan on p (cost=0.00..0.00 rows=1 width=36)
Filter: (id = 0)
-> Seq Scan on t0 (cost=0.00..25.88 rows=6 width=36)
Filter: (id = 0)
-> Seq Scan on t1 (cost=0.00..25.88 rows=6 width=36)
Filter: (id = 0)
-> Seq Scan on t2 (cost=0.00..25.88 rows=6 width=36)
Filter: (id = 0)
-> Seq Scan on t3 (cost=0.00..25.88 rows=6 width=36)
Filter: (id = 0)
(11 rows)
postgres=# explain select * from p where id=0 and abs(mod(id,4)) = abs(mod(0,4)); -- 所以必须带上与约束一致的操作数
QUERY PLAN
----------------------------------------------------------
Append (cost=0.00..35.40 rows=2 width=36)
-> Seq Scan on p (cost=0.00..0.00 rows=1 width=36)
Filter: ((id = 0) AND (abs(mod(id, 4)) = 0))
-> Seq Scan on t0 (cost=0.00..35.40 rows=1 width=36)
Filter: ((id = 0) AND (abs(mod(id, 4)) = 0))
(5 rows)
~~~
如果我们使用的是范围分区，就不存在以上的问题。因为约束中的操作数和WHERE子句中的操作数可以做到一致。
从以上的例子可以了解到，PostgreSQL优化器的逻辑推理能力还可以加强。
只要能推理出一定为假的，就可以被优化器用于排除表。例如一些几何类型的操作符，数组类型的操作符等等。
## 参考
1. 分区字段的分区方法，这种方法对应的函数或操作符必须是immutable的，同时尽量以字段加btree operator来分区，方便写SQL，如果做不到，那么SQL中必须带上原样的表达式，同时代入，例如 abs(mod(id,4)) = abs(mod(?,4)) ；
2. [Partitioning and Constraint Exclusion](http://www.postgresql.org/docs/9.5/static/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION)；
~~~
The following caveats apply to constraint exclusion:
Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time.
Keep the partitioning constraints simple, else the planner may not be able to prove that partitions don't need to be visited. Use simple equality conditions for list partitioning, or simple range tests for range partitioning, as illustrated in the preceding examples. A good rule of thumb is that partitioning constraints should contain only comparisons of the partitioning column(s) to constants using B-tree-indexable operators.
All constraints on all partitions of the master table are examined during constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably. Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions.
~~~
3. [constraint_exclusion](http://www.postgresql.org/docs/9.5/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER)。
~~~
constraint_exclusion (enum)
Controls the query planner's use of table constraints to optimize queries. The allowed values of const
~~~
- 数据库内核月报目录
- 数据库内核月报 － 2016/09
- MySQL · 社区贡献 · AliSQL那些事儿
- PetaData · 架构体系 · PetaData第二代低成本存储体系
- MySQL · 社区动态 · MariaDB 10.2 前瞻
- MySQL · 特性分析 · 执行计划缓存设计与实现
- PgSQL · 最佳实践 · pg_rman源码浅析与使用
- MySQL · 捉虫状态 · bug分析两例
- PgSQL · 源码分析 · PG优化器浅析
- MongoDB · 特性分析· Sharding原理与应用
- PgSQL · 源码分析 · PG中的无锁算法和原子操作应用一则
- SQLServer · 最佳实践 · TEMPDB的设计
- 数据库内核月报 － 2016/08
- MySQL · 特性分析 ·MySQL 5.7新特性系列四
- PgSQL · PostgreSQL 逻辑流复制技术的秘密
- MySQL · 特性分析 · MyRocks简介
- GPDB · 特性分析· Greenplum 备份架构
- SQLServer · 最佳实践 · RDS for SQLServer 2012权限限制提升与改善
- TokuDB · 引擎特性 · REPLACE 语句优化
- MySQL · 专家投稿 · InnoDB物理行中null值的存储的推断与验证
- PgSQL · 实战经验 · 旋转门压缩算法在PostgreSQL中的实现
- MySQL · 源码分析 · Query Cache并发处理
- PgSQL · 源码分析· pg_dump分析
- 数据库内核月报 － 2016/07
- MySQL · 特性分析 ·MySQL 5.7新特性系列三
- MySQL · 特性分析 · 5.7 代价模型浅析
- PgSQL · 实战经验 · 分组TOP性能提升44倍
- MySQL · 源码分析 · 网络通信模块浅析
- MongoDB · 特性分析 · 索引原理
- SQLServer · 特性分析 · XML与JSON应用比较
- MySQL · 最佳实战 · 审计日志实用案例分析
- MySQL · 性能优化 · 条件下推到物化表
- MySQL · 源码分析 · Query Cache内部剖析
- MySQL · 捉虫动态 · 备库1206错误问题说明
- 数据库内核月报 － 2016/06
- MySQL · 特性分析 · innodb 锁分裂继承与迁移
- MySQL · 特性分析 ·MySQL 5.7新特性系列二
- PgSQL · 实战经验 · 如何预测Freeze IO风暴
- GPDB · 特性分析· Filespace和Tablespace
- MariaDB · 新特性 · 窗口函数
- MySQL · TokuDB · checkpoint过程
- MySQL · 特性分析 · 内部临时表
- MySQL · 最佳实践 · 空间优化
- SQLServer · 最佳实践 · 数据库实现大容量插入的几种方式
- 数据库内核月报 － 2016/05
- MySQL · 引擎特性 · 基于InnoDB的物理复制实现
- MySQL · 特性分析 · MySQL 5.7新特性系列一
- PostgreSQL · 特性分析 · 逻辑结构和权限体系
- MySQL · 特性分析 · innodb buffer pool相关特性
- PG&GP · 特性分析 · 外部数据导入接口实现分析
- SQLServer · 最佳实践 · 透明数据加密在SQLServer的应用
- MySQL · TokuDB · 日志子系统和崩溃恢复过程
- MongoDB · 特性分析 · Sharded cluster架构原理
- PostgreSQL · 特性分析 · 统计信息计算方法
- MySQL · 捉虫动态 · left-join多表导致crash
- 数据库内核月报 － 2016/04
- MySQL · 参数故事 · innodb_additional_mem_pool_size
- GPDB · 特性分析 · Segment事务一致性与异常处理
- GPDB · 特性分析 · Segment 修复指南
- MySQL · 捉虫动态 · 并行复制外键约束问题二
- PgSQL · 性能优化 · 如何潇洒的处理每天上百TB的数据增量
- Memcached · 最佳实践 · 热点 Key 问题解决方案
- MongoDB · 最佳实践 · 短连接Auth性能优化
- MySQL · 最佳实践 · RDS 只读实例延迟分析
- MySQL · TokuDB · TokuDB索引结构--Fractal Tree
- MySQL · TokuDB · Savepoint漫谈
- 数据库内核月报 － 2016/03
- MySQL · TokuDB · 事务子系统和 MVCC 实现
- MongoDB · 特性分析 · MMAPv1 存储引擎原理
- PgSQL · 源码分析 · 优化器逻辑推理
- SQLServer · BUG分析 · Agent 链接泄露分析
- Redis · 特性分析 · AOF Rewrite 分析
- MySQL · BUG分析 · Rename table 死锁分析
- MySQL · 物理备份 · Percona XtraBackup 备份原理
- GPDB · 特性分析· GreenPlum FTS 机制
- MySQL · 答疑解惑 · 备库Seconds_Behind_Master计算
- MySQL · 答疑解惑 · MySQL 锁问题最佳实践
- 数据库内核月报 － 2016/02
- MySQL · 引擎特性 · InnoDB 文件系统之文件物理结构
- MySQL · 引擎特性 · InnoDB 文件系统之IO系统和内存管理
- MySQL · 特性分析 · InnoDB transaction history
- PgSQL · 会议见闻 · PgConf.Russia 2016 大会总结
- PgSQL · 答疑解惑 · PostgreSQL 9.6 并行查询实现分析
- MySQL · TokuDB · TokuDB之黑科技工具
- PgSQL · 性能优化 · PostgreSQL TPC-C极限优化玩法
- MariaDB · 版本特性 · MariaDB 的 GTID 介绍
- MySQL · 特性分析 · 线程池
- MySQL · 答疑解惑 · mysqldump tips 两则
- 数据库内核月报 － 2016/01
- MySQL · 引擎特性 · InnoDB 事务锁系统简介
- GPDB · 特性分析· GreenPlum Primary/Mirror 同步机制
- MySQL · 专家投稿 · MySQL5.7 的 JSON 实现
- MySQL · 特性分析 · 优化器 MRR & BKA
- MySQL · 答疑解惑 · 物理备份死锁分析
- MySQL · TokuDB · Cachetable 的工作线程和线程池
- MySQL · 特性分析 · drop table的优化
- MySQL · 答疑解惑 · GTID不一致分析
- PgSQL · 特性分析 · Plan Hint
- MariaDB · 社区动态 · MariaDB on Power8 (下)
- 数据库内核月报 － 2015/12
- MySQL · 引擎特性 · InnoDB 事务子系统介绍
- PgSQL · 特性介绍 · 全文搜索介绍
- MongoDB · 捉虫动态 · Kill Hang问题排查记录
- MySQL · 参数优化 ·RDS MySQL参数调优最佳实践
- PgSQL · 特性分析 · 备库激活过程分析
- MySQL · TokuDB · 让Hot Backup更完美
- PgSQL · 答疑解惑 · 表膨胀
- MySQL · 特性分析 · Index Condition Pushdown (ICP)
- MariaDB · 社区动态 · MariaDB on Power8
- MySQL · 特性分析 · 企业版特性一览
- 数据库内核月报 － 2015/11
- MySQL · 社区见闻 · OOW 2015 总结 MySQL 篇
- MySQL · 特性分析 · Statement Digest
- PgSQL · 答疑解惑 · PostgreSQL 用户组权限管理
- MySQL · 特性分析 · MDL 实现分析
- PgSQL · 特性分析 · full page write 机制
- MySQL · 捉虫动态 · MySQL 外键异常分析
- MySQL · 答疑解惑 · MySQL 优化器 range 的代价计算
- MySQL · 捉虫动态 · ORDER/GROUP BY 导致 mysqld crash
- MySQL · TokuDB · TokuDB 中的行锁
- MySQL · 捉虫动态 · order by limit 造成优化器选择索引错误
- 数据库内核月报 － 2015/10
- MySQL · 引擎特性 · InnoDB 全文索引简介
- MySQL · 特性分析 · 跟踪Metadata lock
- MySQL · 答疑解惑 · 索引过滤性太差引起CPU飙高分析
- PgSQL · 特性分析 · PG主备流复制机制
- MySQL · 捉虫动态 · start slave crash 诊断分析
- MySQL · 捉虫动态 · 删除索引导致表无法打开
- PgSQL · 特性分析 · PostgreSQL Aurora方案与DEMO
- TokuDB · 捉虫动态 · CREATE DATABASE 导致crash问题
- PgSQL · 特性分析 · pg_receivexlog工具解析
- MySQL · 特性分析 · MySQL权限存储与管理
- 数据库内核月报 － 2015/09
- MySQL · 引擎特性 · InnoDB Adaptive hash index介绍
- PgSQL · 特性分析 · clog异步提交一致性、原子操作与fsync
- MySQL · 捉虫动态 · BUG 几例
- PgSQL · 答疑解惑 · 诡异的函数返回值
- MySQL · 捉虫动态 · 建表过程中crash造成重建表失败
- PgSQL · 特性分析 · 谈谈checkpoint的调度
- MySQL · 特性分析 · 5.6 并行复制恢复实现
- MySQL · 备库优化 · relay fetch 备库优化
- MySQL · 特性分析 · 5.6并行复制事件分发机制
- MySQL · TokuDB · 文件目录谈
- 数据库内核月报 － 2015/08
- MySQL · 社区动态 · InnoDB Page Compression
- PgSQL · 答疑解惑 · RDS中的PostgreSQL备库延迟原因分析
- MySQL · 社区动态 · MySQL5.6.26 Release Note解读
- PgSQL · 捉虫动态 · 执行大SQL语句提示无效的内存申请大小
- MySQL · 社区动态 · MariaDB InnoDB表空间碎片整理
- PgSQL · 答疑解惑 · 归档进程cp命令的core文件追查
- MySQL · 答疑解惑 · open file limits
- MySQL · TokuDB · 疯狂的 filenum++
- MySQL · 功能分析 · 5.6 并行复制实现分析
- MySQL · 功能分析 · MySQL表定义缓存
- 数据库内核月报 － 2015/07
- MySQL · 引擎特性 · Innodb change buffer介绍
- MySQL · TokuDB · TokuDB Checkpoint机制
- PgSQL · 特性分析 · 时间线解析
- PgSQL · 功能分析 · PostGIS 在 O2O应用中的优势
- MySQL · 引擎特性 · InnoDB index lock前世今生
- MySQL · 社区动态 · MySQL内存分配支持NUMA
- MySQL · 答疑解惑 · 外键删除bug分析
- MySQL · 引擎特性 · MySQL logical read-ahead
- MySQL · 功能介绍 · binlog拉取速度的控制
- MySQL · 答疑解惑 · 浮点型的显示问题
- 数据库内核月报 － 2015/06
- MySQL · 引擎特性 · InnoDB 崩溃恢复过程
- MySQL · 捉虫动态 · 唯一键约束失效
- MySQL · 捉虫动态 · ALTER IGNORE TABLE导致主备不一致
- MySQL · 答疑解惑 · MySQL Sort 分页
- MySQL · 答疑解惑 · binlog event 中的 error code
- PgSQL · 功能分析 · Listen/Notify 功能
- MySQL · 捉虫动态 · 任性的 normal shutdown
- PgSQL · 追根究底 · WAL日志空间的意外增长
- MySQL · 社区动态 · MariaDB Role 体系
- MySQL · TokuDB · TokuDB数据文件大小计算
- 数据库内核月报 － 2015/05
- MySQL · 引擎特性 · InnoDB redo log漫游
- MySQL · 专家投稿 · MySQL数据库SYS CPU高的可能性分析
- MySQL · 捉虫动态 · 5.6 与 5.5 InnoDB 不兼容导致 crash
- MySQL · 答疑解惑 · InnoDB 预读 VS Oracle 多块读
- PgSQL · 社区动态 · 9.5 新功能BRIN索引
- MySQL · 捉虫动态 · MySQL DDL BUG
- MySQL · 答疑解惑 · set names 都做了什么
- MySQL · 捉虫动态 · 临时表操作导致主备不一致
- TokuDB · 引擎特性 · zstd压缩算法
- MySQL · 答疑解惑 · binlog 位点刷新策略
- 数据库内核月报 － 2015/04
- MySQL · 引擎特性 · InnoDB undo log 漫游
- TokuDB · 产品新闻 · RDS TokuDB小手册
- PgSQL · 社区动态 · 说一说PgSQL 9.4.1中的那些安全补丁
- MySQL · 捉虫动态 · 连接断开导致XA事务丢失
- MySQL · 捉虫动态 · GTID下slave_net_timeout值太小问题
- MySQL · 捉虫动态 · Relay log 中 GTID group 完整性检测
- MySQL · 答疑释惑 · UPDATE交换列单表和多表的区别
- MySQL · 捉虫动态 · 删被引用索引导致crash
- MySQL · 答疑释惑 · GTID下auto_position=0时数据不一致
- 数据库内核月报 － 2015/03
- MySQL · 答疑释惑· 并发Replace into导致的死锁分析
- MySQL · 性能优化· 5.7.6 InnoDB page flush 优化
- MySQL · 捉虫动态· pid file丢失问题分析
- MySQL · 答疑释惑· using filesort VS using temporary
- MySQL · 优化限制· MySQL index_condition_pushdown
- MySQL · 捉虫动态·DROP DATABASE外键约束的GTID BUG
- MySQL · 答疑释惑· lower_case_table_names 使用问题
- PgSQL · 特性分析· Logical Decoding探索
- PgSQL · 特性分析· jsonb类型解析
- TokuDB ·引擎机制· TokuDB线程池
- 数据库内核月报 － 2015/02
- MySQL · 性能优化· InnoDB buffer pool flush策略漫谈
- MySQL · 社区动态· 5.6.23 InnoDB相关Bugfix
- PgSQL · 特性分析· Replication Slot
- PgSQL · 特性分析· pg_prewarm
- MySQL · 答疑释惑· InnoDB丢失自增值
- MySQL · 答疑释惑· 5.5 和 5.6 时间类型兼容问题
- MySQL · 捉虫动态· 变量修改导致binlog错误
- MariaDB · 特性分析· 表/表空间加密
- MariaDB · 特性分析· Per-query variables
- TokuDB · 特性分析· 日志详解
- 数据库内核月报 － 2015/01
- MySQL · 性能优化· Group Commit优化
- MySQL · 新增特性· DDL fast fail
- MySQL · 性能优化· 启用GTID场景的性能问题及优化
- MySQL · 捉虫动态· InnoDB自增列重复值问题
- MySQL · 优化改进· 复制性能改进过程
- MySQL · 谈古论今· key分区算法演变分析
- MySQL · 捉虫动态· mysql client crash一例
- MySQL · 捉虫动态· 设置 gtid_purged 破坏AUTO_POSITION复制协议
- MySQL · 捉虫动态· replicate filter 和 GTID 一起使用的问题
- TokuDB·特性分析· Optimize Table
- 数据库内核月报 － 2014/12
- MySQL· 性能优化·5.7 Innodb事务系统
- MySQL· 踩过的坑·5.6 GTID 和存储引擎那会事
- MySQL· 性能优化·thread pool 原理分析
- MySQL· 性能优化·并行复制外建约束问题
- MySQL· 答疑释惑·binlog event有序性
- MySQL· 答疑释惑·server_id为0的Rotate
- MySQL· 性能优化·Bulk Load for CREATE INDEX
- MySQL· 捉虫动态·Opened tables block read only
- MySQL· 优化改进· GTID启动优化
- TokuDB· Binary Log Group Commit with TokuDB
- 数据库内核月报 － 2014/11
- MySQL· 捉虫动态·OPTIMIZE 不存在的表
- MySQL· 捉虫动态·SIGHUP 导致 binlog 写错
- MySQL· 5.7改进·Recovery改进
- MySQL· 5.7特性·高可用支持
- MySQL· 5.7优化·Metadata Lock子系统的优化
- MySQL· 5.7特性·在线Truncate undo log 表空间
- MySQL· 性能优化·hash_scan 算法的实现解析
- TokuDB· 版本优化· 7.5.0
- TokuDB· 引擎特性· FAST UPDATES
- MariaDB· 性能优化·filesort with small LIMIT optimization
- 数据库内核月报 － 2014/10
- MySQL· 5.7重构·Optimizer Cost Model
- MySQL· 系统限制·text字段数
- MySQL· 捉虫动态·binlog重放失败
- MySQL· 捉虫动态·从库OOM
- MySQL· 捉虫动态·崩溃恢复失败
- MySQL· 功能改进·InnoDB Warmup特性
- MySQL· 文件结构·告别frm文件
- MariaDB· 新鲜特性·ANALYZE statement 语法
- TokuDB· 主备复制·Read Free Replication
- TokuDB· 引擎特性·压缩
- 数据库内核月报 － 2014/09
- MySQL· 捉虫动态·GTID 和 DELAYED
- MySQL· 限制改进·GTID和升级
- MySQL· 捉虫动态·GTID 和 binlog_checksum
- MySQL· 引擎差异·create_time in status
- MySQL· 参数故事·thread_concurrency
- MySQL· 捉虫动态·auto_increment
- MariaDB· 性能优化·Extended Keys
- MariaDB·主备复制·CREATE OR REPLACE
- TokuDB· 参数故事·数据安全和性能
- TokuDB· HA方案·TokuDB热备
- 数据库内核月报 － 2014/08
- MySQL· 参数故事·timed_mutexes
- MySQL· 参数故事·innodb_flush_log_at_trx_commit
- MySQL· 捉虫动态·Count(Distinct) ERROR
- MySQL· 捉虫动态·mysqldump BUFFER OVERFLOW
- MySQL· 捉虫动态·long semaphore waits
- MariaDB·分支特性·支持大于16K的InnoDB Page Size
- MariaDB·分支特性·FusionIO特性支持
- TokuDB· 性能优化·Bulk Fetch
- TokuDB· 数据结构·Fractal-Trees与LSM-Trees对比
- TokuDB·社区八卦·TokuDB团队