Greenplum Database 管理员指南 (V6.2.1)

Greenplum Database 管理员指南 V6.2.1

 本地操作与分布式操作 -- 在处理查询时，很多处理如关联、排序、聚合等算子，

如果能够在Instance本地完成，其效率将高于需要从其他Instance获取数据的

操作。当不同的Table使用相同的DK时，在DK上的关联或者排序操作将会以最高

效的方式把绝大部分工作在Instance本地完成。若Table使用随机分布策略，将

会出现更多的数据移动(Motion)，虽然随机分布策略可以绝对确保数据分布的平

坦性，但也只是确保了数据分布的平坦性。

 平坦的查询处理 -- 在一个查询正被处理时，我们希望所有的Instance都能够

处理等量的工作负载，从而尽可能达到最好的性能。有时候查询场景与数据分布策

略很不吻合，这时很可能导致工作负载的倾斜。例如，有一张交易流水表。该表的

DK为公司名称，那么数据分布的HASH算法将基于公司名称的值来计算，假如有一

个查询以某个特定的公司名称作为查询条件，该查询任务将仅在一个Instance

上执行。这里需要解释一下，这种交易流水表，特定公司名称可能会对应大量的记

录，这就导致，针对某些交易规模很大的公司进行查询时，繁重的计算任务由一个

Instance来完成，这种情况是不应该发生的。编者认为，这个例子是用以说明查

询倾斜是如何发生的，这个例子讲述的倾斜与表的分布键有关，虽然该表整体上可

能没有显著的倾斜。但不等于说不能使用查询条件字段作为分布键，在真实的应用

中，有时为了提高索引查询的并发性能，会特意按照查询字段作为分布键，当然这

种字段的重复度较低，由单个Instance完成任务更经济。

复制表(DISTRIBUTED REPLICATED)，应该仅用于小表，将数据复制到所有

Instance 的是代价极高的，而且，随着集群规模的增大，这种代价愈加的凸显，应

该绝对禁止将尺寸较大的事实表使用复制分布策略！

复制表的主要应用场景：

 在复制表上没有UDF无法在Instance上查询的限制，以前，UDF如果访问了业务

表，则不允许在Instance上执行，而如果访问的是复制表，UDF在Instance上

允许对该表进行只读的查询，当然，修改数据的操作仍然是不被允许的。

 对于与其他表关联时需要被广播的小表来说，使用复制分布策略可以避免广播操作，

从而提升查询的性能，实际上，数据相当于提前广播好了。

注意：隐藏的系统字段（ctid、cmin、cmax、xmin、xmax和gp_segment_id）在

复制表上是不可用的，如果试图查询这些字段，将会得到一个字段不存在的报错信息。

虽然，这些系统字段对于从Master的访问来说没有意义，或者可能会带来歧义或者混

淆，但是，不等于说这些字段是真的不存在的，因为复制表在Instance上也是普通的

表，也是有这些字段的，所以，如果直接连接到Instance上去查询，是可以查到这些

字段信息的。

Greenplum Database 管理员指南 V6.2.1

声明分布键

在创建Table时有一个额外的子句用以指明分布策略。如果在创建Table时没有指

明DISTRIBUTED BY、DISTRIBUTED RANDOMLY或者DISTRIBUTED REPLICATED

子句，GP将会选择使用HASH分布，并依次考虑使用主键(假如该Table有的话)或者第

一个字段作为HASH分布的DK。几何类型或者自定义类型的Column是不适合作为GP的

DK的。如果一个Table没有一个合适类型的Column作为DK，该表将使用随机分布策略。

另外，如果设置了gp_create_table_random_default_distribution参数的值

为on，在不指定任何分布策略的情况下，将会等同于DISTRIBUTED RANDOMLY，这

很重要，至少可以避免自动选择DISTINCT值很少的第一个字段作为分布键。

复制表是没有分布键的，因为每一条记录都会分布到整个集群的所有Instance上。

为了确保数据的平坦分布，可能需要选择一个具有较高唯一性的Column作为DK，

如果达不到平坦的效果，也可以选择DISTRIBUTED RANDOMLY策略，但这只应该作为

最后的选择。例如：

=# CREATE TABLE products (

name varchar(40),

prod_id integer,

supplier_id integer

)DISTRIBUTED BY (prod_id);

=# CREATE TABLE random_stuff (

things text,

doodads text,

etc text

) DISTRIBUTED RANDOMLY;

选择表的存储模式

GP提供几种灵活的存储模式(或者混合模式)。在创建一张新的TABLE时，有几个

选项来决定数据如何储存在磁盘上。本节介绍这几种选项，以及出于工作负载的考虑如

何实现最佳的储存模式。

注意：为了简化建表时定义存储模式，可以设置缺省的存储选项，可以通过参数

gp_default_storage_options定义缺省存储选项。

Greenplum Database 管理员指南 V6.2.1

堆存储

缺省情况下GP使用与PostgreSQL相同的存储模式--堆存储。堆存储模式在OLTP

类型工作负载的DB中很常用 -- 更适合数据在初始装载后经常变化的场景。UPDATE

和DELETE操作需要对ROW级别做版本控制从而确保DB事务处理的可靠性。堆表更适合

一些小表，例如维表，这种表可能会在初始化装载后经常更新数据。

创建堆表

行存堆表是缺省的存储模式，创建堆表时不需要额外的CREATE TABLE语法。例如：

=# CREATE TABLE foo (a int, b text) DISTRIBUTED BY (a);

在6版本中，引入了新的概念，全局死锁检测，以实现降低UPDATE和DELETE锁级

别的目标，在6以前的版本中，无论是Heap表还是AO(Column表也是AO表的一种，虽

然有人喜欢称为CO表)表，UPDATE和DELETE操作都是EXCLUSIVE锁，也就是说，在6

之前的版本，一张表上，同时只能有一个UPDATE或者DELETE的语句正在被执行，其

他的UPDATE或者DELETE语句需要等待前面的语句执行完成之后才能获得所需要的锁。

在6版本开始，对于Heap表，打开全局死锁监测并关闭Orac优化器，Heap表的

UPDATE和DELETE操作的锁将降低为行级排他锁：

$ gpconfig -c gp_enable_global_deadlock_detector -v on

$ gpconfig -c optimizer -v off

$ psql postgres -c "CHECKPOINT"

$ gpstop -af

$ gpstart -a

另外，如果要进行大并发的INSERT，DELETE和UPDATE操作，建议关闭

log_statement参数，因为过多的日志输出也会影响这种操作的极限性能。编者认为，

不应该多度追求这种OLTP型的性能，如果可以通过批量或者微批的形式来处理业，将

可以更好的发挥和利用GP的MPP优势，不要总是热衷于跟技术较劲。

追加优化存储

在数据仓库等分析型场景，追加优化(AO)表会表现出更好的性能，这种存储模式

非常适合事实表，事实表通常都是规模很大的表，一般都是批量数据操作和只读查询操

作，另外，AO表不再维护MVCC信息，可以节省一些存储空间，不仅如此，AO表一般还

Greenplum Database 管理员指南 V6.2.1

会选择压缩存储，将可以大大节省存储空间。不过，AO表不适合单行INSERT操作，这

是强烈建议应该避免的操作。

创建追加优化表

通过CREATE TABLE的WITH子句来定义存储选项，缺省不指定WITH子句时，创建

的是行存堆表(如果设置了gp_default_storage_options参数，存储模式将与该

参数的设置一致)。例如，这是一个创建不带压缩选项的追加优化表的例子：

=# CREATE TABLE bar (a int, b text)

WITH (APPENDOPTIMIZED=true) DISTRIBUTED BY (a);

注意：APPENDOPTIMIZED是以前的APPENDONLY的别称，在系统表中仍然存储着

APPENDONLY关键字，在显示存储信息时也将显示APPENDONLY。CLUSTER,

DECLARE . . . FOR UPDATE不适用AO表。

选择行存或列存

GP支持在CREATE TABLE时选择行存或者列存，或者在分区表中为不同的分区做

不同的选择，可以有的分区用行存，有的分区用列存。本节提供一些关于正确选择行存

或列存的常规指导。不过具体情况还需根据业务场景进行确切的评估，编者认为，绝

大部分情况下，不要选择列存，因为现在的列存技术，文件数膨胀极其严重，后果更

严重。

从一般性的角度来说，行存具有更广泛的适用性。列存对于一些特定的业务场景可

以大量节省IO资源以提升性能，也可以提供更好的压缩效果。在考虑选择行存还是列

存时，可以参考如下几点：

 更新操作：如果一张表在装载完之后有频繁的更新操作，那么就选择行存Heap表。

列存表必须是AO表，所以没有列存的Heap表。

 INSERT操作：如果有频繁的INSERT操作，那么就选择行存表。列存表不擅长频

繁的INSERT操作，因为列存表在物理存储上每一个字段都对应一个文件，频繁的

INSERT操作将需要每次都写很多个文件。

 查询涉及的COLUMN数量：如果在SELECT列表或者WHERE条件中经常涉及大量的

字段，那么就选择行存表。对于大数据量的单字段AGG查询，列存表将会表现的更

好。例如：

=# SELECT SUM(salary)...

Greenplum Database 管理员指南 V6.2.1

=# SELECT AVG(salary)... WHERE salary > 10000

或者在WHERE条件中使用单独的字段进行条件过滤且返回相对少量的记录数。例如：

=# SELECT salary, dept ... WHERE state='CA';

 表中的字段数量：当查询涉及的字段数量很多，或者表中总的字段数量很少，这

时，行存表将更有优势。对于表中字段数量很多，而查询只涉及很少的字段时，列

存表将更有优势。

 压缩:对于列存来说，因为是相同的数据类型连续存储在一起，压缩效果会更好，

而行存则无法具备这种优势，所以，同样的数据，同样的压缩选项，往往列存可以

获得更好的压缩效果。当然，越好的压缩效果就意味着越困难的随机访问，因为数

据的读取都需要解压缩，不过，在6版本引入了ZSTD压缩算法，有非常优秀的压

缩和解压缩效率。

创建列存表

在CREATE TABLE时使用WITH子句来指明TABLE的存储模式。如果没有指明，该

表将会是缺省的行存堆表。使用列存的TABLE必须是AO表。例如，创建一张不带压缩

选项的列存储表：

=# CREATE TABLE bar (a int, b text)

WITH (appendonly=true, orientation=column) DISTRIBUTED BY (a);

编者再次提醒，就目前来说，如无必要，请不要在生产中使用列存，否则后果自负！

使用压缩(必须是 AO 表)

在GP中，AO表可以选择表级别的压缩，也可以针对列存表选择不同的列不同的压

缩选项，前者应用到整个TABLE，后者应用到指定的COLUMN。在选择COLUMN级别压

缩时，可以为不同的COLUMN选择不同的压缩算法。下表是可用的压缩算法：

行或列

可用压缩类型

支持压缩算法

行

表级别

ZLIB、ZSTD 和 QUICKLZ(开源版本不可用)

列

列级别和表级别

RLE_TYPE、ZLIB、ZSTD 和 QUICKLZ(开源版本不

可用)

使用库内压缩要求Instance所在的机器具备较强的CPU来压缩和解压缩数据，不

过，就目前的主流配置来说，应付ZSTD压缩算法绝对没有问题。不过，不要在使用了

Greenplum Database 管理员指南 V6.2.1

压缩文件系统的GP集群使用压缩表，因为这样只会带来额外的CPU消耗，而不会带来文

件尺寸的压缩。

在选择AO表的压缩方式和级别时，需要考虑以下几点因素：

 CPU性能：机器需要有足够的CPU资源来压缩和解压数据。

 压缩比和磁盘尺寸：既要考虑压缩比以减少数据文件的尺寸，也需要考虑CPU的能

力，因为越高级别的压缩需要消耗更多的CPU资源来压缩和解压数据。这就要求，

我们需要找到一个适中的压缩选项来兼顾压缩比和压缩解压的性能。

 压缩速度：虽然说，quicklz与zlib相比，有更好的压缩解压速度，相对来说压

缩率差一些，但是，已经有ZSTD可以用了，谁还在乎这些呢。例如，quicklz与

zlib level 1的压缩相比，具有相当的压缩率，会有更好的性能，与zlib level

6相比，zlib level 6会有好很多的压缩率，当然性能也会有显著的下降。选择

ZSTD，通过选择压缩级别，就可以兼顾压缩率与性能。所以，在6版本之前，一

般选择zlib level 5或者6，而在6版本开始，就直接选择ZSTD好了。

 解压速度或扫表效率：压缩表的查询性能不仅取决于压缩选项，还与硬件的配比，

查询优化等因素有关。应该进行压缩选项的测试以选择适合的压缩选项。

quicklz压缩只有1个压缩级别可以选择，zlib有1~9压缩级别可以选择，ZSTD

有1~19压缩级别可以选择，RLE有1~4压缩级别可以选择。

一般来说，在6版本之前，使用zlib 5级或者6级压缩就可以了，6版本使用ZSTD

是最好的选择。经过编者在虚拟机中的简单测试发现，ZSTD在9级以上，压缩效率几乎

不再有明显的优势，但INSERT的耗时会有很大的增加，例如19级，INSERT的时间可

能会是1级的很多倍，不过，各个压缩级别的表扫描性能都表现优良。所以，可以考虑

适当的选择ZSTD 1~6级的压缩选项作为通用压缩选项，如果对压缩效果不是很敏感，

可以使用1级ZSTD压缩作为通用压缩选项。

RLE可能是一个最没有存在感的压缩算法，因为真的没有什么实用价值，另外，列

存表在不同的列上设置不同的压缩选项往往也是多余的，实在多余，毫无价值，因为这

样做并不会得到什么有意义的效果，所以，还不如忘了这个事情，直接在表级别设置压

缩属性就好了，编者编写的ddl备份脚本就直接忽略了字段级别的压缩属性，因为这毫

无意义，列存表的使用就应该是非常谨慎的事情，编者再次提醒，就目前来说，如无必

要，请不要在生产中使用列存，否则后果自负！编者见过太多因为滥用列存导致的灾难，

如果再结合了滥用分区表，那将是无尽的折磨！

创建压缩表

在CREATE TABLE时使用WITH子句来指明TABLE的存储模式。使用压缩模式的

TABLE必须是AO表。例如，要创建一张5级ZSTD压缩的AO表：

Greenplum Database 管理员指南 V6.2.1

=# CREATE TABLE foo (a int, b text)

WITH (appendonly=true, compresstype=zstd, compresslevel=5);

检查 AO 表的压缩与分布情况

GP提供了内置的函数用以检查AO表的压缩率和分布情况。这两个函数可以使用对

象ID或者TABLE的NAME作为参数。表名可能需要带模式名。

函数

返回类型

解释

get_ao_distribution(name)

集合类型

展示 AO 表的分布情况，每

get_ao_distribution(oid)

(dbid,

行对应 segid 和记录数。

tuplecount)

get_ao_compression_ratio(name)

float8

计算出 AO 表的压缩率。如

get_ao_compression_ratio(oid)

果该信息未得到，将返回

-1 值。

压缩率得到的是一个常见的比值类型。例如，6.19，意思是该TABLE未压缩状态

下的储存尺寸是压缩状态下的储存尺寸的6.19倍。

分布信息展示的是每个Instance存储该TABLE的记录数量。例如，在一个有着4

个Instance的系统，其dbid范围为0 - 3，该函数返回类似下面的结果集：

=# SELECT * FROM get_ao_distribution('lineitem_comp');

segmentid | tupcount

-----------+----------

0 |

7500721

1 |

7501365

2 |

7499978

3 |

7497731

(4 rows)

支持运行长度编码

GP已支持COLUMN级别的运行长度编码(Run-length Encoding /RLE)压缩算

Greenplum Database 管理员指南 V6.2.1

法。RLE是一种将连续重复的数据作为一种计数方式存储的压缩算法。RLE对于重复元

素是很有效的。例如，在一个表中有两个COLUMN，一个日期COLUMN和一个描述COLUMN，

其中包含200000个date1和400000个data2，RLE压缩处理这种数据为类似data1

200000 data2 400000这样的效果。对于那些没有很多重复值的数据RLE是不适合

的，而且还可能会显著的增加存储文件的尺寸。

RLE压缩有4种级别。级别越高，压缩效率越高，但压缩速度也会越低。

使用了RLE压缩的行存表对4.2.1之前的版本是不兼容的。若需要将这些表备份并

在之前的版本上恢复，可在恢复操作前，先将这些表ALTER为无压缩或者旧版本兼容的

压缩模式(ZLIB或QUICKLZ)，再执行恢复操作，因为RLE模式的表定义在4.2.1之前

的版本不兼容。

实际上，正如前面所述，RLE压缩算法并没有什么实用意义，忘记这个事情就好了，

好好使用ZSTD就对了。

在列上设置压缩

注意：编者不希望读者浪费很多时间来学习这部分的知识，所以，先把观点列出来，编

者根据10年的经验判断，除了作为一块知识来学习外，可能永远也不需要在每个字段

上设置压缩，因为那是极其多余和毫无意义的。在真实的使用环境中，往往列存储的选

择都应该是极其少见的，因为列存储的选择需要满足多方面条件，选择列存的往往是那

种尺寸很大的表，追求较高的压缩率，字段很多，而往往只需要访问很少的字段，且追

求很好的查询性能。另外，列存表，在底层的数据文件层面，每个字段都单独存放一个

文件，随意大量的使用列存储会造成文件数量巨大，文件尺寸过于零碎，影响文件系统

的性能，从而导致数据库性能下降甚至稳定性变差。即便是在列存表上，通常也只需要

在TABLE层面设置统一的压缩选项即可，为不同的COLUMN指定不同的压缩选项往往是

徒劳的，因为这样并不会得到显著的压缩率的提升或者性能的提升。所以，如果您是为

了扩展知识，可以继续阅读，如果是为了实用，可以跳过此节，不要浪费生命。

在CREATE列存表时，允许针对每个字段设置单独的存储选项，包括如下三种属性可选：

 压缩算法

 压缩级别

 列数据文件的块尺寸(Block Size)

在CREATE TABLE、ALTER TABLE和CREATE TYPE命令中包含对COLUMN设置压

缩类型、压缩级别和块尺寸(Block Size)的选项(注意，ALTER TABLE时不能修改

Greenplum Database 管理员指南 V6.2.1

这些选项，仅在增加分区时可选)，这些参数统称为列存储参数。下面列举这3种存储

参数及每种参数的可选值：

名称

解释

可选值

COMPRESSTYPE

使用的压缩类型

ZSTD(最好的压缩选择)

ZLIB(更高压缩)

QUICKLZ(更快压缩)

RLE_TYPE(运行长度编码)

none(无压缩、缺省值)

COMPRESSLEVEL

压缩级别

ZSTD 为 1~19 级可选

ZLIB 为 1~9 级可选

QUICKLZ 仅 1 个级别可选(缺省不需指定)

RLE_TYPE 为 1~4 级可选

BLOCKSIZE

表的存储块大小

8192 ~ 209715(8K ~ 2M)该值必须是 8192 的

倍数

对列存表单独的字段使用存储参数的格式如下：

[ ENCODING ( storage_options [, . . .] ) ]

这里ENCODING关键字是必须的，存储参数包含3个部分：参数名称、等于号、参

数值。要指定多个存储参数，用逗号(,)分割即可。存储参数可以应用在单独的COLUMN

上，还可以作为所有COLUMN的缺省设置，如下面的CREATE TABLE语句所示：

一般用法：

column_name data_type ENCODING ( storage_options [, . . . ] ),. . .

COLUMN column_name ENCODING ( storage_options [, . .

] ), . . .

DEFAULT COLUMN ENCODING ( storage_options [, . . . ] )

例如：

C1 char ENCODING (compresstype=quicklz, blocksize=65536)

COLUMN C1 ENCODING (compresstype=quicklz, blocksize=65536)

DEFAULT COLUMN ENCODING (compresstype=quicklz)

列级缺省压缩属性

在使用了列压缩，没有为字段设置ENCODING选项，又没有设置DEFAULT COLUMN

Greenplum Database 管理员指南 V6.2.1

ENCODING的情况下，该字段将不会使用压缩存储，而块大小使用系统配置参数

block_size指定的值，此时，该字段的存储属性，与普通AO表一致。

列级压缩设置的优先级

COLUMN的压缩设置通过TABLE向分区再向子分区传递。在越低级别的设置具有越

高的优先级。

 表层面的COLUMN压缩选项的设置，将覆盖TYPE层面的设置。这里可能会有点糊

涂，不过，后续会介绍到通过TYPE来设置COLUMN级别的压缩选项。

 子分区的表层面的COLUMN压缩设置将覆盖其父级表的任何COLUMN和TABLE层面

的设置。

 子分区的COLUMN层面的COLUMN压缩设置将覆盖任何分区层面的压缩设置、父表

层面的COLUMN设置、父表层面的设置。

 ENCODING的设置高于WITH子句的设置。

这里关于优先级的说明，源文档中有5句，但是，实在难以说清楚，原文的英文说

明理解起来可能还比较清晰，但要用中文讲清楚实在是困难，这里可以简单的一句话来

总结：越靠近具体字段的压缩设置，优先级越高。具体的可以参考后续的例子来学习和

理解。

注意：COLUMN表不允许使用INHERITS语法，若使用LIKE子句来创建TABLE，表层面

的存储参数以及COLUMN层面的存储参数都会被忽略，不会被继承。

列压缩设置的优选

最佳的方法是根据不同的数据设置特定的列压缩。下面的例5展示了2级分区表在

子分区上使用RLE_TYPE压缩。

Greenplum Database 管理员指南 V6.2.1

列级压缩的例子

下面的例子展示了在使用CREATE TABLE语句时使用列级压缩设置。

例1

该例子中，COLUMN c1使用ZSTD压缩并使用系统定义的块尺寸。COLUMN c2使

用QUICKLZ压缩并使用块尺寸为65536。COLUMN c3不使用压缩且使用系统定义的块

尺寸。

=# CREATE TABLE T1 (

c1 int ENCODING (compresstype=zstd),

c2 char ENCODING (compresstype=quicklz, blocksize=65536),

c3 char

) WITH (appendonly=true, orientation=column);

例2

该例子中，COLUMN c1使用ZLIB压缩并使用系统定义的块尺寸。COLUMN c2使

用QUICKLZ压缩并使用块尺寸为65536。COLUMN c3使用RLE_TYPE压缩并使用系统

定义的块尺寸。

=# CREATE TABLE T2 (

c1 int ENCODING (compresstype=zlib),

c2 char ENCODING (compresstype=quicklz, blocksize=65536),

c3 char,

COLUMN c3 ENCODING (compresstype=RLE_TYPE)

)WITH (appendoptimized=true, orientation=column);

例3

该例子中，COLUMN c1使用ZLIB压缩并使用系统定义的块尺寸。COLUMN c2使

用QUICKLZ压缩并使用块尺寸为65536。COLUMN c3使用RLE_TYPE压缩并使用系统

定义的块尺寸。值得注意的是在子分区的定义中，COLUMN c3使用了ZLIB(不是

RLE_TYPE)压缩，由于分区中的COLUMN存储设置的优先级比父表层面的COLUMN存储

设置的优先级高，实际上c3使用的是ZLIB压缩而非RLE_TYPE压缩。

=# CREATE TABLE T3 (

c1 int ENCODING (compresstype=zlib),

c2 char ENCODING (compresstype=quicklz, blocksize=65536),

c3 text, COLUMN c3 ENCODING (compresstype=RLE_TYPE)

)WITH (appendoptimized=true, orientation=column)

Greenplum Database 管理员指南 V6.2.1

PARTITION BY RANGE (c3)(

START ('2020-01-01'::DATE) END ('2100-12-31'::DATE),

COLUMN c3 ENCODING (compresstype=zlib)

);

例4

该例子中，创建TABLE时，COLUMN c1是ZLIB压缩并使用系统定义的块尺寸。而

COLUMN c2没有明确指定存储设置，因此其将从DEFAULT COLUMN ENCODING子句

继承压缩方式(QUICKLZ)和块尺寸(65536)。COLUMN c3指定了压缩方式为

RLE_TYPE，而块尺寸(65536)从DEFAULT COLUMN ENCODING子句继承而来。

COLUMN c4没有压缩。因为缺省列存储设置指定了压缩模式，COLUMN c4明确指定了

不压缩，其块尺寸从DEFAULT COLUMN ENCODING子句继承而来为65536。

=# CREATE TABLE T4 (

c1 int ENCODING (compresstype=zlib),

c2 char,

c3 text,

c4 smallint ENCODING (compresstype=none),

DEFAULT COLUMN ENCODING (compresstype=quicklz,blocksize=65536),

COLUMN c3 ENCODING (compresstype=RLE_TYPE)

)WITH (appendoptimized=true, orientation=column);

例5

该例子中，创建一个2层分区表。p1分区的子分区sp1的i字段采用zlib压缩，块

尺寸为65536，p2分区的子分区sp1的字段i采用rle_type压缩并使用系统定义的块

尺寸，p2分区的子分区sp1的字段k使用块尺寸为8192。

=# CREATE TABLE T5(

i int,

j int,

k int,

l int

)WITH (appendoptimized=true, orientation=column)

PARTITION BY range(i) SUBPARTITION BY range(j)(

partition p1 start(1) end(2) (

subpartition sp1 start(1) end(2),

column i encoding(compresstype=zlib, blocksize=65536)

partition p2 start(2) end(3) (

subpartition sp1 start(1) end(2),

column i encoding(compresstype=rle_type),

column k encoding(blocksize=8192)

Greenplum Database 管理员指南 V6.2.1

)

);

关于如何在现有TABLE上新增COLUMN并设置压缩，请参考ALTER TABLE命令。

通过 TYPE 来设置列的压缩

在创建新类型时，可以定义该类型的默认压缩属性。例如，下面的CREATE TYPE

命令定义了一个名为int33的类型(这只是个例子，实际上这个SQL不能成功执行，因

为没有定义类型所需的函数)，该类型设置了quicklz压缩：

=# CREATE TYPE int33 (

internallength = 4,

input = int33_in,

output = int33_out,

alignment = int4,

default = 123,

passedbyvalue,

compresstype="quicklz",

blocksize=65536,

compresslevel=1

);

在CREATE TABLE时将int33指定为字段类型时，将使用在类型定义中指定的压

缩选项：

=# CREATE TABLE t2 (

c1 int33

)WITH (appendoptimized=true, orientation=column);

注意：编者不建议使用这种方式来定义列压缩，虽然在定义TABLE时看起来精简了不少，

但对于别人来说，阅读和理解可能都存在障碍。另外替代原生TYPE的定义未必适应所

有情况。建议慎用。

选择块尺寸

Greenplum Database 管理员指南 V6.2.1

在一个TABLE中，每个块尺寸意味着相应数量byte的存储。块尺寸必须在8192到

2097152之间，并且必须是8192的整数倍。缺省值为32768。需要注意的是，指定大

的块大小会消耗更多的内存资源。GP会为每个分区、列存表的每个列维护一块BUFFER，

因此重度分区表和列存储表也会消耗更多的内存。

根据以往的经验，修改blocksize往往并不会带来任何的好处，所以，没必要在

这一块花很多的心思，因为不会有什么有价值的收获。

修改表定义

ALTER TABLE命令用于改变现有表的定义。通过ALTER TABLE命令可以改变

TABLE的各种属性，如：列定义、分布策略、存储模式和分区结构(可参见"维护分区

表"章节)等。例如，将TABLE的一个COLUMN添加一个非空限制：

=# ALTER TABLE address ALTER COLUMN street SET NOT NULL;

修改表的分布策略

ALTER TABLE命令提供了改变分布策略的选项。在修改TABLE的分布策略时，表

中的数据要在磁盘上做重分布，该操作可能需要密集的资源消耗。还有一个按照现有策

略重新分布数据的选项。对于分区表来说，修改分布策略会递归的应用于所有的子分区。

该操作不会改变表的OWNER以及其他TABLE属性。例如，下面的SQL是修改sales表的

DK为customer_id并重分布数据：

=# ALTER TABLE sales SET DISTRIBUTED BY (customer_id);

在修改TABLE为HASH分布时，表数据会自动重新分布。然而，将分布策略改为随

机分布时不会同时进行数据重新分布操作。例如：

=# ALTER TABLE sales SET DISTRIBUTED RANDOMLY;

当修改分布策略为DISTRIBUTED REPLICATED或者从DISTRIBUTED

REPLICATED修改为其他类型时，将会自动进行数据重分布。

Greenplum Database 管理员指南 V6.2.1

重分布表数据

对于由HASH分布改为随机分布策略的表来说，因为数据并不会自动完成重分布操

作，所以，很可能需要有数据重分布的操作，或者在进行扩容时，数据的重分布操作也

是很有必要的，使用REORGANIZW=TRUE来对数据进行重分布。例如：

=# ALTER TABLE sales SET WITH (REORGANIZE=TRUE);

该命令会在Instance之间按照现有的分布策略(包括随机分布策略)重新分散表

中数据。

当修改分布策略为DISTRIBUTED REPLICATED或者从DISTRIBUTED

REPLICATED修改为其他类型时，即便指定了REORGANIZE=FALSE，也一定会自动进

行数据重分布。

修改表的存储模式

存储选项只能在CREATE TABLE时指定，包括Heap和AO的选择，压缩的选择，行

和列的选择等，CREATE TABLE时WITH子句中的属性都只能在CREATE TABLE时指定。

要修改这些属性，必须采用重新建表的方式进行，即，按照预期的属性设置，创建一张

新表，把数据插入新表，删除旧的表，修改新表的名称为旧的表名。另外，还需要重建

权限和依赖关系，假如旧的表上有视图，则需要先删除视图，等修改了表名之后再重建

视图。例如：

=# CREATE TABLE sales2 (LIKE sales)

WITH (appendoptimized=true,compresstype=zstd,compresslevel=9);

=# INSERT INTO sales2 SELECT * FROM sales;

=# DROP TABLE sales;

=# ALTER TABLE sales2 RENAME TO sales;

=# GRANT ALL PRIVILEGES ON sales TO admin;

=# GRANT SELECT ON sales TO guest;

在现有表上添加压缩列

Greenplum Database 管理员指南 V6.2.1

可以使用ALTER TABLE命令一个列存表上添加一个压缩字段，非列存表不能为新

增的字段指定ENCODING子句，否则会报错。关于压缩列的设置，参见"在列上设置压

缩"章节。下面的例子演示了在现有的T1表上增加zlib压缩列：

=# ALTER TABLE T1 ADD COLUMN c4 int DEFAULT 0 ENCODING (compresstype=zlib);

继承压缩设置

在增加一个子分区(非一级分区)时，新的分区如果没有明确指定压缩属性，将不

会从父表继承，TEMPLATE是预先设置的模版，可以指定新增子分区的压缩属性。下面

的例子演示了创建一个带子分区设置的表，然后增加一个分区：

=# CREATE TABLE ccddl (

i int,

j int,

k int,

l int

)WITH(appendoptimized = TRUE, orientation=COLUMN)

PARTITION BY range(j)

SUBPARTITION BY list (k)

SUBPARTITION template(

SUBPARTITION sp1 values(1, 2, 3, 4, 5),

COLUMN i ENCODING(compresstype=ZLIB),

COLUMN j ENCODING(compresstype=QUICKLZ),

COLUMN k ENCODING(compresstype=ZLIB),

COLUMN l ENCODING(compresstype=ZLIB)

)

(

PARTITION p1 START(1) END(10),

PARTITION p2 START(10) END(20)

);

=# ALTER TABLE ccddl ADD PARTITION p3 START(20) END(30);

运行ALTER TABLE命令创建了ccddl表的一级分区ccddl_1_prt_p3和二级分

区ccddl_1_prt_p3_2_prt_sp1。一级分区ccddl_1_prt_p3与二级分区sp1具有

不同的压缩设置。这里的二级分区(sp1)其实是沿用了SUBPARTITION TEMPLATE的

设置，而不是所谓的继承，基本上很多版本中，分区的存储设置不会自动从父级表继承，

需要明确指定，SUBPARTITION TEMPLATE与普通的继承不同，是一种预置的模板。

Greenplum Database 管理员指南 V6.2.1

关于为分区表增加分区时，新增的分区不能继承父表的压缩选项，这会是一个让人

非常不爽的问题，至少曾经有些版本是继承的，也有很多版本是没有继承的，不过，从

目前编者的测试来看，目前最新的5版本和6版本都是不继承的，所以，养成新增分区

时写好WITH子句的习惯很重要，或者设置好gp_default_storage_options参数。

删除表

可通过DROP TABLE命令从DB中删除表。例如：

=# DROP TABLE mytable;

要想在不删除表定义的情况下清空表中的记录，使用DELETE或TRUNCATE命令。例如：

=# DELETE FROM mytable;

=# TRUNCATE mytable;

DROP TABLE会删掉所有与该表相关的索引、规则、触发器、约束等。然而要一起

删除与该表相关的视图VIEW，必须使用CASCADE。CASCADE会删除所有依赖该TABLE

的VIEW。如果不使用CASCADE，当表上有依赖时，DROP操作将会报错失败。例如：

=# DROP TABLE mytable CASCADE;

DELETE操作是将表中的数据标记为丢弃，不会真正的释放存储的空间，而

TRUNCATE会直接清空数据文件，前者的数据找回相对容易，后者只能通过文件系统来

恢复，会很困难。

分区大表

表分区用以解决特别大的表的管理问题，例如事实表，可将这种数据量很大的表分

成一些尺寸适中且更容易管理的分区表。分区表在执行特定的查询语句时只扫描相关分

区的数据而不是全部的数据从而可以提升查询性能。分区表对于数据库的管理也有帮助，

可以更好的进行数据周期管理。但是，编者提醒，不要滥用分区表，如果在不分区的

情况下，性能可以满足需求，那么就不要分区，如无必要，不要分区，尤其不要滥用

分区，尤其要杜绝过度分区，否则后果自负！

Greenplum Database 管理员指南 V6.2.1

理解 GP 的表分区

在CREATE TABLE时使用PARTITION BY(以及可选的SUBPARTITION BY)子句

来对表进行分区。在GP中对一张表做分区，实际上是创建了一张根(ROOT表)表和多个

子表。在内部，GP在ROOT表与子表之间创建了继承关系(类似于PostgreSQL中的继

承/INHERIT功能)，不过，切忌想当然的去使用INHERIT语法来创建分区表，那样创

建出来的并不是分区表，GP的分区表不仅仅有继承关系，还有一些其他的系统表信息

来决定分区关系。

根据分区创建时定义的准则，每个分区在创建时都带有一个不同的检查(CHECK)

约束，其限制了该表可以包含的数据。该检查约束还用于查询优化器在执行特定的查询

语句时决定扫描哪些分区。

分区层级关系被储存在GP系统表中，因此插入到ROOT表的数据将被存储到相应的

叶子节点的分区中。任何对分区结构的修改或者TABLE定义的修改都需要通过对ROOT

表使用ALTER TABLE命令来完成，修改分区结构需要再结合PARTITION子句来完成。

GP支持范围(根据数值型字段的取值范围分割数据，可以比较大小的数据类型都可

以作为范围分区的字段，例如日期或价格)分区和列表(根据值列表分区，例如区域或

生产线)分区，或者两种类型的结合(在不应该被使用的多级分区表中，不同层级的分

区可以选择不同的分区类型)。

分区表和其他非分区表一样都是在GP的Instance之间分布的。表在GP的

Instance之间物理上的分散可以确保并行查询处理。表分区是一种大表逻辑切分的手

Greenplum Database 管理员指南 V6.2.1

段。分区本身不会改变Instance间物理上的数据分布规律。

决定表是否分区的原则

并不是任何TABLE都适合做分区。如果下列问题的全部或者大部分的答案是Yes，

这样的表可以通过分区来提高查询性能。如果大部分的答案是No，分区不是好的选择：



表是否足够大？大的事实表才适合做分区。对于一张很大的表，从逻辑上把表分

成较小的分区将可以改善性能。而对于较小的表，对分区的管理和维护的开销可能

已经超过了性能的改善程度。那么对于是否选择分区这个问题来说，什么样的表算

大表，什么样的表算小表，根据编者十多年的经验来看，不能仅仅从表的尺寸或者

记录数来简单的区分，还应该结合集群规模来考虑，一般建议每个分区在每个

Instance上的数据量可以控制在100万到1000万左右的范围(落实到项目中的

具体标准可以视情况而定，例如有100个Primary的集群，可能会规定记录数在

一亿条以下的表不允许分区)会比较合适，这样的分区粒度是适中的。如果对于列

存储的表来说，这个范围还可以再放大10倍甚至更高，因为列存储的表是按照每

个字段一个数据文件来存储的。



对目前的性能不满意？作为一种调优方案，应该在查询性能低于预期时再考虑对

表进行分区。分区不是万能的优化手段，GP已经是MPP架构，对于很多不是很大

的表，不分区的性能已经完全满足预期的情况下，分区是多余的。



查询条件是否能匹配分区条件？检查查询语句的WHERE条件是否与考虑分区的

COLUMN一致。例如，如果大部分的查询使用日期条件，那么按照月或者周进行分

区设计也许很有用，而如果查询条件更多的是使用地区条件，可以考虑使用地区字

段将表做列表类型的分区。不过，一般来说，List分区的使用场景极少，尤其要

避免多级分区，因为维护更复杂。



数据仓库是否需要滚动历史数据？历史数据的滚动需求也是分区设计的考虑因素。

例如，数据仓库中仅需要保留过去两个月的数据。如果数据按月进行分区，将可以

很容易的删除掉两个月之前的数据(TRUNCATE分区或者删除分区)，而最近的数

据存入最近月份的分区即可。



按照某个规则数据是否可以被均匀的分拆？应该选择尽量把数据均匀分拆的规则。

若每个分区储存的数据量相当或者与分区跨度成比例，那么查询性能的改善将与分

区的数量或者条件的范围相关。例如，把一张表分为10个分区，命中单个分区条

件的查询性能可能会比未分区的情况下高10倍。编者认为，我们不应该简单的说

查询性能是10倍，因为多数的查询不是count(*)这样简单的计数，对于其他复

杂的算子，除了扫描过滤数据这部分可以提升外，后续的处理部分是不会有性能提

升的，因为计算量是不变的。

注意：千万不要随意的创建大规模分区表，因为分区的维护和管理也是需要消耗资源和

Greenplum Database 管理员指南 V6.2.1

精力的，例如全表查询，VACUUM，gprecoverseg，gpexpand等操作，都是需要一

个一个的叶子分区去操作。另外，如果是全表扫描，分区不仅无法带来性能的提升，反

而会更慢。因此，如果不太会用到分区消除的查询场景，应尽量避免分区，当然，有时

为了数据周期管理，需要进行分区，此时应考虑更粗粒度的分区。尤其应该杜绝使用多

级分区，多级分区一般并不会比只有一级分区的表带来更显著的性能提升，同时会带来

大量的系统信息维护的压力，以及大量的数据文件管理压力。正如前面所述，应该按照

每个Instance存储的记录数(理想情况是按照尺寸来衡量，但一般界定和落实比较难)

来制定标准界定合理的分区粒度。编者的一键式集群安装初始化脚本中会缺省将

gp_max_partition_level参数设置为1，不允许建多级分区。

创建分区表

TABLE只能在执行CREATE TABLE命令时被分区。

表做分区的第一步是选择分区类型(范围分区或列表分区)和分区字段，决定分区

的层数，分区字段的取值范围，分区的粒度等。例如，先按照日期范围划分一级月分区，

再按照区域做二级列表分区。本节通过示例演示如何创建多种分区表。编者再次提醒，

如无必要，杜绝在生产中创建多级分区表，否则后果自负！

 定义时间范围分区表

 定义数字范围分区表

 定义列表分区表

 定义多级分区表

 将现有表分区

定义时间范围分区表

日期范围分区，可以使用单个date或者timestamp类型的字段作为分区字段。如

果需要，还可以使用同样的字段做子分区(例如按月分区后再按日做子分区，当然，实

际使用中应杜绝这样做，此处只是举例而已)。使用日期分区时优先考虑直接使用最细

粒度的分区。例如，一年365天，直接按日每日一个分区，而不是先按年分区、然后按

月分区、再按日分区。多级分区会降低执行计划的性能，但水平的分区设计对执行计划

的影响会好一些(只是比多级分区好一些，但过多的分区仍然会有很大的影响，要杜绝

过度分区！)。

Greenplum Database 管理员指南 V6.2.1

可以通过使用START和END指定分区边界，还可以结合EVERY子句定义分区步长让

数据库自动在分区边界内，产生多个范围分区，不过这是一次性的，当表创建成功后，

数据库不会自动增减分区，GP中目前没有自动维护分区的概念(自动增加不存在的分

区)。缺省情况下，START值总是被包含而END值总是被排除。例如：

=# CREATE TABLE sales (

id int,

date date,

amt decimal(10,2)

)DISTRIBUTED BY (id) PARTITION BY RANGE (date) (

START (date '2020-01-01') INCLUSIVE

END (date '2021-01-01') EXCLUSIVE EVERY (INTERVAL '1 month')

);

也可以为每个分区单独指定名称。例如：

=# CREATE TABLE sales (

id int,

date date,

amt decimal(10,2)

) DISTRIBUTED BY (id) PARTITION BY RANGE (date) (

PARTITION Jan20 START (date '2020-01-01') INCLUSIVE ,

PARTITION Feb20 START (date '2020-02-01') INCLUSIVE ,

PARTITION Mar20 START (date '2020-03-01') INCLUSIVE ,

PARTITION Apr20 START (date '2020-04-01') INCLUSIVE ,

PARTITION May20 START (date '2020-05-01') INCLUSIVE ,

PARTITION Jun20 START (date '2020-06-01') INCLUSIVE ,

PARTITION Jul20 START (date '2020-07-01') INCLUSIVE ,

PARTITION Aug20 START (date '2020-08-01') INCLUSIVE ,

PARTITION Sep20 START (date '2020-09-01') INCLUSIVE ,

PARTITION Oct20 START (date '2020-10-01') INCLUSIVE ,

PARTITION Nov20 START (date '2020-11-01') INCLUSIVE ,

PARTITION Dec20 START (date '2020-12-01') INCLUSIVE

END (date '2021-01-01') EXCLUSIVE

);

注意：由于分区的范围限制是连续的，这种情况下，可以不用为每个分区指定END值，

而只需要为最后一个分区指定即可，这个时候，上一个分区的END值就是下一个分区的

START值。但如果分区的范围不是连续的，应该为每个不连续的上一个分区指定END值。

Greenplum Database 管理员指南 V6.2.1

定义数字范围分区表

数字范围分区表使用单个数字列作为分区字段。例如：

=# CREATE TABLE rank (

id int,

rank int,

year int,

gender char(1),

count int

)DISTRIBUTED BY (id) PARTITION BY RANGE (year) (

START (2020) END (2021) EVERY (1),

DEFAULT PARTITION extra

);

关于默认分区的更多信息，参见"添加默认分区"相关章节。

定义列表分区表

列表分区表可以使用任何数据类型的列作为分区字段，分区规则使用等值比较。列

表分区可以使用多个COLUMN(组合起来)作为分区字段，而范围分区只允许使用单独

COLUMN作为分区字段。对于列表分区，必须为每个分区指定相应的值。例如：

=# CREATE TABLE customer (

id int,

name varchar(32),

birthday date,

gender char(1),

viplevel int

) DISTRIBUTED BY (id) PARTITION BY LIST (gender,viplevel) (

PARTITION girls1 VALUES (('F',1)),

PARTITION girls2 VALUES (('F',2)),

PARTITION boys1 VALUES (('M',1)),

PARTITION boys1 VALUES (('M',2)),

DEFAULT PARTITION other

);

这里只是展示一下多字段LIST分区的语法，实际使用中可能永远也不需要用到这

Greenplum Database 管理员指南 V6.2.1

种多字段分区的语法，LIST分区的使用场景本来就少，而且这种分区表会很难维护。

关于默认分区的更多信息，参见"添加默认分区"相关章节。

定义多级分区表

当需要多级分区时(应该永远都不需要)，可以使用多级分区的设计。使用

SUBPARTITION TEMPLATE来确保每个分区具有相同的子分区结构，尤其是对那些后增加

的分区来说。例如，创建一个两层的分区表：

=# CREATE TABLE sales (

trans_id int,

date date,

amount

decimal(9,2),

region text

) DISTRIBUTED BY (trans_id)

PARTITION BY RANGE (date)

SUBPARTITION BY LIST (region)

SUBPARTITION TEMPLATE (

SUBPARTITION usa VALUES ('usa'),

SUBPARTITION asia VALUES ('asia'),

SUBPARTITION europe VALUES ('europe'),

DEFAULT SUBPARTITION other_regions

)

(

START (date '2020-01-01') INCLUSIVE

END (date '2021-01-01') EXCLUSIVE

EVERY (INTERVAL '1 month'),

DEFAULT PARTITION outlying_dates

);

下面是一个3级分区表的例子，这里sales表被按照年、月、区域进行三级分区。

第一个SUBPARTITION TEMPLATE子句确保每年的一个一级分区都有12个月的子分区

和1个默认分区，第二个SUBPARTITION TEMPLATE子句确保每个月的二级分区都有3

个LIST分区和1个默认分区：

=# CREATE TABLE sales (

id int,

year int,

Greenplum Database 管理员指南 V6.2.1

month int,

day int,

region text

) DISTRIBUTED BY (id)

PARTITION BY RANGE (year)

SUBPARTITION BY RANGE (month)

SUBPARTITION TEMPLATE (

START (1) END (13) EVERY (1),

DEFAULT SUBPARTITION other_months

)

SUBPARTITION BY LIST (region)

SUBPARTITION TEMPLATE (

SUBPARTITION usa VALUES ('usa'),

SUBPARTITION europe VALUES ('europe'),

SUBPARTITION asia VALUES ('asia'),

DEFAULT SUBPARTITION other_regions

)

(

START (2020) END (2026) EVERY (1),

DEFAULT PARTITION outlying_years

);

注意：当使用多级RANGE分区时，很容易产生大量的子分区，如前面所说的，这会

带来极大的性能问题和系统表压力。应该尽可能杜绝创建多级分区表。

将现有表分区

对已经创建的未分区的表是不能直接修改为分区表的。只能在CREATE TABLE的

时候进行分区定义。要想对现有的表做分区，只能重新创建一个分区表、重新装载数据

到新的分区表中、删掉旧表然后把新的分区表改为旧表的名称。另外，还需要重建权限

和依赖关系，例如旧的表上有视图，则需要先删除视图，等修改了表名之后再重建视图。

例如：

=# CREATE TABLE sales2 (LIKE sales)

PARTITION BY RANGE (date) (

START (date 2020-01-01') INCLUSIVE

END (date '2021-01-01') EXCLUSIVE

EVERY (INTERVAL '1 month')

);

=# INSERT INTO sales2 SELECT * FROM sales;

Greenplum Database 管理员指南 V6.2.1

=# DROP TABLE sales;

=# ALTER TABLE sales2 RENAME TO sales;

=# GRANT ALL PRIVILEGES ON sales TO admin;

=# GRANT SELECT ON sales TO guest;

分区表的限制

对于一个分区表中任何一个层级的某个分区来说，其最多只能有32767个子分区，

这是因为pg_partition_rule系统表的parruleord字段是smallint类型。

分区表上的主键或者唯一约束，必须包含所有的分区字段。唯一索引可以不包含分

区字段，但其只对叶子分区有效，不能对整个分区表有效，所以，这种不包含分区字段

的唯一索引不能在整个分区表层面保证数据的唯一性。

使用DISTRIBUTED REPLICATED分布策略的复制表不能进行分区。

Orca支持规整(每个分区的子分区结构完全相同)的多级分区表，Orca是缺省打开

的，如果Orca启用且查询的是不规整的多级分区表，该查询将会自动切换到

Legacy(PostgreSQL优化器)的优化器。

当叶子分区存在外部表时，分区表还会有以下的限制(看到这么多的限制，估计想

一想就不太会用外部表做分区表的分区了)：

 对包含外部表的分区表进行查询时，将只能使用PostgreSQL优化器。

 当分区是外部表时，一些访问或者修改外部表数据的操作将会报错，例如：

 INSERT、DELETE和UPDATE命令试图修改外部表分区的数据会报错。

 TRUNCATE命令涉及外部表分区时会报错。

 当COPY命令向分区表COPY数据且需要修改外部表时会报错。

 用COPY命令从一个包含外部表的分区表COPY数据是不允许的，如果使用

IGNORE EXTERNAL PARTITIONS语法是可以的，这样可以过滤掉外部表叶

子分区。可以通过COPY一个SQL查询的方式，从一个包含外部表的分区表

COPY数据，例如这个例子，将数据COPY到标准输出：

=# COPY (SELECT * from my_sales ) TO stdout;

Greenplum Database 管理员指南 V6.2.1

 VACUUM命令会忽略外部表分区。

 下面的操作，如果没有数据被修改，是允许的，否则将会报错：

 增加或删除字段。

 修改字段类型。

 如果分区表包含外部表，一些ALTER PARTITION操作也是不支持的：

 设置子分区模板。

 修改分区属性。

 创建缺省分区。

 设置分布策略。

 修改字段NOT NULL约束。

 增加或删除约束。

 分割外部表分区。

 如果分区表的叶子分区是外部表，备份工具将不会备份该分区的数据。

插入数据到分区表

一旦创建了分区表，所有的非叶子分区，永远是没有数据的。数据只储存在最底层

的表中(叶子分区)。在多级分区表中，仅仅在最底层的叶子分区中有数据。

如果有记录无法匹配到叶子分区，该数据将会被拒绝并导致插入失败。若希望在任

何时候插入时都不出现失败，可以增加默认分区。这样所有不能匹配分区CHECK约束的

数据将插入到默认分区。可参考相关的"添加默认分区"章节。

在生成执行计划时，优化器会根据查询条件的情况，检查分区表的CHECK约束来决

定有哪些分区需要被扫描。对于PostgreSQL优化器来说，该分区表中的所有默认分区

(只要该层级中存在)总是会被扫描，如果默认分区中包含数据，其一定会影响处理时

间。对于Orca优化器来说，如果查询条件不涉及默认分区，则不会扫描默认分区，如

果分区条件不是常量，Orca还会进行动态分区裁剪。

在使用COPY或者INSERT向ROOT表装载数据时，这些数据会默认自动路由到正确

的叶子分区。因此，可以像使用普通的未分区表一样插入数据到分区表。

Greenplum Database 管理员指南 V6.2.1

如果有必要，可以直接把数据装载到叶子分区中，实际上很多工具都需要这样做，

例如，备份恢复操作，集群之间数据同步操作等，因为把整个分区表作为一个整体来操

作可能是一个特别巨大的任务，按照不同的叶子分区来操作，可以将巨大的任务分拆为

多个较小的任务，还可以通过叶子分区之间的并发来加速。对于需要直接插入数据到叶

子分区的情况，在业务实现中，还可以先创建一个临时的表、插入数据、然后与相应的

分区表进行分区交换，假如分区表上有索引，直接插入数据的性能会受到影响，这种分

区交换的方式，可以在数据准备结束之后再创建索引，整个数据处理过程对分区表没有

任何影响，总体性能高于直接的COPY和INSERT。参考相关的"交换分区"章节。

验证分区策略

表分区的目的是减少查询的数据扫描量。若一张表基于相应的查询条件做分区，可

以使用EXPLAIN查看执行计划来验证是否只扫描了相关的分区而不是扫描全表。

例如，有一张表sales已经根据日期按月做了范围分区，同时按区域做了子分区，

参见"定义多级分区表"章节的例子。对于下面的查询：

=# EXPLAIN SELECT * FROM sales WHERE date='2020-01-07' AND region='usa';

下图是PostgreSQL优化器生成的执行计划：

下图是Orca生成的执行计划

从以上的的两个执行计划可以发现，不管是PostgreSQL优化器生成的执行计划，

还是Orca生成的执行计划，都只选择了部分分区，不过，Orca生成的执行计划是不同

的，因为其分区选择只选择了一个分区，分区表中是有默认分区的，Orca认为，默认

Greenplum Database 管理员指南 V6.2.1

分区中存储的数据与其他普通的分区不应该有重叠，当然，在正常的插入数据时也是这

样检查的。经过编者测试，直接插入非法数据到默认分区是会报错的，只能通过设置

gp_enable_exchange_default_partition参数为on然后交换默认分区

WITHOUT VALIDATION的形式完成，所以，如果要强制交换分区，确保数据的正确性

很重要，后面还会提到。

对于这个执行计划应该只显示对下列表的扫描：

 默认分区返回0-1条数据(可能不止一个默认分区，PostgreSQL优化器一定会扫

描默认分区，Orca根据条件判断，不一定会扫描默认分区)

 符合日期的USA地区叶子分区(sales_1_2_prt_usa)返回一些记录

从PostgreSQL优化器生成的执行计划发现，有3个默认分区被扫描了，分别是第

一级一月份分区的其他地区子分区，第一级其他时间分区的usa子分区，第一级其他时

间分区的其他地区子分区。而Orca只扫描了条件完全匹配的一个分区。

注意：要确保执行计划只扫描了必要的叶子分区。

分区选择性的诊断

如果执行计划显示没有出现分区过滤，而是扫描全表，可能和以下的限制有关：

 执行计划仅可以对稳定(函数根据易失性可以分为VOLATILE、IMMUTABLE和

STABLE，IMMUTABLE是事务稳定的，STABLE是永远稳定的，这些概念可以参考

PG的文档，在函数章节也会继续介绍)的比较运算符执行选择性扫描，如：

 执行计划不识别非稳定函数来执行选择性扫描。例如，WHERE子句中使用如date >

CURRENT_DATE会使得执行计划有选择性的只扫描部分叶子分区，而time >

TIMEOFDAY不会。这里涉及的是函数的易失性问题，VOLATILE类型的函数，对

于完全相同的输入参数，不能保证输出的结果不变，例如random()函数，在创建

函数时如果没有明确指定，就是VOLATILE的。IMMUTABLE类型的函数，在整个

事务范围内，相同的输入参数，一定会得到相同的输出结果，例如now()函数。

STABLE类型的函数，任何时候，输入的参数相同，输出的结果就相同，例如sin()

函数。如果分区字段匹配的条件是VOLATILE类型函数，那么将无法替换为常量值，

Greenplum Database 管理员指南 V6.2.1

因为这类函数的输出值随时可能发生改变，而IMMUTABLE和STABLE类型的函数，

可以在事务开始时计算出一个结果并一直使用，这样就可以进行分区过滤了，所以，

对于那些可能用于分区过滤的函数，请注意其VOLATILE属性。

查看分区设计

要查看分区表的设计情况，可以通过查询pg_partitions视图来查看。例如，查

看sales表的分区情况：

=# SELECT partitionboundary, partitiontablename, partitionname,

partitionlevel, partitionrank

FROM pg_partitions

WHERE tablename='sales';

另外还有如下系统表和视图可以查看分区表的信息：

 pg_partition -- 记录分区表的层级关系。

 pg_partition_rule -- 记录各个层级的分区的规则和隶属关系。

 pg_partition_templates -- 展示SUBPARTITION TEMPLATE信息的视图，

实际上，SUBPARTITION TEMPLATE信息也是存储在pg_partition和

pg_partition_rule系统表中的，通过paristemplate字段来标识。

 pg_partition_columns -- 分区表的分区字段信息。

关于这些系统表和视图的更详细的信息，可以参照相关章节。

维护分区表

必须使用ALTER TABLE命令从ROOT表来维护分区。最常见的场景是根据日期范围

维护数据，例如，删除旧的分区并添加新的分区。还有一种可能就是把旧的分区转换为

压缩AO表以节省空间。若在父表中存在默认分区，添加分区的操作只能是从默认分区

拆分出一个新的分区。

 添加新分区

 修改分区名称

 添加默认分区

Greenplum Database 管理员指南 V6.2.1

 删除分区

 清空分区数据

 交换分区

 拆分分区

 修改子分区模版

 交换叶子分区为外部表

重要提示：在定义和更改分区时，使用分区名称而不是分区TABLE的relation name。

虽然可以在CREATE分区表时通过WITH子句中的tablename属性的方式为分区指定个

性化的relation name，但是建议永远不要这样做，这是一个违反规范的做法(也许

可以作为一个考题，例如，如何创建一个10级分区表，因为按照缺省的分区命名规则，

到8级分区时就会出现表名重复的报错)。虽然可以使用SQL命令直接针对分区表进行查

询和装载操作，但只能通过ALTER TABLE . . . PARTITION的方式来修改分区。

从语法限制的角度来说，分区不强制要求定义名称，若分区没有名称，下面的两种

表达式仍可用于选择一个分区:

PARTITION FOR (value);

PARTITION FOR(RANK(number));

注意：编者建议，在真实的生产中，务必为每个分区指定一个名称，这样对分区表的维

护等都会带来便利。永远不要使用FOR(RANK(number))的方式来选择分区，例如，

在删除分区时，rank是会不断发生变化的，这种方式很容易导致删错分区。

添加新分区

可以使用ALTER TABLE命令在现有的分区表上添加新分区。如果现有的分区表包

含了SUBPARTITION TEMPLATE定义，新增的分区将根据该模版创建子分区。例如：

=# CREATE TABLE sales (

trans_id int,

date date,

amount

decimal(9,2),

Greenplum Database 管理员指南 V6.2.1

region text

) DISTRIBUTED BY (trans_id)

PARTITION BY RANGE (date)

SUBPARTITION BY LIST (region)

SUBPARTITION TEMPLATE (

SUBPARTITION usa VALUES ('usa'),

SUBPARTITION asia VALUES ('asia'),

SUBPARTITION europe VALUES ('europe')

)

(

START (date '2020-01-01') INCLUSIVE

END (date '2021-01-01') EXCLUSIVE

EVERY (INTERVAL '1 month')

);

=# ALTER TABLE sales ADD PARTITION p202101

START (date '2021-01-01') INCLUSIVE END (date '2021-02-01') EXCLUSIVE;

如果在创建TABLE时没有SUBPARTITION TEMPLATE，在新增分区时需要定义子分区：

=# CREATE TABLE sales (

trans_id int,

date date,

amount

decimal(9,2),

region text

) DISTRIBUTED BY (trans_id)

PARTITION BY RANGE (date)

SUBPARTITION BY LIST (region)

(

START (date '2020-01-01') INCLUSIVE

END (date '2021-01-01') EXCLUSIVE

EVERY (INTERVAL '1 month')

(

SUBPARTITION usa VALUES ('usa'),

SUBPARTITION asia VALUES ('asia'),

SUBPARTITION europe VALUES ('europe')

)

);

=# ALTER TABLE sales ADD PARTITION p202101

START (date '2021-01-01') INCLUSIVE END (date '2021-02-01') EXCLUSIVE

(

SUBPARTITION usa VALUES ('usa'),

SUBPARTITION asia VALUES ('asia'),

SUBPARTITION europe VALUES ('europe')

Greenplum Database 管理员指南 V6.2.1

);

如果要在现有分区上添加子分区，可以指定分区执行ALTER命令。例如：

=# ALTER TABLE sales ALTER PARTITION FOR ('2020-12-01')

ADD PARTITION africa VALUES ('africa');

注意：这种方式会导致该多级分区表变的不规整，Orca无法处理这种多级分区表。

注意：若在有默认分区的分区表中添加新的分区，只能从默认分区拆分出一个新的分区。

参见"拆分分区"相关章节。编者建议，尽可能避免添加默认分区，因为维护更困难。

另外，在真实的生产中，所有的新增分区都要明确指定分区名称，因为不指定的情况下，

数据库将会自动产生一个随机的名称，当需要查找分区表的relation name或者在不

同的集群之间比对分区结构时，将是很大麻烦。

修改分区名称

子表的名称同样是受唯一性约束和长度限制的，GP中对象长度限制为63个字符，

pg_class系统表的唯一索引pg_class_relname_nsp_index限制了名称不能重复。

若名称超过长度限制，表名的超长部分会被截断，如果截断之后的relation name存

在重复就会报表已存在的错误。子表的表名格式如下：

<父表名称>_<分区层级>_prt_<分区名称>

例如：

sales_1_prt_jan20

对于未指定分区名称而自动产生的范围分区表来说可能是这样的：

sales_1_prt_1

子表的relation name不能通过直接执行ALTER表名来实现。但修改ROOT表的

relation name时，该修改将会影响所有相关的分区表。例如：

=# ALTER TABLE sales RENAME TO globalsales;

该操作将会把相关的分区表名称改为如下的格式：

globalsales_1_prt_1

Greenplum Database 管理员指南 V6.2.1

只修改分区名称的操作如下所示：

=# ALTER TABLE sales RENAME PARTITION FOR ('2020-01-01') TO jan20;

该操作将会把相关分区表的表名改为：

sales_1_prt_jan20

注意：在使用ALTER TABLE命令修改分区时，使用的是分区名称(如jan8)，而不是

分区表的relation name(如sales_1_prt_jan08)。对于使用FOR (value)指定

分区的方式，GP是使用FOR指定的value来匹配分区表的CHECK约束，换言之，只要

FOR()的条件在分区条件内即可匹配。

添加默认分区

可以使用ALTER TABLE命令为现有分区表添加默认分区(当有多级子分区且没有

SUBPARTITION TEMPLATE时，添加默认分区也需要定义子分区)：

=# ALTER TABLE sales ADD DEFAULT PARTITION other;

如果是多级分区表，同一层次中的每个分区都需要一个默认分区。例如：

=# ALTER TABLE sales ALTER PARTITION FOR (RANK(1))

ADD DEFAULT PARTITION other;

=# ALTER TABLE sales ALTER PARTITION FOR (RANK(2))

ADD DEFAULT PARTITION other;

=# ALTER TABLE sales ALTER PARTITION FOR (RANK(3))

ADD DEFAULT PARTITION other;

RANK指的是范围分区同一层级中的顺序，在不涉及rank可能会发生变化的操作时

不会造成误操作。可参见pg_partition_rule表的parruleord字段。若分区表没

有默认分区，无法匹配到叶子分区CHECK约束的新增记录将被拒绝，如果有默认分区，

无法匹配到叶子分区CHECK约束的新增记录将进入默认分区。

删除分区

Greenplum Database 管理员指南 V6.2.1

可以使用ALTER TABLE命令删除分区表中的分区。如果被删除的分区有子分区，

其所有的子分区(包括所有的数据)会一起被删除。对于范围分区的表来说，在滚动数

据时，通常是删除最老的数据。例如：

=# ALTER TABLE sales DROP PARTITION FOR (RANK(1));

=# ALTER TABLE sales DROP PARTITION FOR ('2020-01-01');

注意：在将RANK(1)的分区删除后，其余分区的rank值仍然是从1开始的连续编号。

编号的顺序按照分区字段的值由小到大从1开始排序，不管分区是否连续(所有的

START和END之间没有空缺即为连续)，所以，在真实的项目场景中，要绝对杜绝这种

删除分区的方式，要使用partition name或者value的方式来匹配分区。

清空分区数据

可以使用ALTER TABLE命令来清空分区。在清空一个包含子分区的分区时，其所

有相关子分区的数据都自动被清空。命令如下：

ALTER TABLE sales TRUNCATE PARTITION FOR (RANK(1));

ALTER TABLE sales DROP PARTITION FOR ('2020-01-01');

注意：建议采用partition name或者value的方式来匹配分区。

交换分区

交换分区是用一个普通的TABLE与现有的分区交换身份。使用ALTER TABLE命令

来交换分区。另外只能交换叶子分区(多级分区中的非叶子分区不可以被交换)。交换

分区对于数据加载是有帮助的。例如，先将数据装载到一个临时的表中，然后与目标分

区进行交换，例如分区表上有索引，则可以在临时的表上重建好索引之后再进行交换，

将对分区表的影响降到最低，这种模式在索引查询场景有着广泛的应用。还可以使用交

换分区将旧的分区储存为AO表。

交换分区，不可以与复制表交换(DISTRIBUTED REPLICATED)，也不可以与其

他分区表或者分区表的子表进行交换，只能与普通表进行交换。

例如：

Greenplum Database 管理员指南 V6.2.1

=# CREATE TABLE sales (

id int,

date date,

amt decimal(10,2)

)DISTRIBUTED BY (id) PARTITION BY RANGE (date) (

START (date '2020-01-01') INCLUSIVE

END (date '2021-01-01') EXCLUSIVE EVERY (INTERVAL '1 month')

);

=# BEGIN;

=# CREATE TABLE jan20y (LIKE sales) WITH (appendonly=true);

=# INSERT INTO jan20y SELECT * FROM sales_1_prt_1 ;

=# ALTER TABLE sales EXCHANGE PARTITION FOR (DATE '2020-01-01')

WITH TABLE jan20y;

=# DROP TABLE jan20y;

=# END;

注意：该例子涉及的是一级分区表sales。编者认为能使用一级分区的情况下，最好不

要选择多级分区这种复杂的结构，避免维护管理时不必要的麻烦。

警告：如果使用了WITHOUT VALIDATION语法进行分区交换，将不会检查数据的合法

性，必须自行确保数据符合分区的约束检查，否则可能会在查询时得到错误的结果。

警告：GP的参数gp_enable_exchange_default_partition用以控制是否可以使

用EXCHANGE DEFAULT PARTITION语法来交换默认分区，这个参数的缺省值是off，

也就是说，缺省情况下，尝试交换默认分区是会失败报错的。正如之前所说，如果使用

WITHOUT VALIDATION语法强制交换默认分区，而该分区中存在本应该存储在其他叶

子分区的数据，这可能会导致Orca的查询得到错误的结果。

拆分分区

拆分分区是将现有的一个分区分成两个分区。使用ALTER TABLE命令来拆分分区。

只能拆分叶子分区(多级分区中的非叶子分区不可以被拆分)，这也是建议不要使用多

级分区的一个因素，维护多级分区将是极大的麻烦。指定的分割值对应的数据将进入后

面一个分区(就是START为INCLUSIVE)。

例如，将一月份的分区拆分成一个1-15日的分区和另一个16-31日的分区：

=# CREATE TABLE sales (

id int,

date date,

Greenplum Database 管理员指南 V6.2.1

amt decimal(10,2)

)DISTRIBUTED BY (id) PARTITION BY RANGE (date) (

START (date '2020-01-01') INCLUSIVE

END (date '2021-01-01') EXCLUSIVE EVERY (INTERVAL '1 month')

);

=# ALTER TABLE sales SPLIT PARTITION FOR ('2020-01-01')

AT ('2020-01-16') INTO (PARTITION jan20y01to15, PARTITION jan20y16to31);

如果分区表有默认分区，要添加新的分区只能从默认分区拆分。而且只能从叶子分

区的默认分区拆分(多级分区中的非叶子分区不可以被拆分)。在使用INTO子句时，第

2个分区名称必须是已经存在的默认分区。例如，从默认分区中拆分出一个新的月份分

区：

=# CREATE TABLE sales (

id int,

date date,

amt decimal(10,2)

)DISTRIBUTED BY (id) PARTITION BY RANGE (date) (

START (date '2020-01-01') INCLUSIVE

END (date '2021-01-01') EXCLUSIVE EVERY (INTERVAL '1 month'),

DEFAULT PARTITION other

);

=# ALTER TABLE sales SPLIT DEFAULT PARTITION

START ('2021-01-01') INCLUSIVE END ('2021-02-01') EXCLUSIVE

INTO (PARTITION jan21, default partition);

注意：如何在有默认分区的多级分区表上增加新的分区？SPLIT也是不行的，因为

SPLIT只能针对叶子分区，所以，有默认分区的情况下，不能直接添多级分区表的一级

子分区，只能先删掉一级分区的默认分区，添加新的分区之后再重新添加默认分区。再

次重复之前的观点，不要使用多级分区，除了会带来麻烦，可能什么好处也不会带来。

同时，最好也不要设置默认分区，不要图一时方便，遗留万分苦难。

修改子分区模版

使用ALTER TABLE SET SUBPARTITION TEMPLATE命令来修改现有分区表的

子分区模版。在修改了子分区模版之后添加的分区，其子分区将按照新的模版产生。已

经存在的分区不会被修改。例如：

=# CREATE TABLE sales (

trans_id int,

Greenplum Database 管理员指南 V6.2.1

date date,

amount

decimal(9,2),

region text

) DISTRIBUTED BY (trans_id)

PARTITION BY RANGE (date)

SUBPARTITION BY LIST (region)

SUBPARTITION TEMPLATE (

SUBPARTITION usa VALUES ('usa'),

SUBPARTITION asia VALUES ('asia'),

SUBPARTITION europe VALUES ('europe'),

DEFAULT SUBPARTITION other_regions)

(

START (date '2020-01-01') INCLUSIVE

END (date '2021-01-01') EXCLUSIVE

EVERY (INTERVAL '1 month')

);

=# ALTER TABLE sales SET SUBPARTITION TEMPLATE (

SUBPARTITION usa VALUES ('usa'),

SUBPARTITION asia VALUES ('asia'),

SUBPARTITION europe VALUES ('europe'),

SUBPARTITION africa VALUES ('africa'),

DEFAULT SUBPARTITION other

);

当有更多级的子分区模版时，可以通过下面的语法形式来逐级修改子分区模版：

=# ALTER TABLE sales ALTER PARTITION FOR (RANK(1)) ALTER PARTITION

FOR (RANK(1)) SET SUBPARTITION TEMPLATE

=# ALTER TABLE sales ALTER PARTITION FOR (RANK(1)) SET SUBPARTITION TEMPLATE

=# ALTER TABLE sales SET SUBPARTITION TEMPLATE

在使用新的模版后为表sales新增一个分区时，其将包含Africa地区的子分区，

下面的命令将创建子分区usa、asia、europe、africa和默认分区other：

=# ALTER TABLE sales ADD PARTITION jan21

START ('2021-01-01') INCLUSIVE END ('2021-02-01') EXCLUSIVE;

注意：这个例子在一级分区有默认分区时是不能执行的。要删除子分区模版，使用SET

SUBPARTITION TEMPLATE并使用空的参数来完成。例如，将上面例子中的sales

表的子分区模版清空：

=# ALTER TABLE sales SET SUBPARTITION TEMPLATE ();

Greenplum Database 管理员指南 V6.2.1

交换叶子分区为外部表

不记得具体是从什么版本(大约是 4.3 的某个版本吧)开始，GP 允许将分区表的叶

子分区和可读外部表进行交换，而外部表的数据可以存储在数据库之外的地方，可能是

文件服务器，NFS，或者 HDFS 等，这样做显然会有不少限制，所以，如果确定没有使

用的必要，可以跳过该部分内容。

举个例子来说，假如有一张分区表，按照月份来分区，而绝大部分的查询只针对最

近几个月的数据来查询，这样就可以通过外部表分区的方式将早期数据以半离线的状态

存储到 GP 集群之外更廉价的存储空间。当对该表进行查询时，通过分区条件对分区进

行过滤，这样可以避免扫描外部表的分区，而当一些查询需要用到外部表分区的数据时，

数据将被从外部存储读取，其性能跟库内的分区相比会有很大的差异，但数据是在线可

查的，这就需要平衡性能与成本。

如果分区表包含 CHECK 约束或者 NOT NULL 约束的字段，将不能跟外部表进行交

换分区。

关于包含外部表的分区表的限制，请参考"分区表的限制"章节。

外部表与分区表交换示例

这个例子将展示，如何将一张基于时间的分区表的历史数据转移到数据库之外并变

成一个外部表的分区。我们将创建一个 2015 年到 2020 年按年分区的表，之后将 2015

年到 2018 年的分区转存到外部表中。首先，我们创建一张分区表：

=# CREATE TABLE sales (

id int,

year int,

qtr int,

day int,

region text

)DISTRIBUTED BY (id)

PARTITION BY RANGE (year) (

PARTITION yr START (2015) END (2020) EVERY (1)

);

Greenplum Database 管理员指南 V6.2.1

我们创建了一个含有 5 个分区的分区表，然后我们向表中插入 5 条数据，每个年

份的分区中有一条数据：

=# INSERT INTO sales

SELECT generate_series(1,5),

generate_series(2015,2019),

generate_series(5,9),

generate_series(10,14),

'region'||generate_series(1,5);

接下来我们将 2015 年到 2018 年的数据存到外部表中。

1、要确保外部表用到的协议在 GP 数据库中已经具备。

目前的例子，我们选择用 gpfdist 协议，例如，我们在 192.168.88.66 机器上

启动 gpfdist 服务。

$ nohup gpfdist -p 8080 -d /../ >/dev/null 2>&1 &

此处说明一下，gpfdist 不允许直接在/目录上启动服务，因此可以通过/../的

方式间接的从/上启动服务。

2、创建一张可写外部表，表结构与需要交换分区的分区表保持一致(使用 LIKE 语法)：

=# CREATE WRITABLE EXTERNAL TABLE my_sales_ext (LIKE sales)

LOCATION ('gpfdist://192.168.88.66/data/sales_2010_2018')

FORMAT 'TEXT'

DISTRIBUTED BY (id);

3、将数据从分区表导出到可写外部表，根据需要交换的数据范围指定条件进行数据过

滤：

=# INSERT INTO my_sales_ext SELECT * FROM sales

WHERE year >= 2015 AND year <= 2018;

注意：在导出数据之前，要先确保文件目录已经存在，gpfdist 不会自动创建目录，

还要确保目标文件不存在或者为空，可写外部表不会清空目标文件。

4、创建可读外部表，数据从刚刚建立的可写外部表写出的文件获取：

=# CREATE EXTERNAL TABLE my_sales_ext_prt (LIKE sales)

LOCATION ('gpfdist://192.168.88.66/data/sales_2010_2018')

FORMAT 'TEXT';

Greenplum Database 管理员指南 V6.2.1

5、因为需要交换的分区有多个，因此，需要删除外部表中需要交换的分区，再重新建

立一个跨度更大的分区：

=# ALTER TABLE sales DROP PARTITION FOR(2015);

=# ALTER TABLE sales DROP PARTITION FOR(2016);

=# ALTER TABLE sales DROP PARTITION FOR(2017);

=# ALTER TABLE sales DROP PARTITION FOR(2018);

=# ALTER TABLE sales ADD PARTITION p2015_2018 START (2015) END (2019);

6、交换分区和外部表：

=# ALTER TABLE sales EXCHANGE PARTITION FOR (2015)

WITH TABLE my_sales_ext_prt WITHOUT VALIDATION;

注意：要确保查询的结果正确，外部表中的数据必须符合被交换的叶子分区的约束检查。

7、删除交换出来的分区表，删除可写外部表：

=# DROP TABLE my_sales_ext_prt;

=# DROP EXTERNAL TABLE my_sales_ext;

可以将外部表的叶子分区修改分区名，使得名字中包含 ext，以助于识别该叶子分

区是一个外部表分区，例如：

=# ALTER TABLE sales RENAME PARTITION p2015_2018

TO p2015_2018_ext;

创建与使用序列

GP数据库中的序列，实质上是一种特殊的单行记录的表，用以生成自增长的数字，

可用于为表的记录生成自自增长的标识。不过，不要以为使用serial类型或者

bigserial类型就可以避免创建序列了，其实这两种类型会自动创建序列。所以，还

不如明确的创建序列，可能还可以优化一下性能。

GP提供了创建、修改、删除序列的命令，还提供了内置的函数用于获取序列的下

一个值(nextval())，重新设置序列的初始值(setval())。

注意：PostgreSQL的currval()函数lastval()在GP中是不支持的。不过，可

以通过直接查询序列这个表来获取。例如：

=# SELECT last_value,start_value FROM myserial;

Greenplum Database 管理员指南 V6.2.1

序列对象包括几个属性，例如，名称，步长(每次增长的量)，最小值，最大值，

缓存大小等，还有一个布尔属性：is_called，该属性的含义是，nextval()先返回

值还是序列的值先增长，例如序列当前的值为100，如果is_called为TRUE，则下一

次调用nextval()时返回的是101，如果is_called为FALSE，则下一次调用

nextval()时返回的是100，编者认为，这个属性差异不大，不必在意。

创建序列

使用CREATE SEQUENCE命令来创建并初始化一个给定名称的序列。序列的名字在

Schema之下必须与其他的对象如SEQUENCE、TABLE、INDEX或VIEW都不同，因为这

些对象的名称信息都是存储在pg_class系统表中的。例如：

=# CREATE SEQUENCE myserial START 101;

在创建序列时，GP 会将 is_called 设置为 FALSE，第一次调用 nextval()函

数访问新创建的序列时，序列的值不增加，并将 is_called 设置为 TRUE。例如刚刚

创建的序列，其 last_value 为 101，is_called 为 FALSE，第一次调用 nextval()

获得的是 101，last_value 属性不变，之后，is_called 变为 TRUE，再次调用

nextval()获得的是 102，last_value 增长为 102。

使用序列

使用CREATE SEQUENCE命令创建好序列之后，就可以使用nextval函数来获取

序列的值了。例如，获取序列的下一个值并插入表中：

=# INSERT INTO vendors VALUES (nextval('myserial'), 'acme');

nextval()根据序列的is_called属性来决定是否在返回数值之前先增加计数

器，如果is_called为TRUE，则先增加计数器，然后返回计数器的值，如果is_called

为FALSE，则先将is_called改为TRUE，然后返回计数器的值。

nextval()函数是不回滚的。只要被调用就被认为返回的值已经被使用，即便是

事务在nextval()之后失败或者被回滚。这就意味着中断事务会使得有空缺的序列没

有被真正的使用。同样的setval函数也是不回滚的。

注意：如果启用的Mirror镜像，那么，在UPDATE和DELETE语句中不能使用nextval()

函数。

可以使用setval()函数重置一个序列计数器的值。例如：

Greenplum Database 管理员指南 V6.2.1

=# SELECT setval('myserial', 201);

setval()函数有两种参数形式，setval(sequence, start_val)和

setval(sequence, start_val, is_called)。setval(sequence,

start_val)等同于setval(sequence, start_val, TRUE)，即，设置is_called

的属性为TRUE，如果不希望第一次调用setval的时候计数器增长，可以指定

is_called为FALSE，编者想说，这几乎毫无意义。

setval()函数同样永远不会回滚，就是说，即便ROLLBACK了，已经修改的值不

会变，等同于COMMIT了。

要查看序列当前的所有属性，可以直接查询序列对象：

=# SELECT * FROM myserial;

修改序列

使用ALTER SEQUENCE命令修改已有的序列表的属性，例如START的值，最小值，

最大值，步长等，也可以设置序列从指定的值重新开始。没有在ALTER SEQUENCE命

令中指定的属性值将保持不变。

ALTER SEQUENCE sequence START WITH start_value语句设置序列的

START为一个新的值，但对last_value属性没有影响，nextval()函数的返回值也

不受影响。

ALTER SEQUENCE sequence RESTART语句设置序列的last_value属性重新

从start_value属性的值开始，同时，is_called被设置为FALSE，nextval()函

数将返回start_value属性的值，序列如同新建。

ALTER SEQUENCE sequence RESTART WITH restart_value语句设置序列

的last_value属性为restart_value的值，同时，is_called被设置为FALSE，

nextval()函数将返回restart_value的值。效果等同于执行了函数SELECT

setval('myserial', restart_value, FALSE);

下面的命令是设置myserial序列重新从105开始：

=# ALTER SEQUENCE myserial RESTART WITH 105;

Greenplum Database 管理员指南 V6.2.1

删除序列

使用DROP SEQUENCE命令删除已有的序列表。例如：

=# DROP SEQUENCE myserial;

如果还有表使用了序列，序列将无法被删除，可以使用 CASCADE 进行级联删除。

设置序列为字段缺省值

除了在CREATE TABLE时使用SERIAL或者BIGSERIAL类型，可以明确的使用序

列来实现自增字段。使用SERIAL或者BIGSERIAL类型会自动创建序列。例如：

=# CREATE TABLE tablename (

id INT4 DEFAULT nextval('myserial'),

name text

);

还可以在创建了表之后，通过ALTER TABLE命令设置字段的缺省值为序列：

=# ALTER TABLE tablename ALTER COLUMN id SET DEFAULT nextval('myserial');

序列回旋

缺省情况下，序列是不允许回旋的(就是 last_value 达到了 max_value 的时候，

重新从 start_value 开始)，也就是说，当序列的值达到最大值时，netvalue()函

数将会报错：

ERROR: nextval: reached maximum value of sequence . . .

虽然说有 3 种 SERIAL 类型，SMALLSERIAL、SERIAL 和 BIGSERIAL，但是，

序列的属性 max_value 是 BIGINT 类型的，所以，如果使用序列的字段类型比 BIGINT

小的话，没等序列出现上述报错，字段就会报 out of range 的错误。BIGINT 的最

大值大约为 922 亿亿，正常的使用可能永远也不会达到。

可以设置序列允许回旋：

=# ALTER SEQUENCE myserial CYCLE;

Greenplum Database 管理员指南 V6.2.1

也可以在创建序列的时候设置允许回旋：

=# CREATE SEQUENCE myserial CYCLE;

在 GP 中使用索引

在大多数的OLTP数据库中，索引可以显著的改善数据访问的性能。然而在分布式

数据库(例如GP)中，应该谨慎使用索引。GP执行顺序扫描已经很快，而索引是通过随

机寻址在磁盘上定位数据记录，两者适用场景不同。与传统的OLTP数据库不同的是，

GP中数据是分布在多个Instance上的。这意味着每个Instance都扫描全部数据的一

小部分来查找结果。如果使用了分区表，扫描的数据可能会更少。通常，商业智能(BI)

的查询需要返回大量的数据，这种情况下使用索引未必有效。

GP建议在没有添加索引的情况下先测试一下查询的性能。索引更易于改善OLTP类

型查询的性能，一般，索引查询期望返回很少量的数据。在返回少量结果的场景下，索

引同样可以改善压缩AO表上查询的性能，当情况合适时优化器会把索引作为获取数据

的选择，而不是一味的全表扫描。对于压缩数据来说，索引访问数据时只解压需要的记

录而不是全表解压(最小单元的压缩块是要解压的)。

值得注意的是，GP会自动为主键字段创建主键索引。在分区表的ROOT表上建立索

引会自动在其相关的子表上也建立索引，分区索引的命名规则与分区表的命名规则类似，

但是，修改ROOT表的索引名称不会自动修改子分区的索引名称，这与表名的修改不同。

添加索引会带来一些资源开销 -- 其必定占用相当的存储空间，在更新数据时的

索引维护也需要消耗计算资源。需确保索引的创建在查询中真正被使用到。同时，需要

检查索引的确对于查询性能有显著的改善(与顺序扫描的性能相比)。可以使用

EXPLAIN查看执行计划来确认是否使用了索引。参考相关"查询剖析"章节。

在创建索引时需要综合考虑以下因素：

 查询的类型。索引有助于改善OLTP型的查询，其返回很少量的数据。对于返回数

据比例较大的查询，使用索引不会带来性能的改善。

 压缩表。在返回少量结果的情况下，索引同样可以改善压缩AO表的查询性能。对

于压缩数据来说，索引访问数据的时只解压需要的记录而不是全表解压。

 避免在频繁更新的字段上使用索引。在频繁更新的字段上创建索引，当该字段被

更新时，需要消耗大量的写盘操作，对IOPS能力的要求很高。

 如何选择B-tree索引。在考虑索引时，数据的唯一性指数(编者认为这样说更容

Greenplum Database 管理员指南 V6.2.1

易理解)，是个重要的指标，唯一性指数，是字段中DISTINCT值的数量除以表中

的总记录数。例如，如果一张表中有1000条记录，某个字段有800个DISTINCT

值，该字段的唯一性指数为0.8，唯一性很高。唯一索引总是具备1.0的唯一性指

数，不能更高了，因为所有的值都互不相同。值得注意的是在GP中唯一索引必须

包含所有的DK字段。唯一性指数高的字段更适合使用B-tree索引。



如何选择Bitmap索引。PostgreSQL不支持GP中的Bitmap索引，唯一性指数很

低的字段可能更适合Bitmap索引。参照"关于位图索引"章节。



索引字段用于关联查询。在经常关联查询的字段上建立索引或许可以改善关联查询

的性能，因为其可以帮助优化器使用其他的关联方法。这可能会涉及到Orca的

optimizer_enable_indexjoin参数或者PostgreSQL优化器的

enable_nestloop参数。



索引字段经常用在查询条件中。对于大表来说，查询语句WHERE条件中经常用到

的字段上，才适合考虑创建索引，因为查询用得到，但不是说WHERE条件中经常用

到的字段，就需要建索引，这又是一种逻辑颠倒的理解，还需要综合考虑其他因素。



避免索引重叠。在一个或多个顺序相同的字段上创建多个索引是多余的。不是说不

能将一个字段用于多个索引中，如果的确有必要也是可以的，但应该避免功能相似

的重复索引。



批量数据加载前删除索引。对于表中已有大量数据，需要批量加载数据的情况，应

该考虑先删除索引，加载数据之后再重新创建索引，这样可能会比带索引加载更快。

索引的维护代价是很高的，所以，有时可以通过分区交换的方式提前把数据和索引

准备好，尽可能将对目标表的影响降到最低，可参见"交换分区"章节。



聚集索引。聚集索引的意思是，表中的数据记录按照索引字段在磁盘上排序存储。

如果需要查询的数据在磁盘上的存储是无序的，数据库需要在磁盘文件上进行离散

扫描来获取，如果数据是有序的，数据库可以在连续的磁盘存储上获取数据，所以

对聚集索引字段的单条件查询的性能会更高效。

在 GP 中使用聚集索引

对于大表来说，使用CLUSTER(该命令只可以作用于Heap表)命令来排序物理记录

以创建聚集索引可能需要耗费极长的时间。要快速达到同样的效果，可以通过创建一张

中间表的方式来手动排序数据，由于CLUSTER命令只能用于Heap表，对于AO表，要达

到聚集索引的效果，也只能通过数据排序插入的方式实现。例如：

=# CREATE TABLE new_table (LIKE old_table) AS

Greenplum Database 管理员指南 V6.2.1

SELECT * FROM old_table ORDER BY myixcolumn;

=# CREATE INDEX myixcolumn_ix ON new_table;

=# ANALYZE new_table;

=# DROP old_table;

=# ALTER TABLE new_table RENAME TO old_table;

注意：从语法上来说，GP不支持CREATE CLUSTER INDEX语法，因此，上述方法是

常被用于实现类似聚集索引的方法，除了重建表，对于分区表，还可以通过交换分区的

方式来实现对分区的聚集索引效果。

索引类型

GP支持的PostgreSQL索引类型包括：B-tree、GiST、SP-GiST、GIN。不支

持HASH索引，每种索引使用不同的算法，适应不同类型的查询场景。缺省情况下CREATE

INDEX命令将创建B-tree索引，其适用于大多数查询场景。关于索引类型的详细说明，

可以参照PostgreSQL相关文档。

注意：GP在使用唯一索引时有特殊考虑。唯一索引必须包含所有的DK键。唯一索引不

支持AO表。在分区表上，唯一索引可以不包含分区字段，但其只对叶子分区有效，不

能对整个分区表有效，所以，这种不包含分区字段的唯一索引不能在整个分区表层面保

证数据的唯一性。

关于位图索引

除了PostgreSQL提供的索引类型之外，GP还提供了位图索引类型的支持。位图

索引对于数据仓库系统和决策分析系统可能会有帮助。这些应用通常拥有海量数据，日

常需要处理很多ad-hoc类型的查询，但少有数据修改的操作。

索引是一系列按照指定字段排序并包含指向表中记录指针的集合。普通的索引每个

Key对应一组数据表中相同字段值Row的tuple ID。而Bitmap索引，为表中的指定字

段的每一个不同的值存储一个位图，用二进制的1和0来标识是否有该值的记录。普通

的索引，尺寸有时可能会比表中的数据尺寸大几倍，而位图索引的尺寸可能只有表中数

据尺寸的N分之一。当然，如果未来GP引入了BRIN索引，可能其尺寸会更小，对于特

定场景的性能也会更高。

位图的每个bit对应表中记录的tuple ID，被标记的bit意味着对应的tuple ID

的记录包含这个位图的字段值。数据的实际位置可以通过映射函数得到。位图索引以压

缩的方式存储位图，位图索引字段DISTINCT值的数量越小，位图索引的尺寸就越小，

Greenplum Database 管理员指南 V6.2.1

压缩效果也会越好，与其他索引相比节省空间方面也更有优势。位图索引的尺寸与表中

记录数和索引字段DISTINCT值的数量正相关。

在WHERE子句中包含多个条件的查询时，相比较WHERE子句中只包含个别条件的场

景，位图索引可能更有效。

何时使用位图索引

Bitmap索引更适合只读的查询场景，不适合数据更新的场景。当索引字段的

DISTINCT值的数量介于100到10万之间，并常与其他索引字段一同查询时，Bitmap

索引可能会表现的更好。DISTINCT值的数量少于100的字段往往可能不适合使用任何

类型的索引，例如，性别字段通常只有两种DISTINCT值：男、女，不适合使用索引。

一个DISTINCT值的数量超过10万的字段，Bitmap索引的尺寸优势和性能优势都将严

重下降。

Bitmap索引可以提升ad-hoc类型查询的性能。对于在WHERE子句中使用AND和

OR的多条件查询，可以直接在位图索引上进行位图运算，而不用先转换为tuple ID，

性能可以得到很大的提升。当需要返回的记录数很小时，查询可以快速得到结果，而不

需要全表扫描。

注意：任何索引都不是万能的，这里说了很多Bitmap索引的优势，不等于就可以随意

的创建，适用有条件，选择需谨慎。此处所述的100~10万之间的DISTINCT值的数量

不是万能公式，实际上绝大部分场景不适合使用Bitmap索引，另外，衡量DISTINCT

值的数量时，要以单个Instance的情况来评估，因为索引也是分布式的。绝大部分情

况下，从全表的角度来看唯一性指数的话，可能会比较低，但从单个Instance的角度

来看，可能又很高。例如，一个100个Primary的集群，一张1亿条记录的表，某个字

段的DISTINCT值的数量为10000，整体计算，唯一性指数为0.0001。而从单个

Primary来看，其DISTINCT值的数量是10000，记录总数为100万，唯一性指数则为

0.01，跟集群角度相比，提高了100倍，也就是说，每个值对应大约100条记录，此时

B-tree索引可能会比Bitmap更适合。

何时不宜使用位图索引

位图索引不适合用于唯一性字段和DISTINCT值的数量很高的字段，例如客户名称、

电话号码。位图索引在DISTINC值的数量超过10万后，不管表中的记录数是多少，

Bitmap索引的尺寸优势和性能优势都将严重下降。

Greenplum Database 管理员指南 V6.2.1

位图索引主要适用数据仓库应用 -- 大量的分析查询但却极少的数据修改。不适

合大量并发事务更新数据的OLTP类型应用。

和B-tree相比，Bitmap索引的使用应该更保守。建议在建立Bitmap索引之后做

必要的测试以证明其可以对查询性能有改善(相对于做全表扫描查询)。另外，最好跟

其他索引类型做必要的对比，就编者的经验来看，正如前面[何时使用位图索引]章节

的[注意]部分所述，可能在很多需要使用索引的时候，直接选择B-tree就足够了，使

用GP时，需要时刻清醒，这是个MPP数据库，数据是分散的，不是集中的，要用分布式

的眼光来看待问题。

创建索引

使用CREATE INDEX命令在表上创建新的索引。缺省情况下，没有明确指定索引

类型时创建的是B-tree索引。例如，在表films的title字段上创建B-tree索引：

=# CREATE INDEX title_idx ON films(title);

在表employee的gender字段上创建位图索引：

=# CREATE INDEX gender_bmp_idx ON employee USING bitmap(gender);

表达式索引

得益于PostgreSQL的特性，GP中的索引不是只能建立在表中的字段之上，还可

以是表中的字段组成的表达式或者函数。这对于那些使用表达式或者函数作为条件的查

询也可以使用索引来加速。

表达式索引的维护代价比普通索引要高一些，因为在插入和更新索引时都需要计算

表达式的值，如果表达式的计算代价不大，这种差异可能也不会很显著。但在查询期间

并不需要重新计算，因为索引存储的是表达式计算的结果，查询时，只需要将条件中的

表达式算出并进行索引扫描即可，所以对于数据库来说，表达式索引在查询时的效果等

同于WHERE indexedcolumn = 'constant'，查询的性能与普通的非表达式索引

完全相同。因此，对于查询性能很重要，而插入和更新的性能相对不那么重要的情况，

表达式索引将很有用。

下面是一个做小写转换查询的例子：

Greenplum Database 管理员指南 V6.2.1

=# SELECT * FROM test1 WHERE lower(col1) = 'value';

如果希望该查询可以实现索引扫描，可以这样创建索引：

=# CREATE INDEX test1_lower_col1_idx ON test1(lower(col1));

假如经常执行下面这种查询：

=# SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

可以这样创建索引为这种查询提供索引扫描：

=# CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

注意：如果是一个表达式，CREATE INDEX 语法要求表达式要用括号括起来，如果是

一个简单的函数，括号可以省略。

索引检验

虽然在GP中索引一般不需要维护和调优，但检查索引是否被使用到，还是很重要

的，因为索引会带来很大的资源开销，没有被查询使用到的索引是对资源的极大浪费。

可以使用EXPLAIN命令来检验查询是否使用了索引。

执行计划显示出不同的执行步骤以及时间评估等信息。在EXPLAIN的输出中寻找

下面的步骤和操作以确认索引的使用情况：

 Index Scan -- 扫描索引。

 Bitmap Heap Scan -- 根据BitmapAnd、BitmapOr或BitmapIndexScan

得到的Bitmap结果到数据表中获取相关的记录。

 Bitmap Index Scan -- 根据查询语句中的某个字段的多个索引条件扫描相关

字段的索引并生成位图。

 BitmapAnd or BitmapOr -- 将多个来自BitmapIndexScan的结果进行AND

或者OR操作，生成一个新的位图。

没有一个通用的公式可以计算出哪些场景会使用索引或者不会使用索引，最好的方

法还是进行验证。应考虑以下因素：

 在创建和更新索引后运行ANALYZE。该命令将收集优化器需要的统计信息，优化

器将利用这些统计信息以评估不同类型执行计划的成本，只有更准确的统计信息才

Greenplum Database 管理员指南 V6.2.1

能更有助于优化器选择更合理的执行计划。



使用真实数据进行测试。用测试数据进行测试，这的确可以测出哪些索引是有用的，

但这样的结论对测试数据是没错的，但这个结论对于真实的数据可能没有任何帮助。



不要使用很少的数据来进行测试，因为跟真实的场景可能偏差太大。当测试的数据

量很小时，特定的条件匹配到的记录数将会非常少，此时优化器很可能会选择使用

索引扫描，而真实数据量很大，同样的条件可能匹配到的记录数会非常多，此时优

化器反而会选择全表扫描。



要小心制造测试数据(通常由于安全等因素，获取真实的数据很难)。数值相似，

完全随机，或者排序的数据等，这些都会与真实的数据特点有很大的差异。



当索引没有被使用，可以强制索引生效(修改参数的类型或者评估的系数让优化器

尽可能选择索引扫描)。有些运行时的参数可以关闭一些执行计划类型。例如，对

于PostgreSQL优化器来说，关闭顺序扫描(enable_seqscan)和打开嵌套循环

(enable_nestloop)等，使用EXPLAIN ANALYZE命令对使用索引前后进行比

较。对于SSD磁盘，还可以减小random_page_cost(缺省值为100，含义是一次

随机访问磁盘的代价是100个page，seq_page_cost的缺省值是1，含义是一次

顺序访问磁盘的代价是1个page)参数的值来强制使用索引。对于Orca优化器来

说，可能会涉及optimizer_nestloop_factor和

optimizer_enable_tablescan等参数的设置。

维护索引

当索引的性能变差，可通过REINDEX命令来重建索引。重建索引时，将使用数据

表中的数据重新建立一个全新的索引，然后取代旧的索引。

重建表上的全部索引

=# REINDEX TABLE my_table;

重建特定的索引

=# REINDEX INDEX my_index;

删除索引

使用DROP INDEX命令来删除一个索引。例如：

Greenplum Database (V6.2.1) - 3