greenplum表级优化建议

虽然GREENPLUM可以降低对优化的要求，但是它也是关系型数据库。所以也需要进行优化。这里主要列出与GP优化的一些建议-PTA(PERFORMANCE TUNNING ADVICE) 

PTA RULE No1   

    在完成大批量数据装载之后，针对目标表总是进行vacuum analyze操作。一方面是如果批处理操作失败，会浪费很多空间，这个操作可以回收这些空间，另一方面，GP也是基于cost的优化器，只有收集相对准确的优化统计，才能保证执行计划的最优，这个操作会进行优化统计的收集。优化统计信息的收集粒度由参数default_statistics_target，该参数设置越高，优化器得到最优执行计划的可能越大，但是收集操作的代价也就越大，耗时越长。
         另外，每次装载完数据还应该检查是否存在数据存放不均匀的情况，比如某些segment存放的记录数远远超过其它segment，会导致多存放记录的segment成为整个系统运算过程中的瓶颈。
         我们可以通过一些简单的方法去检查是否存在数据分布的不平衡。比如系统级，
我们可以直接用df -h 或du -h检查磁盘或者目录数据是否均匀。在segment一级，可以通过
select gp_segment_id,count(*) from fact_table group by gp_segment_id的方式检查每张表的数据是否均匀存放。

PTA RULE No2 表的布局

    尽量把数据分布键放在最前面，如果是分区表，那么接下来是分区键，并且在此基础上建议按照数据类型宽度从大到小的顺序排列比如先8 byte的列，再4字节，再2字节。比如某张表列的定义顺序是
Bigint    （8 byte)
Timestamp   （8 byte)
Bigint     （8 byte)
Timestamp   （8 byte)
Int (Distribution key) (4 byte)
Date (partition key)     (4 byte)
Smallint                 (2 byte）
Int                      (4byte）
那么建议改成
Int (Distribution key) 
Date (partition key)
bigint
Bigint
Timestamp
Bigint
Timestamp
Int
Smallint

PTA RULE No3 数据分布键的选择。
    数据分布均匀是保证GP高效并行处理能力的基础。因此定义表时，如果选用HASH分布策略，保证数据分布均匀是获取高性能的关键所在。选择的依据遵从三大原则，第一个就是首先保证前面提到的所有节点数据存放是均匀的。第二，如果经常进行大表连接，那么尽量把连接键定义成数据分布键（如果多个列作为数据分布键，他们应该都出现在连接中，否则还是会造成无效广播），这样尽量减少无效的interconnect。第三，尽量保证where条件产生的结果集的存储也尽量是均匀的。

PTA RULE NO4 行存vs列存

    GREENPLUM同时支持行存和列存方式，那么到底该怎么选择？如果记录需要update/delete，那么只能选择非压缩的行存方式。对于查询，如果选择的列的数量经常超过30个以上的列，那么也应该选择行存方式。如果选择列的数量非常有限，并且希望通过较高的压缩比换取海量数据查询时的较好的IO性能，那么就应该选择列存模式。但是需要注意的是，如果是列存分区表，每个分区的每个列都会有一个对应的物理文件。比如有100个分区，100个列，那么就会产生10000个文件，默认情况下，这些文件都会放在一个目录中。这可能会导致两方面的问题，一个是访问数据时有可能超越linux上允许同时打开文件数量的上限。另一个是在同一个目录内，存放过多的文件，会导致DDL命令的效率很差。解决这两个问题，一个是尽量在SQL中使用事实表的分区键作为条件，或者尽量指定需要访问的事实表分区，以减少文件的访问。另外，应该创建不同的表空间（相当于不同的系统目录），把分区放入不同的表空间。

PTA RULE NO5 SQL优化的考虑
如果SQL效率差，我们可以通过EXPLAIN命令查看执行计划，判断问题出在什么地方，比如是书写不合理导致的问题，还是优化统计不准确导致的问题。
explain和explain analyze是有区别的。explain是基于统计，给出执行计划，他不会真正执行SQL，因此没有执行统计。explain analyze会真正执行SQL因此会提供执行过程中真实资源消耗统计，但是对在线系统可能影响会较大。
尽量避免对大表进行DISTINCT操作，因为GP中DISTINCT要进行排序操作。可以考虑用group by对distinct操作进行改写。
DISTINCT 操作
asiainfo=# explain analyze select distinct * from   dns_agg_temp;                                                    
 Slice statistics:
   (slice0)    Executor memory: 2466K bytes.
   (slice1)    Executor memory: 4146K bytes avg x 96 workers, 4146K bytes max (seg7).
   (slice2)  * Executor memory: 153897K bytes avg x 96 workers, 153981K bytes max (seg71).  Work_mem: 153588K bytes max, 1524650K bytes wanted.
 Statement statistics:
   Memory used: 128000K bytes
   Memory wanted: 1525449K bytes
 Total runtime: 1301251.778 ms
asiainfo=# explain analyze select * from dns_agg_temp group by rq,dnsnode,servicenode,isarpa,iswap,prov_id,sub4,sub3,sub2,sub1,status,count;                   
 Slice statistics:
   (slice0)    Executor memory: 431K bytes.
   (slice1)    Executor memory: 70368K bytes avg x 96 workers, 70368K bytes max (seg7).
   (slice2)  * Executor memory: 66502K bytes avg x 96 workers, 66562K bytes max (seg0).  Work_mem: 63701K bytes max, 664568K bytes wanted.
 Statement statistics:
   Memory used: 128000K bytes
   Memory wanted: 1329734K bytes
 Total runtime: 275129.895 ms
大家可以从响应时间和内存资源的消耗上看到，用group by改写之后，SQL性能有了非常明显的提升。同理，对于UNION操作应该用UNION ALL加GROUP BY的方式进行改写。
比如
SELECT * FROM A UNION SELECT * FROM B
可以改写成
SELECT * FROM (SELECT * FROM A UNION ALL SELECT * FROM B) C GROUP BY 1,2....;
另外，尽量使用GREENPLUM自身提供的聚合函数和窗口函数去完成一些复杂的分析。

PTA RULE NO6 索引使用的考虑
如果是从超大结果集合中返回非常小的结果集（不超过5%），建议使用BTREE索引（非典型数据仓库操作）。表记录的存储顺序最好与索引一致，可以进一步减少IO（好的index cluster），where条件中的列用or的方式进行join，可以考虑使用索引，还有就是记录很多，键值大量重复时，比较适合使用bitmap索引。
通常来说在传统数据库中使用索引可以有效的提高数据访问效率，特别是在OLTP系统中，往往只是需要从大表中获取几行或者部分的数据记录，这个情况下索引确实是特别有效的提高数据获取速度的方法。但是在gp中未必如此，因为首先数据都是均匀最大可能的均匀分布到segs的，这意味着每个instances只是扫描整体数据的一部分，而且如果使用了分区技术，需要扫描的数据可能会更少，最后不同于OLTP的是在大数据环境中往往获取的都是大部分的记录集，在这个情况下使用索引未必是有效的提高数据获取速度的方法，应该保守使用索引。正确的方式应该是在不创建任何索引的情况下测试下性能，而后再做出正确的决定。
如果使用了主键，则会自动为之创建索引；在压缩只读表中使用索引，索引只会解压所需要部分的数据而不是整张表；避免在频繁更新的列上创建索引；在高选择性的列上使用b-tree索引，在低选择性的列上使用位图索引；未外键或者关联键创建索引；为经常使用的过滤词创建索引；避免在同一个列上重复创建索引，这是不必要的冗余；在load数据之前删除掉索引，之后再创建索引，这通常比update索引更快；使用类似cluster索引的技术创建索引。
含有以下类型的索引：B-tree、GiST、Bitmap
不支持hash和gin索引，默认创建的是B-tree索引；唯一索引必须包含在分布键中（可以是全部或者部分列，在第1个索引中可以部分列，之后必须全部列），唯一索引不支持ao表，唯一索引不会跨越分区起作用，只是针对独立的分区；位图索引比普通索引占用更小的空间，在不同键值在100到100000之间都能发挥很好的效果，位图索引不应在经常更新的表中使用。
 create index idx1 on t3 (id);
 create index idx2 on t3 using bitmap (id);
 drop index idx1;
通过explain命令来检验是否使用索引：index scan、bitmap heap scan、bitmap index scan、BitmapAnd or BitmapOr 
重建索引：reindex idx1;
重建索引将会使用已有索引上的数据信息，这将会比删除重建索引要快的多。删除和更新操作不会更新位图索引，因此需要手工reindex bitmap index。