Greenplum Database (V6.2.1)

Greenplum Database 管理员指南 V6.2.1

关于PostgreSQL中SQL规则和概念的完整解释，参考相关PostgreSQL的相关文

档。

SQL 值表达式

SQL值表达式，由数值、符号、运算符、SQL函数和数据组成。使用表达式来做数

据的比较，或执行运算。运算包括逻辑运算、算术运算和集合运算等。

以下这些都是值表达式(这一块编者也不能逐个确定示例，姑且先这样)：



聚合表达式



数组构造函数



字段的引用



常量或字符串



关联子查询



字段选择表达式



函数调用



插入或更新字段的新值



涉及字段引用的运算符



位置参数引用 -- 例如在函数定义中或者Prepared statement中



行构造函数



标量子查询



WHERE子句中的查询条件



SELECT命令的字段列表



类型转换



括号中的值表达式 -- 用于分组子表达式或设置优先级

Greenplum Database 管理员指南 V6.2.1

 开窗表达式

还有一些SQL结构，例如函数和运算符，也是表达式，但其遵循一般的语法规则。

可参考"使用函数和运算符"章节。

字段的引用

引用字段的格式为：

correlation.columnname（对象名.字段名）

对象名可以是一张表(有时还需要模式名)，或视图，或者一个FROM子句中的对象

的别名，或者在RULE中的NEW、OLD关键字，用于表示对新数据、旧数据的引用。当字

段名在当前查询的所有表中是唯一的，"对象名."这部分可以省略，因为不会有歧义。

位置参数

位置参数指的是SQL语句或者函数的参数，通过参数的位置来引用参数。例如，

$1指的是第一个参数，$2是第二个参数，$3是第三个参数，以此类推。这些参数的值，

是在SQL之外设置的(例如Prepared statement对象的一系列setValue的操作)，

或者在调用函数时指定的。例如在sql语言的function中，参数的值是在调用函数时

指定，此时，位置参数指向的函数定义之外(调用时指定)的值，参数引用的格式为：

$number

例如：

=# CREATE FUNCTION dept(text) RETURNS dept

AS $$

SELECT * FROM dept WHERE name = $1

$$ LANGUAGE sql;

这里，在函数被调用时$1就是函数的第一个参数的值。

Greenplum Database 管理员指南 V6.2.1

下标表达式

在访问数组类型数据的特定位置的元素时，可以使用这样格式的表达式：

expression[subscript]

还可以直接获取数组中的连续元素子集。例如：

expression[lower_subscript:upper_subscript]

每个下标，必须是一个结果为整数值的表达式，可以是一个常量，或者一个函数，

一个子查询，只要结果是一个整数即可。

通常，数组表达式必须在括号中，但是，当使用下标的表达式是字段的引用或位置

参数时，可以省略括号。当原始数组是多维数组时，可以连续使用多个下标。例如：

mytable.arraycolumn[4]

mytable.two_d_column[17][34]

$1[10:42]

(arrayfunction(a,b))[42]

--这里的括号不可以省略

字段选择表达式

当一个表达式产生的是复合类型(row type)，可以使用字段选择表达式来获取该

复合类型的特定属性(或者称为字段也可以)。例如：

expression.fieldname

通常，复合类型表达式必须被括起来，但是，当表达式是字段的引用或位置参数时，

可以省略括号。例如：

mytable.mycolumn

$1.somecolumn

Greenplum Database 管理员指南 V6.2.1

(rowfunction(a,b)).col3

当省略括号可能会带来歧义时，括号不能被省略，例如，一个带表名的字段引用：

(mytable.arraycolumn).somefield

因为此时如果省略了括号，将会和如下用法混淆：

mydatabase.mytable.mycoloumn

运算符调用

运算符调用有3中可能的语法：

expression operator expression(二目运算符)

operator expression(一目前置运算符)

expression operator(一目后置运算符)

这里的operator可以是一些符号(例如+、-、*、/、>、<等等)，还可以是AND、

OR、NOT等SQL关键字。例如：

OPERATOR(datatype,datatype)

存在哪些特定的运算符以及他们的目数，取决于系统预定义的情况或用户的定义。

函数调用

调用函数的语法是函数名(有时还需要模式名)，后面跟着相关的参数，这些参数

用括号括起来：

function ([expression [, expression ... ]])

例如，下面是调用求平方根的函数来计算2的平方根：

Greenplum Database 管理员指南 V6.2.1

sqrt(2)

聚合表达式

聚合表达式，对查询多行记录应用聚合函数，并得到一个结果，例如，求和、求平

均值。聚合表达式的语法如下：

aggregate_name(expression [ , ... ] ) [ FILTER ( WHERE filter_clause ) ]

对所有输入的非空记录(NOT NULL)进行聚合运算。

aggregate_name(ALL expression [ , ... ] ) [ FILTER ( WHERE filter_clause ) ]

与第一种等价，因为第一种就是ALL的缺省情况。

aggregate_name(DISTINCT expression [ , ... ] ) [ FILTER ( WHERE

filter_clause ) ]

对所有输入的记录的唯一值(且NOT NULL)进行聚集运算。

aggregate_name(*) [ FILTER ( WHERE filter_clause ) ]

对所有输入的记录(NULL也包含在内)进行聚合运算。通常用于count(*)函数。

其中aggregate_name是一个预先定义好的聚合函数，expression是一个除聚

合表达式以外的任意的值表达式，也就是说，聚合函数表不可以嵌套调用。

这里的FILTER子句的作用是，为聚合函数指定特定的条件以过滤输入的记录，只

有满足FILTER子句中WHERE条件的记录才会被作为聚合函数的的输入。例如：

=# SELECT count(*) AS unfiltered,count(*) FILTER (WHERE i < 5) AS filtered

FROM generate_series(1,10) AS s(i);

GP数据库提供了中值函数MEDIAN，该函数返回排序在50%位置点的数值，还提供

了百分比插值函数PERCENTILE_CONT和PERCENTILE_DISC，返回数据集中排序后指

Greenplum Database 管理员指南 V6.2.1

定百分比位置的线性插值的结果，MEDIAN和PERCENTILE_CONT返回的结果是线性插

值，PERCENTILE_DISC返回的结果是距离线性插值最近的输入值。例如：

=# SELECT MEDIAN(i),

PERCENTILE_CONT(0.22) WITHIN GROUP (ORDER BY i),

PERCENTILE_DISC(0.22) WITHIN GROUP (ORDER BY i)

FROM generate_series(0,15) AS i;

聚合表达式的限制

以下是一些聚合表达式的限制：

 一些高级的聚集函数不能和ALL、DISTINCT、FILTER或OVER等关键字一起使用，

这里需要澄清，不是GP不支持这些关键字。

 一些聚合表达式不能与分组规范一起使用：CUBE、ROLLUP和GROUPING SETS。

 聚合表达式只能作为结果字段或者在HAVING子句中出现。不能出现在其他位置

(例如WHERE条件中)，因为这些位置的条件过滤或者运算是在得到聚合结果之前。

 当一个聚合表达式出现在子查询中，常用于评估记录数量(例如count(*))或者求

极值(例如max、min等)等，在子查询中，如果该聚集函数的参数包含了外部的字段引

用，该子查询的聚合表达式的结果需要以一个常量的形式出现在外部相关的结果中。

 GP数据库不支持将聚合函数作为另一个聚合函数的参数(不可以嵌套调用)。

 GP数据库不支持将开窗函数作为聚合函数的参数。

开窗表达式

开窗函数的支持，使得应用开发人员，可以使用标准SQL命令，方便的构造复杂的

在线分析处理(OLAP)查询。例如，可以计算移动平均值，或者不同时间段总数，根据

Greenplum Database 管理员指南 V6.2.1

不同的字段值进行分组聚合，得到聚合结果或者等级关系。

开窗表达式是在开窗分组(OVER()子句)的基础上执行开窗函数的计算。开窗分组

将一个数据集合分割为多个部分，开窗函数对每个分组进行独立处理，这与聚合函数和

GROUP BY的配合有相似之处，不同的是，聚集函数为每组记录返回一个结果，而开窗

函数为每条记录返回一个结果，这些计算只是在一个开窗分组内完成。如果没有指定开

窗分组条件，开窗函数会将全部数据作为一个开窗分组来进行计算。

GP数据库不支持将开窗函数作为另一个开窗函数的参数(不可以嵌套调用)。

开窗表达式的语法为：

window_function ( [expression [, ...]] ) [ FILTER ( WHERE filter_clause ) ]

OVER ( window_specification )

window_function是开窗函数，后续还会介绍，有哪些常见的开窗函数，还可以

是用户自定义的开窗函数，expression是一个值表达式，但不能是开窗表达式。其中

window_specification的语法格式为：

[window_name]

[PARTITION BY expression [, ...]]

[[ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST}]

[, ...]

[{RANGE | ROWS}

{ UNBOUNDED PRECEDING

| expression PRECEDING

| CURRENT ROW

| BETWEEN window_frame_bound AND window_frame_bound }]]

其中window_frame_bound可以是下面的一种格式：

UNBOUNDED PRECEDING

-- 第一行

expression PRECEDING

-- 前 expression 行

CURRENT ROW

-- 当前行

expression FOLLOWING

-- 后 expression 行

UNBOUNDED FOLLOWING

-- 最后一行

一个开窗表达式，只能出现在SELECT语句的结果字段列表中。例如：

=# SELECT count(*) OVER(PARTITION BY customer_id), * FROM sales;

如果要指定FILTER子句，则只有满足FILTER中WHERE条件的记录会被开窗函数

处理，其他不满足的记录不会被开窗函数处理(只是影响计算的结果，不影响SELECT

语句输出的记录数)。在开窗表达式中，FILTER子句只能与本身为聚合函数的开窗函

Greenplum Database 管理员指南 V6.2.1

数一起使用。例如：

=# SELECT count(*) filter(WHERE id < 10)

OVER(PARTITION BY customer_id), * FROM sales;

下面这种是不允许的：

=# SELECT rank() filter(WHERE id < 10)

OVER(PARTITION BY customer_id), * FROM sales;

在开窗表达式中，表达式必须包含OVER子句，通过OVER子句来指定开窗分组的方

式，这也是开窗表达式与普通的函数和聚合函数的显著区别。

开窗分组的规范如下：

 PARTITION BY子句，其决定了开窗函数通过该表达式对数据进行开窗分组。如

果没有指定PARTITION BY子句，将会把全部数据作为一个开窗分组。

 ORDER BY子句定义了在一个开窗分组如何对记录进行排序。值得注意的是，开窗

分组中的ORDER BY仅对开窗分组内的数据进行局部排序。对于计算Rank的开窗

函数来说需要有ORDER BY子句，不然Rank值就是随机排序的结果。对于OLAP聚

合来说，在使用ROWS或RANGE子句的开窗分组时，也要有ORDER BY子句，不然

开窗函数计算得到的也是随机排序的结果。

 ROWS/RANGE子句用于定义开窗分组内的动态分组。PARTITION BY子句定义了

数据如何进行开窗分组。当开窗分组的方式确定了以后，使用了ROWS/RANGE子

句的情况下，开窗函数将进行步进式的动态计算，而不是将整个开窗分组内的数据

进行整体计算。ROWS关键字对应基于行偏移的动态计算，RANGE关键字对应基于

值偏移的动态计算，ROWS偏移基于每一条记录进行动态计算，不关心ORDER BY

字段的值是否有差异，RANGE偏移基于ORDER BY字段的值来进行动态计算，相同

的值得到相同的结果，后面的例子中会有展示。

开窗表达式例子

下面的示例演示如何将开窗函数与开窗分组一起使用。

首先，为了这些例子，创建一张 empsalary 表用于演示：

=# CREATE TABLE empsalary (

depname varchar(10),

Greenplum Database 管理员指南 V6.2.1

empno int,

salary numeric(10,0)

) ;

INSERT INTO empsalary VALUES

('研发',1,20000),('研发',2,20000),('研发',3,20000),

('研发',4,30000),('研发',5,30000),('研发',6,30000),

('客户部',7,40000),('客户部',8,40000),

('客户部',9,50000),('客户部',10,50000);

例一，如何将开窗函数与开窗分组一同使用。

PARTITION BY 子句将相同值的字段进行分组。

该例是将员工的工资与部门的平均工资进行对比：

=# SELECT depname, empno, salary, avg(salary)

OVER(PARTITION BY depname) FROM empsalary;

前面 3 个字段来自 empsalary 表中，表中的每一条记录都有一行输出。第四列是

每个部门的平均工资，具有相同部门名称的记录为同一部门，一起算出平均值。例子中

有 2 个部门，avg 函数与一般的 avg 函数没有区别，但计算平均值的范围是由 OVER

子句中的 PARTITION BY 字段决定的。

还可以将开窗分组的定义放到 WINDOW 子句中，在开窗分组的 OVER 位置引用这个

开窗分组，下面的 SQL 与上面的 SQL 等价：

=# SELECT depname, empno, salary, avg(salary) OVER(mywindow)

FROM empsalary WINDOW mywindow AS (PARTITION BY depname);

当 SELECT 的字段列表中有多个相同的开窗分组方式时，这种引用的方式比较有用。

例二，带有 ORDER BY 子句的开窗分组。

Greenplum Database 管理员指南 V6.2.1

OVER 子句中的 ORDER BY 子句指定了开窗分组内的记录顺序，此处的 ORDER BY

与输出的顺序没有直接关系。此例使用 rank()函数来对员工的工资进行部门内的排序：

=# SELECT depname, empno, salary,

rank() OVER (PARTITION BY depname ORDER BY salary DESC)

FROM empsalary;

例三，基于记录偏移的动态开窗。

ROWS/RANGE 子句用于定义开窗分组内的动态分组 -- 开窗函数的输出基于开窗

分组内的一部分记录进行计算。例如，从分组开始的位置到当前行的所有行。

这个例子计算每个部门，从开始到当前员工的薪水总和：

=# SELECT depname, empno, salary,

sum(salary) OVER (PARTITION BY depname ORDER BY salary

ROWS between UNBOUNDED PRECEDING AND CURRENT ROW)

FROM empsalary ORDER BY depname, sum;

Greenplum Database 管理员指南 V6.2.1

例四，基于值偏移的动态开窗。

RANGE 根据 OVER 子句中 ORDER BY 表达式的值来决定动态开窗的情况。这个例

子演示了 RANGE 与 ROWS 之间的区别。这个例子与上面的例子类似，不同的的是，薪

水相同的员工算出的薪水总和是相同的，并且包含所有这些薪水相同的员工的薪水：

=# SELECT depname, empno, salary,

sum(salary) OVER (PARTITION BY depname ORDER BY salary

RANGE between UNBOUNDED PRECEDING AND CURRENT ROW)

FROM empsalary ORDER BY depname, sum;

类型转换

类型转换指的是，从一种数据类型转换为另外一种数据类型。类型转换是运行时进

行的。必须定义了合适的类型转换函数，才能进行对应的类型转换操作。对于字符串的

类型转换，是将其作为对应类型的字符表现进行转换的，如果转换合法，则可以成功。

GP数据库支持三种可用于值表达式的强制类型转换：

 显式的强制转换 -- 明确的指定两个数据类型之前的强制转换，有两个等效的语

法形式：

CAST ( expression AS type )

expression::type

 赋值转换 -- GP数据库可以在进行目标赋值时进行自动类型转换。在CREATE

CAST时，通过AS ASSIGNMENT子句来创建一个赋值转换。例如，tbl1.f1字段

是TEXT类型，则在执行如下INSERT时，INT类型将会自动转换为TEXT类型：

Greenplum Database 管理员指南 V6.2.1

=# INSERT INTO tbl1 (f1) VALUES (42);

 隐式转换 -- 根据表达式的上下文情况自动进行强制类型转换。在CREATE CAST

时，通过AS IMPLICIT子句来创建一个隐式转换。隐式转换会根据表达式的上下

文情况自动进行类型的转换。例如，tbl1.c1是int类型的字段，下面的SQL中，

c1会自动进行int到decimal类型的转换：

=# SELECT * FROM tbl1 WHERE tbl1.c2 = (4.3 + tbl1.c1);

对于自动转换不会出现歧义的情况，往往可以进行隐式转换，有些可能会造成歧义

的类型转换往往需要显式的转换，例如5版本开始，一些类型到TEXT类型的隐式转换被

废除了，这种时候往往需要显式的类型转换。例如，字段为TEXT类型时使用INT类型

的WHERE条件，将会收到如下的报错信息：

ERROR: operator does not exist: text = integer

可以通过psql的命令\dC来查看类型转换，类型转换的元数据信息存储在

pg_cast系统表中，类型信息存储在pg_type系统表中。

标量子查询

标量子查询是一个在括号中的普通SELECT查询，其返回单行单列的结果。该

SELECT查询被执行，其返回的标量值，将作为其他表达式的一部分。作为一个标量子

查询，不能使用返回多行或者多字段的子查询。如果标量子查询引用了子查询之外的字

段，该子查询被称为关联标量子查询。例如：

=# SELECT *,

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

FROM part p1;

关联子查询

关联子查询提供了一种使用其他查询结果来组织结果的方法。GP支持关联子查询，

其为很多已有的应用提供了兼容性。关联子查询是一个普通的SELECT查询，其WHERE

Greenplum Database 管理员指南 V6.2.1

子句或目标列表包含了外部子句的引用。关联子查询可以是标量子查询，也可以是非标

量子查询，区别在于其返回的是单个数值还是多条记录或多个字段。例如，这是一个跨

级非标量关联子查询：

=# 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

)

);

关联子查询例子

例1 -- 标量关联子查询

=# SELECT * FROM t1 WHERE t1.x > (SELECT MAX(t2.x) FROM t2 WHERE t2.y = t1.y);

例2 -- EXISTS在的关联子查询

=# SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.x = t1.x);

GP执行关联子查询有两种方式，如下：

1. 关联子查询可以被拆解为关联(JOIN)操作，这是高效的。大部分的关联子查询查

询属于这种，包括所有TPC-H基准测试的查询。

2. 外部查询的每条记录都执行一次关联子查询的查询语句，这是极其低效的。在

SELECT列表中或者使用OR连接的子句中出现的关联子查询属于这种。

接下来的例子将介绍如何改写这种SQL语句来提升性能。

例3 -- 在SELECT列表中的关联子查询

原始的SQL：

=# SELECT T1.a,

(SELECT COUNT(DISTINCT T2.z) FROM t2 WHERE t1.x = t2.y) dt2

FROM t1;

Greenplum Database 管理员指南 V6.2.1

实际上，Orca已经已经能够很好的优化该SQL，正确的将这种关联子查询转化为

HASH JOIN，已经很高效，而PostgreSQL优化器无法优化这种SQL，需要进行等价

的改写。

改写后的SQL：

=# SELECT t1.a, t2.count FROM t1 LEFT JOIN (

SELECT t2.y AS y, COUNT(DISTINCT t2.z) AS count

FROM t2 GROUP BY t2.y

) t2 ON (t1.x = t2.y);

例4 -- 带有OR子句的关联子查询

原始的SQL：

=# SELECT * FROM t1 WHERE

x > (SELECT COUNT(*) FROM t2 WHERE t1.x = t2.x)

x < (SELECT COUNT(*) FROM t3 WHERE t1.y = t3.y);

实际上，Orca已经已经能够很好的优化该SQL，正确的将这种关联子查询转化为

HASH JOIN，已经很高效，而PostgreSQL优化器无法优化这种SQL，需要进行等价

的改写。

改写后的SQL：

=# SELECT * FROM t1

WHERE x > (SELECT count(*) FROM t2 WHERE t1.x = t2.x)

UNION

SELECT * FROM t1

WHERE x < (SELECT count(*) FROM t3 WHERE t1.y = t3.y);

要检查对比改写前后的SQL，优化器生成的执行计划是如何处理这些关联子查询的，

可以通过EXPLAIN命令查看执行计划来确认。例如例4中的SQL，关闭Orca后，原始的

SQL的执行计划为：

Greenplum Database 管理员指南 V6.2.1

改写后的执行计划执行计划为：

开启Orca时，原始SQL的执行计划为：

显然，对于Orca来说，原始的SQL生成的执行计划已经够优化，无需进行改写。

数组构造函数

数据构造函数，通过数据元素的值，构造出一个数组。简单的来说，数组构造函数

由一个ARRAY关键字，一个左中括号([)，一串由逗号分隔的值表达式，一个右中括号

(])组成。例如：

=# SELECT ARRAY[1,2,3+4];

array

---------

{1,2,7}

(1 row)

数据元素的类型是成员表达式的类型，其与UNION或CASE结构使用相同的规则。

多维数据可以通过嵌套数据构造函数的方式构造。在构造函数内部，ARRAY关键字

可以被省略。例如，这两个SELECT语句是等价的：

Greenplum Database 管理员指南 V6.2.1

=# SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];

array

---------------

{{1,2},{3,4}}

(1 row)

=# SELECT ARRAY[[1,2],[3,4]];

array

---------------

{{1,2},{3,4}}

(1 row)

多维数据必须是规整的，内部同一层级上的构造函数必须生成完全相同维度的子数组。

多维数据构造函数，还可以适用于各种能正确的产生多维数据的用法。例如

=# WITH arr AS(

SELECT ARRAY[[1,2],[3,4]] AS f1, ARRAY[[5,6],[7,8]] AS f2

)

SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;

array

------------------------------------------------

{{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}

(1 row)

还可以将一个子查询的结果构造成数组。这种情况下，数组构造函数被写为关键字

ARRAY和一个括号括起来的子查询。例如：

=# SELECT ARRAY(SELECT generate_series(1,10));

array

------------------------

{1,2,3,4,5,6,7,8,9,10}

(1 row)

子查询必须返回单字段的结果集(多字段可以使用ROW函数构造成复合类型)。生成

的单维数组将子查询得到的每行作为一个元素，元素的类型与子查询输出的字段类型一

致。数组的下标值始终从1开始。

行构造函数

行构造函数，使用字段的值，构造出一个ROW对象。例如：

Greenplum Database 管理员指南 V6.2.1

=# SELECT ROW(1,2.5,'this is a test');

行构造函数可以包含rowvalue.*语法，就像在SELECT中使用.*那样，.*会被

自动展开。假如t表包含f1和f2两个字段，下面两句是等价的：

=# SELECT ROW(t.*, 42) FROM t;

=# SELECT ROW(t.f1, t.f2, 42) FROM t;

缺省情况下，使用ROW表达式构造匿名record类型。如果有必要，其可以转换为

命名的复合类型 -- 或者一张表的行类型，或者一个通过CREATE TYPE AS创建的复

合类型。有时为避免歧义可以进行明确的类型转换。例如：

=# CREATE TABLE mytable(f1 int, f2 float, f3 text);

=# CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1'

LANGUAGE SQL;

在下面的查询中，不需要强制转换，因为只有一个getf1()函数，没有歧义：

=# SELECT getf1(ROW(1,2.5,'this is a test'));

getf1

-------

=# CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);

=# CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT

$1.f1' LANGUAGE SQL;

现在需要强制转换以明确调用哪个函数：

=# SELECT getf1(ROW(1,2.5,'this is a test'));

ERROR: function getf1(record) is not unique

=# SELECT getf1(ROW(1,2.5,'this is a test')::mytable);

getf1

-------

=# SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));

getf1

-------

在表中有符合类型时，行构造函数，可用于构造这种符合类型的字段值。或者，在

调用符合类型参数的函数时，为函数构造参数。

Greenplum Database 管理员指南 V6.2.1

表达式评估规则

表达式的评估顺序没有定义。通常，一个运算符或者函数的输入参数不能保证按照

固定的顺序从左到右进行评估。此外，如果一个表达式的结果可以通过只评估其一部分

来确定，其它部分的表达式可能完全没必要被评估。例如，一种写法为：

=# SELECT true OR somefunc();

函数somefunc()可能根本不会被调用。另一种写法仍然不能保证somefunc()

函数一定会被调用：

=# SELECT somefunc() OR true;

注意：这与一些程序语言布尔操作的从左到右"固定执行顺序"是不同的，在GP中这种

表达式会被优化器优化掉。

不要在复杂的表达式中利用函数的副作用，更不要在WHERE和HAVING子句中利用

函数的副作用，因为在生成执行计划时，这些表达式可能会被优化掉，或者被重新组织

顺序或者逻辑结构。

如果一定要强制评估的顺序，可以选择CASE结构。例如，这样在WHERE子句中避

免被0除是靠不住的：

=# SELECT . . . WHERE x <> 0 AND y/x > 1.5;

但这样做是安全的：

=# SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

优化器不会对CASE结构进行重新组织，因此请在必要的时候这么做。

WITH 语句(CTE)

WITH 子句，为复杂的 SELECT 和修改数据的操作，提供了不同的表达方式，除了

常用于 SELECT 语句外，还可以用于 INSERT、UPDATE 和 DELETE 语句。

使用 WITH 子句的限制：

Greenplum Database 管理员指南 V6.2.1

 包含WITH子句的SELECT命令，最多只能包含一个修改表数据(INSERT、UPDATE

或DELETE)的WITH子句。

 包含WITH子句的数据修改命令(INSERT、UPDATE或DELETE)，WITH子句中只能

是SELECT命令，不能在WITH子句中再包含数据修改的命令。

缺省情况下，WITH 子句的 RECURSIVE 关键字是可用的，其是否可用，通过参数

gp_recursive_cte 来控制。

WITH 子句中使用 SELECT 命令

WITH 子句通常被称为 CTE(中文可以叫通用表表达式)，CTE 的效果和创建一张临

时表是很像的，虽然在执行查询时，并不会在数据库中真的创建一张临时表。下面的示

例显示了在执行 SELECT 命令时使用了 WITH 子句，这些示例中的 WITH 子句同样可以

与 INSERT、UPDATE 和 DELETE 一起使用，因为，在命令主体看来，这个 WITH 就相

当于一个普通的子查询。

在每次执行整个 SQL 时，WITH 子句中的 SELECT 命令只会被执行一次，如果在

WITH 之外的语句中多次引用了 WITH 子查询，也只执行一次。因此，从这个角度来说，

WITH 子句与临时表很像，对于代价较大的需要多次引用的子查询，可以使用 WITH 来

避免重复的计算。为了避免有副作用的功能被多次执行，也可以使用 WITH 子句来实现。

优化器不会将 WITH 中的查询拆解然后与外部的查询进行整体优化，优化器可以将外部

的条件下推到 WITH 子句中(这一点，官方文档的表述有误)，

一个常见的用途是将复杂的查询分解为多个简单的部分。例如：

=# WITH regional_sales AS (

SELECT region, SUM(amount) AS total_sales

FROM orders GROUP BY region

), top_regions AS (

SELECT region

FROM regional_sales

WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)

)

SELECT region, product, SUM(quantity) AS product_units,

SUM(amount) AS product_sales

FROM orders

WHERE region IN (SELECT region FROM top_regions)

GROUP BY region, product;

这个查询也可以不使用 WITH 子句，但需要使用二级嵌套子查询，而使用 WITH 子

Greenplum Database 管理员指南 V6.2.1

句，则可以把 SQL 的逻辑结构简化很多。

启用了 RECURSIVE 关键字之后，WITH 子句就可以完成普通 SQL 无法完成的递归

查询，这时，WITH 子句中的查询可以引用 WITH 子句自己的输出。下面的这个例子，

是计算从 1 到 100 的整数求和：

=# WITH RECURSIVE t(n) AS (

VALUES (1)

UNION ALL

SELECT n + 1 FROM t WHERE n < 100

)

SELECT sum(n) FROM t;

递归 WITH 子句，一般由一个非递归项，后面 UNON 或者 UNION ALL 一个递归项

组成，只有递归项才包含 WITH 子句本身的引用。

non_recursive_term UNION [ ALL ] recursive_term

包含 UNION 或者 UNION ALL 的递归 WITH 查询按照如下方式执行：

1、计算非递归项。对于 UNION（但不是 UNION ALL），去除重复的记录，把递归查

询中其余的记录，存储在一个临时工作表中。

2、重复如下步骤，直到这个临时的工作表被清空为止：

a、计算递归项，使用临时工作表中的数据来进一步填充递归自引用，意思是，

根据递归关联条件，把临时工作表中与递归自引用能关联(递归项 WHERE 条

件)上的记录进一步填充到递归自引用中，对于 UNION（但不是 UNION ALL），

放弃重复的记录(包括与之前的任意记录重复)，把剩余的记录(没有关联上的)

存储到到一个临时中间表中。

b、清空临时工作表，将临时中间表的记录插入临时工作表，清空临时中间表。

这是一个迭代的过程，不过，RECURSIVE 这个关键字是 SQL 标准定义的。

递归查询通常用于处理多层次或者有树状关系的数据。例如，此查询，用于查找产

品的所有直接和间接组成部分，表中的数据只有直接的包含关系：

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (

SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'

UNION ALL

SELECT p.sub_part, p.part, p.quantity

FROM included_parts pr, parts p

WHERE p.part = pr.sub_part

Greenplum Database 管理员指南 V6.2.1

)

SELECT sub_part, SUM(quantity) as total_quantity

FROM included_parts

GROUP BY sub_part;

这个 SQL 对应的表结构应该是这样的，记录的父子关系由父一级的记录来存储，

而且可能会有多条记录，因为其不只是存储父子关系，还需要存储计件数量。

在使用递归查询时，必须确保查询的递归部分最终一定会有找不到记录的情况，否

则查询将无限循环。在前面计算整数求和的例子中，工作表在每个步骤中包含一行记录，

并且在连续的步骤中包含的值从 1 到 100。在第 100 步中，由于 WHERE 子句而没有输

出，查询结束。

对于某些查询，使用 UNION 而不是 UNION ALL 可以确保查询的递归部分最终不

会无限循环，方法是丢弃与先前输出行有重复的记录。但是，有时候，决定重复关系的

只是一个或几个字段，而不是全部的输出字段。通过检查相关的字段，可以确定是否达

到了相同的点，标准的方法是通过数组来记录访问的路径，在递归时进行检验。例如下

面的查询，搜索图表的路径：

WITH RECURSIVE search_graph(id, link, data, depth) AS (

SELECT g.id, g.link, g.data, 1

FROM graph g

UNION ALL

SELECT g.id, g.link, g.data, sg.depth + 1

FROM graph g, search_graph sg

WHERE g.id = sg.link

)

SELECT * FROM search_graph;

如果存在环状关系，此查询将无限循环下去，因为查询需要输出 depth 属性，

depth 属性随着循环不断增长，此时修改 UNION ALL 为 UNION 也无法解决无限循环

问题。所以，需要识别递归是否到达了同一条记录。修改 SQL，加入 path 和 cycle

以帮助检查是否到达了同一条记录：

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (

SELECT g.id, g.link, g.data, 1,

ARRAY[g.id],false

FROM graph g

UNION ALL

SELECT g.id, g.link, g.data, sg.depth + 1,

path || g.id,g.id = ANY(path)

FROM graph g, search_graph sg

WHERE g.id = sg.link AND NOT cycle

)

Greenplum Database 管理员指南 V6.2.1

SELECT * FROM search_graph;

除了检测环状关系外，path 数组的值还显示了关联关系的路径信息。

在需要检查多个字段以识别环状关系时，可以使用 record 数组。例如，如果需

要比较字段 f1 和 f2：

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (

SELECT g.id, g.link, g.data, 1,

ARRAY[ROW(g.f1, g.f2)],false

FROM graph g

UNION ALL

SELECT g.id, g.link, g.data, sg.depth + 1,

path || ROW(g.f1, g.f2),ROW(g.f1, g.f2) = ANY(path)

FROM graph g, search_graph sg

WHERE g.id = sg.link AND NOT cycle

)

SELECT * FROM search_graph;

在不确定查询是否存在无限循环时，一种有效的测试方法是在主体查询中设置一个

限制条件。例如，此查询如果不使用 LIMIT 子句，将会无限循环：

WITH RECURSIVE t(n) AS (

SELECT 1

UNION ALL

SELECT n+1 FROM t

)

SELECT n FROM t LIMIT 100;

该方法之所以奏效，是因为 WITH 只计算了外部查询实际需要的记录数。但应该注

意，这个方法不是任何时候都奏效的，例如先排序再取 LIMIT 的情况，或者递归查询

的结果还需要与其他表进行关联，这种方式就失效了。

WITH 子句中使用数据修改命令

在 SELECT 命令的 WITH 子句中可以使用修改数据的命令：INSERT、UPDATE 或

者 DELETE，从而实现在一个查询中执行不同类型的操作。

WITH 子句中的数据修改命令只执行一次，但也不是一定会被执行的，如果主查询

压根就没有引用到 WITH 子句，该 WITH 子句可能会被优化掉，从而得不到执行。

Greenplum Database 管理员指南 V6.2.1

下面这个 CTE 查询，在 WITH 子句中，从 products 表中删除了一些记录，并通

过 RETURNING 子句返回被删除的记录。

WITH deleted_rows AS (

DELETE FROM products

WHERE

"date" >= '2010-10-01' AND

"date" < '2010-11-01'

RETURNING *

)

SELECT * FROM deleted_rows;

WITH 子句中修改数据的命令，必须有一个 RETURNING 子句，RETURNING 子句

返回的是被修改的记录。如果 WITH 子句中有数据修改的命令，则必须有 RETURNING

子句，否则会报错。

启用了 RECURSIVE 关键字的情况下，不允许在数据修改语句中进行递归操作。

不过，可以通过访问递归 WITH 的输出来完成相似的的效果。例如，此查询将删除产品

的所有直接和间接组成部分。

WITH RECURSIVE included_parts(sub_part, part) AS (

SELECT sub_part, part FROM parts WHERE part = 'our_product'

UNION ALL

SELECT p.sub_part, p.part

FROM included_parts pr, parts p

WHERE p.part = pr.sub_part

)

DELETE FROM parts WHERE part IN (SELECT part FROM included_parts);

WITH 子句中的语句与主查询同时执行。因此，在 WITH 中使用数据修改语句时，

该语句实际上是在快照上执行。语句的修改效果在目标表上并不可见。RETURNING 子

句是在 WITH 子句和主查询之间传递修改后数据的唯一方式。在下面的例子中，外部直

接在 products 表上的 SELECT 在 WITH 子句中的 UPDATE 操作之前返回了原始

price 值。查询结果中，将会出现原始 price 值和被修改后的 price 值。这部分的

内容，经编者测试，与官方文档的介绍完全不一致。

WITH t AS (

UPDATE products SET price = price * 1.05

RETURNING *

)

SELECT * FROM t

UNION ALL

SELECT * FROM products;

Greenplum Database 管理员指南 V6.2.1

下面的语句，效果完全等同于没有 WITH 子查询。因为 WITH 子查询被优化掉了。

WITH t AS (

UPDATE products SET price = price * 1.05

RETURNING *

)

SELECT * FROM products;

在同一个语句中，一条记录不能被更新两次，因为顺序是不确定的，所以，如果允

许更新两次，结果将是不确定的。这个限制是从 5 版本开始的，也是 4 版本升级需要

注意的事项。

使用函数和运算符

 在GP中使用函数

 自定义函数

 内置函数和运算符

 开窗函数

 高级聚合函数

在 GP 中使用函数

在GP中调用函数时，函数的一些属性，决定了该函数的运行差异。函数的易变性

(VOLATILE)属性决定了函数的行为差异，EXECUTE ON属性决定了函数可以在什么地

方被调用。VOLATILE属性原自PostgreSQL，该属性很重要，之前在"分区选择性的

诊断"章节也提到过，VOLATILE属性有三种类型IMMUTABLE、STABLE和VOLATILE。

EXECUTE ON属性是GP数据库特有的属性。

对于IMMUTABLE(不变的)型的函数，其特点是，只要输入的参数相同，输出的结

果一定相同，在任何时间调用该函数，输出结果与输入参数保持绝对的匹配关系。这就

允许这种函数在生成执行计划时被执行，因为其输出不会发生变化。STABLE(稳定的)

型的函数，其特点是，在事务范围内，效果与IMMUTABLE一样，在事务期间输出是不

变的，所以，这种函数也可以在生成执行计划时被执行。因为可以在生成执行计划时被

执行，IMMUTABLE和STABLE这两种函数，可以在生成执行计划时直接被优化为函数结

果的常量表达式。而VOLATILE(易变)型的函数，其特点是，任何时间执行，都不能保

Greenplum Database 管理员指南 V6.2.1

证输出的不变，所以，这种函数不能被优化器优化为常量，必须为查询中的每条记录执

行一次函数。

对于EXECUTE ON属性，EXECUTE ON MASTER型的函数只能在Master上被执行，

EXECUTE ON ALL SEGMENTS型的函数只能在所有的Primary上被执行，不可以在

Master上执行。

下面的表格总结了GP数据库中函数的不同属性对应的行为特征。

VOLATILE属性：

VOLATILE 属性

描述

备注

在任何时候执行，只要输入的参数不变，

IMMUTABLE

可以被优化器优化为常量

函数的结果就不变

在事务过程中，只要输入的参数不变，函

STABLE

可以被优化器优化为常量

数的结果就不变

函数的结果与输入参数之间没有关系，随

VOLATILE

必须为每一条记录执行一次

时会发生变化

EXECUTE ON属性：

EXECUTE ON 属性

描述

备注

缺省的属性，允许在 Master 或任何

EXECUTE ON ANY

由数据库决定在哪里执行

Primary 上执行。

EXECUTE ON

对于需要访问业务表的自定义

只能在 Master 上执行

MASTER

函数，应该选择这种类型

EXECUTE ON ALL

只能在 Primary 上执行

SEGMENTS

函数包含了一个在所有 Primary 上执行的

EXECUTE ON

编者还没有没有完全理解这种

SQL 命令，并希望在 Master 上调用时有

用法

INITPLAN

特殊的处理，这种函数不能用于 CTE 子句

在GP中使用VOLATILE型函数是受限的。VOLATILE表明，即便是单表的扫描，函

数值也可能发生变化。相对来说，很少有数据库内置函数属于这种类型，一些常见例子

Greenplum Database 管理员指南 V6.2.1

如random()、currval()、timeofday()等。不过需要提醒的是，所有有副作用的

函数都必须是VOLATILE的，即便其返回值是可预测的(例如setval())。

在GP中，数据分散存储在各Instance上 -- 每个Instance是一个独立的

PostgreSQL数据库。为了防止节点之间的数据出现不一致，任何含有SQL语句或者修

改数据库的VOLATILE函数都不可以在Instance上执行。

在6版本，因为引入了复制表，函数可以对Instance上的复制表(DISTRIBUTED

REPLICATED)执行只读查询命令，但任何修改数据的命令都必须在Master上执行。

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

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

为确保数据的一致性，VOLATILE和STABLE函数在Master上执行是安全的。例如，

下面的语句在Master上被执行(没有FROM子句的语句)：

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

=# SELECT foo();

简单的来说，在函数中，可以在Instance上执行对复制表的只读查询。除此之外，

在函数中对任何表的访问和修改操作，都是不允许在Instance上执行的，只能在

Master上执行。

函数的 VOLATILE 属性与执行计划缓存

从生成执行计划的角度来说，IMMUTABLE、STABLE 和 VOLATILE 三种类型最明

显的区别是，VOLATILE 不能进行常量替换的优化，IMMUTABLE 和 STABLE 一种是永

远保持稳定，一种是在事务过程中保持稳定，都可以通过先计算出函数的结果来进行常

量替换的优化。

但是，IMMUTABLE 和 STABLE 在执行计划缓存时，就有本质的区别，因为

IMMUTABLE 是永远稳定的，所以，多次执行可以用相同的结果来进行常量替换，而

STABLE 只是事务稳定的，在其他事务中执行不能保证之前的计算结果是正确的，所以，

使用了这种类型的函数的 SQL，执行计划不能被缓存。

自定义函数

GP像PostgreSQL一样支持自定义函数的使用。更多信息可以参看PostgreSQL

Greenplum Database 管理员指南 V6.2.1

文档的。

可以使用CREATE FUNCTION命令来创建自定义函数。就像"在GP中使用函数"章

节描述的那样，缺省状态下，函数被声明为VOLATILE，因此，如果自定义函数是

IMMUTABLE或者STABLE的，在创建函数时应该明确指定其VOLATILE属性，这一点很

重要，不然，VOLATILE的函数用于查询条件时，将无法被优化，会影响分区裁剪等。

注意，对于有副作用的函数(例如修改表中数据，执行Linux命令等)，必须指定为

VOLATILE类型，否则，即便创建时不会报错，也将无法正常调用。

CREATE FUNCTION时缺省的EXECUTE ON属性是EXECUTE ON ANY。函数中，

除了可以在Instance上对复制表的进行只读查询，函数只有在Master上被执行时，

才能访问表中的数据或者修改数据。对于需要在函数中访问非复制表的情况，应该将该

函数设置为EXECUTE ON MASTER属性，在没有指定该属性的情况下，执行计划可能

会把函数下推到Instance去执行，可能会遭遇报错。

在创建自定义函数时，要避免出现FATAL错误或者有破坏性的操作，其可能会导致

数据库宕机或者重启等异常。

在GP中，自定义函数的共享库文件在每个GP主机(Master和Instance所在机器)

上的库路径必须相同。

从5版本开始，支持匿名代码块功能，这些匿名代码块，使用GP的函数过程语言编

写，匿名代码块会像函数一样被执行。更多信息，可以参考DO命令。

内置函数和运算符

下表列出了PostgreSQL支持的内置函数和运算符的类别。除了STABLE和

VOLATILE函数外，所有PostgreSQL的函数和运算符在GP中都支持，所受限制如"在

GP中使用函数"中的描述。

关于内置函数和运算符的更多信息参照PostgreSQL文档。

运算符/函数类型

不稳定函数

稳定函数

逻辑运算符

比较运算符

数学函数和运算符

random、setseed

字符串函数和运算符

所有内置转换函数

convert、

pg_client_encoding

二进制字符串函数和运算符

按 bit 位字符串函数和运算符

模式匹配

日期类型格式化函数

to_char、to_timestamp

Greenplum Database 管理员指南 V6.2.1

日期/时间函数和运算符

timeofday

age、current_date、

current_time

current_timestamp、

localtime

localtimestamp、now

枚举类型的支持函数

几何函数和运算符

网络地址函数和运算符

序列处理函数

nextval、setval

条件表达式

数组函数和运算符

所有数据函数

聚合函数

子查询表达式

行比较和数组比较

集合返回函数

generate_series

系统信息函数

所有会话信息函数

所有访问权限查询函数

所有模式可见性查询函数

所有系统表信息函数

所有备注信息函数

所有事务 ID 和快照

系统管理函数

set_config、

current_setting

pg_cancel_backend

所有数据库对象尺寸函数

pg_reload_conf、

pg_rotate_logfile

pg_start_backup、

pg_stop_backup

pg_size_pretty、

pg_ls_dir

pg_read_file、

pg_stat_file

XML 函数

xmlagg(xml)

xmlexists(text, xml)

xml_is_well_formed(te

xt)

xml_is_well_formed_do

cument(text)

xml_is_well_formed_co

ntent(text)

xpath(text, xml)

xpath(text, xml,

text[])

xpath_exists(text,

xml)

Greenplum Database 管理员指南 V6.2.1

xpath_exists(text,

xml, text[])

xml(text)

text(xml)

xmlcomment(xml)

xmlconcat2(xml, xml)

更多内容参考官方文档

开窗函数

以下内置的开窗函数是GP数据库对PostgreSQL的扩展。所有开窗函数都是

IMMUTABLE的。有关开窗函数的详细信息，请参见"开窗表达式"章节。

函数

返回值类型

完整语法

描述

cume_dist()

double

CUME_DIST() OVER

计算一组值的累计

precision

( [PARTITION BY expr] ORDER

分布(0 到 1 的浮点

BY expr )

数)，相同值的记录

得到相同的分布

dense_rank()

bigint

DENSE_RANK () OVER

计算一组值的排名。

( [PARTITION BY expr] ORDER

相同值的排名相同，

BY expr)

有并列排名且排名

连续

first_value(expr)

与输入表达式

FIRST_VALUE(expr) OVER

计算一组值的第一

类型相同

( [PARTITION BY expr] ORDER

个值

BY expr [ROWS|RANGE

frame_expr] )

lag(expr

与输入表达式

LAG(expr [,offset]

一种跨行访问方式。

[,offset]

类型相同

[,default]) OVER

按照排序的位置，

[,default])

( [PARTITION BY expr] ORDER

LAG 将一行记录向

BY expr )

后偏移。如果不指定

offset，缺省值为

1。如果不指定

default 缺省值为

null

last_value(expr)

与输入表达式

LAST_VALUE(expr) OVER

计算一组值的最后

类型相同

( [PARTITION BY expr] ORDER

一个值

BY expr [ROWS|RANGE

frame_expr] )

lead(expr

与输入表达式

LEAD(expr [,offset]

一种跨行访问方式。

[,offset]

类型相同

[,default]) OVER

lead 将一行记录向

[,default])

( [PARTITION BY expr] ORDER

前偏移。如果不指定

Greenplum Database 管理员指南 V6.2.1

BY expr )

offset，缺省值为

1。如果不指定

default 缺省值为

null

ntile(expr)

bigint

NTILE(expr) OVER

将一组值，按照排序

( [PARTITION BY expr] ORDER

后的顺序分割为

BY expr )

expr 个部分，生成

从 1 到 expr 的序

号，同一部分内的序

号相同

percent_rank()

double

PERCENT_RANK () OVER

计算一组值的百分

precision

( [PARTITION BY expr] ORDER

比排名。相同值的排

BY expr )

名相同

rank()

bigint

RANK () OVER ( [PARTITION BY

计算一组值的排名。

expr] ORDER BY expr )

相同值的记录排名

相同且排名可能不

连续

row_number()

bigint

ROW_NUMBER () OVER

为排序的一组值，每

( [PARTITION BY expr] ORDER

行分配一个唯一的

BY expr )

连续编号

高级聚合函数

以下内置的高级聚合函数是GP数据库对PostgreSQL的扩展。所有这些函数都是

IMMUTABLE的。

函数

返回值类型

完整语法

描述

MEDIAN (expr)

timestamp,

MEDIAN (expression)

返回一个中间

timestampz,

例如：

值或者线性插

interval,

SELECT MEDIAN(i)

值。空值被忽

float

FROM generate_series(0,15) AS i;

略。

PERCENTILE_CONT

timestamp,

PERCENTILE_CONT(percentage)

根据给定的百

(expr) WITHIN

timestamptz,

WITHIN GROUP (ORDER BY expression)

分比计算排序

GROUP (ORDER

interval,

例如:

集合的线性插

BY expr [DESC/

float

SELECT PERCENTILE_CONT(0.22) WITHIN

值。空值会被

ASC])

GROUP (ORDER BY i)

忽略。

FROM generate_series(0,15) AS i;

PERCENTILE_DISC

timestamp,

PERCENTILE_DISC(_percentage_) WITHIN

根据给定的百

(expr) WITHIN

timestampz,

GROUP (ORDER BY _expression_)

分比计算排序

GROUP (ORDER BY

interval,

例如：

集合的最接近

expr [DESC/ASC])

float

SELECT PERCENTILE_DISC(0.22) WITHIN

的值。空值会

Greenplum Database 管理员指南 V6.2.1

GROUP (ORDER BY i)

被忽略。

FROM generate_series(0,15) AS i;

sum(array[])

smallint[]int

sum(array[[1,2],[3,4]])

矩阵求和，可

[], bigint[],

例如:

以使用二维数

float[]

WITH a AS(

组作为输入参

SELECT ARRAY[[1,2],[3,4]] AS arr

数。

UNION ALL

SELECT ARRAY[[5,6],[7,8]] AS arr)

SELECT sum(arr) FROM a;

sum

-----------------

{{6,8},{10,12}}

(1 row)

pivot_sum(label[

int[],

pivot_sum(array['A1','A2'], attr,

编者也没搞明

], label, expr)

bigint[],

value)

白怎么使用这

float[]

例如:

个函数。

WITH a AS(

SELECT generate_series(1,3) i,

generate_series(1,7) j

)SELECT i,

pivot_sum(array['j'],'j',2)

FROM a GROUP BY 1;

在编者看来就是分组计数，编者没有找到更多有用

的信息

unnest (array[])

任何元素集合

unnest(array['one', 'row', 'per',

将一维数组转

'item'])

换为 ROW 记

录。

查询性能

GP数据库会动态消除查询中不相关的分区，并且为执行计划中不同的算子优化内

存分配。为非内存密集型算子分配固定尺寸的内存，剩余的内存分给内存密集型算子。

这些增强，使得查询扫描更少的数据，内存得到更优化的分配，加速处理性能，可以提

升并发的支持能力。

 动态分区消除

在GP中，在运行过程中才能获取到的值，将会被用于动态分区消除，这样可以提

升查询的性能。通过配置gp_dynamic_partition_pruning设置为ON或OFF来

启用或禁用动态分区消除。缺省情况下是ON的。

Greenplum Database 管理员指南 V6.2.1

 内存优化

GP数据库为查询中的不同算子，根据是否为内存密集型算子，优化内存的分配，

为非内存密集型算子分配固定尺寸的内存，剩余的内存分给内存密集型算子，并在

处理的过程中，及时释放已经完成计算的算子的可释放内存并重新分配给后续的算

子。

注意：默认情况下，GP数据库使用ORCA优化器。ORCA扩展了PostgreSQL优化器的功

能。关于ORCA的特点和限制，参见"关于ORCA优化器"章节。

控制溢出文件

GP 在执行 SQL 时，如果分配的内存不足，则会将文件溢出到磁盘上，通常称为

workfile，workfile 是 GP 内的标准称呼，因为相关的参数，视图，函数的名字都

是以 workfile 来命名的。gp_workfile_limit_files_per_query 参数用于控

制最大的溢出文件的数量，缺省值为 100000，可以满足大多数的场景，一般不需要修

改这个参数。

如果溢出文件的数量超过了这个参数的值，数据库会返回一个报错：

ERROR: number of workfiles per query limit exceeded

有时，数据库可能会产生大量的溢出文件：

 存在严重的数据倾斜。关于数据倾斜的检查，可以参见"数据倾斜"章节。

 为查询分配的内存太少。可以通过max_statement_mem 和statement_mem参

数来控制查询可用的最大内存尺寸或者通过资源组或者资源队列来控制。

可以通过修改查询语句 -- 优化 SQL 以降低内存的需求，更改数据分布 -- 避免

数据倾斜，或修改内存配置来成功运行查询命令。gp_toolkit.gp_workfile_*视

图可以用来查看溢出文件的信息，这些视图，对于查询性能的排查非常有帮助。

查询剖析

可以通过检查性能不符合预期的查询的执行计划，来确定可能存在的性能优化机会。

GP会为每个查询语句生成一个执行计划。要获得好的性能，选择正确的执行计划

来适配数据的情况，是最重要的条件。执行计划决定了SQL命令在GP数据库中如何被执

行。如果SQL本身的逻辑非常糟糕，可能数据库无论如何也无法产生好的执行计划，例

Greenplum Database 管理员指南 V6.2.1

如大表之间的非等值关联。

优化器使用统计信息(存储在系统表中)来选择一个成本更低的执行计划(根据优

化器的实际情况，有时可能不一定是成本最低的)。成本衡量的标准是IO的代价，每个

磁盘PAGE为一个单位。优化的目标是，选择成本最小的执行路径，但往往，生成符合

预期的执行计划才是优的结果。

可以使用EXPLAIN命令来查看SQL命令的执行计划。例如：

=# EXPLAIN SELECT * FROM names WHERE id=22;

EXPLAIN ANALYZE命令会真正的执行SQL语句，而不仅仅是生成执行计划。这对

于检验优化器评估的是否接近实际运行情况比较有用。例如：

=# EXPLAIN ANALYZE SELECT * FROM names WHERE id=22;

查看 EXPLAIN 输出

执行计划是一棵有很多个算子构成的树，其中每个算子是一个独立的计算操作，例

如表扫描、关联、聚合或排序等。

从下到上来看执行计划，每个算子的计算结果作为上面一个算子的输入。执行计划

最底部的算子，往往是表扫描算子：Seq Scan、Index Scan、Bitmap Index Scan。

如果查询有关联、聚合或者排序，在扫描算子之上会有其他算子来执行这些操作。最顶

端的算子往往是GP的移动算子(重分布、广播或汇总)。移动算子负责将处理过程中产

生记录在Instance之间移动。

EXPLAIN的输出中每个算子都有一行，其显示基本的算子类型和该算子的成本估算：

 cost -- 访问的磁盘页数量，就是说，1.0等于一个连续的磁盘页操作。第一个

值是获得第一条记录的成本，第二个值是获得所有记录的总成本。总成本是假设会检索

所有的记录，但有时并不会真的检索所有记录，比如使用了LIMITX子句，可能不会真

的检索所有记录。例如：

=# EXPLAIN SELECT * FROM pg_class;

Seq Scan on pg_class

(cost=0.00..17.19 rows=1019 width=265)

Optimizer: Postgres query optimizer

=# EXPLAIN SELECT * FROM pg_class LIMIT 1;

Limit

(cost=0.00..0.02 rows=1 width=265)

-> Seq Scan on pg_class

(cost=0.00..17.19 rows=1019 width=265)

Optimizer: Postgres query optimizer

Greenplum Database 管理员指南 V6.2.1

注意：Orca优化器和PostgreSQL优化器生成的执行计划中，cost不具有可比性。这

两个优化器，使用不同的成本估算模型和算法来评估执行计划的成本。对比两个优化器

之间的cost值是没有实际意义的。

另外，对于任意优化器生成的执行计划的cost值来说，只对当前的查询和当前的

统计信息有意义，不同的语句会生成不同cost的执行计划。即便如此，如果看到一个

数量级非常大的cost，可能执行计划的确是有问题的。有时，cost的值会严重失真，

例如统计信息失真的情况下，这时的cost将变的不再真实。

 rows -- 该算子输出的记录数。该值可能与真实数量有较大的出入，其会反映

WHERE子句的条件对记录的过滤。顶端算子评估的数量，在理想状态下与真实返回的、

更新的或者删除的数据量接近。

 width -- 该算子产生的每条记录的尺寸(字节数)。这里不一定能真实体现计算

的数据每条记录的尺寸，这里会去除掉表中没有被涉及到的字段的尺寸，对于列存表，

这样做是准确的，但对于行存表，因为真实处理时，行存表的一条记录是一个tuple，

不会因为只使用了少量字段而把tuple拆解。

注意：一个上层算子的cost包含其所有子算子的cost，最顶端算子的cost包含了整

个执行计划的总cost，这就是优化器要试图减小的数字。另外，cost仅仅反映了优化

器所在意的代价。除了这些，cost不包含结果集传输到客户端的开销或耗时的预估。

EXPLAIN 示例

要说明如何阅读EXPLAIN得到的执行计划，参考一下下面这个简单的例子：

=# EXPLAIN SELECT * FROM names WHERE name = 'Joelle';

QUERY PLAN

------------------------------------------------------------

Gather Motion 2:1 (slice1) (cost=0.00..20.88 rows=1 width=13)

-> Seq Scan on 'names' (cost=0.00..20.88 rows=1 width=13)

Filter: name::text ~~ 'Joelle'::text

从下向上查看这个执行计划，执行计划从顺序扫描names表开始。注意，WHERE

子句被用作一个filter条件。这意味着，扫描操作将根据条件检查扫描的每一行，并

只输出符合条件的记录。

扫描算子的输出传递给汇总移动算子。在GP中，汇总移动是Instance向Master

发送记录的操作，该场景下，有2个Instance向1个Master发送(2:1)记录。每个算

Greenplum Database 管理员指南 V6.2.1

子都在执行计划的一个Slice中。在GP中，一个执行计划可能会被分为多个Slice，

以确保计算任务可以在Instance之间并行工作，往往不同的Slice可能会被Motion

算子分开。

评估的开始成本为00.00(无cost)且总成本为20.88个磁盘页。优化器评估这个

查询将返回一行记录。单条记录的尺寸为13个字节。

查看 EXPLAIN ANALYZE 输出

EXPLAIN ANALYZE会真正的执行语句，而不仅仅是生成执行计划。EXPLAIN

ANALYZE依然会输出优化器的评估cost，同时会输出真实执行的cost。据此，可以评

估优化器生成的执行计划与真实的执行情况是否接近。EXPLAIN ANALYZE还会额外输

出如下信息(Orca和PostgreSQL优化器会有差异)：

 执行该查询总的耗时(以毫秒计)。

 执行计划的每个Slice使用的内存，以及分配给该查询的总的内存量。

 参与一个算子计算的Instance数量，只统计有记录返回的Instance。

 算子中输出记录数最多的Instance输出的记录数。如果有多个Instance输出的

记录数相同，则显示耗时最长的Instance的信息。

 算子的内存使用情况，对于工作内存不足的算子，将显示性能最低的Instance的

溢出文件的数量。例如：

PostgreSQL 优化器：

Extra Text: (seg0) . . . ; 100038 spill groups.

* (slice2)

Executor memory: 2114K bytes avg x 2 workers, 2114K bytes max

(seg0). Work_mem: 925K bytes max, 6721K bytes wanted.

Memory used:

2048kB

Memory wanted:

13740kB

Orca优化器：

Sort Method: external merge Disk: 1664kB

* (slice2)

Executor memory: 2256K bytes avg x 2 workers, 2256K bytes max

(seg0). Work_mem: 2105K bytes max, 5216K bytes wanted.

Greenplum Database 管理员指南 V6.2.1

Memory used:

2048kB

Memory wanted:

5615kB

 算子中输出记录数最多的Instance，输出第一条记录所用的时间(以毫秒计)，输

出最后一条记录所用的时间。如果两个时间相同，开始时间会被省略。随着执行计

划从下向上被执行，时间可能是有重叠的。

EXPLAIN ANALYZE 示例

我们使用一个相对复杂一点的查询来说明。

先看一下Orca优化器的输出：

EXPLAIN ANALYZE

SELECT customer_id,count(*) FROM sales GROUP BY 1;

Gather Motion 2:1

(slice2; segments: 2)

(cost=0.00..476.54 rows=85709

width=12) (actual time=376.394..452.434 rows=99351 loops=1)

-> HashAggregate

(cost=0.00..471.92 rows=42855 width=12) (actual

time=377.094..421.520 rows=49765 loops=1)

Group Key: customer_id

Extra Text: (seg0)

49765 groups total in 32 batches; 1 overflows;

169919 spill groups.

(seg0)

Hash chain length 2.0 avg, 16 max, using 42789 of 72704 buckets;

total 8 expansions.

-> Redistribute Motion 2:2

(slice1; segments: 2)

(cost=0.00..441.24 rows=250500 width=4) (actual time=2.788..171.396

rows=250602 loops=1)

Hash Key: customer_id

-> Seq Scan on sales

(cost=0.00..436.24 rows=250500 width=4)

(actual time=0.019..46.502 rows=250755 loops=1)

Planning time: 31.606 ms

(slice0)

Executor memory: 87K bytes.

(slice1)

Executor memory: 58K bytes avg x 2 workers, 58K bytes max (seg0).

* (slice2)

Executor memory: 3106K bytes avg x 2 workers, 3106K bytes max

(seg0). Work_mem: 1849K bytes max, 4737K bytes wanted.

Memory used:

2048kB

Memory wanted:

5036kB

Optimizer: Pivotal Optimizer (GPORCA)

Execution time: 466.982 ms

Greenplum Database 管理员指南 V6.2.1

从下往上看，将看到每个算子的额外信息。花费的总时间为466.982毫秒。

顺序扫描表的操作，输出记录数最多的Instance，执行计划评估的记录数是

250250条，实际输出的是250755条，输出第一条的用时是0.019毫秒，输出最后一

条的用时是46.502毫秒。重分布算子，输出第一条的用时是2.788毫秒，输出最后一

条的用时是171.396毫秒。总的内存使用量是2048kB，而wanted是5036kB，在HASH

聚合算子中因为内存不足，使用了spill溢出文件。

下面在再看一下PostgreSQL优化器的输出：

EXPLAIN ANALYZE

SELECT customer_id,count(*) FROM sales GROUP BY 1;

Gather Motion 2:1

(slice2; segments: 2)

(cost=11066.78..11923.86

rows=85708 width=12) (actual time=567.520..653.070 rows=99351 loops=1)

-> HashAggregate

(cost=11066.78..11923.86 rows=42854 width=12) (actual

time=566.833..619.733 rows=49765 loops=1)

Group Key: sales.customer_id

Extra Text: (seg0)

49765 groups total in 32 batches; 1 overflows;

218258 spill groups.

(seg0)

Hash chain length 1.7 avg, 12 max, using 38835 of 69632 buckets;

total 2 expansions.

-> Redistribute Motion 2:2

(slice1; segments: 2)

(cost=8067.00..9781.16 rows=42854 width=12) (actual

time=12.020..363.668 rows=227461 loops=1)

Hash Key: sales.customer_id

-> HashAggregate

(cost=8067.00..8067.00 rows=42854 width=12)

(actual time=10.862..212.384 rows=227625 loops=1)

Group Key: sales.customer_id

Extra Text: (seg0)

Hash chain length 4.4 avg, 15 max, using

52054 of 53248 buckets; total 2 expansions.

-> Seq Scan on sales

(cost=0.00..5562.00 rows=250500

width=4) (actual time=0.018..66.742 rows=250755 loops=1)

Planning time: 0.113 ms

(slice0)

Executor memory: 87K bytes.

(slice1)

Executor memory: 1076K bytes avg x 2 workers, 1076K bytes max

(seg0).

* (slice2)

Executor memory: 2114K bytes avg x 2 workers, 2114K bytes max

(seg0). Work_mem: 925K bytes max, 4673K bytes wanted.

Memory used:

2048kB

Memory wanted:

9644kB

Optimizer: Postgres query optimizer

Greenplum Database 管理员指南 V6.2.1

Execution time: 666.468 ms

这里不再详细解读PostgreSQL优化器的输出。需要注意，不同的算子的耗时是有

交叉和重叠的，这是因为，GP的执行器是流水线操作，下一步操作并不一定需要等待

上一步完全执行完才开始执行，有些操作需要等待上一步的完成，例如Hash Join必

须要等Hash操作完成才能开始。

检查执行计划排查问题

若一个查询表现出很差的性能，查看执行计划可能会有助于找到问题所在。下面是

一些需要查看的事项：



执行计划中是否有某些算子耗时特别长？找到占据大部分查询时间的算子。例如，

如果一个索引扫描比预期的时间长，可能该索引已经过期，需要考虑重建索引。还

可尝试使用enable_之类的参数(对于PostgreSQL优化器来说，这些参数很重

要)，检查是否可以强制优化器选择不同的执行计划，这些参数可以设置特定的算

子为开启或关闭状态。



优化器的评估是否接近实际情况？执行EXPLAIN ANALYZE查看优化器评估的记

录数与真实运行时的记录数是否一致。如果差异很大，可能需要在相关表的某些字

段上收集统计信息。不过，如果SQL本身已经完全无法运行出结果，EXPLAIN

ANALYZE将无法进行，该方法仅对运行慢的SQL有效。



选择性强的条件是否较早出现？选择性越强的条件应该越早被使用，从而使得在

计划树中向上传递的记录越少。如果执行计划在选择性评估方面没有对查询条件作

出正确的判断，可能需要在相关表的某些字段上收集统计信息。不过，收集了准确

的统计信息仍可能无法使得选择性的评估更准确，因为GP的选择性评估是基于MCV

模型的，没有被统计信息记录的值，需要通过线性插值算法得到其存在概率，这种

评估本身误差就较大，当需要同时对多个条件进行评估时，这种误差会呈几何倍数

放大。有时，将太过复杂的SQL进行必要的拆解会更有效。



优化器是否选择了最佳的关联顺序？如查询使用多表关联，需要确保优化器选择

了选择性最好的关联顺序。那些可以消除大量记录的关联应该尽早的被执行，从而

使得在计划树中向上传递的记录快速减少。如果优化器没有选择最佳的关联顺序，

可以尝试设置join_collapse_limit=1(Orca由

optimizer_join_order_threshold参数控制)并在SQL语句中构造特定的关

联顺序，从而可以强制优化器选择指定的关联顺序。还可以尝试在相关表的某些字

段上收集统计信息。



优化器是否选择性的扫描分区表？如果使用了分区，优化器是否只扫描了查询条

件匹配的相关分区。关于执行计划中是否选择了分区扫描，可以参见"验证分区策

略"章节。

Greenplum Database 管理员指南 V6.2.1

 优化器是否恰当的选择了HASH聚合或HASH关联算子？HASH操作通常比其他类型

的关联和聚合要快。记录在内存中进行比较和排序比在磁盘上操作要快很多。要使

得优化器能选择HASH算子，必须确保有足够的内存来存放记录。可以尝试增加工

作内存来提升性能(当缺省的内存配置不充裕时，如果已经足够，再增加不会提升

性能，所以，不要盲目的以为增加内存就一定可以提升性能，内存只是一个通常不

太会出问题的因素)。如果可能，执行EXPLAIN ANALYZE，可以发现哪些算子会

用到溢出文件，使用了多少内存，需要多少内存。例如：

Extra Text: (seg0)

49765 groups total in 32 batches; 1 overflows;

218258 spill groups.

* (slice2)

Executor memory: 2114K bytes avg x 2 workers, 2114K bytes max

(seg0). Work_mem: 925K bytes max, 4673K bytes wanted.

Memory used:

2048kB

Memory wanted:

9644kB

需要注意的是wanted信息只是一个提示，是基于溢出文件尺寸来评估的。实际需要的

内存，可能与实际情况有出入。

Greenplum Database 管理员指南 V6.2.1

第十一章：数据导入与导出

本章讲述，在GP数据库中，如何将数据导入到常规的数据表中，如何从常规的数

据表中将数据导出，以及数据文件的格式和异常处理等问题。

GP数据库支持高速并行数据导入和导出，对于数据量很小的导入和导出场景，也

可以选择非并行的方式(原自PostgreSQL的COPY命令)。

GP支持导入和导出多种外部数据，比如，文本文件，Hadoop文件系统文件，Amazon

S3，Web数据源等。

 SQL命令中的COPY命令，可以支持从psql的客户端，Master服务器，Instance

服务器等位置，将文件导入到数据库中，或者从数据库导出到文件。

 可读外部表，支持通过SQL直接对外部数据进行查询，除了SELECT外，还可以进

行条件过滤、关联和排序等操作，也可以在外部表之上创建视图。不过，外部表最

常见的使用场景是，将数据导入到常规的数据表中，而且，一般建议复杂的查询和

处理操作，应先将数据导入数据库内，再进行复杂运算，因为，外部表查询虽然也

是并行的，但性能还是远比不上常规的数据表。例如：

=# CREATE TABLE table AS SELECT * FROM ext_table;

=# INSERT INTO table SELECT * FROM ext_table;

 WEB型的外部表提供了更灵活的数据访问方式，可以通过访问http协议的URL来获

取动态数据，或者通过执行GP集群内主机上的Linux脚本或命令来获取数据。编

者编写的gpdbtransfer命令，就是通过这种外部表来实现GP集群之间的并行数

据传输的，实现了灵活丰富的功能支持，编者一直自称为目前最先进的跨集群数据

同步方案。

 GP数据库还提供了并行文件分发程序gpfdist，gpfdist命令与外部表配合，基

于HTTP协议实现并行的文件数据导入和导出，支持多机部署gpfdist服务，从而

实现，GP的Primary和gpfdist服务之间直接通过网络高速并行数据传输。

 gpload命令，通过YML格式文件进行参数控制，通过对gpfdist命令和外部表的

包装(只是包装)，具备一定程度的自动化，实现将文件数据导入到GP数据库中。

实际上，编者从未真正使用过gpload命令，因为直接使用外部表更灵活，过于追

求傻瓜式，并不利于问题的发现和解决，编者不会介绍gpload命令。

 还可以使用PXF协议来创建可读外部表或者可写外部表，来实现数据的并行导入和

导出。编者也不会介绍PXF，如有需要，请按照官方说明来操作，编者想说，学会

读文档和看HELP真的很重要，编者能做的是，帮助各位入门和理解，但做不到详

细教学所有知识点。在6版本之前，访问Hadoop文件的主要方式是gphdfs协议，

在6版本才正式切换为PXF协议，实际上gphdfs的配置和调试更加的麻烦。

Greenplum Database 管理员指南 V6.2.1

 商业版本还提供了gpkafka工具，实现从kafka高速并行导入数据到GP数据库中。

 商业版本还提供了gpsc(Greenplum-Spark Connector)工具，实现GP和

Spark之间的高速数据交互。

 商业版本还提供了Greenplum-Informatica Connector工具，实现GP和

Informatica PowerCenter之间的高速数据传输。

根据编者的理解，以上这些方法，除了PostgreSQL的COPY命令，都是通过外部

表来实现的，理论上来说，通过基于命令的WEB型外部表，完全可以自行实现与任何外

部数据的高速交互。

选择什么样的数据交互方式，取决于数据的具体情况，比如，数据在哪里，数据规

模的大小，需要什么样的转换和处理等。

对于数据量不大，不追求很高的性能的情况下，在可以运行psql的环境，可以简

单的通过COPY命令来实现数据的导入和导出。COPY操作的文件尺寸限制，取决于psql

所在环境的文件系统容量，COPY操作的性能限制，取决于psql所在环境的文件系统的

性能，一般情况下，单个COPY命令就可以达到350MB/S甚至更高的数据导入和导出的

性能，是一个很不错的选择。不过，通过COPY命令导入或者导出数据时，数据需要通

过Master进行处理。

对于大规模的数据导入和导出，更高效的方法，是选择充分利用MPP特点的方式，

让Instance同时从多个文件服务，利用多个网络端口，进行并行数据处理。可读外部

表，对于查询操作来说，访问可读外部表，就如同访问常规的数据表一样，可以执行

SELECT等操作。外部表与gpfdist服务配合，所有Instance与gpfdist服务之间进

行全并行的数据传输，这是目前为止性能最高的数据导入导出的方式。

GP可以利用HDFS的并行架构实现与HDFS之间的高速并行文件访问，比如PXF协议。

创建外部表

GP的外部表，是数据存储在数据库之外的一种表，通过创建外部表来定义数据的

位置和数据的格式。外部表根据数据的流向，可以分为可读外部表和可写外部表，可读

外部表，主要用于将数据导入GP数据库，可写外部表主要用于将数据从GP数据库导出。

对于外部表的使用，可以如同常规的数据库表一样来执行相应的SQL命令。比如对于可

读外部表，可以通过SELECT来查询数据，对于可写外部表可以通过INSERT来导出数

据，如果有必要，可读外部表还可以与其他表进行关联查询。

要创建外部表，首先需要确定数据的位置，文件的格式，根据这些内容来创建外部

表的定义，之后，就可以通过外部表来实现与外部数据的交互了。

Greenplum Database 管理员指南 V6.2.1

数据格式

不管是导入数据到GP数据库中，还是从GP数据库导出数据，都需要指定数据的格

式。COPY和CREATE EXTERNAL TABLE(gpload实际上只是外部表的包装，不再单

独介绍，有需要的话，可以查阅相关资料)命令都可以指定数据的格式，数据可以是带

分隔符的TEXT文本，逗号分隔的CSV格式等。只有正确的定义了数据的格式，在操作

这些数据时才能正确的处理。

行分隔符

GP数据库可以识别的行分隔符包括：换行(LF | 0x0A)、回车(CR | 0x0D)或

回车换行(CR+LF | 0x0D 0x0A)。换行符，是Unix类操作系统使用的标准行记录分

隔符。Windows等操作系统可能会使用回车或回车换行作为行记录分隔符。这些行记

录分隔符，GP数据库都可以支持。

字段分隔符

对于TEXT格式的文件，缺省的字段分隔符是水平制表符(0x09)。对于CSV格式的

文件，缺省的字段分隔符是英文逗号(0x2C)。在使用COPY命令或者创建外部表时，都

可以通过DELIMITER关键字来指定一个单字节的分隔符，作为字段之间的分隔符。字

段分隔符，指的是数据文件中，以这个字符作为两个字段之间的分隔。但记录的开头和

结尾位置不能有多余的字段分隔符(第一个字段或最后一个字段为空，属于正常现象)。

例如，使用管道符(|)作为分隔符的一行记录：

data value 1|data value 2|data value 3

下面的例子展示，使用管道符(|)作为字段分隔符来创建外部表：

CREATE EXTERNAL WEB TABLE test_ext (a int, b int)

EXECUTE E'echo "08|25"'

FORMAT 'text' (DELIMITER E'\x7c');

不过，还是建议选择一个ASCII值较小的字符作为字段分隔符，常见的中文编码

Greenplum Database 管理员指南 V6.2.1

GBK编码中，低字节的范围在40 ~ FE之间，如果选择ASCII小于40的字段分隔符，

将可以更有效的处理半个中文的吃字和乱码问题。在GP中，字符串类型的长度是以字

符为单位的，但像Oracle等数据库，经常是以字节为单位计数的，就容易出现存储了

半个中文的现象，如果半个高字节的中文和ASCII小于40的字段分隔符遇到一起，就

可以在编码转换时明确的知道这是一个非法中文，如果和一个ASCII大于40的字段分

隔符遇到一起，将无法界定高字节是半个中文，因为这是一个合法的GBK编码。

NULL 值的定义

NULL值表示字段的值未被设定，在GP数据库中，NULL值和空字符串是不同的概

念，NULL只能用IS NULL或者IS NOT NULL来判断，空字符串可以使用=''来判断。

在使用COPY命令或者创建外部表时，可以通过NULL关键字来指定，将指定的字符串当

作NULL值。仅当一个字段的字符串和NULL关键字指定的字符串相同时(不能有任何多

余的字符)，该字段将会被作为NULL来识别。

在GP数据库导入外部数据时，缺省情况下，TEXT格式的NULL字符串为\N(反斜杠

+N)，CSV格式的NULL字符串是没有双引号的空字符串，就是两个逗号之间什么都没有。

例如，当定义NULL是'0'这个字符串时，将会得到下面的效果：

CREATE EXTERNAL WEB TABLE test_ext (a int, b int, c int)

EXECUTE E'echo "01|00|02

03|0|04"' ON MASTER

FORMAT 'text' (delimiter E'\x7c' NULL '0');

SELECT *,b IS NULL as isnull FROM test_ext;

从查询的输出可以看出，第二行的第二个字段被识别为NULL。而其他字段中只是

包含了'0'，并不是整个字段为'0'，不会被当做NULL来对待。

如果在GP数据库之间导出和导入数据，可以直接使用缺省的设置。

转义符

Greenplum Database 管理员指南 V6.2.1

对于GP数据库导入导出数据来说，有两类字符是特殊字符，分别是行分隔符和字

段分隔符。如果数据本身包含这两类字符，则需要对字段本身进行转义，否则会造成歧

义。缺省情况下，TEXT格式的转义符是反斜杠(\)，CSV格式的转义符是双引号(")。

编者想说的是，转义符是解决歧义的最根本的方法，包含转义的数据格式，可以从根本

上确保绝对不会有歧义，除此之外，不管是CSV，XML，JSON，还是多字节分隔符，只

要没有转义，都不能绝对保证数据文件中一定不会有歧义。所以，在导入和导出数据时，

使用转义符，可以绝对解决所有数据歧义问题。

对于文本中的字符转义，也是可以识别的，比如，对于与运算符(&)，在文本中，

其16进制转义表示和八进制转义表示分别为[\x26]和[\046]。例如：

CREATE EXTERNAL WEB TABLE test_ext (a int, b int, c text)

EXECUTE 'echo "01|00|\x26 \046"' ON MASTER

FORMAT 'text' (delimiter E'\x7c');

如果要关闭转义功能，可以使用ESCAPE 'OFF'，这样，文本中的所有字符就表

现为原本的值，不过，建议不要这么做，尤其是中文环境，没有转义根本无法正常工作，

最早的gptransfer工具就是这么做的，根本用不了。

字符编码

字符编码系统，是将字符集中的符号和一堆数字进行一对一映射，以便于传输和存

储。GP服务端，虽然也支持多种编码集，比如ISO8859系列的单字节编码，还有UTF8

等多字节编码，但一般不建议修改GP数据库的服务端编码，尤其在中文环境，UTF8是

目前最佳的选择，也是缺省选择，建议不要修改。对于客户端的编码支持非常丰富，但

GP服务端，并不支持所有编码格式，当从客户端输入数据时，数据库将根据客户端设

定的编码格式自动进行编码转换，在数据返回给客户端时，数据库又会将数据自动转换

为客户端的编码格式。

数据文件必须采用GP支持的字符编码，在导入数据时，如果数据文件中包含不支

持的编码字符，加载将会遇到报错信息。对于少量的中文乱码，半个中文等问题，可以

请求专业服务适当解决。

Greenplum Database 管理员指南 V6.2.1

外部表协议

外部表主要用于大规模并行导入或导出数据，目前已经支持多种外部表协议。例如，

file，gpfdist，PXF，S3等。本节将主要介绍常用的几种，关于其他协议，因为编

者目前无法进行测试，不做介绍，如有需要，可参考官方文档。

file 协议

file协议，通过URI来指向操作系统的文件。URI由主机名、文件所属的路径和

文件名组成，不需要指定端口号，因为访问的是GP各个计算节点的本地文件。文件所

在的位置，必须是初始化GP数据库的用户(按照管理，通常为gpadmin)可以访问的位

置。主机名，必须存在于系统表gp_segment_configuration中的hostname字段

中，或者address字段中，如果在这两个字段中都找不到这个主机名，则会在查询外

部表时报如下的错误：

ERROR: could not assign a segment database for

DETAIL: There isn't a valid primary segment database on host . . .

LOCATION子句中可以有多个URI属性，用逗号分隔。例如：

CREATE EXTERNAL TABLE test_ext (a int, b int, c text)

LOCATION(

'file://mdw/tmp/file1*',

'file://mdw/tmp/file[2-4,6]'

)

FORMAT 'text' (delimiter E'\x7c');

在file协议中，URI中可以使用通配符或者C格式的模式匹配，来表示多个文件，

比如上例中，file1*表示所有以file1开头的文件，[2-4,6]表示2、3、4、6。

在LOCATION中指定的URI的数量，是Primary访问文件时的并行数，对于每一个

URI属性，数据库会根据主机名匹配gp_segment_configuration系统表中的

hostname字段和address字段，并决定由哪个Primary来执行这条URI。理论上，为

每个Primary指定一个URI，可以确保数据读取时的最大并行度。不过，每个主机上的

最大URI的数量，取决于主机上有多少个Primary，也就是说，同一个Primary最多

只能分配一个URI。另外，如果匹配的是address字段，则，相同address值的数量

决定了URI中该主机名的数量，例如，有一台计算节点主机，主机名叫sdw01，主机上

有两个Primary，对应address名称分别为sdw01-1和sdw01-2，下面的外部表在查

询时会报错：

Greenplum Database 管理员指南 V6.2.1

=# SELECT hostname,address FROM gp_segment_configuration WHERE hostname =

'sdw01' AND content >= 0 AND role = 'p';

hostname | address

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

sdw01

| sdw01-1

sdw01

| sdw01-2

(2 rows)

=# CREATE EXTERNAL TABLE test_ext (a int, b int, c text)

LOCATION(

'file://sdw01-1/tmp/file1',

'file://sdw01-1/tmp/file2'

)

FORMAT 'text' (delimiter E'\x7c');

=# SELECT * FROM test_ext;

ERROR: could not assign a segment database for "file://sdw01-1/tmp/file2"

DETAIL: There are more external files than primary segment databases on host

"sdw01-1"

如果URI的数量超过了Primary的数量，还可能会有如下的报错：

WARNING: number of locations (3) exceeds the number of segments (2)

HINT: The table cannot be queried until cluster is expanded so that there

are at least as many segments as locations.

gpfdist 协议

gpfdist协议，通过URI来指向gpfdist服务的相对路径的文件，数据库的所有

Primary都必须能够访问gpfdist服务。gpfdist服务将所在机器的文件并行的分发

给GP数据库的Primary。gpfdist服务的命令，在GP数据库集群的每台主机上都存在，

在$GPHOME/bin目录下，source了GP的path文件之后，就可以直接使用该命令了。

在文件所在的服务器上启动gpfdist服务，对于Linux环境来说，可以直接复制

Server的安装目录(缺省在/usr/local目录下，编者建议不要修改)。对于可读外部

表，gpfdist会自动解压gzip(.gz后缀)压缩文件和bzip2(.bz2后缀)压缩文件。

如果是压缩文件，需要确保后缀名称与压缩格式一致，否则可能无法正确的识别。对于

可写外部表，如果目标文件的后缀名称是[.gz]，gpfdist会自动对目标文件进行

gzip压缩，编者记得早些年好像导出的时候不会自动压缩，不过，编者目前测试了

4.3.29版本，5.21版本和6.8版本，导出文件都可以自动压缩。

Greenplum Database 管理员指南 V6.2.1

在gpfdist协议中，URI中可以使用通配符或者C格式的模式匹配，来表示多个文

件，这与file协议是完全一致的。往往的确需要这样做，因为gpfdist协议对

LOCATION中的URI的数量同样有限制，不能超过Primary的数量。与file协议不同

的是，file协议的URI是绝对路径，而gpfdist协议的URI是gpfdist工作目录的相

对路径。

注意：对于Windows平台的gpfdist服务，不论是可读外部表还是可写外部表，

gpfdist服务均不支持解压和压缩。

和file协议一样，所有Primary并行请求URI的资源，不同的是，gpfdist协议

不需要资源在集群内的机器上，更不需要跟gp_segment_configuration系统表中

的hostname或者address字段匹配。不同的Primary可能会请求相同的URI资源，但

同一个Primary不会请求多个URI资源，所以，URI的数量不能超过Primary的数量，

否则，将有URI资源没有Primary来请求，会报错。在CREATE EXTERNAL TABLE时，

指定多个gpfdist数据源，可以提升外部表获取数据的性能，不过对于规模不大的集

群来说，一个gpfdist的吞吐能力就足够上百个Primary处理了，如果要提升多个外

部表的并发导入能力，可以启动多个gpfdist服务供不同的外部表访问。

对可读外部表进行查询时，请求同一个gpfdist服务的Primary数量，还与

gp_external_max_segs参数有关，当URI的数量小于该参数的值时，将最多有该参

数指定的数量的Primary会发起请求，该参数的缺省值为64，绝大部分情况下已经可

以满足需求，甚至在一些网络环境较差的集群，还需要降低该参数的值以缓解网络压力。

gpfdist还支持数据转换(transform)，通过在URI中使用transform参数来

指定。在启动gpfdist服务时，需要通过-c参数来指定一个yml格式的配置文件，

gpfdist接收到transform参数时，会根据参数的值来匹配yml文件中的配置，以确

定执行具体的动作。例如：

$ pwd

/tmp

$ cat transform.yml

---

VERSION: 1.0

TRANSFORMATIONS:

input:

TYPE: input

CONTENT: data

COMMAND: /bin/bash /tmp/input.sh %filename%

output:

TYPE: output

CONTENT: data

COMMAND: /bin/bash /tmp/output.sh %filename%

$ cat input.sh

Greenplum Database 管理员指南 V6.2.1

#!/bin/bash

cd $(cd "$(dirname "$0")"; pwd)

URI=$1

echo $URI

$ cat output.sh

#!/bin/bash

cd $(cd "$(dirname "$0")"; pwd)

URI=$1

more > $URI

$ gpfdist -c /tmp/transform.yml -d /../

=# DROP EXTERNAL TABLE test_ext;

=# CREATE EXTERNAL TABLE test_ext (a text)

LOCATION(

'gpfdist://mdw/tmp/file1#transform=input'

)

FORMAT 'text' (delimiter E'\x7c');

=# SELECT * FROM test_ext;

-------------

//tmp/file1

(1 row)

=# DROP EXTERNAL TABLE test_ext;

=# CREATE WRITABLE EXTERNAL TABLE test_ext (a text)

LOCATION(

'gpfdist://mdw/tmp/file0#transform=output'

)

FORMAT 'text' (delimiter E'\x7c');

=# INSERT INTO test_ext SELECT generate_series(1,3);

$ cat file0

这是一个简单的使用transform的例子，不过，在此基础之上，可以继续做很多

的定制开发，例如，访问任意希望访问的数据源，做任意的数据转换。

WEB 型的外部表

GP支持WEB型的外部表，以允许对动态数据或者网络数据进行访问。使用CREATE

EXTERNAL WEB TABLE命令来创建WEB型外部表。WEB型外部表还包含两种类型，基

Greenplum Database 管理员指南 V6.2.1

于URL类型和基于Linux命令类型。

基于命令的 WEB 型外部表

GP支持通过Linux脚本或命令来实现对外部数据的操作。在使用CREATE

EXTERNAL WEB TABLE命令创建外部表时，通过EXECUTE子句来指定需要执行的命令。

基于命令的可读WEB型外部表，读取数据的具体内容取决于执行命令的时间。基于命令

的可读WEB型外部表，脚本或命令可以在GP数据库集群内的Master、Instance或者

任意主机上(范围可以选择)被执行，命令或者脚本必须事先已经在这些机器上被定义

好，缺省情况下，所有Primary都会执行指定的命令或脚本来产生数据，比如一个计

算节点主机上有6个Primary，则指定的命令或脚本会在该主机上运行6次，每个

Primary都会运行且只运行一次，如果要修改这种缺省的行为，可以通过EXECUTE子

句的ON子句来定义。对于基于命令的可写WEB型外部表，所有Primary都会运行且只

运行一次。

基于命令的WEB型外部表中的命令是由数据库执行的，所以，并不会source

gpadmin用户的登录文件(.bashrc或.profile)，在执行命令时，可以手动source

这些环境文件，也可以使用预先定义好的GP环境变量，这些变量主要用于识别不同的

Primary信息和事务信息等。例如：

变量名称

描述

$GP_CID

执行外部表语句的事物的命令计数器，用于区分同一事物中的

不同语句。

$GP_DATABASE

外部表的定义所在的 Database 的名称。

$GP_DATE

外部表运行的日期。

$GP_MASTER_HOST

GP 数据库集群的 Master 的主机名。

$GP_MASTER_PORT

GP 数据库集群的 Master 的服务端口。

$GP_QUERY_STRING

正在被执行的 SQL 语句。

$GP_SEG_DATADIR

当前 Primary 的工作目录。

$GP_SEG_PG_CONF

当前 Primary 的 postgresql.conf 文件位置。

$GP_SEG_PORT

当前 Primary 的服务端口。

$GP_SEGMENT_COUNT

GP 集群的 Primary 总个数。

$GP_SEGMENT_ID

当前 Primary 的 DBID，与 gp_segment_configuration

系统表中的 dbid 相同。其实更有用的是 content 值。

$GP_SESSION_ID

执行外部表语句的 Session ID。

$GP_SN

执行计划中外部表扫描节点的序号，用于区分同一的 SQL 中多

次扫描同一张外部表。

$GP_TIME

外部表运行的时间。

$GP_USER

执行外部表的数据库 Role Name。

$GP_XID

执行外部表的事物 ID。

Greenplum Database 管理员指南 V6.2.1

关于可读WEB型外部表的ON子句，不同的选项和含义如下表：

选项

描述

ON ALL

缺省值。在所有 Primary 上运行。

ON MASTER

只在 Master 上运行。

ON number_of_segments

由数据库随机挑选 number 个 Primary 运行。

ON HOST

每台有 Primary 的主机上运行一次。

ON HOST segment_hostname

指定主机名上的 Primary 都运行一次。

SEGMENT segment_id

指定 ID 的 Primary 运行。ID 与

gp_segment_configuration系统表中的 content 字段一

致。-1 是 Master，且不能在此处指定。

基于 URL 的 WEB 型外部表

基于 URL 的 WEB 型外部表，使用 HTTP 协议来获取 WEB 服务器的数据，通过在

LOCATION 子句中指定[http://]开头的位置信息来实现。与 file 协议类似，URL

的数量不能多于 Primary 的数量，在访问该类型外部表时，会把这些 URL 分配给一些

Primary，每个 URL 只能由一个 Primary 来执行，每个 Primary 最多只能执行一个

URL。基于 URL 的 WEB 型外部表，只有可读外部表，没有可写外部表。

例如，使用 http 协议来访问 gpfdist 服务(gpfdist 实际上也是一个 WEB 服务)：

=# CREATE EXTERNAL WEB TABLE test_ext (a text)

LOCATION(

'http://mdw:8080/tmp/input.sh',

'http://mdw:8080/tmp/output.sh'

)

FORMAT 'text';

SELECT * FROM test_ext;

Greenplum Database (V6.2.1) - 5