postgresql/lightdb中分区的Constraint Exclusion详解

在postgresql 10支持声明式分区之前，分区是通过继承实现的，如下：

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2006m03 (
    CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);

...
CREATE TABLE measurement_y2007m11 (
    CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2007m12 (
    CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2008m01 (
    CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

　　从上可知，基于继承实现的分区是通过在子表上施加不重叠的约束条件实现的。分区的目的是为了更少的访问数据，所以配套的，有个特性来实现不访问子表、但是排除它的目的。它就是约束排除（Constraint Exclusion），在PostgreSQL 8.1中开始支持，通过参数constraint_exclusion控制，默认是partition，表示仅针对分区表启用约束排除，也就是table必须有个属性inher，一般来说足够，因为针对非分区表判断意义不大，总是要检查约束的。

　　在声明式分区中，对应的参数选项为enable_partition_pruning，默认值同样启用，enable_partition_pruning相比constraint_exclusion要强大得多，也支持执行时剪除，所以目前基本可以废弃constraint_exclusion。

http://www.light-pg.com/docs/lightdb/13.3-22.2/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE

A Guide to Constraint Exclusion (Partitioning)

https://www.postgresonline.com/journal/archives/39-Constraint-Exclusion-when-it-fails-to-work.html

https://www.postgresql.org/message-id/flat/CAFjFpRcuRaydz88CY_aQekmuvmN2A9ax5z0k=ppT+s8KS8xMRA@mail.gmail.com