GreenPlum企业应用实践--第五章SQL执行计划
-------------------------------------------------------------------------------------------------------------------------
2018/07/11 wx
注解：位图扫描也是一种走索引的方式。方法是扫描索引，把满足条件的行或块在内存中建一个位图，扫描完索引后，再根据位图到表的数据文件把相应的数据读出来。如果走两个索引，可以把两个索引形成的位图进行 and /or计算合成一个位图。再到表的数据文件把数据读出来。
2018/10/08
“对于是需要使用的表如果分布键是粗粒度的使用BitMap索引，如果细粒度的使用BTree索引。”
何为粗细粒度？
记录值相同值的数量。
f b ：“值分布比较均匀的走bitmap会慢”？ 比如glassID就是分布比较均匀的数据，即就是重复值多的字段。实际业务中glassID的重复值就很少。
--什么时候会发生Bitmap Heap Scan
explain select relkind,* from pg_class_temp where relkind = 'c'
--pg_class中的relkind字段，重复值很多，在这个字段上建立索引
create index pg_class_relkind_idx on pg_class_temp(relkind);
--enable_seqscan禁止顺序扫描，确保执行计划是通过pg_class_relkind_idx来查询数据
set enable_seqscan = off;
--relkind重复值很多，因此通过位图索引的方式来查询数据
-- -> Bitmap Index Scan on pg_class_relkind_idx (cost=0.00..100.28 rows=1 width=0)
-------------------------------------------------------------------------------------------------------------------------------------------------------
2018-08-07
5.3.2分布式执行
1、gather motion(N:1)
聚合操作，在master上将子节点所有的数据聚合起来。一般的聚合规则是：哪一个节点的数据先返回到maser上就将该节点的数据先放在master上。例如 简单的sum。不含有group by
2、broadcast motion（N:N）
广播，将每个segment上某一个表的数据全部发送给所有segment。这样每一个segment都相当于有一份全量数据，广播基本只会出现在两个表关联的时候，相关内容再选择广播或者重分布。
3、redistribute motion(N:N)
当需要做跨库关联或者聚合的时候，当数据不能满足广播的条件，或者广播的消耗过大时，greenplum就会选择重分布数据，即按照新的分布键(关联健)重新打散到每个segment上，重分布一般会在一下三种情况下发生：（9/28 如果两表关联键为分布键的时候数据打散到每个节点的速度相对较快，如果关联健非分布键，效率上会有影响。）
①关联：将每个segment的数据根据关联健重新计算hash值，并根据GP的路由算法由到目标节点中，使关联时属于同一个关联健的数据都在同一个segment上。
②group by：当表需要group by，但是group by的字段不是分布键时，为了使group by的字段在同一个库中。GP会分两个group by操作来执行，首先，在单个库上执行一个group by操作，从而减少要重分布的数据量；然后将结果数据按照group by字段重复分布，之后再做聚合获得最终结果。
3、开窗函数：跟group by类似，开窗函数的实现也需要将结果数据充分不到每个节点上进行计算，不过其实现比group by更复杂一些。
-------------------------------------------------
4、切片(Slice)
GP的重要感念：slice，每一个广播或者重分布都会产生一个slice，每个slice在每个数据节点上都会发起一个进程来处理该slice负责的数据。上一层负责该slice的进程会读取下级slice广播或者重分布的数据，然后进行计算。
GP在实现分布式执行计划的时候，需要将SQL拆分成多个切片，每一个slice其实是单库执行的一部分SQL，上面的每一个motion都会导致GP多一个slice操作，而每一个slice操作子节点都会发起一个进程来处理数据。
所以应该尽量控制slice的个数，将太复杂的SQL拆分，减少进程数，在执行计划中，最常见的slice关键字的地方就是广播和重分布。
------------------------------------------------------------------------------------------------------
5.3.3 聚合的两种方式
当用到聚合函数的时候一般会用到两种聚合方式 hash聚合和group聚合
①hash 聚合
② group 聚合
关联方式：
5.3.5 SQL执行计划的相关术语 教你如何看懂SQL的执行计划
1、cost: 数据库自定义的消耗单位，具体消耗单位以官方文档为准
2、Rows: 根据统计信息估计SQL返回结果集的行数
3、width：返回结果集每一行的长度，这个长度值是pg_statistic表的统计信息来计算
4、filter：where筛选条件，在执行计划中就是filter关键字
5、index cond(condition) 如果在where的筛选条件的字段有index，那么执行计划会通过index定位，提高查询效率，index cond就是定位索引的条件
6、recheck cond在使用位图索引的时候，由于postersql里面使用的是MVCC协议，为了保证结果的正确性，要重新检查一下过滤条件。
7、hash cond 执行hash join的时候的关联条件
8、merge在执行排序操作时数据会在子节点上各自排好序，然后在master节点上做一个归并操作。
9、hash key在数据重分布时指定重算hash值的分布键
10、
11、join filter 对数据进行关联后再进行筛选
12、sort / sort key 排序的操作排序字段为 sort key
13、Windows，partition by，order by
HashAggregate (cost=2726.44..2773.45 rows=3761 width=12)
Group By: oid
-> Seq Scan on pg_class (cost=0.00..2613.61 rows=22566 width=4)
--------=---------------------=---------------------------------------------------------------------------
注解：1、cost后面有两个数字，中间有=由 .. 分开，第一个数字代表启动成本，也就是说返回第一行需要多少cost。第二个数字代表返回所有数据的成本。
2、rows表示返回的行数
3、width 表示每行平均宽度 (单位是 字节)
4、随机处理的cost最大为4个cost
5.4.1
5.5控制执行计划的参数
控制执行计划的参数设置都是session级别的，如果要设定全局的参数，则需要在master节点上设定。不用重启DB
create table A as select id,id+1 as id2 from generate_series(1,59999) id distributed by(id);
create table B as select id,id+1 as id2 from generate_series(1,10000) id distributed by(id);
explain select * from A,B where A.id = B.id
--A，B表的统计信息
--reltuples是表的数据量
select relname,relpages,reltuples from pg_class where reltuples in ('59999','10000')
5.7.1 详解关联的广播与重分布
--重分布 & 广播
--A表的数据量是M B表的数据量是N
--1、内连接
--情况1 分布键与关联健一致，属于单库关联，不会造成广播或者重分布。
select * from A,B where A.id = B.id
--情况2 表A的关联键是分布键，表B的关联键不是分布键。那么可以通过以上两种方法来实现表的关联
--1、将表B按照id2字段将数据重分布到每一个节点上，然后再与A表进行关联。重分布的数据量为N
--2、将A表广播，每一个节点上都放一份全量数据，数据然后再与表B关联得到。广播的数据量是M * 节点数
--所以当 N > M * 节点数的时候 选A表广播，否则选择表B重分布。
explain select * from A,B where A.id = B.id2
--情况3
--两个表的关联健的分布键都不一样，那么还有两种做法：
--1、将A表与B表按照字段，将数据重分布到每个节点，重分布的代价是M + N
--2、将其中一张表进行广播后再关联，当然是选择小表广播，代价小，广播的代价是min(M,N) * 节点数
--所以当 N + M > min(M,N) * 节点数的时候选择小表广播，否则选择两个表都重分布。
--M = 10000,N = 59999节点数 = 6 满足 N + M > min(M,N) * 节点数 故将A表广播。
--把generate_series(1,59999)改成generate_series(1,50000) B表不变
--则 N + M >min(M,N) * 节点数 不成立两个表都重分布
select * from A,B where a.id2 = b.id2;
select * from pg_class
select 1259::regclass;
5.7.2 HashAggregate 与 GroupAggregate
1、hashaggregate
对于hash聚合来说，数据库会根据group by字段后面的值算出hash值，并根据前面使用的聚合函数在内存中维护对应的列表。如果select后面有两个聚合函数，那么内存中就会维护两个对应的数据。同样的有N哥聚合函数就会维护n个同样的数组。对于hash聚合来说，数组的长度肯定是大于group by的字段的distinct值的个数。且与这个值应该呈线性关系，group by后面的字段重复值越少使用的内存越大。
explain select count(1) from pg_class group by oid;
HashAggregate (cost=2729.60..2776.66 rows=3765 width=12)
Group By: oid
-> Seq Scan on pg_class (cost=0.00..2616.65 rows=22590 width=4)
2、groupaggregate
对于普通的聚合函数，使用groupaggregate。原理是先将表中的数据按照group by的字段排序，这样同一个group by的值就在一起，只需要对排好序的数据进行一次全扫描，并进行对应的聚合函数进行计算，既可以得到聚合的结果了。
--关闭哈希聚合的方式
set enable_hashagg = off;
explain select count(1) from pg_class group by oid;
GroupAggregate (cost=4250.29..4466.78 rows=3765 width=12)
Group By: oid
-> Sort (cost=4250.29..4306.77 rows=22590 width=4)
Sort Key: oid
-> Seq Scan on pg_class (cost=0.00..2616.65 rows=22590 width=4)
Settings: enable_hashagg=off
从执行计划来看，Groupaggregate需要排序，效率很差，消耗是Hashaggrefate的7倍。所以GP中，对于聚合函数的使用，采用的是Hashaggregate。
3、两种实现的内存消耗
--测试两种聚合方式对内存的消耗(HASH聚合和普通的聚合函数)
create table test_group(
id integer
,col1 numeric
,col2 numeric
,col3 numeric
,col4 numeric
,col5 numeric
,col6 numeric
,col7 numeric
,col8 numeric
,col9 numeric
,col11 varchar(100)
,col12 varchar(100)
,col13 varchar(100)
,col14 varchar(100)
) distributed by(id);
--插入数据 100W条记录
insert into test_group
select generate_series(1,100000),(random()*200)::int,(random()*800)::int,(random()*1600)::int,(random()*3200)::int,(random()*6400)::int,
(random()*12800)::int,(random()*40000)::int,(random()*100000)::int,(random()*1000000)::int,'hello','welcome','CEC','CHOT';
--TEST GP有六个node
SELECT gp_segment_id,count(*) from test_group group by 1 order by gp_segment_id;
select * from test_group
--表大小 25MB左右
explain analyze select pg_size_pretty(pg_relation_size('test_group'));
select sum(col1)
,sum(col2)
,sum(col3)
,sum(col4)
,sum(col5)
,sum(col6)
,sum(col7)
,sum(col8)
,sum(col9) from test_group group by id
-> HashAggregate (cost=3929.73..7199.92 rows=16771 width=292)
Group By: id
Rows out: Avg 16666.7 rows x 6 workers. Max 16676 rows (seg3) with 32 ms to first row, 60 ms to end, start offset by 1.656 ms.
Executor memory: 5900K bytes avg, 5900K bytes max (seg0).
-> Seq Scan on test_group (cost=0.00..1414.21 rows=16771 width=75)
Rows out: Avg 16666.7 rows x 6 workers. Max 16676 rows (seg3) with 0.036 ms to first row, 6.539 ms to end, start offset by 1.972 ms.
Slice statistics:
(slice0) Executor memory: 267K bytes.
(slice1) Executor memory: 6214K bytes avg x 6 workers, 6214K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Settings: enable_hashagg=on
Total runtime: 92.462 ms
不同group by字段差异比较大。可以试试增加更多的聚合函数，调整不同的group by字段看消耗的时间。
经过比较，对于groupaggregate来说，消耗的内存基本上都是恒定的，无论对哪个字段进行group by。当聚合函数较少的时候，速度也相对较慢。但相对稳定。
hash aggregate在少数聚合函数时表现优异，但是对于聚合函数很多的情况，性能和消耗的内存差异很明显。尤其时受Group By字段唯一性的影响，字段count（distinct）值越大，hashaggregate消耗的内存越多，性能下降越明显。
所以SQL中有大量聚合函数，Group By的字段重复值比较少的时候，应该用Groupaggregate，不能用hashaggregate。 Settings: enable_hashagg=off即可。
5.7.3 nestloop join/hash join/merge join
1、nestloop join
nestloop join就是所谓的笛卡儿积，这种关联的效率极差。例如 有两张100W的表关联，则会产生100W * 100W = 1W亿的数据量，如果还有筛选，再对数据集筛选，这就相当可怕了。 尤其对于GP这种资源独占的系统，一个SQL就可以将数据库的所有资源耗尽。造成其他SQL无法运行，如果是对两种大表做笛卡儿积，会产生极大的中间表，随时有可能将数据库的存储耗光，导致整个数据库垮掉，后果相当严重。所以在执行计划中看到nestloop的时候就要小心了。一般都是SQL有问题。
2、Hash join
这是一种关联的时候一种很高效的方法。 先对其中一张表关联的表计算hash值，在内存中用一个散列表保存，然后对另外一张表进行全表扫描，之后将每一行与这个散列表进行关联。GP默认使用这个join方式。
3、merge join
这种方式是对两张关联表先进行排序，然后按照排好序的内容顺序遍历一遍，将相同的值连接起来，GP默认关掉这种join 方式。
note：
Ø Sort-merge join：思路也很简单，就是按join字段排序，然后进行归并排序。当join字段存在重复值时，相当于每个重复值形成了一个分区。Join字段是否排序和重复值的多少决定了sort-merge的效率。适用于两表都很大的情况，尤其当join字段上存在聚集索引时(相当于已经排好序了)，效率很高。算法主要消耗在磁盘上。
Ø Hash join：类似于存在重复值情况时的sort-merge，只不过是人为的使用哈希函数进行分区。思路是扫描小表建立哈希表(build阶段，小表也叫build表)，然后逐行扫描大表进行比较(probe阶段，大表也叫probe表)。适用于两表都很大又没有索引的情况，限制是只适用于等值连接。算法主要消耗在CPU上。
GP默认将Merge join关掉因为，其消耗主要在磁盘上，事实上，存储的瓶颈也就是在磁盘I/O上，CUP的消耗并不是瓶颈。