Greenplum表存储模型
文章目录
1 背景
Greenplum支持行存和列存，支持堆表和AO表，那么他们有什么不同，如何选择呢？另外介绍一下压缩和非压缩表，同时如何修改表的分布，如何进行分布表的重新分布。修改表的存储类型。
2 堆表和AO表
2.1 堆表和AO表的原理
2.1.1 堆表
1、堆表，实际上就是PG的堆存储，堆表的所有变更都会产生REDO，可以实现时间点恢复。但是堆表不能实现逻辑增量备份（因为表的任意一个数据块都有可能变更，不方便通过堆存储来记录位点。） 堆表存储在OLTP类型负载下表现最好，这种环境中数据会在初始载入后被频繁地修改。 UPDATE和DELETE操作要求存储行级版本信息来确保可靠的数据库事务处理。堆表最适合于较小的表，例如维度表，它们在初始载入数据后会经常被更新。
一个事务结束时，通过clog以及REDO来实现它的可靠性。同时支持通过REDO来构建MIRROR节点实现数据冗余。
2.1.2 AO表
2、AO表，看名字就知道，只追加的存储，删除更新数据时，通过另一个BITMAP文件来标记被删除的行，通过bit以及偏移对齐来判定AO表上的某一行是否被删除。
追加优化表存储在数据仓库环境中的非规范表表现最好。 非规范表通常是系统中最大的表。 事实表通常成批地被载入并且被只读查询访问。 将大型的事实表改为追加优化存储模型可以消除每行中的更新可见性信息负担，这可以为每一行节约大概20字节。这可以得到一种更加简洁并且更容易优化的页面结构。 追加优化表的存储模型是为批量数据装载优化的，因此不推荐单行的INSERT语句。
事务结束时，需要调用FSYNC，记录最后一次写入对应的数据块的偏移。（并且这个数据块即使只有一条记录，下次再发起事务又会重新追加一个数据块）同时发送对应的数据块给MIRROR实现数据冗余。
因此AO表不适合小事务，因为每次事务结束都会FSYNC，同时事务结束后这个数据块即使有空余也不会被复用。（你可以测试一下，AO表单条提交的IO放大很严重）。
虽然如此，AO表非常适合OLAP场景，批量的数据写入，高压缩比，逻辑备份支持增量备份，因此每次记录备份到的偏移量即可。加上每次备份全量的BITMAP删除标记（很小）。
AO表，适合批量数据写入，不适合单行的insert,适合大表使用，所以一般用在数据仓库系统，适合OLAP系统.
2.2 什么时候选择堆表
当数据写入时，小事务偏多时选择堆表。
当需要时间点恢复时，选择堆表。
2.3 什么时候选择AO表
当需要列存时，选择AO表。
当数据批量写入时，选择AO表。
2.4 创建一个堆表
CREATE TABLE foo (a int, b text) DISTRIBUTED BY (a);
2.5 创建一个AO表
创建一个不带压缩的AO表
=> CREATE TABLE bar (a int, b text)
WITH (appendoptimized=true)
DISTRIBUTED BY (a);
Note: 使用appendoptimized=value语法来指定追加优化类型的表存储。 appendoptimized是appendonly传统存储选项的精简别名。 Greenplum数据库在catalog里存储appendonly，并在列出追加优化表的存储选项时显示相同的内容。
在一个可重复读或可序列化事务中的追加优化表上不允许UPDATE和DELETE，它们将导致该事务中止。追加优化表上不支持CLUSTER、DECLARE...FOR UPDATE和触发器。
3 行存和列存
Greenplum提供面向存储的模型选择：行，列或两者的组合。 本主题提供了为表选择最佳存储的一般准则。 使用您自己的数据和查询工作负载评估性能。
- 面向行的存储：适用于具有许多迭代事务的OLTP类型的工作负载以及一次需要多列的单行，因此检索是高效的。
- 面向列的存储：适合于在少量列上计算数据聚集的数据仓库负载，或者是用于需要对单列定期更新但不修改其他列数据的情况。
对于大部分常用目的或者混合负载，面向行的存储提供了灵活性和性能的最佳组合。 不过，也有场景中面向列的存储模型提供了更高效的I/O和存储。 在为一个表决定存储方向模型时，请考虑下列需求：
-
表数据的更新。
如果用户会频繁地装载和更新表数据，请选择一个面向行的堆表。
面向列的表存储只能用于追加优化表。
参考堆存储获得更多信息。
-
**频繁的插入。**如果频繁地向表中插入行，请考虑面向行的模型。列存表并未对写操作优化，因为一行的列值必须被写到磁盘上的不同位置。
-
查询中要求的列数。
如果在查询的SELECT列表或者WHERE子句中常常要求所有或者大部分列，请考虑面向行的模型。 面向列的表最适合的情况是，查询会聚集一个单一列中的很多值且WHERE或者HAVING谓词也在该聚集列上。例如：
SELECT SUM(salary)...SELECT AVG(salary)... WHERE salary > 10000或者面向列的情况是WHERE谓词在一个单一列上并且返回相对较少的行。例如：
SELECT salary, dept ... WHERE state='CA' -
**表中的列数。**在同时要求很多列或者表的行尺寸相对较小时，面向行的存储会更有效。对于具有很多列的表且查询中访问这些列的一个小子集时，面向列的表能够提供更好的查询性能。
-
**压缩。**列数据具有相同的数据类型，因此在列存数据上支持存储尺寸优化，但在行存数据上则不支持。 例如，很多压缩方案使用临近数据的相似性来进行压缩。 不过，临近压缩做得越好，随机访问就会越困难，因为必须解压数据才能读取它们。
3.1 行存和列存的原理
3.1.2 行存
1、行存，以行为形式组织存储，一行是一个tuple，存在一起。当需要读取某列时，需要将这列前面的所有列都进行deform，所以访问第一列和访问最后一列的成本实际上是不一样的。
在这篇文档中，有deform的详细介绍。《PostgreSQL 向量化执行插件(瓦片式实现) 10x提速OLAP》
行存小结：
全表扫描要扫描更多的数据块。
压缩比较低。
读取任意列的成本不一样，越靠后的列，成本越高。
不适合向量计算、JIT架构。（简单来说，就是不适合批处理形式的计算）
需要REWRITE表时，需要对全表进行REWRITE，例如加字段有默认值。
3.1.3 列存储
2、列存，以列为形式组织存储，每列对应一个或一批文件。读取任一列的成本是一样的，但是如果要读取多列，需要访问多个文件，访问的列越多，开销越大。
列存小结：
压缩比高。
仅仅支持AO存储（后面会将）。
读取任意列的成本是一样的。
非常适合向量计算、JIT架构。对大批量数据的访问和统计，效率更高。
读取很多列时，由于需要访问更多的文件，成本更高。例如查询明细。
需要REWRITE表时，不需要对全表操作，例如加字段有默认值，只是添加字段对应的那个文件。
3.2 什么时候选择行存
如果OLTP的需求偏多，例如经常需要查询表的明细（输出很多列），需要更多的更新和删除操作时。可以考虑行存。
3.3 什么时候选择列存
如果OLAP的需求偏多，经常需要对数据进行统计时，选择列存。
需要比较高的压缩比时，选择列存。
如果用户有混合需求，可以采用分区表，例如按时间维度的需求分区，近期的数据明细查询多，那就使用行存，对历史的数据统计需求多那就使用列存。
3.4 创建一个列存储表
CREATE TABLE命令的WITH子句指定表的存储选项。默认是面向行的堆表。使用面向列的存储的表必须是追加优化表。例如，要创建一个列存表：
=> CREATE TABLE bar (a int, b text)
WITH (appendoptimized=true, orientation=column)
DISTRIBUTED BY (a);
4 压缩（只适用于追加优化表）和非压缩
4.1 压缩的方式
对于追加优化表，在Greenplum数据库中有两种类型的库内压缩可用：
- 应用于一整个表的表级压缩。
- 应用到一个指定列的列级压缩。用户可以为不同的列应用不同的列级压缩算法。
4.2 压缩的算法
下面的表总结了可用的压缩算法。
| 表方向 | 可用的压缩类型 | 支持的算法 |
|---|---|---|
| 行 | 表 | ZLIB, ZSTD和 QUICKLZ1 |
| 列 | 列和表 | RLE_TYPE, ZLIB, ZSTD和 QUICKLZ1 |
Note: 1QuickLZ压缩在Greenplum数据库的开源版本中不可用。
4.3 压缩因素
在为追加优化表选择一种压缩类型和级别时，要考虑这些因素：
-
CPU使用。用户的Segment系统必须具有可用的CPU能力来压缩和解压数据。
-
压缩率/磁盘尺寸。最小化磁盘尺寸是一个因素，但也要考虑压缩和扫描数据所需的时间和CPU计算能力。要找到能高效压缩数据但不导致过长压缩时间或者过慢扫描率的最优设置。
-
压缩的速度。与zlib比较，QuickLZ在较低的压缩率下通常使用较少的CPU计算能力、能更快地压缩数据。zlib提供更高的压缩率，但是速度较慢。
例如，在压缩级别1上（compresslevel=1），QuickLZ和zlib具有差不多的压缩率，但是压缩速度不同。 使用compresslevel=6的zlib能比QuickLZ更显著地提升压缩率，但是压缩速度更慢。ZStandard压缩可以提供良好的压缩比或速度，具体取决于压缩级别，或两者的良好折衷。
-
解压速度/扫描率。压缩的追加优化表的性能取决于硬件、查询调优设置和其他因素。请执行对比测试来判断在用户的环境中的真实性能。
Note: 不要在使用压缩的文件系统上创建压缩的追加优化表。如果用户的Segment数据目录所在的文件系统是一个压缩的文件系统，用户的追加优化表不能使用压缩。
压缩的追加优化表的性能取决于硬件、查询调优设置和其他因素。请执行对比测试来判断在用户的环境中的真实性能。
Note: Zstd压缩级别可以设置为1-19。
QuickLZ的压缩级别只能被设置为级别1，没有其他的选项可用。
zlib的压缩级别可以设置为1-9的值。 RLE的压缩级别可以设置为1-4的值。
ENCODING子句指定个别列的压缩类型和级别。
当ENCODING子句与WITH子句冲突时，ENCODING子句的优先级高于WITH子句。
4.4 创建一个压缩表(表级别)
CREATE TABLE命令的WITH子句声明表的存储选项。使用压缩技术的表必须是追加优化表。 例如，要创建一个使用zlib压缩且压缩级别为5的追加优化表：
=> CREATE TABLE foo (a int, b text)
WITH (appendoptimized=true, compresstype=zlib, compresslevel=5);
4.5 检查追加优化表的压缩和分布
Greenplum提供了内建函数来检查一个追加优化表的压缩率和分布情况。这些函数要求对象ID或者表名作为参数。用户可以用schema名来限定表名。
| 函数 | 返回类型 | 描述 |
|---|---|---|
| get_ao_distribution(name)get_ao_distribution(oid) | (dbid, tuplecount)行的集合 | 展示一个追加优化表的行在阵列中的分布情况。 返回一个行集合，其中每一个行包括了一个节点dbid以及存储在其中的元组数。 |
| get_ao_compression_ratio(name)get_ao_compression_ratio(oid) | float8 | 为一个压缩的追加优化表计算压缩率。如果该信息不可用，这个函数会返回-1。 |
压缩比作为公共比率返回。 例如，返回值3.19或者3.19:1表示解压后的表比压缩表的尺寸的3倍略大。
表的分布被返回为一个行集合，它们反映了每个Segment上存储了多少元组。 例如，在一个具有四个Primary Segment（dbid值从0-3）的系统中，该函数返回与以下类似的四个行：
=# SELECT get_ao_distribution('lineitem_comp');
get_ao_distribution
---------------------
(0,7500721)
(1,7501365)
(2,7499978)
(3,7497731)
(4 rows)
4.6 游程编码支持
Greenplum数据库对列级压缩支持游程编码（RLE）。 RLE数据压缩把重复的数据存储为一个单一的数据值和一个计数。例如，在有两个列date和description的表中，它包含200,000个含有值date1的项以及400,000个含有值date2的项，对这个date域的RLE压缩会类似于date1 200000 date2 400000。 RLE对于没有大量重复数据集合的文件来说用处不大，因为它会大幅度增加这种文件的尺寸。
有四种级别的RLE压缩可用。这些级别逐步增加了压缩率，但是同时也会降低压缩速度。
Greenplum数据库4.2.1及其后的版本支持面向列的RLE压缩。如果要备份一个用了RLE压缩的表用来恢复到一个早期版本的Greenplum数据库中， 应在开始备份操作前修改该表为不采用压缩或者采用早期版本中支持的压缩类型（ZLIB或者QUICKLZ）。
Greenplum数据库为BIGINT、INTEGER、DATE、TIME或者TIMESTAMP列中的数据的RLE压缩结合了增量压缩。 该增量压缩算法基于连续列值之间的变化来进行压缩，它的设计目的是在以排序顺序装载数据时或者对已排序数据应用压缩时改进压缩性能。
4.7 列压缩
4.7.1 增加列级压缩
用户可以为列存追加优化表的列增加下列存储指令：
- 压缩类型
- 压缩级别
- 列的块尺寸
使用CREATE TABLE、ALTER TABLE以及CREATE TYPE命令增加存储指令。
下面的表格详细介绍了存储指令的类型以及每一个指令可能的值。
| 名称 | 定义 | 值 | 注释 |
|---|---|---|---|
| compresstype | 压缩的类型。 | zstd: ZStandard算法 zlib: 缩小算法quicklz: 快速算法RLE_TYPE: 游程编码none: 无压缩 | 值不区分大小写。 |
| compresslevel | 压缩级别。 | zlib压缩： 1-9 | 1是最快的方法但压缩率最低。1是默认值。9是最慢的方法但压缩率最高。 |
| zstd压缩： 1-19 | 1是最快的方法但压缩率最低。1是默认值。 19是最慢的方法但压缩率最高。 | ||
| QuickLZ压缩：1 – 使用压缩 | 1是默认值。 | ||
| RLE_TYPE压缩：1 – 41 - 只应用RLE2 - 应用RLE然后应用zlib压缩级别13 - 应用RLE然后应用zlib压缩级别54 - 应用RLE然后应用zlib压缩级别9 | 1是最快的方法但压缩率最低。 4是最慢的方法但压缩率最高。 1是默认值。 | ||
| blocksize | 表中每一块的以字节计的尺寸 | 8192 – 2097152 | 该值必须是8192的倍数。 |
下面是增加存储指令的格式。
[ ENCODING ( storage_directive [,…] ) ]
其中单词ENCODING是必需的并且存储指令有三个部分：
- 指令的名称
- 一个等号
- 参数
多个存储指令用逗号分隔。 如下面的CREATE TABLE子句所示，可以把一个存储指令应用到单一列或者把它作为所有列的默认指令。
一般用法：
column_name data_type ENCODING ( storage_directive [, … ] ), …
COLUMN column_name ENCODING ( storage_directive [, … ] ), …
DEFAULT COLUMN ENCODING ( storage_directive [, … ] )
示例：
C1 char ENCODING (compresstype=quicklz, blocksize=65536)
COLUMN C1 ENCODING (compresstype=zlib, compresslevel=6, blocksize=65536)
DEFAULT COLUMN ENCODING (compresstype=quicklz)
4.7.2 默认压缩值
如果没有定义压缩类型、压缩级别和块尺寸，默认是无压缩并且块尺寸被设置为服务器配置参数block_size。
4.7.3 压缩设置的优先级
列压缩设置从表级继承到分区级，再到子分区级。最低级别上的设置优先。
- 表级定义的列压缩设置会覆盖该类型的任何压缩设置。
- 表级指定的列压缩设置会覆盖整个表的任何压缩设置。
- 为分区指定的列压缩设置会覆盖列级或表级别的任何压缩设置。
- 为子分区指定的列压缩设置会覆盖分区，列或表级别的任何压缩设置。
- 当ENCODING子句与WITH子句冲突时，ENCODING子句的优先级高于WITH子句。
Note: 在一个含有存储指令或者列引用存储指令的表中不允许INHERITS子句。
使用LIKE子句创建的表忽略存储指令以及列引用存储指令。
4.7.4列压缩设置的最佳位置
最佳做法是在数据所在的层次上设置列压缩设置。 参考例 5，它展示了一个分区深度为2的表。 RLE_TYPE压缩在子分区层次上被增加到一个列。
4.7.5 存储指令示例
下面的例子展示了在CREATE TABLE语句中存储指令的使用。
例 1
在这个例子中，列c1被使用zstd压缩并且使用系统中定义的块尺寸。 列c2用quicklz压缩并且使用的块尺寸为65536。列c3没有被压缩并且使用系统中定义的块尺寸。
CREATE TABLE T1 (c1 int ENCODING (compresstype=zstd),
c2 char ENCODING (compresstype=quicklz, blocksize=65536),
c3 char) WITH (appendoptimized=true, orientation=column);
例 2
在这个例子中，列c1使用zlib压缩并且使用系统中定义的块尺寸。 列c2使用quicklz压缩，并且使用的块尺寸为65536。列c3使用RLE_TYPE压缩并且使用系统中定义的块尺寸。
CREATE TABLE T2 (c1 int ENCODING (compresstype=zlib),
c2 char ENCODING (compresstype=quicklz, blocksize=65536),
c3 char,
COLUMN c3 ENCODING (compresstype=RLE_TYPE)
)
WITH (appendoptimized=true, orientation=column);
例 3
在这个例子中，列c1使用zlib压缩并且使用系统中定义的块尺寸。列c2使用quicklz压缩，并且使用的块尺寸为65536。 列c3使用zlib压缩并且使用系统中定义的块尺寸。注意，列c3在分区中使用zlib（而不是RLE_TYPE），因为在分区子句中的列存储比表的列定义中的存储指令优先级高。
CREATE TABLE T3 (c1 int ENCODING (compresstype=zlib),
c2 char ENCODING (compresstype=quicklz, blocksize=65536),
c3 text, COLUMN c3 ENCODING (compresstype=RLE_TYPE) )
WITH (appendoptimized=true, orientation=column)
PARTITION BY RANGE (c3) (START ('1900-01-01'::DATE)
END ('2100-12-31'::DATE),
COLUMN c3 ENCODING (compresstype=zlib));
例 4
在这个例子中，CREATE TABLE把zlib压缩类型存储指令分配给c1。列c2没有存储指令并且从DEFAULT COLUMN ENCODING子句继承了压缩类型（quicklz）和块尺寸（65536）。
列c3的ENCODING子句定义其压缩类型RLE_TYPE。为特定列定义的ENCODING子句会覆盖DEFAULT ENCODING子句，因此列c3使用默认块大小32768。
列c4的压缩类型为none，并使用默认块大小。
CREATE TABLE T4 (c1 int ENCODING (compresstype=zlib),
c2 char,
c3 text,
c4 smallint ENCODING (compresstype=none),
DEFAULT COLUMN ENCODING (compresstype=quicklz,
blocksize=65536),
COLUMN c3 ENCODING (compresstype=RLE_TYPE)
)
WITH (appendoptimized=true, orientation=column);
例 5
这个例子创建一个追加优化的列存表T5。T5有两个分区p1和p2，每一个都有子分区。 每一个子分区都有ENCODING子句：
-
分区p1的子分区sp1的ENCODING子句定义了列i的压缩类型是zlib并且块尺寸是65536。
-
分区p2的子分区sp1的ENCODING子句定义了列i的压缩类型是rle_type并且块尺寸为默认值。 列
k使用默认压缩并且其块尺寸为8192。
CREATE TABLE T5(i int, j int, k int, l int) WITH (appendoptimized=true, orientation=column) PARTITION BY range(i) SUBPARTITION BY range(j) ( partition p1 start(1) end(2) ( subpartition sp1 start(1) end(2) column i encoding(compresstype=zlib, blocksize=65536) ), partition p2 start(2) end(3) ( subpartition sp1 start(1) end(2) column i encoding(compresstype=rle_type) column k encoding(blocksize=8192) ) );
用ALTER TABLE命令把一个压缩列增加到现有表的例子，请见为表增加一个压缩列。
4.7.6 在TYPE命令中增加压缩
创建新类型时，可以为该类型定义默认压缩属性。 例如，以下CREATE TYPE命令定义了一个名为int33的类型，它指定了quicklz压缩：
CREATE TYPE int33 (
internallength = 4,
input = int33_in,
output = int33_out,
alignment = int4,
default = 123,
passedbyvalue,
compresstype="quicklz",
blocksize=65536,
compresslevel=1
);
在CREATE TABLE命令中将int33指定为列类型时，将使用您为该类型指定的存储指令创建该列：
CREATE TABLE t2 (c1 int33)
WITH (appendoptimized=true, orientation=column);
您在表定义中指定的表级或列级存储属性会覆盖类型级存储属性。有关为类型创建和添加压缩属性的信息，请参阅CREATE TYPE。 有关更改类型中的压缩规范的信息，请参阅ALTER TYPE。
4.7.7 选择块尺寸
blocksize是一个表中每一块的尺寸，以字节计。块尺寸必须介于8192字节和2097152字节之间，并且必须是8192的倍数。默认是32768。
指定大的块尺寸可能会消耗大量的内存。 块大小决定了存储层中的缓存。 在面向列的表中，Greenplum维护为每个分区每个列维护了一个缓存。具有许多分区或列的表占用大量内存。
5 测试对比行存deform和列存的性能差别
1、创建一个函数，用于创建400列的表（行存堆表、AO行存表、AO列存表）。
create or replace function f(name, int, text) returns void as $$
declare
res text := '';
begin
for i in 1..$2 loop
res := res||'c'||i||' int8,';
end loop;
res := rtrim(res, ',');
if $3 = 'ao_col' then
res := 'create table '||$1||'('||res||') with (appendonly=true, blocksize=8192, compresstype=none, orientation=column)';
elsif $3 = 'ao_row' then
res := 'create table '||$1||'('||res||') with (appendonly=true, blocksize=8192, orientation=row)';
elsif $3 = 'heap_row' then
res := 'create table '||$1||'('||res||') with (appendonly=false)';
else
raise notice 'use ao_col, ao_row, heap_row as $3';
return;
end if;
execute res;
end;
$$ language plpgsql;
2、创建表如下
postgres=# select f('tbl_ao_col', 400, 'ao_col');
postgres=# select f('tbl_ao_row', 400, 'ao_row');
postgres=# select f('tbl_heap_row', 400, 'heap_row');
3、创建1个函数，用于填充数据，其中第一个和最后3个字段为测试数据的字段，其他都填充1。
create or replace function f_ins1(name, int, int8) returns void as $$
declare
res text := '';
begin
for i in 1..($2-4) loop
res := res||'1,';
end loop;
res := 'id,'||res;
res := rtrim(res, ',');
res := 'insert into '||$1||' select '||res||'id,random()*10000,random()*100000 from generate_series(1,'||$3||') t(id)';
execute res;
end;
$$ language plpgsql;
4、填充数据
postgres=# select f_ins1('tbl_ao_col',400,1000000);
5、创建1个函数，用于填充数据，其中前4个字段为测试数据的字段，其他都填充1。
create or replace function f_ins2(name, int, int8) returns void as $$
declare
res text := '';
begin
for i in 1..($2-4) loop
res := res||'1,';
end loop;
res := 'id,id,random()*10000,random()*100000,'||res;
res := rtrim(res, ',');
res := 'insert into '||$1||' select '||res||' from generate_series(1,'||$3||') t(id)';
execute res;
end;
$$ language plpgsql;
6、填充数据
postgres=# select f_ins1('tbl_ao_col',400,1000000);
f_ins1
--------
(1 row)
postgres=# insert into tbl_ao_row select * from tbl_ao_col;
INSERT 0 1000000
postgres=# insert into tbl_heap_row select * from tbl_ao_col;
INSERT 0 1000000
7、表分析
postgres=# analyze tbl_ao_col ;
ANALYZE
postgres=# analyze tbl_ao_row;
ANALYZE
postgres=# analyze tbl_heap_row;
ANALYZE
8、表大小
postgres=# select pg_size_pretty(pg_relation_size('tbl_ao_col'));
pg_size_pretty
----------------
3060 MB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('tbl_ao_row'));
pg_size_pretty
----------------
3117 MB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('tbl_heap_row'));
pg_size_pretty
----------------
3473 MB
(1 row)
9、行存堆表，前面几个字段的统计
postgres=# explain analyze select c2,count(*),sum(c3),avg(c3),min(c3),max(c3) from tbl_heap_row group by c2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 48:1 (slice2; segments: 48) (cost=136132.40..136132.42 rows=1 width=96)
Rows out: 1 rows at destination with 135 ms to end, start offset by 1.922 ms.
-> HashAggregate (cost=136132.40..136132.42 rows=1 width=96)
Group By: tbl_heap_row.c2
Rows out: 1 rows (seg42) with 0.002 ms to first row, 36 ms to end, start offset by 48 ms.
-> Redistribute Motion 48:48 (slice1; segments: 48) (cost=136132.35..136132.37 rows=1 width=96)
Hash Key: tbl_heap_row.c2
Rows out: 48 rows at destination (seg42) with 53 ms to end, start offset by 48 ms.
-> HashAggregate (cost=136132.35..136132.35 rows=1 width=96)
Group By: tbl_heap_row.c2
Rows out: Avg 1.0 rows x 48 workers. Max 1 rows (seg0) with 0.008 ms to first row, 1.993 ms to end, start offset by 48 ms.
-> Seq Scan on tbl_heap_row (cost=0.00..121134.54 rows=20831 width=16)
Rows out: Avg 20833.3 rows x 48 workers. Max 20854 rows (seg42) with 40 ms to first row, 73 ms to end, start offset by 50 ms.
Slice statistics:
(slice0) Executor memory: 345K bytes.
(slice1) Executor memory: 751K bytes avg x 48 workers, 751K bytes max (seg0).
(slice2) Executor memory: 359K bytes avg x 48 workers, 374K bytes max (seg42).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=off
Optimizer status: legacy query optimizer
Total runtime: 138.524 ms
(22 rows)
10、行存堆表，末尾几个字段的统计
postgres=# explain analyze select c398,count(*),sum(c399),avg(c399),min(c399),max(c399) from tbl_heap_row group by c398;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 48:1 (slice2; segments: 48) (cost=136576.82..136799.05 rows=9877 width=96)
Rows out: 10001 rows at destination with 212 ms to end, start offset by 1.917 ms.
-> HashAggregate (cost=136576.82..136799.05 rows=206 width=96)
Group By: tbl_heap_row.c398
Rows out: Avg 208.4 rows x 48 workers. Max 223 rows (seg17) with 0.001 ms to first row, 70 ms to end, start offset by 14 ms.
-> Redistribute Motion 48:48 (slice1; segments: 48) (cost=136132.35..136329.89 rows=206 width=96)
Hash Key: tbl_heap_row.c398
Rows out: Avg 8762.2 rows x 48 workers at destination. Max 9422 rows (seg46) with 93 ms to end, start offset by 48 ms.
-> HashAggregate (cost=136132.35..136132.35 rows=206 width=96)
Group By: tbl_heap_row.c398
Rows out: Avg 8762.2 rows x 48 workers. Max 8835 rows (seg2) with 0.003 ms to first row, 12 ms to end, start offset by 49 ms.
-> Seq Scan on tbl_heap_row (cost=0.00..121134.54 rows=20831 width=16)
Rows out: Avg 20833.3 rows x 48 workers. Max 20854 rows (seg42) with 40 ms to first row, 133 ms to end, start offset by 51 ms.
Slice statistics:
(slice0) Executor memory: 377K bytes.
(slice1) Executor memory: 1156K bytes avg x 48 workers, 1156K bytes max (seg0).
(slice2) Executor memory: 414K bytes avg x 48 workers, 414K bytes max (seg1).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=off
Optimizer status: legacy query optimizer
Total runtime: 214.024 ms
(22 rows)
11、行存AO表，前面几个字段的统计
postgres=# explain analyze select c2,count(*),sum(c3),avg(c3),min(c3),max(c3) from tbl_ao_row group by c2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 48:1 (slice2; segments: 48) (cost=124755.04..124755.07 rows=1 width=96)
Rows out: 1 rows at destination with 149 ms to end, start offset by 1.890 ms.
-> HashAggregate (cost=124755.04..124755.07 rows=1 width=96)
Group By: tbl_ao_row.c2
Rows out: 1 rows (seg42) with 0.004 ms to first row, 55 ms to end, start offset by 64 ms.
-> Redistribute Motion 48:48 (slice1; segments: 48) (cost=124755.00..124755.02 rows=1 width=96)
Hash Key: tbl_ao_row.c2
Rows out: 48 rows at destination (seg42) with 32 ms to end, start offset by 64 ms.
-> HashAggregate (cost=124755.00..124755.00 rows=1 width=96)
Group By: tbl_ao_row.c2
Rows out: Avg 1.0 rows x 48 workers. Max 1 rows (seg0) with 0.001 ms to first row, 46 ms to end, start offset by 59 ms.
-> Append-only Scan on tbl_ao_row (cost=0.00..109755.00 rows=20834 width=16)
Rows out: Avg 20833.3 rows x 48 workers. Max 20854 rows (seg42) with 24 ms to end, start offset by 59 ms.
Slice statistics:
(slice0) Executor memory: 345K bytes.
(slice1) Executor memory: 770K bytes avg x 48 workers, 770K bytes max (seg0).
(slice2) Executor memory: 359K bytes avg x 48 workers, 374K bytes max (seg42).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=off
Optimizer status: legacy query optimizer
Total runtime: 152.386 ms
(22 rows)
12、行存AO表，末尾几个字段的统计
postgres=# explain analyze select c398,count(*),sum(c399),avg(c399),min(c399),max(c399) from tbl_ao_row group by c398;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 48:1 (slice2; segments: 48) (cost=125186.01..125401.52 rows=9578 width=96)
Rows out: 10001 rows at destination with 183 ms to end, start offset by 1.846 ms.
-> HashAggregate (cost=125186.01..125401.52 rows=200 width=96)
Group By: tbl_ao_row.c398
Rows out: Avg 208.4 rows x 48 workers. Max 223 rows (seg17) with 0.003 ms to first row, 97 ms to end, start offset by 22 ms.
-> Redistribute Motion 48:48 (slice1; segments: 48) (cost=124755.00..124946.56 rows=200 width=96)
Hash Key: tbl_ao_row.c398
Rows out: Avg 8762.2 rows x 48 workers at destination. Max 9422 rows (seg46) with 32 ms to end, start offset by 68 ms.
-> HashAggregate (cost=124755.00..124755.00 rows=200 width=96)
Group By: tbl_ao_row.c398
Rows out: Avg 8762.2 rows x 48 workers. Max 8835 rows (seg2) with 0.013 ms to first row, 48 ms to end, start offset by 22 ms.
-> Append-only Scan on tbl_ao_row (cost=0.00..109755.00 rows=20834 width=16)
Rows out: Avg 20833.3 rows x 48 workers. Max 20854 rows (seg42) with 22 ms to end, start offset by 71 ms.
Slice statistics:
(slice0) Executor memory: 377K bytes.
(slice1) Executor memory: 1144K bytes avg x 48 workers, 1144K bytes max (seg0).
(slice2) Executor memory: 414K bytes avg x 48 workers, 414K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=off
Optimizer status: legacy query optimizer
Total runtime: 184.723 ms
(22 rows)
13、列存AO表，前面几个字段的统计
postgres=# explain analyze select c2,count(*),sum(c3),avg(c3),min(c3),max(c3) from tbl_ao_col group by c2;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 48:1 (slice2; segments: 48) (cost=122928.04..122928.07 rows=1 width=96)
Rows out: 1 rows at destination with 104 ms to end, start offset by 1.878 ms.
-> HashAggregate (cost=122928.04..122928.07 rows=1 width=96)
Group By: tbl_ao_col.c2
Rows out: 1 rows (seg42) with 0.003 ms to first row, 18 ms to end, start offset by 55 ms.
-> Redistribute Motion 48:48 (slice1; segments: 48) (cost=122928.00..122928.02 rows=1 width=96)
Hash Key: tbl_ao_col.c2
Rows out: 48 rows at destination (seg42) with 30 ms to end, start offset by 55 ms.
-> HashAggregate (cost=122928.00..122928.00 rows=1 width=96)
Group By: tbl_ao_col.c2
Rows out: Avg 1.0 rows x 48 workers. Max 1 rows (seg0) with 0.007 ms to first row, 3.991 ms to end, start offset by 54 ms.
-> Append-only Columnar Scan on tbl_ao_col (cost=0.00..107928.00 rows=20834 width=16)
Rows out: 0 rows (seg0) with 40 ms to end, start offset by 56 ms.
Slice statistics:
(slice0) Executor memory: 345K bytes.
(slice1) Executor memory: 903K bytes avg x 48 workers, 903K bytes max (seg0).
(slice2) Executor memory: 359K bytes avg x 48 workers, 374K bytes max (seg42).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=off
Optimizer status: legacy query optimizer
Total runtime: 106.859 ms
(22 rows)
14、列存AO表，末尾几个字段的统计
postgres=# explain analyze select c398,count(*),sum(c399),avg(c399),min(c399),max(c399) from tbl_ao_col group by c398;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 48:1 (slice2; segments: 48) (cost=123364.18..123582.28 rows=9693 width=96)
Rows out: 10001 rows at destination with 120 ms to end, start offset by 1.921 ms.
-> HashAggregate (cost=123364.18..123582.28 rows=202 width=96)
Group By: tbl_ao_col.c398
Rows out: Avg 208.4 rows x 48 workers. Max 223 rows (seg17) with 0.001 ms to first row, 54 ms to end, start offset by 35 ms.
-> Redistribute Motion 48:48 (slice1; segments: 48) (cost=122928.00..123121.86 rows=202 width=96)
Hash Key: tbl_ao_col.c398
Rows out: Avg 8762.2 rows x 48 workers at destination. Max 9422 rows (seg46) with 31 ms to end, start offset by 63 ms.
-> HashAggregate (cost=122928.00..122928.00 rows=202 width=96)
Group By: tbl_ao_col.c398
Rows out: Avg 8762.2 rows x 48 workers. Max 8835 rows (seg2) with 0.004 ms to first row, 8.004 ms to end, start offset by 82 ms.
-> Append-only Columnar Scan on tbl_ao_col (cost=0.00..107928.00 rows=20834 width=16)
Rows out: 0 rows (seg0) with 28 ms to end, start offset by 64 ms.
Slice statistics:
(slice0) Executor memory: 377K bytes.
(slice1) Executor memory: 1272K bytes avg x 48 workers, 1272K bytes max (seg0).
(slice2) Executor memory: 414K bytes avg x 48 workers, 414K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=off
Optimizer status: legacy query optimizer
Total runtime: 122.173 ms
(22 rows)
对于非分布键的分组聚合请求，Greenplum采用了多阶段聚合如下：
第一阶段，在SEGMENT本地聚合。（需要扫描所有数据，这里不同存储，前面的列和后面的列的差别就体现出来了，行存储的deform开销，在对后面的列进行统计时性能影响很明显。） Greenplum会根据字段的distinct值的比例，考虑是直接重分布数据，还是先在本地聚合后再重分布数据（减少重分布的数据量）。
第二阶段，根据分组字段，将结果数据重分布。（重分布需要用到的字段，此时结果很小。）
第三阶段，再次在SEGMENT本地聚合。（需要对重分布后的数据进行聚合。）
第四阶段，返回结果给master，有必要的话master节点调用聚合函数的final func（已经是很少的记录数和运算量）。
6 如何查看表的存储结构
pg_class.relstorage表示这个对象是什么存储：
postgres=# select distinct relstorage from pg_class ;
relstorage
------------
a -- 行存储AO表
h -- heap堆表、索引
x -- 外部表(external table)
v -- 视图
c -- 列存储AO表
(5 rows)
查询当前数据库有哪些AO表：
postgres=# select t2.nspname, t1.relname from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and relstorage in ('c', 'a');
nspname | relname
----------+-------------------
postgres | tbl_tag
postgres | tbl_pos_1_prt_p1
postgres | tbl_pos_1_prt_p2
postgres | tbl_pos_1_prt_p3
postgres | tbl_pos_1_prt_p4
postgres | tbl_pos_1_prt_p5
postgres | tbl_pos_1_prt_p6
postgres | tbl_pos_1_prt_p7
postgres | tbl_pos_1_prt_p8
postgres | tbl_pos_1_prt_p9
postgres | tbl_pos_1_prt_p10
postgres | tbl_pos
postgres | xx_czrk_qm_col
postgres | ao1
(14 rows)
查询当前数据库有哪些堆表：
select t2.nspname, t1.relname from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and relstorage in ('h') and relkind='r';
7 更改分布策略
对于已分区的表，对于分布策略的更改会递归地应用到子分区上。 这种操作会保留拥有关系和该表的所有其他属性。例如，下列命令使用customer_id列作为分布键在所有Segment之间重新分布表sales：
ALTER TABLE sales SET DISTRIBUTED BY (customer_id);
在用户改变一个表的哈希分布时，表数据会被自动重新分布。把分布策略改成随机分布不会导致数据被重新分布。例如，下面的ALTER TABLE命令不会立刻产生效果：
ALTER TABLE sales SET DISTRIBUTED RANDOMLY;
将表的分布策略更改为DISTRIBUTED REPLICATED或从DISTRIBUTED REPLICATED修改为其他分布，会自动重新分配表数据。
8 重新分布表的数据
要用一种随机分布策略（或者当哈希分布策略没有被更改时）对表重新分布数据，可使用REORGANIZE=TRUE。 重新组织数据对于更正一个数据倾斜问题是必要的，当系统中增加了Segment资源后也需要重新组织数据。 例如，下面的命令会使用当前的分布策略（包括随机分布）在所有Segment上重新分布数据。
ALTER TABLE sales SET WITH (REORGANIZE=TRUE);
将表的分布策略更改为DISTRIBUTED REPLICATED或从DISTRIBUTED REPLICATED改为其它总是重新分配表数据 即使REORGANIZE=FALSE也是如此。
9 修改表的存储模型
表存储、压缩和行列类型只能在创建时声明。 要改变存储模型，用户必须用正确的存储选项创建一个表，再把原始表的数据载入到新表中，接着删除原始表并且把新表重命名为原始表的名称。 用户还必须重新授权原始表上有的权限。例如：
CREATE TABLE sales2 (LIKE sales)
WITH (appendoptimized=true, compresstype=quicklz,
compresslevel=1, orientation=column);
INSERT INTO sales2 SELECT * FROM sales;
DROP TABLE sales;
ALTER TABLE sales2 RENAME TO sales;
GRANT ALL PRIVILEGES ON sales TO admin;
GRANT SELECT ON sales TO guest;
10 为表增加一个压缩列
使用ALTER TABLE命令为一个表增加一个压缩列。 所有在增加列级压缩中描述的用于压缩列的选项和约束都适用于用ALTER TABLE命令增加的列。
下面的例子展示了如何向一个表中增加一个使用zlib压缩的列T1。
ALTER TABLE T1
ADD COLUMN c4 int DEFAULT 0
ENCODING (compresstype=zlib);
11 压缩设置的继承
如果一个表有子分区且子分区带有压缩设置，则向该表增加的一个分区会从子分区继承那些压缩设置。下面的例子展示了如何创建一个有子分区编码的表，然后修改它来增加一个分区。
CREATE TABLE ccddl (i int, j int, k int, l int)
WITH
(appendoptimized = TRUE, orientation=COLUMN)
PARTITION BY range(j)
SUBPARTITION BY list (k)
SUBPARTITION template(
SUBPARTITION sp1 values(1, 2, 3, 4, 5),
COLUMN i ENCODING(compresstype=ZLIB),
COLUMN j ENCODING(compresstype=QUICKLZ),
COLUMN k ENCODING(compresstype=ZLIB),
COLUMN l ENCODING(compresstype=ZLIB))
(PARTITION p1 START(1) END(10),
PARTITION p2 START(10) END(20))
;
ALTER TABLE ccddl
ADD PARTITION p3 START(20) END(30)
;
运行ALTER TABLE命令会创建表ccddl的名为ccddl_1_prt_p3和ccddl_1_prt_p3_2_prt_sp1的分区。分区ccddl_1_prt_p3继承了子分区sp1的不同的压缩编码。
356
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
