【PostgreSQL】表管理-分区表

最新推荐文章于 2024-05-19 17:03:37 发布

DBA圈小圈

最新推荐文章于 2024-05-19 17:03:37 发布

阅读量1k

点赞数 31

分类专栏： PostgreSQL 文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/sinat_36528886/article/details/135477949

版权

PostgreSQL 专栏收录该内容

78 篇文章 16 订阅

订阅专栏

概述

分区是指将逻辑上一个大表拆分为较小的物理部分。分区可以提供以下几个好处：

在某些情况下，查询性能可以显著提高，特别是当表的大多数访问量很大的行位于单个分区或少量分区中时。分区有效地替代了索引的上层树级别，使索引中大量使用的部分更有可能适合内存。
当查询或更新访问单个分区的很大一部分时，可以通过使用该分区的顺序扫描而不是使用索引来提高性能，因为索引需要分散在整个表中进行随机访问读取。
如果在分区设计中考虑了使用模式，则可以通过添加或删除分区来完成批量加载和删除。使用 DROP TABLE或ALTER TABLE执行，DETACH PARTITION 删除单个分区比批量操作快得多。VACUUM，DELETE命令也完全避免了批量
很少使用的数据可以迁移到更便宜、速度更慢的存储介质。

通常，只有当一张桌子非常大时，这些好处才是值得的。表从分区中获益的确切时间点取决于应用程序，但经验法则是表的大小应超过数据库服务器的物理内存。

表分区

PostgreSQL 为以下形式的分区提供内置支持：

范围分区
该表被划分为由一个键列或一组列定义的“范围”，分配给不同分区的值范围之间没有重叠。例如：可以按日期范围或特定业务对象的标识符范围，来进行分区。
列表分区
通过显式列出每个分区中显示的键值来对表进行分区。
哈希分区
通过为每个分区指定一个模数和余数来对表进行分区。每个分区将保存分区键的哈希值除以指定模数将产生指定余数的行。

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

Range范围分区

先创建一张表带有年龄，然后我们根据年龄分段来进行分区，创建表语句如下：

CREATE TABLE pkslow_person_r (
    age int not null,
    city varchar not null
) PARTITION BY RANGE (age);

这个语句已经指定了按age字段来分区了，接着创建分区表：

create table pkslow_person_r1 partition of pkslow_person_r for values from (MINVALUE) to (10);
create table pkslow_person_r2 partition of pkslow_person_r for values from (11) to (20);
create table pkslow_person_r3 partition of pkslow_person_r for values from (21) to (30);
create table pkslow_person_r4 partition of pkslow_person_r for values from (31) to (MAXVALUE);

这里创建了四张分区表，分别对应年龄是0到10岁、11到20岁、21到30岁、30岁以上。

接着我们插入一些数据：

insert into pkslow_person_r(age, city) VALUES (1, 'GZ');
insert into pkslow_person_r(age, city) VALUES (2, 'SZ');
insert into pkslow_person_r(age, city) VALUES (21, 'SZ');
insert into pkslow_person_r(age, city) VALUES (13, 'BJ');
insert into pkslow_person_r(age, city) VALUES (43, 'SH');
insert into pkslow_person_r(age, city) VALUES (28, 'HK');

可以看到这里的表名还是pkslow_person_r，而不是具体的分区表，说明对于客户端是无感知的。
我们查询也一样

postgres=# select * from pkslow_person_r;
 age | city 
-----+------
   1 | GZ
   2 | SZ
  13 | BJ
  21 | SZ
  28 | HK
  43 | SH
(6 rows)

postgres=# select * from pkslow_person_r1;
 age | city 
-----+------
   1 | GZ
   2 | SZ
(2 rows)

我们再看看表的分区关系：

postgres=# \d+ pkslow_person_r;
                                    Partitioned table "public.pkslow_person_r"
 Column |       Type        | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+-------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 age    | integer           |           | not null |         | plain    |             |              | 
 city   | character varying |           | not null |         | extended |             |              | 
Partition key: RANGE (age)
Partitions: pkslow_person_r1 FOR VALUES FROM (MINVALUE) TO (10),
            pkslow_person_r2 FOR VALUES FROM (11) TO (20),
            pkslow_person_r3 FOR VALUES FROM (21) TO (30),
            pkslow_person_r4 FOR VALUES FROM (31) TO (MAXVALUE)

postgres=#

List列表分区

类似的，列表分区是按特定的值来分区，比较某个城市的数据放在一个分区里。这里不再给出每一步的讲解，代码如下：

-- 创建主表
create table pkslow_person_l (
                          age int not null,
                          city varchar not null
) partition by list (city);

-- 创建分区表
CREATE TABLE pkslow_person_l1 PARTITION OF pkslow_person_l FOR VALUES IN ('GZ');
CREATE TABLE pkslow_person_l2 PARTITION OF pkslow_person_l FOR VALUES IN ('BJ');
CREATE TABLE pkslow_person_l3 PARTITION OF pkslow_person_l DEFAULT;

-- 插入测试数据
insert into pkslow_person_l(age, city) VALUES (1, 'GZ');
insert into pkslow_person_l(age, city) VALUES (2, 'SZ');
insert into pkslow_person_l(age, city) VALUES (21, 'SZ');
insert into pkslow_person_l(age, city) VALUES (13, 'BJ');
insert into pkslow_person_l(age, city) VALUES (43, 'SH');
insert into pkslow_person_l(age, city) VALUES (28, 'HK');
insert into pkslow_person_l(age, city) VALUES (28, 'GZ');

当我们查询第一个分区的时候，只有广州的数据：

postgres=# select * from pkslow_person_l1;
 age | city 
-----+------
   1 | GZ
  28 | GZ
(2 rows)

postgres=#

Hash哈希分区

哈希分区是指按字段取哈希值后再分区。具体的语句如下：

-- 创建主表
create table pkslow_person_h (
                          age int not null,
                          city varchar not null
) partition by hash (city);

-- 创建分区表
create table pkslow_person_h1 partition of pkslow_person_h for values with (modulus 4, remainder 0);
create table pkslow_person_h2 partition of pkslow_person_h for values with (modulus 4, remainder 1);
create table pkslow_person_h3 partition of pkslow_person_h for values with (modulus 4, remainder 2);
create table pkslow_person_h4 partition of pkslow_person_h for values with (modulus 4, remainder 3);

-- 插入测试数据
insert into pkslow_person_h(age, city) VALUES (1, 'GZ');
insert into pkslow_person_h(age, city) VALUES (2, 'SZ');
insert into pkslow_person_h(age, city) VALUES (21, 'SZ');
insert into pkslow_person_h(age, city) VALUES (13, 'BJ');
insert into pkslow_person_h(age, city) VALUES (43, 'SH');
insert into pkslow_person_h(age, city) VALUES (28, 'HK');

可以看到创建分区表的时候，我们用了取模的方式，所以如果要创建N个分区表，就要取N取模。
随便查询一张分区表如下：

postgres=# select * from pkslow_person_h4;
 age | city 
-----+------
   2 | SZ
  21 | SZ
  13 | BJ
(3 rows)

postgres=#

可以看到同是SZ的哈希值是一样的，肯定会分在同一个分区，而BJ的哈希值取模后也属于同一个分区。

分区维护

通常，最初定义表时建立的分区集不会保持静态。通常希望删除保存旧数据的分区，并定期为新数据添加新分区。分区最重要的优点之一恰恰是，它允许通过操作分区结构几乎立即执行这项痛苦的任务，而不是在物理上移动大量数据。

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

DROP TABLE pkslow_person_r4;

这可以非常快速地删除数百万条记录，因为它不必单独删除每条记录。但请注意，上述命令需要对父表进行锁定(ACCESS EXCLUSIVE)。

另一个通常更可取的选项是从分区表中删除分区，但保留对分区本身作为表的访问权。这有两种形式：

ALTER TABLE pkslow_person_r DETACH PARTITION pkslow_person_r4;
ALTER TABLE pkslow_person_r DETACH PARTITION pkslow_person_r4 CONCURRENTLY;

这允许在删除数据之前对数据执行进一步的操作。例如COPY，这通常是使用、pg_dump 或类似工具备份数据的有用时间。这也可能是将数据聚合为较小格式、执行其他数据操作或运行报表的有用时机。该命令的第一种形式需要对父表进行锁定ACCESS EXCLUSIVE。添加第二种形式CONCURRENTLY的限定符允许分离操作只需要锁定父表 SHARE UPDATE EXCLUSIVE，但请了解 ALTER TABLE …DETACH PARTITION 有关限制的详细信息。

同样，我们可以添加一个新分区来处理新数据。我们可以在分区表中创建一个空分区，就像上面创建原始分区一样：

CREATE TABLE pkslow_person_r5 PARTITION OF pkslow_person_r 
    FOR VALUES FROM ('40') TO ('50');

作为替代方案，有时在分区结构外部创建新表，并在以后将其附加为分区会更方便。这允许在新数据出现在分区表中之前对其进行加载、检查和转换。此外，该操作只需要对分区表进行锁定，而不是所需的锁，因此对分区表上的并发操作更友好。该选项有助于避免繁琐地重复父表的定义：

CREATE TABLE pkslow_person_a 
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

ALTER TABLE pkslow_person_a ADD CONSTRAINT cstr_pkslow_person_a
   CHECK ( age >= int MINVALUE AND age < int  '10' );

\copy cstr_pkslow_person_a from 'pkslow_person_a'
-- possibly some other data preparation work

ALTER TABLE pkslow_person_a ATTACH PARTITION cstr_pkslow_person_a 
    FOR VALUES FROM MINVALUE TO 10;

在运行该命令之前，建议在要附加的表上创建一个与预期分区约束匹配的约束，如上所示。这样，系统将能够跳过扫描，否则需要扫描来验证隐式分区约束。如果没有该约束，将扫描该表以验证分区约束，同时对该分区保持锁定。建议在完成后删除 now-redundant 约束。如果附加的表本身是分区表，则其每个子分区都将被递归锁定和扫描，直到遇到合适的约束或到达叶分区。

同样，如果分区表具有分区，则建议创建一个约束，以排除要附加的分区的约束。如果不这样做，则将扫描该分区以验证它是否不包含应位于所附加分区中的记录。此操作将在对分区保持锁定时执行。如果分区本身是一个分区表，则其每个分区都将以与所附加表相同的方式进行递归检查，如上所述。

如上所述，可以在分区表上创建索引，以便它们自动应用于整个层次结构。这非常方便，因为不仅现有分区会被索引，而且将来创建的任何分区也会被索引。一个限制是，在创建此类分区索引时，不能使用限定符。为了避免长时间的锁定时间，可以使用分区表;此类索引被标记为无效，并且分区不会自动应用索引。可以使用单独创建分区上的索引，然后使用将分区上的索引附加到父分区上的索引。将所有分区的索引附加到父索引后，将自动将父索引标记为有效。

CREATE INDEX pkslow_person_b_idx ON ONLY pkslow_person_b (id);

CREATE INDEX pkslow_person_b_idx 
    ON pkslow_person_b (unitsales);
ALTER INDEX pkslow_person_b_idx 
    ATTACH PARTITION pkslow_person_b1;
...

这种技术也可以与和约束一起使用;索引是在创建约束时隐式创建的。

ALTER TABLE ONLY pkslow_person_b ADD UNIQUE (cid, logdate);

ALTER TABLE pkslow_person_b_idx ADD UNIQUE (city_id, logdate);
ALTER INDEX pkslow_person_b_cid_logdate_key
    ATTACH PARTITION pkslow_person_b1_cid_logdate_key;
...

限制

以下限制适用于分区表：

若要在分区表上创建唯一键或主键约束，分区键不得包含任何表达式或函数调用，并且约束的列必须包含所有分区键列。之所以存在这种限制，是因为构成约束的各个索引只能直接在自己的分区中强制执行唯一性;因此，分区结构本身必须保证不同分区中没有重复项。
无法创建跨整个分区表的排除约束。只能对每个叶分区单独施加这种约束。同样，此限制源于无法强制实施跨分区限制。
BEFORE ROW触发器无法更改哪个分区是新行的最终目标。
不允许在同一分区树中混合使用临时关系和永久关系。因此，如果分区表是永久性的，那么它的分区也必须是永久性的，如果分区表是临时的，则同样如此。使用临时关系时，分区树的所有成员必须来自同一会话。

各个分区在后台使用继承链接到其分区表。但是，不可能将继承的所有通用功能都用于以声明方式分区的表或其分区，如下所述。值得注意的是，分区不能具有除分区表以外的任何父级，也不能同时从分区表和常规表继承。这意味着分区表及其分区永远不会与常规表共享继承层次结构。

由于由分区表及其分区组成的分区层次结构仍然是继承层次结构，并且所有正常的继承规则都适用，但有一些例外：tableoid

分区不能包含父分区中不存在的列。在创建分区时无法指定列，也不能在事后使用ALTER TABLE向分区添加列。只有当表的列与父表完全匹配时，才能将表添加为分区:ALTER TABLE … ATTACH PARTITION。

分区表的CHECK和NOT NULL约束始终由其所有分区继承。不允许在分区表上创建已标记的约束。如果父表中存在相同的约束，则不能删除分区列的约束。

只要没有分区，就支持仅在分区表上添加或删除约束。一旦分区存在，使用将导致错误。相反，可以添加ONLY对分区本身的约束，并删除（如果父表中不存在这些约束）。

由于分区表本身没有任何数据，因此尝试在分区表上使用TRUNCATE ONLY将始终返回错误。

使用继承进行分区

虽然内置的声明性分区适用于大多数常见用例，但在某些情况下，更灵活的方法可能会有所帮助。可以使用表继承来实现分区，这允许声明式分区不支持的多个功能，例如：

对于声明性分区，分区必须具有与分区表完全相同的列集，而对于表继承，子表可能具有父表中不存在的额外列。
表继承允许多重继承。
声明式分区仅支持范围、列表和哈希分区，而表继承允许以用户选择的方式划分数据。（但请注意，如果约束排除无法有效地修剪子表，则查询性能可能较差。

示例

此示例生成与上述声明性分区示例等效的分区结构。使用以下步骤：

创建“根”表，所有“子”表都将从该表继承。此表将不包含任何数据。不要在此表上定义任何检查约束，除非您希望将它们平等地应用于所有子表。定义任何索引或唯一约束也毫无意义。在我们的示例中，根表是最初定义的表：measurement

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

创建多个“子”表，每个表都继承自根表。通常，这些表不会向从根继承的集合中添加任何列。就像声明式分区一样，这些表在各个方面都是普通的 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);

向子表添加非重叠表约束，以定义每个子表中允许的键值。

典型的例子是：

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

对于每个子表，在键列上创建一个索引，以及您可能需要的任何其他索引。

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

我们希望我们的应用程序能够说出数据并将其重定向到相应的子表中。我们可以通过将合适的触发函数附加到根表来安排它。如果数据将只添加到最新的子项中，我们可以使用一个非常简单的触发函数：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;

触发器定义与之前相同。请注意，每个测试必须与其子表的约束完全匹配。IFCHECK

虽然此函数比单月情况更复杂，但它不需要经常更新，因为可以提前添加分支。

将插入重定向到相应子表的另一种方法是在根表上设置规则，而不是触发器。例如：

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

规则的开销明显高于触发器，但开销是按每个查询支付一次，而不是每行支付一次，因此此方法可能适用于大容量插入情况。但是，在大多数情况下，触发方法将提供更好的性能。

请注意，忽略规则。如果要用于插入数据，则需要复制到正确的子表中，而不是直接复制到根表中。触发触发器，因此如果使用触发器方法，则可以正常使用它。COPYCOPYCOPY

规则方法的另一个缺点是，如果规则集没有涵盖插入日期，则没有简单的方法可以强制执行错误;数据将以静默方式进入根表。

确保 constraint_exclusion 配置参数未在 ;否则，可能会不必要地访问子表。postgresql.conf

正如我们所看到的，复杂的表层次结构可能需要大量的 DDL。在上面的示例中，我们将每个月创建一个新的子表，因此编写一个自动生成所需 DDL 的脚本可能是明智的。

要快速删除旧数据，只需删除不再需要的子表：

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;

注意事项

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

没有自动方法可以验证所有约束是否互斥。创建生成子表并创建和/或修改关联对象的代码比手动编写每个子表更安全。CHECK
索引和外键约束适用于单个表，而不适用于其继承子表，因此它们需要注意一些注意事项。
此处显示的方案假定行的键列的值永远不会更改，或者至少不会更改到需要移动到另一个分区的程度。尝试这样做的 An 将因约束而失败。如果需要处理此类情况，可以在子表上放置合适的更新触发器，但这会使结构的管理变得更加复杂。UPDATECHECK
如果您使用的是手动或命令，请不要忘记您需要在每个子表上单独运行它们。像这样的命令：VACUUMANALYZE将只处理根表。

ANALYZE measurement;

INSERT带有子句的语句不太可能按预期工作，因为只有在对指定目标关系（而不是其子关系）发生唯一冲突时才会执行该操作。ON CONFLICTON CONFLICT
需要触发器或规则才能将行路由到所需的子表，除非应用程序明确知道分区方案。触发器的编写可能很复杂，并且比通过声明性分区在内部执行的元组路由慢得多。

分区修剪

分区修剪是一种查询优化技术，可提高以声明方式分区的表的性能。举个例子：

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

如果不进行分区修剪，上述查询将扫描表的每个分区。启用分区修剪后，规划器将检查每个分区的定义，并证明不需要扫描该分区，因为它不能包含任何符合查询子句的行。当计划器可以证明这一点时，它会从查询计划中排除（修剪）分区。measurementWHERE

通过使用 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)
   ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2008-01-01'::date)

请注意，分区修剪仅由分区键隐式定义的约束驱动，而不是由索引的存在驱动。因此，没有必要在键列上定义索引。是否需要为给定分区创建索引取决于您是希望扫描该分区的查询通常扫描分区的大部分，还是只扫描一小部分。索引在后一种情况下会有所帮助，但对前一种情况没有帮助。

分区修剪不仅可以在规划给定查询期间执行，还可以在执行期间执行。这很有用，因为当子句包含其值在查询规划时未知的表达式时，它可以允许修剪更多分区，例如，在语句中定义的参数，使用从子查询获取的值，或者使用嵌套循环联接内侧的参数化值。可以在以下任何时间执行执行期间的分区修剪：PREPARE

在查询计划初始化期间。可以在此处对在执行的初始化阶段已知的参数值执行分区修剪。在此阶段被修剪的分区将不会显示在查询的或中。通过观察输出中的“已删除的子计划”属性，可以确定在此阶段删除的分区数。EXPLAINEXPLAIN ANALYZEEXPLAIN

在实际执行查询计划期间。还可以在此处执行分区修剪，以使用仅在实际查询执行期间已知的值删除分区。这包括来自子查询的值和来自执行时参数的值，例如来自参数化嵌套循环联接的值。由于这些参数的值在执行查询期间可能会多次更改，因此每当分区修剪使用的执行参数之一发生更改时，都会执行分区修剪。确定分区是否在此阶段被修剪，需要仔细检查输出中的属性。对应于不同分区的子计划可能具有不同的值，具体取决于每个分区在执行期间被修剪的次数。有些可能看起来好像每次都被修剪过。loopsEXPLAIN ANALYZE(never executed)

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

分区和约束排除

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

约束排除的工作方式与分区修剪非常相似，不同之处在于它使用每个表的约束（这因此而得名），而分区修剪使用表的分区边界，该边界仅在声明性分区的情况下存在。另一个区别是约束排除仅在计划时应用;在执行时不会尝试删除分区。

与分区修剪相比，约束排除使用约束使其速度较慢，这一事实有时可以用作一个优势：由于约束甚至可以在声明性分区表上定义，因此除了其内部分区边界外，约束排除可能能够从查询计划中省略其他分区。

constraint_exclusion 的默认（和推荐）设置既不是也不是，而是一个名为的中间设置，这会导致该技术仅应用于可能处理继承分区表的查询。该设置使计划器检查所有查询中的约束，即使是不太可能受益的简单约束。

以下注意事项适用于约束排除：

约束排除仅在查询规划期间应用，这与分区修剪不同，分区修剪也可以在查询执行期间应用。
仅当查询的子句包含常量（或外部提供的参数）时，约束排除才有效。例如，无法优化与非不可变函数（如WHERECURRENT_TIMESTAMP
保持分区约束简单，否则规划人员可能无法证明可能不需要访问子表。对列表分区使用简单的相等条件，或对范围分区使用简单的范围测试，如前面的示例所示。一个好的经验法则是，分区约束应仅包含分区列与使用 B 树可索引运算符的常量的比较，因为分区键中只允许 B 树可索引列。
在排除约束期间，将检查父表的所有子表的所有约束，因此大量子约束可能会大大增加查询规划时间。因此，基于继承的传统分区可以很好地处理多达一百个子表;不要试图使用成千上万的孩子。

DBA圈小圈

关注

31
点赞
踩
17

收藏

觉得还不错? 一键收藏
打赏
0
评论
【PostgreSQL】表管理-分区表

分区是指将逻辑上一个大表拆分为较小的物理部分。通常，只有当一张桌子非常大时，这些好处才是值得的。表从分区中获益的确切时间点取决于应用程序，但经验法则是表的大小应超过数据库服务器的物理内存。
复制链接

扫一扫