PgSQL · 源碼分析 · 優化器邏輯推理 · 數據庫內核月報

## 背景知識數據庫優化器需要具備邏輯推理能力，而且越強越好，為什么呢？舉一些例子，通過已知的一個人講的是真話，推理另一個人講的一定是真話或一定是假話。例子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可以利用這些邏輯推理來做什么呢？通過推斷 “一定為假” 來排除哪些表不需要參與到執行計劃。直接排除掉。 ![](https://box.kancloud.cn/2016-04-11_570b4820b04a6.png) 通過推斷 “一定對真” ，可以用在建立執行計劃的過程中。 ![](https://box.kancloud.cn/2016-04-11_570b485cc432d.png) 以一定為假為例，我們看看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 ~~~