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

Greenplum Database 管理员指南 V6.2.1

=# DROP INDEX title_idx;

在装载大量数据时，应该先删除索引、再装载数据、然后再重新创建索引，这样可

能比直接装载数据要快很多。编者建议使用这样的操作。编者提醒，有些版本存在不会

自动删除分区索引的情况，即，在删除ROOT表的索引时，分区的索引不会自动删除(目

前6版本会自动删除)，如果没有自动删除，可能需要逐个分区删除。

创建和管理视图

对于那些使用频繁或者比较复杂的查询，通过创建视图(VIEW)可以把其当作一张

表来使用SELECT语句访问。视图不会存储真实的数据(后面提到的物化视图会存储数

据)。每当视图被访问时，创建视图的查询语句会被作为子查询而执行。

使用视图可能需要考虑以下几个因素：

 创建视图的最佳实践 -- 如何创建视图才最合理。

 视图的依赖关系 -- 查看视图信息，查看视图依赖哪些对象，在GP中视图有强依

赖关系，这些依赖信息存储在系统表中。

 视图是如何被存储的 -- 描述视图依赖的机制。

创建视图

使用CREATE VIEW命令将查询语句定义为一个视图。例如：

=# CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'comedy';

注意：官方文档总是这样说：视图会忽略ORDER BY或者SORT操作，虽然在定义视图

的语句中可以使用ORDER BY子句，但该子句不会得到执行，除非有LIMIT子句同时出

现。但实际上，很多时候，在VIEW中定义了ORDER BY子句之后，查询的时候，真的

会是有排序的(也许有版本差异，但6版本真的会执行)。

删除视图

使用DROP VIEW命令删除已有的视图。例如：

Greenplum Database 管理员指南 V6.2.1

=# DROP VIEW topten;

如果视图之上还有其他视图依赖此视图，使用DROP VIEW . . . CASCADE命令

可以将这些依赖的对象一同删除，在此视图被其他视图依赖时，没有CASCADE选项的

话，DROP VIEW的操作将会报错失败。

创建视图的最佳实践

在创建视图时需要时刻谨记，视图其实就是一个SQL，实际执行时，其与直接写出

SQL没有任何区别，优化器可能会把视图中的SQL拆开进行优化调整计算的顺序。

视图的常见用处：

 视图可以把一个复杂的SQL作为一个简单的对象来重复使用。

 视图可以把一张表以另一种形式呈现，例如设置不同的数据过滤条件，不同的访问

权限等，这样，不需要再创建一张表。

如果一种SQL查询只是在个别语句中用到，可以使用SELECT命令的WITH子句来实

现，可能不需要为此而创建一张很少用到的视图。另外，WITH子句中的查询还可以将

执行计划分离，WITH子句中的查询，优化器不会尝试进行拆解，而是直接当做一个整

体来执行，因此往往会是一个非常好的选择。

通常，不要创建多层视图 -- 就是基于其他视图来创建视图，这样会极大增加视

图的管理难度，因为在GP中视图是有强依赖关系的，当需要删除并重建(CREATE OR

REPLACE命令不可以修改视图的字段定义)某个视图时，所有依赖该视图的上层视图，

都需要被删除。

有两类使用视图的方式是应该避免的，刚刚讲的多层视图还有其他弊端：

 定义了很多层的视图，最后的查询语句看起来很简单 -- 这样的设计看起来一点

都不酷，因为当遇到问题需要排查时，执行计划可能会变得很复杂，导致无从下手。

以前有不少人以写得出一个巨大无比的单条SQL搞定一个复杂的问题而自我陶醉，

带来的后续维护问题是痛苦的，反而拆分为多个相对简单的步骤更便于排查问题和

维护。编者认为，优雅而高效的解决问题才最重要，故意把问题复杂化不值得提倡，

那不能证明能力。

 定义一个大而全的视图，涉及很多表，然后可以用于各种场景 -- 这种设计也是

极其糟糕的，乍一看很酷，实际上，因为适用的场景多，就很难兼顾到每个场景，

所以，可能有的场景SQL执行的比较优化，而有的场景SQL执行的很糟糕，这样的

视图想要优化到每个场景都用起来都能表现出很好的性能，那几乎是不可能的。

Greenplum Database 管理员指南 V6.2.1

视图的依赖关系

如果要删除一张表，而该表上有视图依赖，则必须使用CASCADE来删除，或者先

把依赖的视图全部删掉。如下面的例子所示：

=# CREATE TABLE t (

id integer,

name text

);

=# CREATE VIEW v AS SELECT * FROM t;

=# DROP TABLE t;

ERROR: cannot drop table t because other objects depend on it

DETAIL: view v depends on table t

HINT: Use DROP ... CASCADE to drop the dependent objects too.

=# ALTER TABLE t DROP id;

ERROR: cannot drop column id of table t because other objects depend on it

DETAIL: view v depends on column id of table t

HINT: Use DROP ... CASCADE to drop the dependent objects too.

这个例子表明，当视图对表有依赖，两者的CREATE必须是有顺序的，必须先创建

了表，才能基于该表创建视图。不可能在创建好需要的表之前创建视图。

如果要修改一张表，例如要把字段的数据类型从integer改为bigint，这种需求

很正常，可能因为业务的调整，需要存储更大的数字，但是，因为表上有视图使用了这

个字段，那就没办法直接完成修改，可以先删除这些依赖的视图，然后修改字段类型，

之后再重建这些被删掉的视图。此时就需要用到视图依赖信息。

查看视图的依赖关系

为了便于阅读和学习实践，下面将使用举例的方式来展示表及字段与视图之间的依

赖关系。这里会介绍以下内容：

 查看一张表上直接依赖的视图

 查看一个字段上的直接依赖

Greenplum Database 管理员指南 V6.2.1

 查看视图与依赖表的模式信息

 查看视图的定义

 查看视图的多层依赖关系

首先创建一些表和视图，用于本节的示例使用：

=# CREATE TABLE t1 (

id integer PRIMARY KEY,

val text NOT NULL

);

=# INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');

=# CREATE FUNCTION f() RETURNS text

LANGUAGE sql AS 'SELECT ''suffix''::text';

=# CREATE VIEW v1 AS SELECT max(id) AS id FROM t1;

=# CREATE VIEW v2 AS SELECT t1.val FROM t1 JOIN v1 USING (id);

=# CREATE VIEW v3 AS SELECT val || f() FROM t1;

=# CREATE VIEW v5 AS SELECT f();

=# CREATE SCHEMA mytest;

=# CREATE TABLE mytest.tm1 (

id integer,

val text NOT NULL

);

=# INSERT INTO mytest.tm1 VALUES (1, 'one'), (2, 'two'), (3, 'three');

=# CREATE VIEW vm1 AS SELECT id FROM mytest.tm1 WHERE id < 3;

=# CREATE VIEW mytest.vm1 AS SELECT id FROM public.t1 WHERE id < 3;

=# CREATE VIEW vm2 AS SELECT max(id) AS id FROM mytest.tm1;

=# CREATE VIEW mytest.v2a AS SELECT t1.val FROM public.t1

JOIN public.v1 USING (id);

查看一张表上直接依赖的视图

要查看哪些视图直接依赖于t1表，使用一个关联多张系统表的SQL来查询，这些系

统表中包含了依赖关系，并限制查询只返回视图类的依赖：

=# SELECT v.oid::regclass AS view,d.refobjid::regclass AS ref_object

FROM pg_depend AS d JOIN pg_rewrite AS r ON r.oid = d.objid

JOIN pg_class AS v ON v.oid = r.ev_class

WHERE v.relkind = 'v' AND d.classid = 'pg_rewrite'::regclass

AND d.deptype = 'n' AND d.refclassid = 'pg_class'::regclass

AND d.refobjid = 't1'::regclass

GROUP BY 1,2 ORDER BY 1,2;

Greenplum Database 管理员指南 V6.2.1

查询中使用了对象标识符类型的强制类型转换，相关信息可以参考PostgreSQL

相关文档。由于原英文手册展示比较复杂，体现出了多字段依赖关系，这里已经进行了

分组去重。对上述SQL进行适当的修改之后可以查询哪些视图依赖于f函数：

=# SELECT v.oid::regclass AS view,d.refobjid::regproc as ref_object

FROM pg_depend AS d JOIN pg_rewrite AS r ON r.oid = d.objid

JOIN pg_class AS v ON v.oid = r.ev_class

WHERE v.relkind = 'v' AND d.classid = 'pg_rewrite'::regclass

AND d.deptype = 'n' AND d.refclassid = 'pg_proc'::regclass

AND d.refobjid = 'f'::regproc

GROUP BY 1,2 ORDER BY 1,2;

查看一个字段上的直接依赖

修改上面的查询语句，可以用于查询依赖于某个字段的视图，当需要修改表上的某

个字段或者删除该字段时(编者建议，不要在一张大表上直接修改字段定义，可能会是

一个性能很差的操作，应该制定规范，采用重建表的方式进行)，会用到这种查询，这

里将会用到pg_attribute系统表：

=# SELECT v.oid::regclass AS view,d.refobjid::regclass AS ref_object,

a.attname AS col_name

FROM pg_attribute AS a

JOIN pg_depend AS d ON d.refobjsubid = a.attnum AND d.refobjid = a.attrelid

JOIN pg_rewrite AS r ON r.oid = d.objid

JOIN pg_class AS v ON v.oid = r.ev_class

WHERE v.relkind = 'v' AND d.classid = 'pg_rewrite'::regclass

AND d.refclassid = 'pg_class'::regclass AND d.deptype = 'n'

AND a.attrelid = 't1'::regclass AND a.attname = 'id'

ORDER BY 1,2,3;

Greenplum Database 管理员指南 V6.2.1

查看视图与依赖表的模式信息

如果在多个模式中创建了视图，而视图依赖的表也分散在不同的模式中，则可以将

视图以及依赖的表以及所属的模式信息一起查询出来。这里将涉及pg_namespace系

统表，不过，会忽略系统模式：

=# SELECT v.oid::regclass AS view,ns.nspname AS schema,

d.refobjid::regclass AS ref_object

FROM pg_depend AS d

JOIN pg_rewrite AS r ON r.oid = d.objid

JOIN pg_class AS v ON v.oid = r.ev_class

JOIN pg_namespace AS ns ON ns.oid = v.relnamespace

WHERE v.relkind = 'v' AND d.classid = 'pg_rewrite'::regclass

AND d.refclassid = 'pg_class'::regclass

AND d.deptype = 'n' AND (ns.oid >= 16384 OR ns.nspname = 'public')

AND NOT (v.oid = d.refobjid)

GROUP BY 1,2,3 ORDER BY 1,2,3;

查看视图的定义

通过该SQL查询出依赖t1表的视图信息，包括依赖的字段和创建视图的SQL：

=# SELECT v.relname AS view,d.refobjid::regclass as ref_object,

string_agg(a.attnum||':'||a.attname,',' order by a.attnum) ref_cols,

'CREATE VIEW ' || v.relname || ' AS ' || pg_get_viewdef(v.oid) AS view_def

FROM pg_depend AS d

Greenplum Database 管理员指南 V6.2.1

JOIN pg_rewrite AS r ON r.oid = d.objid

JOIN pg_class AS v ON v.oid = r.ev_class

JOIN pg_class AS t ON t.oid = d.refobjid

JOIN pg_attribute AS a

ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid

WHERE NOT (v.oid = d.refobjid) AND d.refobjid = 't1'::regclass

GROUP BY 1,2,4

ORDER BY 1,2;

查看视图的多层依赖关系

这是一个CTE语句(这不是RECURSIVE CTE，所以，几乎所有版本都支持)，WITH

中查询出所有的视图，主体查询中查出哪些视图依赖其他视图：

=# WITH views AS (

SELECT v.relname AS view,d.refobjid AS ref_object,

v.oid AS view_oid,ns.nspname AS namespace

FROM pg_depend AS d

JOIN pg_rewrite AS r ON r.oid = d.objid

JOIN pg_class AS v ON v.oid = r.ev_class

JOIN pg_namespace AS ns ON ns.oid = v.relnamespace

WHERE v.relkind = 'v' AND (ns.oid >= 16384 OR ns.nspname = 'public')

AND d.deptype = 'n' AND NOT (v.oid = d.refobjid)

)

SELECT views.view, views.namespace AS schema,

views.ref_object::regclass AS ref_view,

ref_nspace.nspname AS ref_schema

FROM views

JOIN pg_depend as dep ON dep.refobjid = views.view_oid

JOIN pg_class AS class ON views.ref_object = class.oid

JOIN pg_namespace AS ref_nspace ON class.relnamespace = ref_nspace.oid

WHERE class.relkind = 'v'

AND dep.deptype = 'n';

Greenplum Database 管理员指南 V6.2.1

实际上，编者在编写并行DDL备份恢复脚本时，已经将这些复杂的视图依赖关系都

拆解了，通过编者的脚本备份出的DDL中，会自动把视图按照多层依赖关系进行分拆，

确保恢复DDL的时候，视图按照依赖关系进行并行恢复，同一层级相互没有依赖关系的

视图可以一起并行恢复。编者在实现集群之间DDL增量比对脚本时也实现了依赖拆解和

并行恢复，对实现灾备集群提供了有力的支持。

视图是如何被存储的

视图与表相似，都是relation，名称存储在pg_class系统表中，也都有字段属

性，字段属性与表一样存储在pg_attribute系统表中。下面是一些区别：

 视图没有数据文件 -- 因为视图不存储数据。

 pg_class系统表中的relkind属性是v，而数据表是r。

 每个视图有一个ON SELECT事件名称为_RETURN的rewrite规则。

视图的rewrite规则存储在pg_rewrite系统表中，视图的定义存储在该系统表

的ev_action字段中。关于视图的更多详细信息，可以参考PostgreSQL的相关文档。

视图的定义不是以字符串的形式存储的，存储的是解析后的查询树，在视图被创建

时生成的查询解析树，这样会有几方面的影响：

 对象名称是在视图创建时解析的，所以创建时的search_path会影响到视图的定

义，如果使用时的search_path与创建时不一致，可能会导致找不到表的报错。

 视图对其他对象的引用是通过OID来实现的，因此，修改依赖的表或者字段的名称

并不会影响视图的依赖关系。也就是说，如果视图依赖是表名是old，从old改为

new之后，依赖的表就是new。

 GP可以精确的获取视图使用了哪些对象，所以能够存储严谨的依赖关系。

注意：GP处理视图的方式和处理函数的方式完全不同，对于函数，GP存储的是字符串，

创建时不会解析为查询树，因为函数中的具体执行情况无法预知，只有具体的参数和具

体的数据在执行时才能确定涉及的对象，所以，没有办法精准获取函数的依赖关系。编

者在社区遇到很多次关于函数涉及的表如何查询的问题，这个的确是无能为力的，即便

Greenplum Database 管理员指南 V6.2.1

通过pg_proc系统表的prosrc(存储function的全部源码)字段来查找，也只能匹配

明文写出的对象名称。

视图的依赖信息存储在哪里

这些表中存储着视图依赖哪些对象：

 pg_class -- 存储所有relation的信息，包括表、视图、索引、外部表和序列

等。通过relkind来区分不同类型的对象。

 pg_depend -- 存储着数据库中非共享对象的依赖关系。

 pg_rewrite -- 存储着表和视图的rewrite规则。

 pg_attribute -- 存储着字段信息。

 pg_namespace -- 存储着模式信息。

需要注意的是，视图对其依赖的对象没有直接的依赖关系，而是通过rewrite规

则来实现依赖关系。其实这话的意义不大，因为依赖关系仍然是明确的且强制。

创建和管理物化视图

物化视图与普通视图类似，都是将一个常用的查询保存为一个relation，之后就

可以如同访问一张表一样执行SELECT操作。而不同的是，物化视图会直接将查询结果

持久化为数据文件，类似数据表的存储形式，所以访问物化视图时，是直接访问持久化

的数据，往往比通过视图访问数据表更快，但数据不是实时的。

物化视图的数据无法直接修改，只能通过REFRESH MATERIALIZED VIEW 命令

来刷新数据，用于存储物化视图的查询语句与普通视图的查询语句的存储方式相同。例

如可以这样创建一个物化视图：

=# CREATE MATERIALIZED VIEW sales_summary AS

SELECT seller_no, invoice_date,

sum(invoice_amt)::numeric(13,2) as sales_amt

FROM invoice

WHERE invoice_date < CURRENT_DATE

GROUP BY seller_no, invoice_date

ORDER BY seller_no, invoice_date;

Greenplum Database 管理员指南 V6.2.1

=# CREATE UNIQUE INDEX sales_summary_seller

ON sales_summary (seller_no, invoice_date);

可以使用如下命令定期刷新视图的数据：

=# REFRESH MATERIALIZED VIEW sales_summary;

GP中的物化视图的属性信息与表或普通视图是一样的，都是relation，表和普通

视图也是relation。当查询一个物化视图时，数据直接从物化视图的数据文件获取，

就如同访问普通的数据表一样，而物化视图中的查询语句，仅用于产生数据以填充物化

视图。

如果业务上可以接受定期更新物化视图的数据，将会为查询带来极大的性能提升。

物化视图还可以建立在外部表之上，以提升外部数据的访问性能，物化视图上还可

以创建索引，外部表上是不能创建索引的。不过，这种场景可能只适合从固定的外部表

查询固定的数据，或者外部表的数据有周期性变化，编者认为，通过物化视图来加速外

部表的访问并不是物化视图特有的功能，在外部表上创建物化视图同样需要读取外部表

的全部数据，这与，把数据加载到一张普通的数据表，没有任何差异。而物化视图的刷

新与普通数据表的TRUNCATE并重新INSERT效果相同。

如果一种SQL查询只是在个别语句中用到，可以使用SELECT命令的WITH子句来实

现，可能不需要为此而创建一张很少用到的视图。编者再次提醒，不要乱用视图，更不

要随意创建多层视图，虽然编者的脚本可以处理这些难题，但日常维护会非常困难。

创建物化视图

使用CREATE MATERIALIZED VIEW命令基于一个查询语句来创建物化视图：

=# CREATE MATERIALIZED VIEW us_users AS

SELECT u.id, u.name, a.zone

FROM users u, address a WHERE a.country = 'USA';

如果查询语句中包含ORDER BY或SORT子句，只会影响物化视图的数据生成，但

不会影响物化视图的查询，也就是说，生成的物化视图的数据会是有序的，但针对物化

视图的查询不保证顺序，不过这不等于说排序是完全无意义的，有序的数据可以有助于

物化视图创建聚集索引。

Greenplum Database 管理员指南 V6.2.1

刷新或停用物化视图

使用REFRESH MATERIALIZED VIEW命令来刷新物化视图的数据：

=# REFRESH MATERIALIZED VIEW us_users;

使用WITH NO DATA子句来刷新物化视图，物化视图中的数据将被清空，并且不

会产生新的数据，此时，物化视图将不能再被查询，查询这种物化视图将会得到一个报

错信息。

=# REFRESH MATERIALIZED VIEW us_users WITH NO DATA;

=# SELECT * FROM us_users;

ERROR: materialized view "us_users" has not been populated

HINT: Use the REFRESH MATERIALIZED VIEW command.

删除物化视图

使用DROP MATERIALIZED VIEW命令来删除物化视图的定义以及数据。例如：

DROP MATERIALIZED VIEW us_users;

使用命令DROP MATERIALIZED VIEW . . . CASCADE将可以级联删除所有依

赖该物化视图的对象，例如另一个物化视图依赖该物化视图，也会一同被删除，此时如

果没有CASCADE子句，DROP MATERIALIZED VIEW命令将会报错失败。物化视图的

依赖关系和普通视图是一样的。

例如，修改"查看视图的依赖关系"章节的相关示例SQL，可以查询物化视图的依赖

信息，注意，物化视图在pg_class中存储的relkind属性为m。例如，查询依赖t1表

的物化视图：

=# SELECT v.oid::regclass AS view,d.refobjid::regclass AS ref_object

FROM pg_depend AS d JOIN pg_rewrite AS r ON r.oid = d.objid

JOIN pg_class AS v ON v.oid = r.ev_class

WHERE v.relkind = 'm' AND d.classid = 'pg_rewrite'::regclass

AND d.deptype = 'n' AND d.refclassid = 'pg_class'::regclass

AND d.refobjid = 't1'::regclass

GROUP BY 1,2 ORDER BY 1,2;

Greenplum Database 管理员指南 V6.2.1

第八章：数据的分布与倾斜

GP 要求数据在 Instance 上均匀分布，在 MPP Share-Nothing 数据库中，对

于一个查询来说，所有操作都完成才算完成，那么这个总的耗时就是最慢 Instance

的耗时。如果有数据的倾斜，处理数据更多的 Instance，完成计算所需要的时间就

越久，所以，如果所有的 Instance 处理的数据量相当，那么总体的执行时间就会保

持一致，如果个别 Instance 要处理更多的数据，将可能导致严重的资源消耗且拖慢

整体的处理时间。

在进行大表关联时，合理的数据分布很重要，当进行关联时，匹配的记录必须在

Instance 本地，如果不能满足这个条件，执行计划中将会加入数据移动的算子，需

要将一个表或多个表做数据重分布，这样将消耗很多的网络资源，当然，在有些时候，

如果其中一个表很小，还可能会选择将小表进行广播。重分布操作是，每个 Instance

按照关联字段重新计算 HASH 值得到记录应该发送到哪个 Instance 然后发送过去。

本地关联

使用 HASH 分布的情况下，表的记录均匀的分散到所有 Instance 上，所以，在

进行关联查询时，表之间匹配的数据都在本地，计算将直接在本地完成，这种情况称为

本地关联。本地关联将可以最大程度的避免数据移动，所有的 Instance 都独立处理

本地的数据，而不需要在 Instance 之间通过内联网络交换数据。

要实现大表之间的本地关联，需要确保关联字段包含全部的分布键，这部分在"解

读 GP 分布策略"章节已经做了很多详细介绍，当关联的数据都在 Instance 本地，将

可以显著提升处理的性能。另外，在 CREATE TABLE 时应该确保关联的字段在不同的

表中采用相同的字段类型，因为，不同的数据类型对应不同的底层数据结构，相同的记

录因为底层存储的差异会分散到不同的 Instance 上，这种情况，在进行关联查询时

仍然会涉及数据的重分布。正如"解读 GP 分布策略"章节所述，尽可能只选择一个字段

作为分布键(这是非常重要的)。

数据倾斜

数据倾斜一般是由于选择了错误的分布键而造成的结果，或者是因为在 CREATE

TABLE 时没有指定分布键而自动以第一个字段作为分布键。通常可能会表现出查询性

能差，甚至出现内存不足的报错。数据倾斜会直接影响表扫描的性能，同时也会影响相

关的关联查询和分组汇总等计算的性能。

Greenplum Database 管理员指南 V6.2.1

检验数据分布是否均匀非常重要，无论是初次加载数据之后，还是增量数据加载之

后。有时，数据量不大时可能不会明显的表现出倾斜，所以需要定期检查倾斜情况。

虽然在官方文档中介绍了查询表中记录数分布情况的方法，但编者不想介绍这种方

法，编者认为，这种方法是陈旧而落后的，因为其需要使用 count(*)的方式来计算

表中的记录数，因为对于很大的表来说，这种操作无疑是难以忍受的。编者推荐，通过

计算一张表在不同 Instance 上所占的空间尺寸来评估是否发生倾斜。例如：

=# SELECT gp_segment_id,pg_relation_size('t1')

FROM gp_dist_random('gp_id') ORDER BY 2 DESC;

在 gp_toolkit 中有可用于查看表倾斜情况的视图，虽然编者从来不用这些视图，

但还是有必要介绍一下，后续编者将介绍更高效的实现方案。

 gp_toolkit.gp_skew_coefficients视图，一个非常复杂的视图，经过编者

了解，该视图最终会针对每张表分为AO表和非AO表来分别计算记录数情况，AO表

会通过get_ao_distribution函数来计算记录数，Heap表会通过count(*)来

计算记录数，真是一个神奇的设计。该视图以Instance记录数的标准差除以平均

值再乘以100来表示倾斜的严重程度，值越大倾斜越严重。

 gp_toolkit.gp_skew_idle_fractions视图，一个非常复杂的视图，经过编

者了解，该视图，会计算Instance中记录数的最大值与平均值的差值，然后除以

最大值，得到一个不大于1的浮点数，值越大倾斜越严重。不过，在获取表的信息

时与gp_toolkit.gp_skew_coefficients视图是一模一样的，所以，没有性

能优势。

编者来说说自己的实现，不去轮询查询每张表的信息，因为这些系统视图性能极差

的根本原因是，都要循环获取每张表的信息，尤其是表的数量很大的时候，每个表的信

息获取都会变慢，Heap 表的 count(*)操作更是致命的。我们的目的是检查倾斜情况，

而反应倾斜情况的未必一定要通过记录数来体现，如前面所述，可以通过尺寸来体现。

编者使用如下函数来获取文件信息：

CREATE OR REPLACE FUNCTION gp_toolkit.gp_table_file_info() RETURNS SETOF

VARCHAR[] AS $$

import os

_rslt = plpy.execute("""select current_database() dbname,

inet_server_port() port;""")

(_dbname, _port) = (_rslt[0]["dbname"], str(_rslt[0]["port"]))

_rslt = plpy.execute("""select oid,dattablespace from pg_database where

datname = '%s';""" % (_dbname))

(_dboid, _dbspc) = (str(_rslt[0]["oid"]), str(_rslt[0]["dattablespace"]))

def getSqlValue(_sql):

_utility = "PGOPTIONS='-c gp_session_role=utility' psql -v

Greenplum Database 管理员指南 V6.2.1

ON_ERROR_STOP=1"

_cmd = """%s -d '%s' -p %s -tAXF '|' 2>&1 <<_END_OF_SQL\n""" % (_utility,

_dbname, _port) + _sql + "\n_END_OF_SQL"

try:

val = os.popen(_cmd).read()

return val.strip()

except Exception, e:

plpy.error(str(e))

_dftpath = getSqlValue("""show data_directory""") + "/base/" + _dboid + "/"

_version = int(getSqlValue("""SELECT

(string_to_array((string_to_array(version(),'Greenplum Database

'))[2],'.'))[1];"""))

_rslt

if _version < 6:

_rslt = plpy.execute("""select

t.oid,trim(n.location_1)||'/'||t.oid||'/'||'%s'||'/' path

from pg_tablespace t,pg_filespace f,gp_persistent_filespace_node n

where t.spcfsoid = f.oid and f.oid = n.filespace_oid;""" % (_dboid))

else:

_dirc = getSqlValue("""show data_directory""")

_rslt = plpy.execute("""select

t.oid,'%s'||'/pg_tblspc'||t.oid||'/'||'GPDB_*'||'/%s/' path from

pg_tablespace t;""" % (_dirc,_dboid))

_spcarray = []

_spcarray.append([str(1663), _dftpath])

for _row in _rslt:

(_spcoid, _spcpath) = (str(_row["oid"]), _row["path"])

if not(os.path.exists(_spcpath)):

continue

if os.path.isfile(_spcpath):

continue

_spcarray.append([_spcoid, _spcpath])

_sizemap = {}

for _spcinfo in _spcarray:

(_spcoid, _spcpath) = (_spcinfo[0], _spcinfo[1])

_lscmd = """ls -lL --full-time %s|awk '{print $9"\t"$5"\t"$6" "$7}'|grep

'^[0-9]'|sort -n""" % (_spcpath)

_rslt = os.popen(_lscmd).read().strip()

if _rslt == "":

continue

for _row in _rslt.split("\n"):

(_relfile, _size, _time) = _row.split("\t")

_relfile = _relfile.split(".")[0]

_key = _spcoid + "-" + _relfile

Greenplum Database 管理员指南 V6.2.1

if _sizemap.has_key(_key):

_sizemap[_key] = [_sizemap[_key][0] + int(_size),

_sizemap[_key][1] + 1, _sizemap[_key][2] + "\n" + _time]

else:

_sizemap[_key] = [int(_size),1,_time]

_rslt = plpy.execute("""select

n.nspname,c.relname,c.reltablespace,c.relfilenode,c.relstorage

from pg_class c, pg_namespace n where c.relnamespace = n.oid

and c.relkind = 'r' and c.relstorage <> 'x' and c.reltablespace <> 1664

and not c.relhassubclass

and n.nspname not like E'pg\_temp\_%' and n.nspname not like

E'pg\_toast\_temp\_%';""")

for _row in _rslt:

(_nspname, _relname, _relspc) = (_row["nspname"], str(_row["relname"]),

str(_row["reltablespace"]))

(_relfile, _storage) = (str(_row["relfilenode"]), _row["relstorage"])

if _relspc == "0":

_relspc = _dbspc

_key = _relspc + "-" + _relfile

if _sizemap.has_key(_key):

if _storage == "h":

yield (_nspname, _relname, _sizemap[_key][0], _sizemap[_key][1],

_relspc, _sizemap[_key][2])

else:

yield (_nspname, _relname, _sizemap[_key][0], _sizemap[_key][1],

_relspc, None)

else:

yield (_nspname, _relname, 0, 0, None)

$$ LANGUAGE PLPYTHONU;

该函数较长，使用时请注意缩进，函数是用 Python 写的，所以，缩进不能出错。

在函数中，直接通过操作系统的 ls 命令查看数据库目录下的所有文件的信息，然后与

系统表进行关联。通过下面的 SQL 使用刚刚创建的函数来获取表的文件信息：

=# select nspname, relname, tablesize, filecount, expectfilecount,

round(filecount / expectfilecount,1) filecountratio, minsize, maxsize,

fileflag from (

select size[1] as nspname, size[2] as relname,

sum(size[3]::bigint) tablesize,string_agg(size[3],','),

sum(size[4]::bigint) filecount,

min(size[3]::bigint) minsize,

max(size[3]::bigint) maxsize,

md5(string_agg(size[6],E'\n' order by segment_id)) fileflag from (

select gp_toolkit.gp_table_file_info() size, gp_segment_id

Greenplum Database 管理员指南 V6.2.1

segment_id from gp_dist_random('gp_id')

) x group by 1,2

) x left join (

select nspname, relname, decode(relstorage, 'c', attcount, 1) * y.segs

expectfilecount from (

select nspname, relname, relstorage, count(*) attcount

from pg_namespace n, pg_class c, pg_attribute a

where n.oid = c.relnamespace and c.oid = a.attrelid

and c.relkind = 'r' and c.relstorage <> 'x' and c.reltablespace <>

1664 and not c.relhassubclass

and n.nspname not like E'pg\_temp\_%' and n.nspname not like

E'pg\_toast\_temp\_%'

group by 1,2,3

) x, (

select count(*) as segs from gp_segment_configuration where role =

'p' and content <> -1

) y

) y using(nspname, relname) order by 6 desc;

这段查询输出的每条记录包含 9 个属性，分别是，模式名称、表名称、该表的总

尺寸、该表总的文件数、预期文件数、文件数膨胀倍数、最小的尺寸(单个 Instance)、

最大尺寸(单个 Instance)、Heap 表的文件时间戳 MD5 值。

该方法经历过多次大规模集群的检验，有数万张表，目录下有几十万个文件的情况

下，也能在几分钟内完成全库的信息收集，可以将查询结果导出到文件中以便于进一步

的详细分析。

复制表的注意事项

在 CREATE TABLE 时指定分布策略为 DISTRIBUTED REPLICATED 就可以创建

复制表。复制表，会在每个 Instance 上存储一份完整的数据，所以复制表是绝对不

会倾斜的，因为每个实例的记录都完全相同。更多关于复制表的注意事项可以参考"复

制表的主要应用场景"章节。

计算倾斜

当数据倾斜到个别 Instance 时，它往往是 GP 数据库性能和稳定性的罪魁祸首

(编者想说的是，有无数的问题的根源都在倾斜上，当然不仅仅说的是数据倾斜，计算

倾斜是更隐蔽的问题，往往可能造成更严重的影响而且难以被发现和解决)，不过，对

于数据分布的倾斜，发现和处理往往不难。然而，当倾斜发生在关联、排序、聚合等各

种算子的计算过程中时，事情就变的十分复杂，这种情况我们称之为计算倾斜。而要处

理计算倾斜，可以说十分困难，一般的技术人员很难发现，更不用说解决这种问题，但

Greenplum Database 管理员指南 V6.2.1

这也是用好 GP 非常关键的一门武功，此门绝技练到第十层，就是大师级的人物了。

如果单个 Instance 出现了故障，这有可能与计算倾斜有关(X86 经不住长期超负

荷的资源压榨，不堪重负必有内伤)，目前，处理计算倾斜还是一个手动的过程(编者

也想不出如何实现自动，因为计算倾斜是个过程)。处理计算倾斜时，首先可以看一下

溢出文件的情况，如果有计算倾斜但又没有出现溢出文件，可能这种倾斜并不会造成严

重的后果。如果发现有计算倾斜现象出现，下面是一些步骤和方法可以参考。

 gp_toolkit.gp_workfile_usage_per_segment -- 通过该视图可以查询

每个Instance目前使用的Workfile溢出文件的尺寸和文件数量。通过该视图，

可以清晰的发现哪些Instance有严重的溢出文件问题。

 gp_toolkit.gp_workfile_usage_per_query -- 通过该视图可以查询每

个Query在每个Instance上的Workfile的使用情况，显示的信息包括，数据库

名称，进程号，会话ID，command count，用户名，查询语句，SegID，溢出

文件尺寸，溢出文件数量。

通常，通过这两个视图就可以确定正在发生倾斜的查询，而要解决这些问题，往往

需要重新优化SQL，例如确认统计信息是否严重失真，如果是，应该尝试更新统计信息，

找到执行计划中不合理的算子，通过修改可能的参数来干预执行计划，使用WITH子句

来分拆SQL以达到隔离执行计划的目的，使用临时表以强制拆分执行步骤，强制执行计

划选择两阶段AGG或者三阶段AGG等，总之，优化的最高目标就是，让数据库生成的执

行计划符合预期，最佳的预期需要基于对MPP的分布式理解和对数据的理解，如果只是

从其他数据库的使用中学到了一些支离破碎的知识，可能预期还不如数据库自动生成的

执行计划，当能够确切的知道什么样的执行计划才是最优的，那么距离优化出这样的执

行计划已经很近了。编者认为，对于GP的学习，首先要能够完全看懂执行计划，其次

是知道哪些步骤是有问题的，然后才能优化。优化的最基本前提是看懂执行计划，否则

一切都是空谈。让数据库按照期望的最佳路径生成执行计划，是最高功力，不过，往往

绝大部分技术人员，只是了解一些SQL语法，并不清楚数据库是如何一步一步计算得到

结果的，也就不可能知道何为最佳路径，所以，缺乏这些基本的内功，是不可能练出十

层绝学的。

Greenplum Database 管理员指南 V6.2.1

第九章：数据增删改

本章讲述关于数据管理和GP中的并发访问。包含如下内容：

 关于GP的并发控制

 插入新记录

 更新记录

 删除记录

 使用事务

 全局死锁检测

 回收空间

关于 GP 的并发控制

与事务型数据库系统通过锁机制来控制并发访问的机制不同，GP(与PostgreSQL

一样)使用多版本控制(Multiversion Concurrency Control/MVCC)保证数据一

致性。这意味着在查询数据库时，每个事务看到的只是数据的快照，其确保当前的事务

不会看到其他事务在相同记录上的修改。据此为数据库的每个事务提供事务隔离保护。

MVCC以避免给数据库事务显式上锁的方式，最大化减少锁争用以确保多用户环境

下的性能。在并发控制方面，使用MVCC而不是使用锁机制的最大优势是，MVCC机制下，

查询(读)的锁与写的锁不存在冲突，并且读与写操作之间从不会互相阻塞。

GP提供了各种锁机制来控制对表数据的并发访问。大多数GP的SQL命令可以自动

获取适当模式的锁以确保在命令执行时相应的表不会被删除或者修改。对于不能适应

MVCC锁的应用来说，可以使用LOCK命令来显式的获取必要的锁。然而，恰当的使用

MVCC比使用LOCK有更好的性能表现。

GP中的锁模式：

锁模式

相关 SQL 命令

冲突

ACCESS SHARE

SELECT

ACCESS EXCLUSIVE

ROW SHARE

SELECT . . . FOR UPDATE、

EXCLUSIVE、ACCESS EXCLUSIVE

SELECT FOR SHARE

ROW EXCLUSIVE

INSERT、COPY

SHARE、SHARE ROW EXCLUSIVE、

EXCLUSIVE、ACCESS EXCLUSIVE

SHARE UPDATE

VACUUM (no FULL)

SHARE UPDATE EXCLUSIVE、SHARE、

Greenplum Database 管理员指南 V6.2.1

EXCLUSIVE

ANALYZE

SHARE ROW EXCLUSIVE、EXCLUSIVE

ACCESS EXCLUSIVE

CREATE INDEX

ROW EXCLUSIVE、SHARE UPDATE

EXCLUSIVE、SHARE ROW EXCLUSIVE、

EXCLUSIVE、ACCESS EXCLUSIVE

SHARE ROW

ROW EXCLUSIVE、SHARE UPDATE

EXCLUSIVE

EXCLUSIVE、SHARE、SHARE ROW

EXCLUSIVE、EXCLUSIVE、ACCESS

EXCLUSIVE

DELETE, UPDATE、

ROW SHARE、ROW EXCLUSIVE、SHARE

SELECT . . . FOR UPDATE,

UPDATE EXCLUSIVE、SHARE、SHARE ROW

REFRESH MATERIALIZED

EXCLUSIVE、EXCLUSIVE、ACCESS

VIEW CONCURRENTLY

EXCLUSIVE

ACCESS

ALTER TABL、DROP TABLE、

ACCESS SHARE、ROW SHARE、ROW

EXCLUSIVE

TRUNCATE、REINDEX、

EXCLUSIVE、SHARE UPDATE EXCLUSIVE、

CLUSTER,

SHARE、SHARE ROW EXCLUSIVE、

REFRESH MATERIALIZED

EXCLUSIVE、ACCESS EXCLUSIVE

VIEW (without

CONCURRENTLY), VACUUM

FULL

注意：对于Heap表的UPDATE、DELETE和SELECT . . . FOR UPDATE操作，GP数

据库缺省使用EXCLUSIVE锁。当开启全局死锁检测时，对于Heap表的UPDATE和

DELETE操作将可以使用ROW EXCLUSIVE锁。对于SELECT . . . FOR UPDATE操

作仍然需要使用表级别的锁。

插入新记录

在表刚被创建时，是没有数据的。在数据库进行更多使用之前的第一步是插入数据。

要插入新的记录，使用INSERT命令。该命令需要表名和该表每个字段的值。数据的值

按照字段在表中出现的顺序排列，使用逗号分割。例如：

=# INSERT INTO products VALUES (1, 'Cheese', 9.99);

如果不知道字段在表中的顺序，还可以将字段显式的列出来。很多用户认为总是列

出字段名称是一种比较好的习惯。例如：

=# INSERT INTO products (name, price, product_no)

VALUES ('Cheese', 9.99, 1);

将一张表中符合条件的记录插入另一张表中：

=# INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

使用一个命令插入多条记录。例如：

=# INSERT INTO products (product_no, name, price) VALUES

(1, 'Cheese', 9.99),

(2, 'Bread', 1.99),

Greenplum Database 管理员指南 V6.2.1

(3, 'Milk', 2.99);

在同时插入大量数据时，应该考虑使用外部表(CREATE EXTERNAL TABLE)或者

COPY命令。在装载大量记录时，这些装载机制比使用INSERT VALUES更高效(是高效

好几个数量级)。更多关于批量装载数据的信息参见"装载和卸载数据"相关章节。

AO表已经为批量装载做了优化。不建议在AO表上使用单条的INSERT VALUES语

句。GP数据库最多支持单个AO表上127个并发INSERT数据，不过，编者建议尽量避免

AO表上的并发INSERT，因为底层的数据文件会分裂最多达到127倍！。

更新记录

UPDATE意味着对数据库中现有的数据进行修改。可以修改表中一条记录、一部分

记录或者全部记录。每个字段都可以被单独更新而不影响其他字段。

要执行更新，需要如下3方面的信息：

1. 要被更新的表和字段的名称。

2. 字段的新值。

3. 用于过滤需要被更新字段的记录的条件。

例如，下面的命令更新products表中所有price为5的记录的price为10：

=# UPDATE products SET price = 10 WHERE price = 5;

在GP中使用UPDATE命令有如下的限制：

 Orca支持更新分布键字段，6版本之前的PostgreSQL优化器不支持(编者实测如

此，而如果断然的说不支持，跟后续的[全局死锁检测]中所述冲突)。

 在启用了Mirror的情况下，UPDATE语句中不能有STABLE或者VOLATILE类型的

函数。

 PostgreSQL优化器不能UPDATE分区字段，Orca可以UPDATE分区字段。

删除记录

使用DELETE命令从指定的表中删除符合WHERE条件的记录。如果没有使用WHERE

Greenplum Database 管理员指南 V6.2.1

子句，将会删除该表的所有记录。例如，从products表中删除所有price为10的记录：

=# DELETE FROM products WHERE price = 10;

或者删除表中所有记录：

=# DELETE FROM products;

在GP中使用DELETE操作的限制：

 在启用了Mirror的情况下，DELETE语句中不能有STABLE或者VOLATILE类型的

函数。

清空表

若想要快速删除表中的所有记录，应该考虑使用TRUNCATE命令。例如：

=# TRUNCATE mytable;

该命令一次清空表中的全部记录。值得注意的是，TRUNCATE不扫描表，直接将数

据文件清空，继承此表的其他表不会被清空，只是被TRUNCATE的表会受到影响。分区

表会被视作一个整体，在父表上执行TRUNCATE操作会清空所有相关叶子分区的数据。

使用事务

事务允许将多个SQL语句放在一起当作一个整体来执行，所有SQL一起成功或失败。

在GP中执行事务的SQL命令为：

 使用BEGIN或START TRANSACTION开始一个事务。

 使用END或COMMIT提交事务。

 使用ROLLBACK回滚事务(放弃所有修改)(序列的增长不会被回滚)。

 使用SAVEPOINT选择性的保存事务点。

 使用ROLLBACK TO SAVEPOINT回滚到之前保存的事务点。

 使用RELEASE SAVEPOINT来释放之前保存的事务点。

Greenplum Database 管理员指南 V6.2.1

更多信息参考相关SQL说明，编者认为在GP中应用场景不多。

事务隔离级别

GP数据库兼容标准的SQL事务级别的情况如下：

 READ UNCOMMITTED和READ COMMITTED的表现类似标准的READ COMMITTED。

 REPEATABLE READ和SERIALIZABLE的表现类似REPEATABLE READ。

下面描述GP的不同事务隔离级别的特征：

读未提交和读已提交

GP数据库不允许任何命令看得到另一个并行事务未提交的更新(其实，对于heap

表，设置gp_select_invisible参数为on之后是可以看到其他事务未提交的数据的，

也看得到未被VACUUM回收的UPDATE和DELETE的数据，INSERT回滚数据也看的到，

一旦VACUUM就看不到了，不过，这个参数要特别慎用！)。所以，READ UNCOMMITTED

的效果与READ COMMITTED一致，READ COMMITTED提供了简单高效的事务隔离机制。

相当于SELECT、UPDATE和DELETE命令在开始执行时(注意，是命令开始时，不是事

务开始时)在数据库上做了个快照。

READ COMMITTED事务的SELECT命令将会：

 看得到查询语句开始之前所有已提交的数据(不是事务开始前)。

 看得到当前事务已经修改的数据。

 看不到其他事务未提交的数据。

 看得到当前事务启动之后其他并发事务已经提交的修改。

如果其他事务在当前事务的不同查询之间提交修改，当前事务中的不同SELECT会

看到不同的数据。UPDATE和DELETE命令只能看得到命令启动时已经提交的数据，不

过，不是事务启动的时间，在事务期间，有其他事务提交的修改，UPDATE和DELETE

依然可见，这就是[读已提交]，只要命令开始时是已经提交的数据，就可见。而不用

关心当前的事务是何时开始的。

READ COMMITTED事务隔离级别，允许一个事务中的UPDATE或DELETE操作开始

之前，其他并发的事务同样可以查询和修改记录。也就是说，READ COMMITTED不能

保证在事务期间，所涉及的数据不会发生变化，所以，对于一些要求事务期间必须保证

Greenplum Database 管理员指南 V6.2.1

数据库完全一致的应用来说，READ COMMITTED事务隔离级别是不够的。

可重复读取和可串行化

根据SQL标准定义的SERIALIZABLE事务隔离级别，其可以保证并发事务的运行结

果与一个接一个的运行结果完全相同。如果指定了SERIALIZABLE事务隔离级别，GP

数据会将其降级到REPEATABLE READ事务隔离级别。REPEATABLE READ事务隔离级

别不需要重量级的锁就可以避免脏读、不可重复读和幻读，但GP数据库不会检测并发

事务期间所有可能的可串行化的相互影响(就是不能保证得到可串行化的结果)。所以，

可以通过检查并行事务之间可能的影响来排查这种可串行化的相互影响，可以通过显式

的LOCK操作来避免并发事务之间的影响(在事务开始的时候就显式的把相关的表加上

必要的锁)。这段很难懂，英文解释也很难懂，总之，GP数据库本身无法保证可串行化

的效果，就是说，并发事务不能保证和串行执行一样的结果。

REPEATABLE READ事务的SELECT命令将会：

 在事务开始时(而不是在查询开始时)确定数据的快照。

 只能看得到事务开始之前已经提交的数据。

 看得到当前事务已经修改的数据。

 看不到其他事务未提交的数据。

 看不到当前事务启动之后其他事务提交的修改。

 在当前事务中多次执行SELECT命令总是得到相同的数据。

 UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE命令只能看得

到事务开始之前的记录，如果其他并发的事务已经修改、删除或LOCK了同一条记

录，REPEATABLE READ事务(修改同一条记录的操作)需要等待该事务提交或者

回滚这些修改，如果其他并发的事务提交了修改，当前的REPEATABLE READ事务

将会失败回滚，如果其他并发事务回滚了修改，当前的REPEATABLE READ事务将

可以提交当前的修改。

GP数据库缺省的事务隔离级别是READ COMMITTED，要修改事务隔离级别，可以

在开始事务时显式的指定事务隔离级别，或者在事务开始之后通过SET TRANSACTION

来设置。例如：

=# BEGIN ISOLATION LEVEL REPEATABLE READ;

=# END;

=# BEGIN;

=# SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

=# END;

Greenplum Database 管理员指南 V6.2.1

关于事务隔离级别，这一段真的很难懂，编者觉得用中文讲，这样算是比较易懂了

吧。简单总结来说，读已提交，就是只要其他事务COMMIT了，就你看得到，多次查询

的结果可能是会变的(结果不可重复)。可重复读，就是在一个事务的生命周期内，多

次查询看到的数据是不变的(结果可重复)。

全局死锁检测

GP 数据库的全局死锁检测，通过后台进程收集所有 Instance 的锁信息，通过检

测算法来检测死锁情况。这样，就可以放宽 Heap 表的并发 UPDATE 和 DELETE 操作

的锁限制。对于 AO 表，UPDATE、DELETE 和 SELECT . . . FOR UPDATE 仍然需

要 EXCLUSIVE 锁。

缺省情况下，全局死锁检测并未开启，GP 执行 Heap 表的 UPDATE 和 DELETE 命

令需要进行串行化操作。

可以通过设置 GUC 参数 gp_enable_global_deadlock_detector 来开启全

局死锁检测，这样就可以对 Heap 表，执行并发单条 UPDATE 和 DELETE 操作了。

在开启了全局死锁检测后，相关的后台进程会跟随数据库启动一起启动，通过设置

gp_global_deadlock_detector_period 参数来指定收集和分析分布式死锁信

息的时间间隔。

如果全局死锁检测发现了死锁，会中断相关事务中最新的一些事务来解除死锁。

如果全局死锁检测发现了下列的死锁类型，将只会有一个事务成功，其他事务会失

败，并且会收到报错信息。

concurrent updates to the same row is not allowed

 同一条记录上的并发事务，第一个事务是UPDATE，后面的的事务是UPDATE或者

DELETE并且其执行计划包含一个Motion算子。

 由PostgreSQL优化器执行的并发UPDATE操作在一张Heap表的同一个分布键上。

 由Orca优化器生成的在一张Hash分布的Heap表上同一条记录的并发UPDATE。

注意：GP 数据库使用 deadlock_timeout 参数来判断本地死锁，由于本地死锁与全

局死锁的检测算法不同，可能会被本地死锁检测发现，也可能被全局死锁检测发现，这

取决于哪个先发现。

Greenplum Database 管理员指南 V6.2.1

注意：如果 lock_timeout 开启且设置的值比 deadlock_timeout 和

gp_global_deadlock_detector_period 小，可能一个查询在被检测到死锁之前

就因为锁等待时间超时而被中断。

通过执行 pg_catalog.gp_dist_wait_status()函数可以查看所有

Instance 的锁等待信息，通过输出信息，可以确认，哪些事务在等待锁，哪些事务

在持有锁，锁资源的类型(锁什么样的对象，例如 relation、row 等)和模式(锁的等

级)，等待锁的 SessionID，持有锁的 SessionID，锁的 SegID。例如：

=# SELECT * FROM pg_catalog.gp_dist_wait_status();

当全局死锁检测解除一个死锁，会有如下报错信息：

ERROR: canceling statement due to user request: "cancelled by global

deadlock detector"

全局死锁检测对 UPDATE 和 DELETE 的并发容许

全局死锁检测可以管理 Heap 表上这些类型的 UPDATE 和 DELETE 命令的并发执

行(这些类型之间的容许情况见本节最后的表格)：

 单表的简单UPDATE。由PostgreSQL优化器执行的，没有FROM子句或者子查询的，

非分布键字段的UPDATE。

=# UPDATE t SET c2 = c2 + 1 WHERE c1 > 10;

 单表的简单DELETE。命令的FROM或者WHERE子句中没有子查询。

=# DELETE FROM t WHERE c1 > 10;

 裂变更新(Split UPDATE -- 在执行计划中的算子叫Split)。PostgreSQL优

化器，可以通过UPDATE命令更新分布键字段。

=# UPDATE t SET c = c + 1; -- c is a distribution key

Greenplum Database 管理员指南 V6.2.1

对于Orca优化器，可以通过UPDATE命令更新分布键字段和分区字段。

=# UPDATE t SET b = b + 1 WHERE c = 10; -- c is a distribution key

 复杂更新。UPDATE命令包含多表关联。

=# UPDATE t1 SET c = t1.c+1 FROM t2 WHERE t1.c = t2.c;

或者包含子查询的 UPDATE。

=# UPDATE t SET c = c + 1 WHERE c > ALL(SELECT * FROM t1);

 复杂删除。类似于复杂UPDATE，包含多表关联或者子查询。

=# DELETE FROM t USING t1 WHERE t.c > t1.c;

下表列举了全局死锁检测管理的 UPDATE 和 DELETE 命令的互相容许情况。涉及

哪些命令之间有没有冲突。例如，在同一条记录上的并发简单更新是容许的，并发复杂

更新和简单更新，将只有一个 UPDATE 会被执行，另一个 UPDATE 会失败报错。

简单更新

简单删除

裂变更新

复杂更新

复杂删除

简单更新

YES

简单删除

YES

裂变更新

复杂更新

YES

复杂删除

YES

回收空间

由于MVCC事务并发模型的原因，已经删除或者更新的记录仍然占据着磁盘空间，

虽然其对于新的事务来说已经不可见。如果数据库有大量的更新和删除操作，其将会产

生大量的过期记录。定期的运行VACUUM命令可以回收这些过期的记录空间。例如：

=# VACUUM mytable;

VACUUM命令还会收集表级别的统计信息，例如，记录数、占用磁盘页面数，所以

在装载数据之后对全表执行VACUUM是有必要的，这条规律同样适用AO表。

编者认为，应该更多的了解VACUUM命令。例如对于系统表来说，应该定期执行

VACUUM操作，系统表会因为数据库的DDL操作逐渐膨胀，如果长时间不做VACUUM，系

Greenplum Database 管理员指南 V6.2.1

统表的空间会严重膨胀，带来性能问题，甚至导致集群异常(这绝非危言耸听，编者遇

到不止一次因为系统表膨胀严重导致的性能问题或集群故障)，所以编者会为客户的集

群配置每日定时任务 -- 执行系统表的VACUUM操作。对于太长时间没有做VACUUM的

系统表，可能会面临需要花费数小时来运行VACUUM FULL的麻烦。

对于业务Heap表(如果不是的确有必要，尽可能用AO表)，虽然目前的版本VACUUM

命令的性能已经有了极大的提升，但还是建议做必要的对比测试，如果有可能，也许做

REORGANIZE会有更好的效率。

对于AO表，gp_appendonly_compaction_threshold参数决定了AO表的

VACUUM操作的实际行为，对于膨胀比例没有超过阈值的情况，不会真的执行VACUUM

操作，不过，如果执行了VACUUM FULL，则会忽略该参数。所以，对于AO表，也可以

定期执行VACUUM命令来尝试回收空间，而且在AO表上执行VACUUM命令时，空间将得

到回收，并将空间返回给文件系统，这与Heap表不同，Heap表是记录垃圾空间以便重

新利用。

配置自由空间映射

注意：从6版本开始，已经不再需要自由空间映射，该章节所讲述的内容是6版本之前

的特性，在6版本开始，因为PostgreSQL版本升级，Heap表中可用tuple的信息将在

文件中进行存储，6版本之前这些信息是存储在内存中的。

在6版本之前，对于Heap表来说，过期的记录会被存在叫做自由空间映射的地方。

自由空间映射的大小必须足够容纳数据库中的所有过期记录。如果尺寸不够大，超出自

由空间映射的过期记录占用的空间将无法被VACUUM命令回收。

VACUUM FULL命令将回收Heap表中所有无效记录，并将空间返回给文件系统，但

这是一个很昂贵的操作，对于一张大表，该操作可能会花费无法接受的时间长度。如果

自由空间映射已经溢出，最好的做法是及时的使用CREATE TABLE AS命令来重建数据

表并删除旧的表，也可以使用REORGANIZE来整理表，两者的效率可能是相当的，后者

的好处在于，不需要重建表，也不会涉及依赖关系的处理。

最好将自由空间映射设置为一个合适的值。自由空间映射由下面的参数来设置：

max_fsm_pages

max_fsm_relations

更多信息可参考PostgreSQL相关文档。

Greenplum Database 管理员指南 V6.2.1

第十章：数据查询

本章讲述在GP中如何使用SQL语言对数据库中的数据进行查询。可以使用标准的

PostgreSQL命令行工具psql来执行SQL语句，或者使用其他客户端工具来执行SQL

语句。

理解 GP 的查询处理

本节讲述 GP 数据库是如何处理查询的。深入理解这些概念，对于写出高效的 SQL

和 SQL 调优将是至关重要的。用户向 GP 数据库提交 SQL 查询的方式与其他数据库是

相似的，通过客户端工具(例如 psql)连接到 Master 机器，执行 SQL 语句。

理解执行计划与分发

查询被 Master 接收、处理、优化、创建一个并行的或者定向的执行计划(根据查

询语句决定)。之后 Master 将执行计划分发到相关的 Instance 去执行，每个

Instance 只负责处理自己本地的那部分数据，如果需要用到其他 Instance 的数据，

执行计划会在每一步的处理之前进行数据移动(Motion)。

大部分的算子 -- 例如表扫描(Scan)、关联(Join)、聚合(Aggregation)、

排序(Sort)都是在 Instance 本地被执行，每个 Instance 同时独立执行，不涉及

其他 Instance 的资源，当某个算子需要使用其他 Instance 的数据时，执行计划中

会被加入一个称为移动(Motion)的算子，以帮助其他算子准备需要的数据。

Greenplum Database 管理员指南 V6.2.1

理解执行计划

执行计划是 GP 根据特定的查询语句的处理逻辑，生成的一系列算子的有序集合，

执行计划是一个安排，一个规划，将 Instance 要做哪些运算，这些运算的顺序等安

排出来。执行计划的每一步代表着特定的算子，例如：扫表、关联、聚合、排序等。执

行计划被从下向上执行。与典型的数据库的处理不同的是，GP 有一个特有的算子：移

动(Motion)。移动算子(Motion)涉及到查询处理期间在 Instance 之间移动数据。

不过并非所有的查询都需要移动数据。例如针对系统表(在 Master 上)的查询不会涉

及通过内联网络移动数据，因为这些数据只需要从 Master 获取，不需要访问

Instance。

为了最大限度的实现并行化处理，GP 会将执行计划分割为多个步骤。每个步骤是

执行计划的一部分，其可以在 Instance 上被独立执行。当需要移动数据时，执行计

划会被分割开，两个算子分处在数据移动算子的两侧，即：先执行一步算子，然后执行

数据移动，再执行下一步算子。

例如，下面两个 Table 的关联查询：

=# SELECT customer, amount FROM sales JOIN customer USING (cust_id)

WHERE sale_date = '2020-06-06';

下图解释了执行计划。每个 Instance 获取到执行计划的副本然后并行开始执行。

对于该执行计划来说，有一个重分布算子(Redistribute motion)，这是为了完成

连接(Join)而执行的数据移动。执行计划被重分布算子分割为两步(slice 1 与

slice 2)。该执行计划还有另外一种数据移动称为汇总移动(Gather motion)，汇

总是 Instance 将计算结果反馈到 Master 从而可以反馈给客户端的一种操作。由于

这是一个 SELECT 操作，所以会有汇总算子(slice 3)。不是所有的查询都有汇总算

子，例如：CREATE TABLE . . . AS SELECT . . . 语句就不需要汇总算子。

Greenplum Database 管理员指南 V6.2.1

理解并行执行

GP 会创建多个 DB 进程来处理查询。在 Master 上被称为查询分发器(Query

Dispatcher/QD)。QD 负责创建、分发执行计划，汇总反馈最终结果。在 Instance

上，处理进程被称为查询执行器(Query executor/QE)。QE 负责完成自身部分的处

理工作以及与其他 QE 之间交换可能需要移动的中间结果数据。

执行计划的每个处理部分都至少涉及一个处理工作。执行进程只处理属于自己部分

的工作。在查询被执行期间，每个 Instance 会并行的执行一系列的处理工作。

同一部分相关的处理工作被称为簇(Gangs，这是编者的翻译，想不出更好的翻译，

所以一直沿用至今，实际上这个概念对于日常使用来说并不重要)。在一部分处理完成

后，数据将从当前处理向上传递，直到执行计划被完成。Instance 之间的通信涉及到

GP 的内联网络组件。

下图展示查询处理如何在 Master 和 2 个 Instance 之间被逐步执行的：

Greenplum Database 管理员指南 V6.2.1

关于 ORCA 优化器

在 GP 数据库中，目前 Orca(optimizer 参数控制)优化器与 PostgreSQL 优化

器共存，不是取代关系(因为取代不了，以前可能想过要取代)。在一些复杂 SQL 场景，

还有一些多分区的分区表场景，Orca 有时会更有优势。目前，在 5 版本和 6 版本中，

Orca 是缺省打开的，数据库会尽可能使用 Orca 优化器，对于 Orca 优化器还不能处

理的场景，会自动切换到 PostgreSQL 优化器。

Orca 优化器在以下场景会表现出更好的性能：

 针对分区表的查询(如果是多级分区表，必须是规整的)。

 包含子查询的查询。

 包含CTE的查询。

 DML操作(主要是功能的增强)。

目前，Orca 优化器与 PostgreSQL 优化器共存，缺省使用 Orca 优化器，如果

Orca 优化器不适用，会自动切换到 PostgreSQL 优化器。下图显示，Orca 优化器和

PostgreSQL 优化器的共存逻辑：

Greenplum Database 管理员指南 V6.2.1

注意：在使用 Orca 优化器时，Orca 优化器在生成执行计划时会忽略所有 PostgreSQL

的执行计划参数，Orca 有自己的一套参数。对于 Orca 搞不定的 SQL，自动切换到

PostgreSQL 优化器时，PostgreSQL 的执行计划参数将会被使用，这些 PostgreSQL

的执行计划参数一般是以 enable_开头或者 gp_enable_开头，一般分别在 guc.c

和 guc_gp.c 文件中可以找到这些参数。

启用或禁用 Orca

在 5 版本和 6 版本中，Orca 是缺省启用状态，可以通过参数 optimizer 来设置

启用或者禁用 orca 优化器。

可以在不同的级别来设置是否开启 Orca，例如，系统级别，数据库级别，ROLE

级别，会话级别，语句级别，按照这些级别来看，级别越低，优先级越高，例如，在

Database 级别的设置会覆盖系统级别的设置，依次类推，所有的参数(当前优先级可

以设置的话)都遵循这种优先级的规则。

注意：可以通过参数 optimizer_control 来禁止修改 optimizer 的设置，这个参

数的缺省值为 on，只有 SUPERUSER 可以修改。不过编者想说，慎用这个参数。

在系统级别开启 Orca

1、使用 gpadmin 用户登录 Master Host 主机。

Greenplum Database 管理员指南 V6.2.1

2、使用 gpconfig 命令来设置 optimizer 参数：

$ gpconfig -c optimizer -v on --masteronly

编者认为，是否需要指定--masteronly 选项，并不重要。

3、重新加载 postgresql.conf 配置文件，使修改生效，此处不是真正停止数据库

的操作，这类似 PostgreSQL 的 pg_ctl reload 操作。

$ gpstop -u

要在系统级别禁用 Orca，类似以上的操作，将 optimizer 参数设置为 off 即可。

在数据库级别开启 Orca

要在指定的数据库范围来设置启用 Orca，可以使用 ALTER DATABASE name SET

命令来实现。例如：

=# ALTER DATABASE test_db SET OPTIMIZER = ON ;

要在数据库级别禁用 Orca，将 optimizer 参数设置为 off 即可。例如：

=# ALTER DATABASE test_db SET OPTIMIZER = OFF ;

还可以恢复缺省设置，即，该级别不做设置，而是从系统级别继承。例如：

=# ALTER DATABASE test_db RESET OPTIMIZER;

在 ROLE 级别开启 Orca

要在指定的 ROLE 来设置启用 Orca，可以使用 ALTER ROLE name SET 命令来

实现。例如：

=# ALTER ROLE user1 SET OPTIMIZER = ON ;

Greenplum Database 管理员指南 V6.2.1

要在 ROLE 级别禁用 Orca，将 optimizer 参数设置为 off 即可。例如：

=# ALTER ROLE user1 SET OPTIMIZER = OFF ;

还可以恢复缺省设置，即，该级别不做设置，而是从更高的别继承。例如：

=# ALTER ROLE user1 RESET OPTIMIZER;

另外，在 6 版本中，还可以在 ROLE 级别针对特定的数据库进行参数设置，这些信

息存储在 pg_db_role_setting 系统表中。例如：

=# ALTER ROLE user1 IN DATABASE test_db SET OPTIMIZER TO OFF;

在会话级别或者语句级别启用 Orca

除了之前的多个级别可以设置参数外，和很多其他参数一样，可以在会话和语句级

别进行设置，通过 SET 命令来完成，可以在一组 SQL 语句的任何一个语句之前进行设

置，SET 设置之后，将影响后续的语句。例如：

=# SET OPTIMIZER TO ON;

收集 ROOT 分区的统计信息

对于分区表，Orca 必须使用 ROOT 分区的统计信息来生成执行计划，这些信息非

常重要，这将决定了 Orca 会如何评估每个算子的代价(Cost)，选择 Join 的顺序，

如何执行 AGG 操作。但对于 PostgreSQL 优化器而言，使用的是叶子分区的统计信息。

如果使用 Orca 来查询分区表，ROOT 分区上需要有统计信息，并且这些信息需要

保持更新，这样 Orca 才能生成更准确的执行计划。如果 ROOT 分区的统计信息过于陈

旧或者缺失，ORCA 仍然会选择动态分区评估，但可能会生成很差的执行计划。这不等

于说必须定期在 ROOT 分区上收集统计信息，因为这个代价往往会很大，后面会介绍关

于叶子分区统计信息上透的内容。

Greenplum Database 管理员指南 V6.2.1

执行 ANALYZE 命令

缺省情况下，直接在ROOT分区上执行ANALYZE命令，会自动为所有叶子分区收集

统计信息，也会为ROOT分区收集统计信息，同时还会收集叶子分区的频度统计信息

(HyperLogLog)，个人理解，是一种可增量更新的统计信息，后面会提到有关叶子分

区的统计信息，自动上透到ROOT分区。ANALYZE ROOTPARTITION命令将只收集ROOT

分区的统计信息。通过参数optimizer_analyze_root_partition可以控制是否

需要ROOTPARTITION关键字来收集ROOT分区的统计信息，不过，这个情况，从编者

的测试来看，不同的版本表现的情况不同，optimizer_analyze_root_partition

参数和gp_statistics_pullup_from_child_partition参数关闭之后，在6版

本中没有使用关键字ROOTPARTITION来ANALYZE ROOT分区，同样会更新ROOT分区

的统计信息，而在5版本，关闭这两个参数之后，的确不会更新ROOT分区的统计信息，

在明确使用了ROOTPARTITION关键字之后，会更新ROOT分区的统计信息。不过编者

认为，这里不需要过多的考虑，使用缺省的配置即可，这样，叶子分区的统计信息更新

会自动上透到ROOT分区，如果这时Orca生成的执行计划有问题，再尝试收集ROOT分

区的统计信息或关闭Orca。

ANALYZE 在收集 ROOT 分区统计信息时会扫描全部叶子分区的数据，如果该分区

表非常大，这将是一个极其耗时的操作。ANALYZE 需要 ShareUpdateExclusive 锁，

与很多操作是冲突的，例如 TRUNCATE 和 VACUUM，和 ANALYZE 本身也是冲突的，所

以，应该合理安排 ANALYZE 的时机，例如在业务空闲时期，最佳的选择是在业务处理

过程中，在数据发生大规模修改之后及时进行 ANALYZE 操作。

以下是一个关于在分区表上执行 ANALYZE 和 ANALYZE ROOTPARTITION 的最佳实践：

 一个分区表在完成数据初始化后执行ANALYZE <root_partition>命令。一个

新的叶子分区在完成数据初始化后，或者发生大规模数据变化后，执行ANALYZE

<leaf_partition>命令。缺省情况下，在叶子分区收集统计信息，会自动上透

到ROOT分区。

 在长期没有更新ROOT分区的统计信息之后，如果在执行计划中发现执行计划变差

了，或者叶子分区有大规模的数据变化之后，可能自动上透的统计信息会有失准，

此时应该更新ROOT分区的统计信息。例如，在上一次收集ROOT分区统计信息之后，

增加了很多叶子分区，导致了大规模的数据变化，此时应该考虑执行ANALYZE 或

者ANALYZE ROOTPARTITION命令来更新ROOT分区的统计信息。

 对于超级大表，应该把ANALYZE或者ANALYZE ROOTPARTITION的间隔设置很长，

例如每次间隔一周或者更久。所有表的统计信息收集间隔都取决于该表中的数据变

化比例和频繁程度。

 避免执行ANALYZE命令而不带任何参数，因为这样会对数据库中的所有表收集统

计信息，包括所有分区，对于一个大规模的数据库，这种操作的耗时是不可控的。

Greenplum Database 管理员指南 V6.2.1

 如果IO资源充足，可以并行执行ANALYZE <table_name>或者ANALYZE

ROOTPARTITION <table_name>命令来加速收集统计信息的速度。

 可以使用GP自带的analyzedb命令来更新表的统计信息，该命令会自动判断一张

表从上次收集了统计信息之后到目前为止是否发生了变化，如果没有发生变化，就

不用重新收集统计信息，这个自动判断的粒度可以精确到每个叶子分区。

Orca 和叶子分区的统计信息

对于在分区表上使用Orca优化器来说，维护ROOT分区的统计信息非常重要，这将

直接决定了Orca生成的执行计划的优劣，不过，维护叶子分区的统计信息同样很重要。

有些查询可能无法适用Orca优化器，此时就会使用PostgreSQL优化器，而

PostgreSQL优化器是必须使用叶子分区的统计信息来生成执行计划的。对于直接查询

叶子分区的查询，Orca也需要使用叶子分区的统计信息来生成执行计划。例如，当明

确知道需要查询的数据在特定的叶子分区时，可以直接查询该叶子分区表，这种情况下，

Orca也需要使用该叶子分区的统计信息。

禁用自动收集 ROOT 分区统计信息

如果不打算使用Orca来查询分区表(设置参数optimizer为off)，则可以禁用

ROOT分区的统计信息自动收集，通过optimizer_analyze_root_partition参数

来控制是否需要明确的指定ROOTPARTITION关键字来收集ROOT分区的统计信息。缺

省值为ON，ANALYZE一个分区表的ROOT分区时，不需要指定ROOTPARTITION关键字，

会自动收集ROOT分区的统计信息，通过设置该参数为OFF来关闭这种自动收集。如果

关闭了这个参数，则需要指定ROOTPARTITION关键字才能收集ROOT分区的统计信息。

不过，正如编者在前面提到的，这个情况还是以实际的测试为准，至少目前测试下来，

5版本的表现符合这个表述，6版本的表现不符合这个描述。

1、使用 gpadmin 用户登录 Master Host 主机。

2、使用 gpconfig 命令来设置 ptimizer_analyze_root_partition 参数：

$ gpconfig -c ptimizer_analyze_root_partition -v off --masteronly

编者认为，是否需要指定--masteronly 选项，并不重要。

Greenplum Database 管理员指南 V6.2.1

3、重新加载 postgresql.conf 配置文件，使修改生效，此处不是真正停止数据库

的操作，这类似 PostgreSQL 的 pg_ctl reload 操作。

$ gpstop -u

使用 Orca 的注意事项

要用好Orca，需要注意以下事项：

 分区表中不能有多字段分区键。

 多级分区表必须是规整的(每个分区的子分区结构完全相同，例如第一个分区有3

个子分区，第二个分区有4个子分区，这种就不是规整的多级分区表)。

 需要确保optimizer_enable_master_only_queries参数设置为ON，才能在

查询只在Master上存在的系统表时使用Orca优化器。实际上，并不需要启用该参

数，因为带来的坏处比好处更大。

 ROOT分区上已经收集了统计信息。

注意：打开 optimizer_enable_master_only_queries 参数会降低针对系统表

的短查询性能，请只在必要时在会话中打开。

如果一个分区表包含了超过 20000 个分区，应该考虑重新规划表的设计。编者认

为超过 500 个分区就已经很不正常了。分区粒度要适中，不能太随意，否则后患无穷。

以下这些参数将会影响 Orca 如何生成执行计划：

 optimizer_cte_inlining_bound -- 编者没有理解这个参数的具体含义，

编者查找了github上的测试用例，对每个测试场景进行了测试，不管该参数设置

为多少，所有测试用例的执行计划是否走Orca和该参数的开关没有任何关系。

 optimizer_force_multistage_agg -- 强制Orca选择多阶段聚合，该参数

在5版本的缺省值为TRUE，在6版本的缺省值为FALSE。为FALSE时，由Orca根据

Cost评估，选择一阶段聚合或二阶段聚合。编者认为，三阶段聚合的适用面更广。

 optimizer_force_three_stage_scalar_dqa -- 强制Orca选择三阶段聚

合。该该参数缺省值为TRUE，建议不要修改。

Greenplum Database 管理员指南 V6.2.1



optimizer_join_order -- 设置Orca对Join的顺序调整的激进程度，有四

个可选级别，分别为query、greedy、exhaustive、exhaustive2，缺省的

级别为exhaustive，各个级别大概的含义分别是，不调整、尝试、努力、穷举。

通常，不建议降低该参数的级别，因为可能会导致很多查询无法找到最优的执行计

划。



optimizer_join_order_threshold -- Orca对Join尝试调整顺序的Join

数量的上限。缺省值为10，编者认为，Join的表的数量超过该参数设置的值之后，

Orca不会再尝试调整Join的顺序，这与一些人的理解有出入，有些人的理解是，

超过10张表Join时，Orca将会按照每组不超过10张表进行分组优化，编者的测

试显示，超过10张表的Join，生成执行计划的结果和耗时，与把该参数设置为1

是完全一致的。而对于PostgreSQL的类似参数join_collapse_limit，则更

符合分组优化的解释。Join的表数量过多时，的确无法穷举所有排序的可能性，

因为N张表Join顺序的穷举数量是N的阶乘，数量级增长过快，CPU无法处理。



optimizer_nestloop_factor -- 设置Orca评估Nestloop Join时的Cost

因子。缺省值为1024，该值越小，越容易选择Nestloop Join，例如在IOPS能

力非常好的SSD系统，可以缩小该参数提高选择索引关联的可能性。



optimizer_parallel_union -- 是否对UNION和UNION ALL执行并行扫描。

缺省值为OFF，当设置为ON时，Orca将可以针对UNION和UNION ALL的不同部分

执行并行扫描，任务会被拆分成更多的Slice，执行计划也会更复杂，有时未必会

带来性能的提升，建议不要随意修改。



optimizer_sort_factor -- 设置Orca选择排序时的成本因子，缺省值为1。

最小值为0，越大的值越可能避免走排序。



gp_enable_relsize_collection -- 设置当一张表没有统计信息时，Orca

和PostgreSQL优化器如何处理该表。缺省情况下，Orca使用一个默认值来(一般

为1条记录)作为没有统计信息的表的统计信息评估结果，如果该参数设置为ON，

Orca会通过该表的尺寸来评估统计信息。

对于一个分区表来说，Orca查询ROOT分区时，如果ROOT分区上没有统计信息，

Orca并不会去评估表的尺寸，因为ROOT表中没有数据，而是继续使用默认值。

PostgreSQL优化器会获取叶子分区的尺寸。这种情况下可能需要使用ANALYZE

ROOTPARTITION命令来收集ROOT分区的统计信息。

以下参数控制着Orca的日志信息的输出：

 optimizer_print_missing_stats -- 控制在Orca执行一个查询时，其中

的字段缺失统计信息时是否显示字段的信息。不过编者实测，目前的5版本和6版

本都不会有相关信息或者日志输出，而且，也的确不需要这个信息。

 optimizer_print_optimization_stats -- 控制Orca是否输出对执行计

Greenplum Database 管理员指南 V6.2.1

划进行优化的过程计量信息，包括一些资源的消耗和耗时等，主要用于跟踪Orca

的性能。缺省为FALSE。

使用 Orca 时还可以通过 optimizer_minidump 参数来控制生成 Orca 的

minidump 文件，该文件会存储在 Master 工作目录下的 minidumps 子目录中，该文

件不是一个平面文件，主要用于原厂支持人员分析诊断问题，例如：

Minidump_20200618_222043_11_33.mdp

该参数的缺省值为ONERROR，设置为ALWAYS则会每次生成minidump文件。该参

数可以在Session中设置生效。

使用 Orca 执行 EXPLAIN 或者 EXPLAIN ANALYZE 命令时，执行计划只会显示

分区消除的数量，并不会显示详细的分区信息，号称设置参数

gp_log_dynamic_partition_pruning 为 ON 就可以显示，经过编者实测目前的 5

版本和 6 版本，并不会显示这些信息。

Orca 特性与增强

Orca，号称是 GP 的下一代查询优化器，在某些查询和算子的场景中优势明显。

 针对分区表的查询。

 包含子查询的查询。

 包含CTE的查询。

 DML操作的增强。

在有些方面 Orca 也有改进：

 Join顺序的调整。

 关联聚合的顺序调整。

 Sort顺序的优化。

 对数据倾斜的评估。

Greenplum Database 管理员指南 V6.2.1

针对分区表的查询

Orca 对于分区表的查询有如下的增强：

 分区消除得到改善。

 可以支持规整的多级分区表(一般不存在多级分区表，所以无所谓是否规整)。

 执行计划可以包含一个算子作为动态分区消除的条件，对于常量分区条件，执行计

划会给出分区消除的数量信息，非常量分区条件则不会给出分区消除的数量信息。

 执行计划中不会枚举所有分区(动态分区扫描，整体作为一个算子)。

对于常量分区过滤条件，Orca 会在执行计划中列出分区消除的数量。例如：

-> Partition Selector for sales (dynamic scan id: 2) . . .

Partitions selected: 1 (out of 12)

-> Dynamic Table Scan on sales (dynamic scan id: 2) . . .

Filter: date = '2020-01-02'::date

对于分区过滤条件是一个算子的情况，具体的分区消除的数量只有在执行时才能知

道，所以，Orca 不会在执行计划中列出分区消除的数量，但在 EXPLAIN ANALYZE

的输出中会有体现。例如，下面是 EXPLAIN ANALYZE 的一部分：

-> Dynamic Table Scan on sales (dynamic scan id: 1) . . .

Rows out: Avg 105.0 rows x 2 workers

Partitions scanned: Avg 1.0 (out of 12) x 2 workers. Max 1 parts (seg0).

 执行计划的尺寸与分区数量无关。

 大大缓解了因为分区数量引起的内存不足的报错。

例如，使用 CREATE TABLE 命令创建了一张 RANGE 分区表：

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

);

Greenplum Database 管理员指南 V6.2.1

Orca 改进了该分区表的这些查询场景：

 全表扫描的查询，执行计划中不会枚举所有分区。

=# SELECT * FROM sales;

 使用常量作为分区过滤条件，执行计划中显示了分区消除的信息。

=# SELECT * FROM sales WHERE date = '2020-01-02';

 使用范围过滤条件，执行计划中显示了分区消除的信息。

=# SELECT * FROM sales WHERE date BETWEEN '2020-01-02' AND '2020-01-03';

 通过一个子查询来指定分区过滤条件，执行计划中，只显示动态分区消除，而不显

示分区消除的数量。

=# SELECT * FROM sales WHERE date =

(SELECT max(date+100) FROM sales WHERE date='2020-01-02');

包含子查询的查询

Orca 可以更好的处理子查询，子查询是嵌套在一个查询中的查询语句，例如在

WHERE 查询中的 SELECT 就是一个子查询。例如：

=# SELECT * FROM part

WHERE price > (SELECT avg(price) FROM part);

Orca 可以更好的处理关联子查询，关联子查询是，在子查询中引用了外部查询的

字段，例如下面这个查询，brand 字段在子查询中被引用：

=# SELECT * FROM part p1

WHERE price > (

SELECT avg(p2.price) FROM part p2

WHERE p2.brand = p1.brand

);

Orca 为以下类型的子查询生成更高效的执行计划：

Greenplum Database 管理员指南 V6.2.1

 查询的字段列表中包含关联子查询。

=# SELECT *,

(SELECT min(p2.price) FROM part p2 WHERE p1.brand = p2.brand) AS foo

FROM part p1;

 在OR条件中包含关联子查询。

=# SELECT FROM part p1 WHERE p1.p_size > 40 OR p1.p_retailprice > (

SELECT avg(p2.p_retailprice)

FROM part p2

WHERE p2.p_brand = p1.p_brand

);

 跨级关联的嵌套子查询(内层的子查询引用了更外层查询的字段 -- 跨级引用)。

=# SELECT * FROM part p1 WHERE p1.p_partkey IN (

SELECT p2.p_partkey FROM part p2

WHERE p2.p_retailprice = (

SELECT min(p3.p_retailprice) FROM part p3

WHERE p3.p_brand = p1.p_brand

)

);

注意：PostgreSQL优化器不支持这种越级关联的嵌套子查询。

 包含AGG和非等值关联的关联子查询。

=# SELECT * FROM part p1 WHERE p1.p_retailprice = (

SELECT min(p2.p_retailprice) FROM part p2

WHERE p2.p_brand <> p1.p_brand

);

 关联子查询中的子查询对于每个关联值只能返回单条记录。

=# SELECT p_partkey,

(SELECT p2.p_retailprice FROM part p2 WHERE p2.p_brand = p1.p_brand)

FROM part p1;

注意：如果子查询对于每个关联值返回多条记录，将会在运行时期间报错，SQL语法并

不会报错。

Greenplum Database 管理员指南 V6.2.1

包含 CTE 的查询

Orca 增强了对包含 WITH 子句的查询的支持。这种查询也被称为 CTE，WITH 中

的语句块可以类似临时表一样来使用。例如：

=# WITH v AS (SELECT a, sum(b) as s FROM T where c < 10 GROUP BY a)

SELECT *FROM v AS v1 , v AS v2

WHERE v1.a <> v2.a AND v1.s < v2.s;

Orca 还可以将条件下推到 CTE 中。例如：

=# WITH v AS (SELECT a, sum(b) as s FROM T GROUP BY a)

SELECT * FROM v as v1, v as v2, v as v3

WHERE v1.a < v2.a AND v1.s < v3.s

AND v1.a = 10 AND v2.a = 20 AND v3.a = 30;

Orca 还可以处理这些类型的 CTE：

 定义了多个AS的CTE。例如：

=# WITH cte1 AS (

SELECT a, sum(b) as s FROM T where c < 10 GROUP BY a

), cte2 AS (

SELECT a, s FROM cte1 where s > 1000

)

SELECT * FROM cte1 as v1, cte2 as v2, cte2 as v3

WHERE v1.a < v2.a AND v1.s < v3.s;

 嵌套CTE

=# WITH v AS (

WITH w AS (

SELECT a, b FROM t WHERE b < 5

)

SELECT w1.a, w2.b FROM w AS w1, w AS w2

WHERE w1.a = w2.a AND w1.a > 2

)

SELECT v1.a, v2.a, v2.b

FROM v as v1, v as v2

WHERE v1.a < v2.a;

Greenplum Database 管理员指南 V6.2.1

注意：编者提醒，这些 CTE 像不像小学生写的，切记不要在业务中写出一堆这种非等

值关联的查询，如果不懂数据库就不要乱给业务写 SQL，害人害己。

DML 操作的增强

Orca 还增强了 DML 操作，例如 INSERT、UPDATE 和 DELETE。

 在执行计划中，DML也是一个算子。

 作为一个常规的算子，可以出现在执行计划的任何地方(目前只在顶端)。

 可以有后续算子(这与上一条的注释矛盾啊，可能目前还不存在吧)。

 UPDATE操作通过Split算子来支持如下操作：

 在分布键字段执行UPDATE命令。

 在分区字段执行UPDATE命令。

例如，这是一个 Split 算子的例子：

Update

(cost=0.00..431.13 rows=1 width=1)

-> Result

(cost=0.00..431.00 rows=1 width=34)

-> Split

(cost=0.00..431.00 rows=1 width=30)

-> Result

(cost=0.00..431.00 rows=1 width=30)

-> Seq Scan on t

(cost=0.00..431.00 rows=1 width=26)

Optimizer: Pivotal Optimizer (GPORCA)

官方文档说，引入了一个新的算子Assert用于约束检查，编者就目前最新的5和6

版本测不出这种算子，而是一堆Result算子，编者认为，这一块可能已经改了。

Orca 带来的改变

跟 PostgreSQL 优化器相比，Orca 优化器带来了一些新的变化(有些是依然没有支持)。

 允许在分布键字段上执行UPDATE命令(在6版本，PostgreSQL优化器也已经支持

了，在6版本之前，只有Orca优化器可以支持)。

Greenplum Database 管理员指南 V6.2.1

 允许在分区字段上执行UPDATE命令。

 支持对规整(每个分区的子分区结构完全相同)的多级分区表的查询。

 分区表的叶子分区有外部表的，Orca会自动切换成PostgreSQL优化器。

 除了INSERT命令，不允许直接对分区表进行DML操作。

可以直接对叶子分区执行 INSERT 操作，仍需要满足分区条件的约束，非叶子分

区不允许执行 INSERT 操作。

 ROOT分区需要统计信息来帮助Orca生成正确的执行计划。

 在执行计划中引入了新的算子：

 Partition selector算子 -- 支持动态分区扫描。

 Split算子 -- 支持对分布键字段和分区字段的UPDATE。

 使用Orca生成的执行计划与PostgreSQL优化器不同(最后一行会标识)。

的确带来了很多改变，有时不知道该用 Orca 优化器，还是用 PostgreSQL 优化

器，二者皆有长短，Orca 可以在一些场景有提升，总归是一件好事吧。

Orca 的限制

Orca 不是万能的，目前，GP 数据库中，Orca 优化器和 PostgreSQL 优化器并

存，因为，Orca 在一些场景有优势，但是又不能完全搞定所有问题，所以，只能共存，

编者也不知道 Orca 的未来在何处。

 不支持的SQL类型。

 性能差异。

不支持的 SQL 类型

Orca 不支持的 SQL 类型：

Greenplum Database 管理员指南 V6.2.1

 参数化的Prepared statement(用Java连过数据库的都清楚这是啥)。

 表达式索引(Orca不会为此选择索引扫描，也不切换到PostgreSQL优化器)。

 SP-GiST索引。Orca支持的索引类型为：B-tree、bitmap、GIN和GiST。

对于不支持的索引，Orca是当作其不存在，例如上一条。

 不规整的多级分区表(每个分区的子分区结构不完全相同)。

 叶子分区有外部表的分区表。

 表名前带有ONLY关键字的SELECT、UPDATE和DELETE操作。

注意：在官方文档中提到的一些其他的限制，编者觉得微不足道或者没能测试验证的，

此处不做列举。

性能差异

一些 Orca 和 PostgreSQL 优化器已知的性能差异：

 Orca的短查询性能不好，因为Orca生成执行计划的开销比起PostgreSQL优化器

来说，大很多，虽然在5.20左右的版本有了较大提升，但相比于后者还是有极大

的差异，虽然这些差异对于复杂计算(例如耗时在分钟级别或以上)不算什么，但

对于原本只需要几十毫秒的短查询来说，不可忽略。

 ANALYZE -- 对于Orca，针对ROOT分区的ANALYZE会收集ROOT分区的统计信

息，而PostgreSQL优化器不会，因为其不需要这个统计信息。

 DML操作 -- Orca增强了对分布键字段和分区字段的更新支持，但也需要更多的

前置(overhead姑且就是这个含义吧)开销。

注意：Orca 生成执行计划的耗时，的确比 PostgreSQL 优化器多很多，这个是一个

已知的问题。

验证查询是否使用了 Orca

Greenplum Database 管理员指南 V6.2.1

可以通过检查 EXPLAIN 的输出来验证，当执行一个 GP 的查询时，是否使用了 Orca

优化器。

例如在 Orca 开启的情况下，下面的例子表示，查询使用了 Orca 优化器：

Settings: optimizer=on

Optimizer status: Pivotal Optimizer (GPORCA) version . . .

例如下面的例子表明，在 Orca 开启的情况下，自动切换到了 PostgreSQL 优化器：

Settings: optimizer=on

Optimizer status: Postgres query optimizer

例如下面的例子表明，在 Orca 关闭的情况下，使用了 PostgreSQL 优化器：

Settings: optimizer=off

Optimizer status: Postgres query optimizer

注意：关于 Orca，编者就讲到这里，想了解更多知识，请参考官方文档。

定义查询

查询是一个查看、修改或者分析数据库中数据的命令。本节介绍如何在GP中构造

SQL查询。

 SQL修辞

 SQL值表达式

SQL 修辞

SQL(结构化查询语言)是用来访问数据库的一种语言。SQL语言有特定的修辞和词

法(单词、特征等)，据此构造数据库引擎可以理解的查询或命令。

SQL由一系列的命令组成。命令由一系列按照语法规范编写的修辞组成，以分号(;)

结尾。

GP基于PostgreSQL，并遵循相同的SQL结构和语法(一些MPP相关的有差异)。大

多情况下，GP的语法与PostgreSQL对等，不过在GP中有些命令可能会有增量或者语

法限制(由MPP的特性决定的)。

Greenplum Database (V6.2.1) - 4