2.10.?表分区

2.10.1. 概述
2.10.2. 声明式分区
2.10.3. 使用继承的实现
2.10.4. 划分和约束排除

UXDB支持基本的表划分。本小节介绍为喊拿庞蜗菲教ㄗ⒉嵬驹及怎样把划分实现为数据库设计的一部分。

2.10.1.?概述

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

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

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

  • 如果需求计划使用划分设计,可以通过增加或移除分区来完成批量载入和删除。 执行ALTER TABLE DETACH PARTITION或者使用DROP TABLE 删除一个单独的分区都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。

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

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

UXDB为以下形式的分区提供了内置支持:

范围分区

该表被分区到由键列或列集定义的范围中, 分配给不同分区的值范围之间没有重叠。例如,可以按日期范围进行分区, 也可以按特定业务对象的标识符范围进行分区。

列表分区

表通过明确列出每个分区中出现的键值进行分区。

如果您的应用程序需要使用上面未列出的其他形式的分区,则可以使用替代方法, 如继承和UNION ALL视图。这种方法提供了灵活性, 但没有内置声明式分区的一些性能优势。

2.10.2.?声明式分区

UXDB提供了一种方法来指定如何将表分成多个分区。 被分割的表被称为分区表。 该规范由分区方法以及要用作分区键 的列或表达式列表组成。

插入到分区表中的所有行将根据分区键的值分配到分区之一。 每个分区都有由其分区边界定义的数据子集。 目前支持的分区方法包括范围和列表,其中每个分区分别分配键的范围和键的列表。

分区本身可以被定义为分区表,使用所谓的子分区。 分区可以有自己的索引、约束和默认值,与其他分区不同。 索引必须为每个分区单独创建。有关创建分区表和分区的更多详细信息, 请参见CREATE TABLE

无法将常规表格转换为分区表格,反之亦然。但是, 可以将包含数据的普通表或分区表添加为分区表的分区,或从分区表中删除分区, 将其转换为独立表;请参阅ALTER TABLE以了解有关 ATTACH PARTITIONDETACH PARTITION子命令的更多信息。

单个分区链接到分区表,并在后台继承;但是, 不可能将前一节中讨论的某些继承特性与分区表和分区一起使用。例如, 分区不能拥有除分区表之外的其他父项,普通表也不能从分区表继承, 使后者成为其父项。这意味着分区表和分区不参与普通表的继承。 由于由分区表及其分区构成的分区层次结构仍然是继承层次结构, 因此除了一些例外情况外,所有正常的继承规则都适用, 如第?2.9?节中所述,最重要的是:

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

  • 当没有分区时,仅支持使用ONLY在分区表上添加或删除约束。 一旦存在分区,使用ONLY会导致错误, 因为仅支持在分区表上添加或删除约束,当分区存在时是不支持的。 相反,当不存在父表时,可以直接在分区上添加或删除约束。 由于分区表不直接拥有任何数据,因此尝试在分区表上使用 TRUNCATE ONLY将始终返回错误。

  • 分区不能拥有父表中不存在的列。在使用CREATE TABLE 创建分区时不能声明字段,也不能在创建完分区后使用ALTER TABLE 添加字段。仅当表的列准确匹配分区表,包括oid列时, 才可以使用ALTER TABLE ... ATTACH PARTITION 将该表添加为分区。

  • 如果NOT NULL约束存在于父表上,那么你不能删除分区字段上的该约束。

分区也可以是外表(参阅CREATE FOREIGN TABLE), 尽管它们会有一些普通表没有的限制。例如,插入到分区表的数据不会传到外表分区中。

2.10.2.1.?示例

假设澳门游戏平台注册网站正在为一个大型的冰淇淋公司构建一个数据库。该公司测量每天在每一个区域的最高气温以及冰淇淋销售。在概念上,澳门游戏平台注册网站想要一个这样的表:

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

由于该表的主要用途是为管理层提供在线报告,澳门游戏平台注册网站知道大部分查询将只会访问上周、上月或者上季度的数据。为了减少需要保存的旧数据的量,澳门游戏平台注册网站决定只保留最近3年的数据。在每一个月的开始,澳门游戏平台注册网站将删除最老的一个月的数据。 在这种情况下,澳门游戏平台注册网站可以使用划分来帮助澳门游戏平台注册网站满足对于测量表的所有不同需求。

要在这种情况下使用声明式分区,请使用以下步骤:

  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. 创建分区。每个分区的定义必须声明对应于父表分区方法和分区键的范围。 请注意,新分区的声明范围值覆盖现有分区的值将导致一个错误。 插入到父表的数据不能映射到现有分区之一的话讲导致一个错误; 必须手动添加适当的分区。

    这样创建的分区在各方面都是普通UXDB表 (或者可能是外表)。可以单独为每个分区指定表空间和存储参数。

    不需要为分区创建描述分区范围条件的表约束。 在需要引用时,分区约束是隐式的从分区范围声明中生成的。

    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')
        TABLESPACE fasttablespace
        WITH (parallel_workers = 4);

    要实现子分区,在创建单个分区的语句中声明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 列进一步重定向到其分区之一。指定的分区键可能与父分区键重叠, 但在指定子分区的边界时应该小心, 以使其接受的数据集构成分区自己的界限允许的子集; 系统不会试图检查是否确实如此。

  3. 对于每一个分区,在键列上创建索引,以及您可能需要的其他索引。 (键索引不是绝对必要的,但在大多数情况下它都是有用的。 如果您希望键值是唯一的,那么您还要为每个分区创建一个唯一或主键约束。)

    CREATE INDEX ON measurement_y2006m02 (logdate);
    CREATE INDEX ON measurement_y2006m03 (logdate);
    ...
    CREATE INDEX ON measurement_y2007m11 (logdate);
    CREATE INDEX ON measurement_y2007m12 (logdate);
    CREATE INDEX ON measurement_y2008m01 (logdate);

  4. 确保在uxsinodb.conf中constraint_exclusion配置参数没有被禁用。如果它被禁用,查询将不会被按照期望的方式优化。

在上面的例子中,澳门游戏平台注册网站将每个月创建一个新的分区, 所以编写一个脚本可以自动生成所需的DDL。

2.10.2.2.?分区维护

通常当初始定义的表倾向于动态变化时,一组分区会被创建。删除旧的分区并周期性地为新数据增加新分区是很常见的。划分的一个最重要的优点是可以通过操纵分区结构来使得这种痛苦的任务几乎是自发地完成,而不需要去物理地移除大量的数据。

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

DROP TABLE measurement_y2006m02;

这可以非常快地删除百万级别的记录,因为它不需要逐一地删除记录。 不过,请注意,上述命令需要在父表上获取一个ACCESS EXCLUSIVE锁。

另一个经常使用的选项是将分区从被划分的表中移除,但是把它作为一个独立的表保留下来:

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

这允许在数据被删除前执行更进一步的操作。例如,这是一个很有用的时机通过COPYux_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'
-- 可能做一些其他数据准备工作

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

在运行ATTACH PARTITION命令之前, 建议在要附加的表上创建一个CHECK约束来描述所需的分区约束。 这样,系统将能够跳过扫描来验证隐式分区约束。如果没有这样的约束, 将在父表上保存一个ACCESS EXCLUSIVE 锁来扫描该表以验证分区约束。然后可以在ATTACH PARTITION 完成后删除约束,因为它不再是必需的。

2.10.2.3.?限制

以下限制适用于分区表:

  • 没有工具可用于在所有分区上自动创建匹配索引。 索引必须通过单独的命令添加到每个分区。 这也意味着无法创建跨所有分区的主键、唯一约束或排除约束; 只能分别约束每个叶子分区。

  • 由于分区表上不支持主键,因此不支持引用分区表的外键, 也不支持将分区表中的外键引用到某个其他表。

  • 对分区表使用ON CONFLICT子句会导致错误, 因为唯一或排除约束只能在单个分区上创建。 不支持在整个分区层次结构中实施唯一性(或排除约束)。

  • 导致行从一个分区移动到另一个分区的UPDATE失败, 因为该行的新值无法满足原始分区的隐式分区约束。

  • 必要时,行触发器必须在单独的分区上定义,而不是在分区表上定义。

2.10.3.?使用继承的实现

尽管内置的声明式分区适用于大多数常见用例,但在某些情况下, 更灵活的方法可能会有用。可以使用表继承来实现分区, 这允许声明式分区不支持的几个功能,例如:

  • 分区强制执行一条规则,即所有分区必须与父表具有完全相同的一组列, 但表继承允许子表具有不在父表中的额外列。

  • 表继承允许多重继承。

  • 声明式分区仅支持列表和范围分区,而表继承允许按照用户选择的方式分割数据。 (但是,请注意,如果约束排除无法有效地修剪分区,查询性能将会很差。)

  • 有些操作在使用声明式分区时需要比使用表继承时更强的锁定。例如, 向分区表添加或删除分区需要在父表上使用ACCESS EXCLUSIVE锁, 而在普通继承的情况下SHARE UPDATE EXCLUSIVE锁就足够了。

2.10.3.1.?示例

澳门游戏平台注册网站使用上面使用的相同的measurement表。 要使用继承将其作为分区表来实现,请使用以下步骤:

  1. 创建表,所有的分区都将继承它。 这个表将不会包含任何数据。不要在这个表上定义任何检查约束, 除非准备将它们应用到所有分区。同样也不需要定义任何索引或者唯一约束。 在澳门游戏平台注册网站的示例中,主表是之前定义的measurement表。

  2. 创建一些继承于主表的表。通常, 这些表不会在从主表继承的列集中增加任何列。 就像声明式分区一样,这些分区在各个方面都是普通的 UXDB表(或外部表)。

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

    完成函数创建后,澳门游戏平台注册网站创建一个调用该触发器函数的触发器:

    CREATE TRIGGER insert_measurement_trigger
        BEFORE INSERT ON measurement
        FOR EACH ROW EXECUTE PROCEDURE 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 pluxsql;

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

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

    注意

    在实践中,如果大部分插入都会进入最新的分区,最好先检查它。为了简洁,澳门游戏平台注册网站为触发器的检查采用了和本例中其他部分一致的顺序。

    另一种将插入重定向到合适的分区表的方法是在主表上建立规则而不是触发器,例如:

    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. 确保uxsinodb.conf没有禁用 constraint_exclusion 配置参数。如果禁用了,查询将不会根据需要进行优化。

如澳门游戏平台注册网站所见,一个复杂的划分模式可能需要大量的DDL。在上面的例子中,澳门游戏平台注册网站需要每月创建一个新分区,所以最好能够编写一个脚本自动地生成所需的DDL。

2.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'
-- 可能做一些其他数据准备工作


ALTER TABLE measurement_y2008m02 INHERIT measurement;

2.10.3.3.?警告

以下注意事项适用于使用继承实现的分区表:

  • 没有自动的方法来验证所有的CHECK约束是相互排斥的。 创建生成分区并创建和/或修改关联对象的代码比手工编写每个代码更安全。

  • 这里显示的方案假定行的分区键列不会改变,或者至少不会变化太大 以至于需要移动到另一个分区。一个试图这样做的UPDATE 会因为CHECK约束而失败。如果您需要处理这种情况, 可以在分区表上放置合适的更新触发器,但这会使结构的管理更为复杂。

  • If you are using manual VACUUM or ANALYZE commands, don't forget that you need to run them on each partition individually. A command like: 如果您正在手动使用VACUUMANALYZE命令, 请不要忘记您需要分别在每个分区上运行它们。像这样的命令:

    ANALYZE measurement;

    将只处理主表。

  • 使用ON CONFLICT子句的INSERT 语句不可能像预期的那样工作,因为ON CONFLICT 仅在指定的目标关系、而不是它的子关系上违反唯一约束时采取动作。

  • 除非应用程序明确知道分区方案, 否则需要触发器或规则将行分配到期望的分区。触发器编写起来可能很复杂, 并且比声明式分区内部执行的元组分配要慢得多。

2.10.4.?划分和约束排除

约束排除是一种查询优化技术,它可以为按照以上方式定义的被划分表提高性能(声明式分区表和使用继承实现的分区表)。例如:

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

如果没有约束排除,上述查询将扫描measurement表的每一个分区。在启用约束排除后,规划器将检查每一个分区的约束来确定该分区需不需要被扫描,因为分区中可能不包含满足查询WHERE子句的行。如果规划器能够证实这一点,则它将会把该分区排除在查询计划之外。

可以使用EXPLAIN命令来显示开启了constraint_exclusion的计划和没有开启该选项的计划之间的区别。一个典型的未优化的计划是:

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=158.66..158.68 rows=1 width=0)
   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m02 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m12 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

其中的某些或者全部分区将会使用索引扫描而不是全表顺序扫描,但是关键在于根本不需要扫描旧分区来回答这个查询。当澳门游戏平台注册网站开启约束排除后,对于同一个查询澳门游戏平台注册网站会得到一个更加廉价的计划:

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=63.47..63.48 rows=1 width=0)
   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

注意约束排除只由CHECK约束驱动,而非索引的存在。因此,没有必要在关键列上定义索引。是否在给定分区上定义索引取决于澳门游戏平台注册网站希望查询经常扫描表的大部分还是小部分。在后一种情况中索引将会发挥作用。

constraint_exclusion的默认(也是推荐)设置实际上既不是on也不是off,而是一个被称为partition的中间设置,这使得该技术只被应用于将要在被分区表上工作的查询。设置on将使得规划器在所有的查询中检查CHECK约束,即使简单查询不会从中受益。

以下注意事项适用于继承和分区表使用的约束排除:

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

  • 保持划分约束简单,否则规划器可能没有办法验证无需访问的分区。按前面的例子所示,为列表划分使用简单相等条件或者为范围划分使用简单范围测试。 一个很好的经验法则是分区约束应该只包含使用B-tree索引操作符的分区列与常量的比较, 这甚至适用于分区表,因为只允许B-tree索引列在分区键中。(这在使用声明式分区时不是问题, 因为自动生成的约束非常简单,可以被规划器理解。)

  • 在约束排除期间,主表所有的分区上的所有约束都会被检查,所以大量的分区将会显著地增加查询规划时间。使用这些技术的划分在大约最多100个分区的情况下工作得很好,但是不要尝试使用成千个分区。

XML 地图 | Sitemap 地图