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      

　　從上可知，基于繼承實作的分區是通過在子表上施加不重疊的限制條件實作的。分區的目的是為了更少的通路資料，是以配套的，有個特性來實作不通路子表、但是排除它的目的。它就是限制排除（Constraint Exclusion），在PostgreSQL 8.1中開始支援，通過參數​​constraint_exclusion​​控制，預設是partition，表示僅針對分區表啟用限制排除，也就是table必須有個屬性inher，一般來說足夠，因為針對非分區表判斷意義不大，總是要檢查限制的。