constraint exclusion约束排除有如下类型：不使用、对所有表使用、对otherrel使用。首先看一下官方解释：Controls the query planner’s use of table constraints to optimize queries. The allowed values of constraint_exclusion are on (examine constraints for all tables), off (never examine constraints), and partition (examine constraints only for inheritance child tables and UNION ALL subqueries). partition is the default setting. It is often used with traditional inheritance trees to improve performance.Currently, constraint exclusion is enabled by default only for cases that are often used to implement table partitioning via inheritance trees. Turning it on for all tables imposes extra planning overhead that is quite noticeable on simple queries, and most often will yield no benefit for simple queries. If you have no tables that are partitioned using traditional inheritance, you might prefer to turn it off entirely. (Note that the equivalent feature for partitioned tables is controlled by a separate parameter, enable_partition_pruning.)
typedef enum{
CONSTRAINT_EXCLUSION_OFF, /* do not use c_e */
CONSTRAINT_EXCLUSION_ON, /* apply c_e to all rels */
CONSTRAINT_EXCLUSION_PARTITION /* apply c_e to otherrels only */
} ConstraintExclusionType;
控制查询优化器使用表约束以优化查询。constraint_exclusion 的允许值是 on（检查所有表的约束）、off（从不检查约束）和 partition（仅检查继承子表和 UNION ALL 子查询的约束）。partition 是默认设置。它通常与传统的继承树一起使用以提高性能。目前，约束排除通常仅在用于通过继承树实现表分区的情况下默认启用。为所有表打开它会带来额外的计划开销，这在简单查询中非常明显，而且通常不会对简单查询产生任何好处。如果没有使用传统继承式分区表，可能更愿意完全关闭它。（请注意，分区表的等效功能由单独的参数 enable_partition_pruning 控制。）
constraint_exclusion GUC参数仅由relation_excluded_by_constraints（src/backend/optimizer/util/plancat.c文件）使用，该函数检测是否因为表具有自不一致的限制(self-inconsistent restrictions)或与表的适用约束不一致的约束而不需要扫描该表(restrictions inconsistent with the relation’s applicable constraints)。注意：这只检查rel->relid、rel->reloptkind和rel->baserestrictinfo；因此可以在填充RelOptInfo的其他字段之前调用它。Detect whether the relation need not be scanned because it has either self-inconsistent restrictions, or restrictions inconsistent with the relation’s applicable constraints. Note: this examines only rel->relid, rel->reloptkind, and rel->baserestrictinfo; therefore it can be called before filling in other fields of the RelOptInfo.
relation_excluded_by_constraints函数在两处分支中调用：文章来源：https://www.toymoban.com/news/detail-475271.html
- set_rel_size为base relation设定大小估计时，如果reloptkind为base relation，调用relation_excluded_by_constraints函数来判定该表是否被exclusion
- set_append_rel_size为simple append relation设定大小估计时，遍历root->append_rel_list，为列表中的每个RelOptInfo调用relation_excluded_by_constraints函数来判定是否被exclusion
入参：rel->baserestrictinfoSQL关联到relation上的SQL谓词表达式列表 rel->reloptkind是枚举类型（RELOPT_BASEREL, RELOPT_JOINREL, RELOPT_OTHER_MEMBER_REL, RELOPT_OTHER_JOINREL, RELOPT_UPPER_REL, RELOPT_OTHER_UPPER_REL, RELOPT_DEADREL），目前仅在其为RELOPT_BASEREL或RELOPT_OTHER_MEMBER_REL才可执行relation_excluded_by_constraints函数rel->relid是表的OID(OID of the relation)
流程：首先对SQL是否有关联到relation上的SQL谓词表达式列表和constant-FALSE-or-NULL限制先进行判定，对constraint_exclusion GUC参数进行判定，由此知道appendrel member时，reloptkind为RELOPT_OTHER_MEMBER_REL。在进行下一步之前我们先阅读一下手册的内容Constraint exclusion only works when the query’s where clause contains constants. The planner analyzes the query before values from parameters (in prepared statemetns) or stored procedures are substituted in the query. For the same reason, “stable” functions such as CURRENT_DATE must be avoided, 约束排除仅在查询的 WHERE 子句包含常量时才起作用。在查询中替换来自参数（在预备语句中）或存储过程的值之前，优化器解析查询。因此下面的代码就是从restrictions和constraint列表中剔除任何包含可变函数的表达式。最终调用函数predicate_refuted_by进行判定。文章来源地址https://www.toymoban.com/news/detail-475271.html
bool relation_excluded_by_constraints(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte){
bool include_noinherit;
bool include_notnull;
bool include_partition = false;
ListCell *lc; List *safe_restrictions; List *constraint_pred; List *safe_constraints;
Assert(IS_SIMPLE_REL(rel)); /* As of now, constraint exclusion works only with simple relations. */
if (rel->baserestrictinfo == NIL) return false; /* If there are no base restriction clauses, we have no hope of proving anything below, so fall out quickly. */
if (list_length(rel->baserestrictinfo) == 1){ /* Regardless of the setting of constraint_exclusion, detect constant-FALSE-or-NULL restriction clauses. Because const-folding will reduce "anything AND FALSE" to just "FALSE", any such case should result in exactly one baserestrictinfo entry. This doesn't fire very often, but it seems cheap enough to be worth doing anyway. (Without this, we'd miss some optimizations that 9.5 and earlier found via much more roundabout methods.) */
RestrictInfo *rinfo = (RestrictInfo *) linitial(rel->baserestrictinfo); Expr *clause = rinfo->clause;
if (clause && IsA(clause, Const) && (((Const *) clause)->constisnull || !DatumGetBool(((Const *) clause)->constvalue))) return true;
}
/* Skip further tests, depending on constraint_exclusion. */
switch (constraint_exclusion){
case CONSTRAINT_EXCLUSION_OFF: /* In 'off' mode, never make any further tests */
return false;
case CONSTRAINT_EXCLUSION_PARTITION: /* When constraint_exclusion is set to 'partition' we only handle appendrel members. Partition pruning has already been applied, so there is no need to consider the rel's partition constraints here. */
if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL) break; /* appendrel member, so process it */
return false;
case CONSTRAINT_EXCLUSION_ON: /* In 'on' mode, always apply constraint exclusion. If we are considering a baserel that is a partition (i.e., it was directly named rather than expanded from a parent table), then its partition constraints haven't been considered yet, so include them in the processing here. */
if (rel->reloptkind == RELOPT_BASEREL) include_partition = true;
break; /* always try to exclude */
}
/* 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, true)) return true; /* We can use weak refutation here, since we're comparing restriction clauses with restriction clauses. */
/* Only plain relations have constraints, so stop here for other rtekinds. */
if (rte->rtekind != RTE_RELATION)
return false;
/* If we are scanning just this table, we can use NO INHERIT constraints, but not if we're scanning its children too. (Note that partitioned tables should never have NO INHERIT constraints; but it's not necessary for us to assume that here.) */
include_noinherit = !rte->inh;
/* Currently, attnotnull constraints must be treated as NO INHERIT unless this is a partitioned table. In future we might track their inheritance status more accurately, allowing this to be refined. */
include_notnull = (!rte->inh || rte->relkind == RELKIND_PARTITIONED_TABLE);
/* Fetch the appropriate set of constraint expressions. */
constraint_pred = get_relation_constraints(root, rte->relid, rel, include_noinherit, include_notnull, include_partition);
/* 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);
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.
* We need strong refutation because we have to prove that the constraints
* would yield false, not just NULL.
*/
if (predicate_refuted_by(safe_constraints, rel->baserestrictinfo, false))
return true;
return false;
}
到了这里，关于PostgreSQL数据库分区裁剪——constraint exclusion的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
