5.10. 表分区

5.10.1. 概述
5.10.2. 声明式划分
5.10.3. 使用继承实现
5.10.4. 分区剪枝
5.10.5. 分区和约束排除

PostgreSQL支持基本的表划分。本小节介绍为何以及怎样把划分实现为数据库设计的一部分。

5.10.1. 概述

划分指的是将逻辑上的一个大表分成一些小的物理上的片。划分有很多益处:

  • 在某些情况下查询性能能够显著提升,特别是当那些访问压力大的行在一个分区或者少数几个分区时。划分可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。

  • 当查询或更新访问一个分区的大部分行时,可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问,这样可以改善性能。

  • 如果批量操作的需求是在分区设计时就规划好的,则批量装载和删除可以通过增加或者去除分区来完成。执行ALTER TABLE DETACH PARTITION或者使用DROP TABLE删除一个分区远快于批量操作。这些命令也完全避免了批量DELETE导致的VACUUM开销。

  • 很少使用的数据可以被迁移到便宜且较慢的存储介质上。

当一个表非常大时,划分所带来的好处是非常值得的。一个表何种情况下会从划分获益取决于应用,一个经验法则是当表的尺寸超过了数据库服务器物理内存时,划分会为表带来好处。

PostgreSQL对下列分区形式提供了内建支持:

范围划分

表被根据一个关键列或一组列划分为范围,不同的分区的范围之间没有重叠。例如,我们可以根据日期范围划分,或者根据特定业务对象的标识符划分。

列表划分

通过显式地列出每一个分区中出现的键值来划分表。

哈希分区

通过为每个分区指定模数和余数来对表进行分区。每个分区所持有的行都满足:分区键的值除以为其指定的模数将产生为其指定的余数。

如果你的应用需要使用上面所列之外的分区形式,可以使用诸如继承和UNION ALL视图之类的替代方法。这些方法很灵活,但是却缺少内建声明式分区的一些性能优势。

5.10.2. 声明式划分

PostgreSQL提供了一种方法指定如何把一个表划分成称为分区的片段。被划分的表被称作分区表。这种说明由分区方法以及要被用作分区键的列或者表达式列表组成。

所有被插入到分区表的行将被基于分区键的值路由到分区中。每个分区都有一个由其分区边界定义的数据子集。当前支持的分区方法是范围、列表以及哈希。

分区本身也可能被定义为分区表,这种用法被称为子分区。分区可以有自己的与其他分区不同的索引、约束以及默认值。创建分区表及分区的更多细节请见CREATE TABLE

无法把一个常规表转换成分区表,反之亦然。不过,可以把一个包含数据的常规表或者分区表作为分区加入到另一个分区表,或者从分区表中移走一个分区并且把它变成一个独立的表。有关ATTACH PARTITIONDETACH PARTITION子命令的内容请见ALTER TABLE

个体分区在内部以继承的方式链接到分区表,不过无法对声明式分区表或其分区使用继承的某些一般特性(下文讨论)。例如,分区不能有除其所属分区表之外的父表,一个常规表也不能从分区表继承使得后者成为其父表。这意味着分区表及其分区不会参与到与常规表的继承关系中。由于分区表及其分区组成的分区层次仍然是一种继承层次,所有Section 5.9中所述的继承的普通规则也适用,不过有一些例外,尤其是:

  • 分区表的CHECK约束和NOT NULL约束总是会被其所有的分区所继承。不允许在分区表上创建标记为NO INHERITCHECK约束。

  • 只要分区表中不存在分区,则支持使用ONLY仅在分区表上增加或者删除约束。一旦分区存在,那样做就会导致错误,因为当分区存在时是不支持仅在分区表上增加或删除约束的。不过,分区表本身上的约束可以被增加(如果它们不出现在父表中)和删除。

  • 由于分区表并不直接拥有任何数据,尝试在分区表上使用TRUNCATE ONLY将总是返回错误。

  • 分区不能有在父表中不存在的列。在使用CREATE TABLE创建分区时不能指定列,在事后使用ALTER TABLE时也不能为分区增加列。只有当表的列正好匹配父表时(包括任何oid列),才能使用ALTER TABLE ... ATTACH PARTITION将它作为分区加入。

  • 如果NOT NULL约束在父表中存在,那么就不能删除分区的列上的对应的NOT NULL约束。

分区也可以是外部表,不过它们有一些普通表没有的限制,详情请见CREATE FOREIGN TABLE

更新行的分区键可能导致它满足另一个不同的分区的分区边界,进而被移动到那个分区中。

5.10.2.1. 例子

假定我们正在为一个大型的冰激凌公司构建数据库。该公司每天测量最高温度以及每个区域的冰激凌销售情况。概念上,我们想要一个这样的表:

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

我们知道大部分查询只会访问上周的、上月的或者上季度的数据,因为这个表的主要用途是为管理层准备在线报告。为了减少需要被存放的旧数据量,我们决定只保留最近3年的数据。在每个月的开始我们将去除掉最早的那个月的数据。在这种情况下我们可以使用分区技术来帮助我们满足对measurement表的所有不同需求。

要在这种情况下使用声明式分区,可采用下面的步骤:

  1. 通过指定PARTITION BY子句把measurement表创建为分区表,该子句包括分区方法(这个例子中是RANGE)以及用作分区键的列列表。

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

    你可能需要决定在分区键中使用多列进行范围分区。当然,这通常会导致较大数量的分区,其中每一个个体都比较小。另一方面,使用较少的列可能会导致粗粒度的分区策略得到较少数量的分区。如果条件涉及这些列中的一部分或者全部,访问分区表的查询将不得不扫描较少的分区。例如,考虑一个使用列lastnamefirstname(按照这样的顺序)作为分区键进行范围分区的表。

  2. 创建分区。每个分区的定义必须指定对应于父表的分区方法和分区键的边界。注意,如果指定的边界使得新分区的值会与已有分区中的值重叠,则会导致错误。向父表中插入无法映射到任何现有分区的数据将会导致错误,这种情况下应该手工增加一个合适的分区。

    分区以普通PostgreSQL表(或者可能是外部表)的方式创建。可以为每个分区单独指定表空间和存储参数。

    没有必要创建表约束来描述分区的分区边界条件。相反,只要需要引用分区约束时,分区约束会自动地隐式地从分区边界说明中生成。

    CREATE TABLE measurement_y2006m02 PARTITION OF measurement
        FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
    
    CREATE TABLE measurement_y2006m03 PARTITION OF measurement
        FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
    
    ...
    CREATE TABLE measurement_y2007m11 PARTITION OF measurement
        FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
    
    CREATE TABLE measurement_y2007m12 PARTITION OF measurement
        FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
        TABLESPACE fasttablespace;
    
    CREATE TABLE measurement_y2008m01 PARTITION OF measurement
        FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
        WITH (parallel_workers = 4)
        TABLESPACE fasttablespace;
    

    为了实现子分区,在创建分区的命令中指定PARTITION BY子句,例如:

    CREATE TABLE measurement_y2006m02 PARTITION OF measurement
        FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
        PARTITION BY RANGE (peaktemp);
    

    在创建了measurement_y2006m02的分区之后,任何被插入到measurement中且被映射到measurement_y2006m02的数据(或者直接被插入到measurement_y2006m02的数据,假定它满足这个分区的分区约束)将被基于peaktemp列进一步重定向到measurement_y2006m02的一个分区。指定的分区键可以与父亲的分区键重叠,不过在指定子分区的边界时要注意它接受的数据集合是分区自身边界允许的数据集合的一个子集,系统不会尝试检查事情情况是否如此。

  3. 在分区表的键列上创建一个索引,还有其他需要的索引(键索引并不是必需的,但是大部分场景中它都能很有帮助)。这会自动在每个分区上创建一个索引,并且后来创建或者附着的任何分区也将会包含索引。

    CREATE INDEX ON measurement (logdate);
    

  4. 确保enable_partition_pruning配置参数在postgresql.conf中没有被禁用。如果被禁用,查询将不会按照想要的方式被优化。

在上面的例子中,我们会每个月创建一个新分区,因此写一个脚本来自动生成所需的DDL会更好。

5.10.2.2. 分区维护

通常在初始定义分区表时建立的分区并非保持静态不变。移除旧分区的数据并且为新数据周期性地增加新分区的需求比比皆是。分区的最大好处之一就是可以通过操纵分区结构来近乎瞬时地执行这类让人头痛的任务,而不是物理地去除大量数据。

移除旧数据最简单的选择是删除掉不再需要的分区:

DROP TABLE measurement_y2006m02;

这可以非常快地删除数百万行记录,因为它不需要逐个删除每个记录。不过要注意上面的命令需要在父表上拿到ACCESS EXCLUSIVE锁。

另一种通常更好的选项是把分区从分区表中移除,但是保留它作为一个独立的表:

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

这允许在它被删除之前在其数据上执行进一步的操作。例如,这通常是一种使用COPYpg_dump或类似工具备份数据的好时候。这也是把数据聚集成较小的格式、执行其他数据操作或者运行报表的好时机。

类似地,我们可以增加一个新分区来处理新数据。我们可以在分区表中创建一个空分区,就像上面创建的初始分区那样:

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;

另外一种选择是,有时候在分区结构之外创建新表更加方便,然后将它作为一个合适的分区。这允许先对数据进行装载、检查和转换,然后再让它们出现在分区表中:

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;

ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );

\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

在运行ATTACH PARTITION命令之前,推荐在要被挂接的表上创建一个CHECK约束来描述想要的分区约束。采用这种方法,系统将能够跳过验证隐式分区约束的扫描。如果没有这样一个约束,则将会扫描表来验证分区约束,其间会持有一个父表上的ACCESS EXCLUSIVE锁。人们可能在ATTACH PARTITION完成之后删除该约束,因为之后就不再需要它了。

5.10.2.3. 限制

分区表有下列限制:

  • 没有办法创建跨越所有分区的排除约束,只可能单个约束每个叶子分区。

  • 虽然在分区表上支持主键,但引用分区表的外键不受支持(但支持从分区表到某个其他表的外键引用)。

  • 当一个UPDATE导致一行从一个分区移动到另一个分区时,另一个并发的UPDATEDELETE可能会错过这一行。假设会话1正在执行一个分区键上的UPDATE,同时一个并发的能看见这个行的会话2执行了对该行的UPDATE或者DELETE操作。如果由于会话1的活动导致那一行从分区中删除,会话2可能会无声无息地错过那一行。在这种情况下,会话2的UPDATE或者DELETE不会留意到那一行的移动,它们会以为那一行已经被删除并且决定不需要对它做任何操作。在表没有被分区的通常情况下或者在没有行移动的情况下,会话2会找到刚被更新的行并且在这个新的行版本上执行UPDATE/DELETE

  • 如果必要,必须在个体分区上定义BEFORE ROW触发器,分区表上不需要。

  • 不允许在同一个分区树中混杂临时关系和持久关系。因此,如果分区表是持久的,则其分区也必须是持久的,反之亦然。在使用临时关系时,分区数的所有成员都必须来自于同一个会话。

5.10.3. 使用继承实现

虽然内建的声明式分区适合于大部分常见的用例,但还是有一些场景需要更加灵活的方法。分区可以使用表继承来实现,这能够带来一些声明式分区不支持的特性,例如:

  • 对声明式分区来说,分区必须具有和分区表正好相同的列集合,而在表继承中,子表可以有父表中没有出现过的额外列。

  • 表继承允许多继承。

  • 声明式分区仅支持范围、列表以及哈希分区,而表继承允许数据按照用户的选择来划分(不过注意,如果约束排除不能有效地剪枝子表,查询性能可能会很差)。

  • 在使用声明式分区时,一些操作比使用表继承时要求更长的持锁时间。例如,向分区表中增加分区或者从分区表移除分区要求在父表上取得一个ACCESS EXCLUSIVE锁,而在常规继承的情况下一个SHARE UPDATE EXCLUSIVE锁就足够了。

5.10.3.1. 例子

我们使用上面用过的同一个measurement表。为了使用继承实现分区,可使用下面的步骤:

  1. 创建表,所有的表都将从它继承。这个表将不包含数据。不要在这个表上定义任何检查约束,除非想让它们应用到所有的子表上。同样,在这个表上定义索引或者唯一约束也没有意义。对于我们的例子来说,主表是最初定义的measurement表。

  2. 创建数个表,每一个都从主表继承。通常,这些表将不会在从主表继承的列集合之外增加任何列。正如声明性分区那样,这些表就是普通的PostgreSQL表(或者外部表)。

    CREATE TABLE measurement_y2006m02 () INHERITS (measurement);
    CREATE TABLE measurement_y2006m03 () INHERITS (measurement);
    ...
    CREATE TABLE measurement_y2007m11 () INHERITS (measurement);
    CREATE TABLE measurement_y2007m12 () INHERITS (measurement);
    CREATE TABLE measurement_y2008m01 () INHERITS (measurement);
    

  3. 为子表增加不重叠的表约束来定义每个分区允许的键值。

    典型的例子是:

    CHECK ( x = 1 )
    CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
    CHECK ( outletID >= 100 AND outletID < 200 )
    

    确保约束能保证不同子表允许的键值之间没有重叠。设置范围约束的常见错误:

    CHECK ( outletID BETWEEN 100 AND 200 )
    CHECK ( outletID BETWEEN 200 AND 300 )
    

    这是错误的,因为不清楚键值200属于哪一个子表。

    像下面这样创建子表会更好:

    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);
    

  4. 对于每个子表,在键列上创建一个索引,以及任何想要的其他索引。

    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);
    

  5. 我们希望我们的应用能够使用INSERT INTO measurement ...并且数据将被重定向到合适的分区表。我们可以通过为主表附加一个合适的触发器函数来实现这一点。如果数据将只被增加到最后一个分区,我们可以使用一个非常简单的触发器函数:

    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    

    完成函数创建后,我们创建一个调用该触发器函数的触发器:

    CREATE TRIGGER insert_measurement_trigger
        BEFORE INSERT ON measurement
        FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();
    

    我们必须在每个月重新定义触发器函数,这样它才会总是指向当前的子表。而触发器的定义则不需要被更新。

    我们也可能希望插入数据时服务器会自动地定位应该加入数据的子表。我们可以通过一个更复杂的触发器函数来实现之,例如:

    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        IF ( NEW.logdate >= DATE '2006-02-01' AND
             NEW.logdate < DATE '2006-03-01' ) THEN
            INSERT INTO measurement_y2006m02 VALUES (NEW.*);
        ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
                NEW.logdate < DATE '2006-04-01' ) THEN
            INSERT INTO measurement_y2006m03 VALUES (NEW.*);
        ...
        ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
                NEW.logdate < DATE '2008-02-01' ) THEN
            INSERT INTO measurement_y2008m01 VALUES (NEW.*);
        ELSE
            RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
        END IF;
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    

    触发器的定义和以前一样。注意每一个IF测试必须准确地匹配它的子表的CHECK约束。

    当该函数比单月形式更加复杂时,并不需要频繁地更新它,因为可以在需要的时候提前加入分支。

    Note

    在实践中,如果大部分插入都会进入最新的子表,最好先检查它。为了简洁,我们为触发器的检查采用了和本例中其他部分一致的顺序。

    把插入重定向到一个合适的子表中的另一种不同方法是在主表上设置规则而不是触发器。例如:

    CREATE RULE measurement_insert_y2006m02 AS
    ON INSERT TO measurement WHERE
        ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
    DO INSTEAD
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ...
    CREATE RULE measurement_insert_y2008m01 AS
    ON INSERT TO measurement WHERE
        ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
    DO INSTEAD
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    

    规则的开销比触发器大很多,但是这种开销是每个查询只有一次,而不是每行一次,因此这种方法可能对批量插入的情况有优势。不过,在大部分情况下,触发器方法将提供更好的性能。

    注意COPY会忽略规则。如果想要使用COPY插入数据,则需要拷贝到正确的子表而不是直接放在主表中。COPY会引发触发器,因此在使用触发器方法时可以正常使用它。

    规则方法的另一个缺点是,如果规则集合无法覆盖插入日期,则没有简单的方法能够强制产生错误,数据将会无声无息地进入到主表中。

  6. 确认constraint_exclusion配置参数在postgresql.conf中没有被禁用,否则将会不必要地访问子表。

如我们所见,一个复杂的表层次可能需要大量的DDL。在上面的例子中,我们可能为每个月创建一个新的子表,因此编写一个脚本来自动生成所需要的DDL可能会更好。

5.10.3.2. 继承分区的维护

要快速移除旧数据,只需要简单地去掉不再需要的子表:

DROP TABLE measurement_y2006m02;

要从继承层次表中去掉子表,但还是把它当做一个表保留:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

要增加一个新子表来处理新数据,可以像上面创建的原始子表那样创建一个空的子表:

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

或者,用户可能想要创建新子表并且在将它加入到表层次之前填充它。这可以允许数据在被父表上的查询可见之前对数据进行装载、检查以及转换。

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work
ALTER TABLE measurement_y2008m02 INHERIT measurement;

5.10.3.3. 提醒

下面的提醒适用于用继承实现的分区:

  • 没有自动的方法啊验证所有的CHECK约束之间是否互斥。编写代码来产生子表以及创建和修改相关对象比手写命令要更加安全。

  • 这里展示的模式假定行的键列值从不改变,或者说改变不足以让行移动到另一个分区。由于CHECK约束的存在,尝试那样做的UPDATE将会失败。如果需要处理那种情况,可以在子表上放置适当的更新触发器,但是那会使对结构的管理更加复杂。

  • 如果使用手工的VACUUM或者ANALYZE命令,不要忘记需要在每个子表上单独运行它们。这样的命令:

    ANALYZE measurement;
    

    将只会处理主表。

  • 带有ON CONFLICT子句的INSERT语句不太可能按照预期工作,因为只有在指定的目标关系而不是其子关系上发生唯一违背时才会采取ON CONFLICT行动。

  • 将会需要触发器或者规则将行路由到想要的子表中,除非应用明确地知道分区的模式。编写触发器可能会很复杂,并且会比声明式分区在内部执行的元组路由慢很多。

5.10.4. 分区剪枝

分区剪枝是一种提升声明式分区表性能的查询优化技术。例如:

SET enable_partition_pruning = on;                 -- the default
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

如果没有分区剪枝,上面的查询将会扫描measurement表的每一个分区。如果启用了分区剪枝,规划器将会检查每个分区的定义并且检验该分区是否因为不包含符合查询WHERE子句的行而无需扫描。当规划器可以证实这一点时,它会把分区从查询计划中排除(剪枝)。

通过使用EXPLAIN命令和enable_partition_pruning配置参数,可以展示剪枝掉分区的计划与没有剪枝的计划之间的差别。对这种类型的表设置,一种典型的未优化计划是:

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=188.76..188.77 rows=1 width=8)
   ->  Append  (cost=0.00..181.05 rows=3085 width=0)
         ->  Seq Scan on measurement_y2006m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m11  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2007m12  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

某些或者全部的分区可能会使用索引扫描取代全表顺序扫描,但是这里的重点是根本不需要扫描较老的分区来回答这个查询。当我们启用分区剪枝时,我们会得到一个便宜很多的计划,而它能给出相同的答案:

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Append  (cost=0.00..36.21 rows=617 width=0)
         ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

注意,分区剪枝仅由分区键隐式定义的约束所驱动,而不是由索引的存在驱动。因此,没有必要在键列上定义索引。是否需要为一个给定分区创建索引取决于预期的查询扫描该分区时会扫描大部分还是小部分。后一种情况下索引的帮助会比前者大。

不仅在给定查询的规划期间可以执行分区剪枝,在其执行期间也能执行分区剪枝。这非常有用,因为如果子句中包含查询规划时值未知的表达式时,这可以剪枝掉更多的分区,例如在PREPARE语句中定义的参数会使用从子查询拿到的值或者嵌套循环连接内侧关系上的参数化值。执行期间的分区剪枝可能在下列任何时刻执行:

  • 在查询计划的初始化期间。对于执行的初始化阶段就已知值的参数,可以在这里执行分区剪枝。这个阶段中被剪枝掉的分区将不会显示在查询的EXPLAINEXPLAIN ANALYZE结果中。通过观察EXPLAIN输出的Subplans Removed属性,可以确定被剪枝掉的分区数。

  • 在查询计划的实际执行期间。这里可以使用只有在实际查询执行时才能知道的值执行分区剪枝。这包括来自子查询的值以及来自执行时参数的值(例如来自于参数化嵌套循环连接的参数)。由于在查询执行期间这些参数的值可能会改变多次,所以只要分区剪枝使用到的执行参数发生改变,就会执行一次分区剪枝。要判断分区是否在这个阶段被剪枝,需要仔细地观察EXPLAIN ANALYZE输出中的nloops属性。

可以使用enable_partition_pruning设置禁用分区剪枝。

Note

当前,UPDATE or DELETE命令规划时的分区剪枝采用约束排除方法实现(但是,它是由enable_partition_pruning而不是constraint_exclusion控制 — 其细节及相应的提醒请参考接下来的小节。

此外,执行时的分区剪枝当前仅发生在Append节点类型上,对MergeAppend则不会。

这些行为都很可能在未来的PostgreSQL发行中被改变。

5.10.5. 分区和约束排除

约束排除是一种与分区剪枝类似的查询优化技术。虽然它主要被用于使用传统继承方法实现的分区上,但它也可以被用于其他目的,包括用于声明式分区。

约束排除以非常类似于分区剪枝的方式工作,不过它使用每个表的CHECK约束 — 这也是它得名的原因 — 而分区剪枝使用表的分区边界,分区边界仅存在于声明式分区的情况中。另一点不同之处是约束排除仅在规划时应用,在执行时不会尝试移除分区。

由于约束排除使用CHECK约束,这导致它比分区剪枝要慢,但有时候可以被当作一种优点加以利用:因为甚至可以在声明式分区的表上(在分区边界之外)定义约束,约束排除可能可以从查询计划中消去额外的分区。

constraint_exclusion的默认(也是推荐的)设置不是on也不是off,而是一种被称为partition的中间设置,这会导致该技术仅被应用于可能工作在继承分区表上的查询。on设置导致规划器检查所有查询中的CHECK约束,甚至是那些不太可能受益的简单查询。

下列提醒适用于约束排除:

  • 约束排除仅适用于查询规划期间,和分区剪枝不同,它不适用于查询执行期间。

  • 只有查询的WHERE子句包含常量(或者外部提供的参数)时,约束排除才能有效果。例如,针对一个非不变函数(如CURRENT_TIMESTAMP)的比较不能被优化,因为规划器不知道该函数的值在运行时会落到哪个子表中。

  • 保持分区约束简单化,否则规划器可能无法验证哪些子表可能不需要被访问。如前面的例子所示,对列表分区使用简单的等值条件,对范围分区使用简单的范围测试。一种好的经验规则是分区约束应该仅包含分区列与常量使用B-树的可索引操作符的比较,因为只有B-树的可索引列才允许出现在分区键中。

  • 约束排除期间会检查父表的所有子表上的所有约束,因此大量的子表很可能明显地增加查询规划时间。因此,传统的基于继承的分区可以很好地处理上百个子表,不要尝试使用上千个子表。