优化内部表的性能

更新统计信息

统计信息决定是否能够生成正确的执行计划。例如，Hologres需要收集数据的采样统计信息，包括数据的分布和特征、表的统计信息、列的统计信息、行数、列数、字段宽度、基数、频度、最大值、最小值、长键值、分桶分布特征等信息。这些信息将为优化器更新算子执行预估COST、搜索空间裁剪、估算最优JOIN ORDER、估算内存开销、估算并行度，从而生成更优的执行计划。关于统计信息更多的介绍，请参见Using Explain。

统计信息的收集也存在一定局限，主要是针对非实时、手动触发或者周期性触发，不一定反映最准确的数据特征。您需要先检查explain的信息，查看explain中包含的统计信息是否正确。统计信息中每个算子的rows和width表示该算子的行数和宽度。

未及时同步统计信息导致生成较差的执行计划，示例如下：

• tmp1表的数据量为1000万行，tmp表的数据量为1000行。 Hologres默认统计信息中的行数为1000行，通过执行explain SQL语句，如下展示结果所示，tmp1表的行数与实际的行数不符，表明该展示结果未及时更新统计信息。

  Seq Scan on tmp1 (cost=0.00..5.01 rows=1000 width=1)

  
• tmp1和tmp表Join时，正确的explain信息展示为数据量大的表tmp1在数据量小的表tmp上方，Hash Join应该采用数据量小的tmp表。因为tmp1表未及时更新统计信息，导致Hologres选择tmp1表创建Hash表进行Hash Join，效率较低，并且可能造成OOM（Out Of Memory，内存溢出）。因此，需要参与Join的两张表均执行analyze收集统计信息，语句如下。

  analyze tmp;
  analyze tmp1

  执行analyze命令后，Join的顺序正确。数据量大的表tmp1在数据量小的表tmp上方，使用数据量小的表tmp做Hash表，如下图所示。并且tmp1表展示的行数为1000万行，表明统计信息已经更新。

当发现explain返回结果中rows=1000，说明缺少统计信息。一般性能不好时，其原因通常是优化器缺少统计信息，需要通过及时更新统计信息，执行analyze tablename，可以简单快捷优化查询性能。

查看并更改扩容后的Shard数

Shard数代表查询执行的并行度。Shard个数对查询性能影响至关重要，Shard数设置少，会导致并行度不足。Shard数设置过多，也会引起查询启动开销大，降低查询效率。设置与实例规格匹配的Shard数，可以改善查询效率。

Hologres为每个实例设置了默认的Shard数，Shard数等于实例中用于核心查询的Core数。这里的core数，略小于实际购买的Core数（实际购买的Core会被分配给不同的节点，包括查询节点、接入节点、控制节点和调度节点等）。不同规格实例默认的Shard数，请参见实例规格概述。当实例扩容后，扩容之前旧的DB对应的默认Shard数不会自动修改，需要根据实际情况修改Shard数，扩容后新建DB的Shard数为当前规格的默认数量。

创建表时选择合适的分布列（Distribution Key）

关闭Dictionary_encoding

对于String类型的相关查询，Dictionary Encoding或Decoding会减少比较字符串的耗时，但是会带来大量的Decode或Encode开销。

Hologres默认对所有的String列建立Dictionary_encoding，您可以设置Dictionary_encoding_columns为空，或关闭部分列的自动Dictionary_encoding功能。

您可以通过上文获取执行后的统计信息获取Decode算子的耗时，如果耗时较高，则请关闭Decode。当表的String类型字段较多时，建议关闭Dictionary_encoding功能可以改善性能。

begin;
create table tbl (a int not null, b text not null, c int not null, d int);
call set_table_property('tbl', 'dictionary_encoding_columns', '');
commit;

其他优化项

• 选择合适的存储类型。
  Hologres支持行存储和列存储两种存储模式，您可以根据业务场景选择合适的存储类型，如下表所示。

  类型	适用场景	缺点
  行存储	按主键进行高QPS的点查询场景。 一次能读取所有列，并且对UPDATE、DELETE及INSERT操作的性能较好。	大范围的查询、全表扫描及聚合等操作性能较差。
  列存储	适用于多列按范围查询、单表聚合及多表连接等数据分析场景。	UPDATE和DELETE操作及无索引场景下的点查询性能慢于行存储。

• 选择合适的数据类型。
  Hologres支持多种数据类型，您可以根据业务场景以及需求选择合适的数据类型，原则如下：

  • 尽量选用存储空间小的类型。
    • 优先使用INT类型，而不是BIGINT类型。
    • DECIMAL的精度尽量小。
    • Group By的列不建议使用Float类型。
    • 优先使用TEXT，而不是VARCHAR(n)和CHAR(n)，n的取值尽量小。
    • 日期类型使用TIMESTAMPTZ、DATE，避免使用TEXT。
  • 使用一致的数据类型。

    进行多表关联时，不同列尽量使用相同的数据类型。避免Hologres将不同类型的列进行隐示类型转换，造成额外的开销。

  • UNION或Group By等操作使用DECIMAL类型。

    UNION或Group By等操作暂不支持DOUBLE PRECISION和FLOAT数据类型，需要使用DECIMAL类型。

• 选择合适的主键。
  主键（Primary Key）主要用于保证数据的唯一性，适用于主键重复的导入数据场景。您可以在导入数据时设置option选择去重方式，如下所示：

  • ignore：忽略新数据。
  • update：新数据覆盖旧数据。

  合理的设置主键能帮助优化器在某些场景下生成更好的执行计划。例如，查询为group by pk,a,b,c的场景。

  但是在列存储场景，主键的设置对于写入数据的性能会有较大的影响。通常，不设置主键的写入性能是设置主键的3倍。

• 选择合适的分区表。
  Hologres当前仅支持创建一级分区表。合理的设置分区会加速查询性能。

  说明 对于按天增量导入的数据，建议按天建成分区表，数据单独存储，只访问当天数据。
  设置分区适用的场景如下：

  • 删除整个子表的分区，不影响其他分区数据。DROP语句的性能高于DELETE语句。
  • 对于分区列在谓词条件中的查询，可以直接通过分区列索引到对应分区，并且可以直接查询子分区，操作更为灵活。
  • 对于周期性实时导入的数据，适用于创建分区表。例如，每天都会导入新的数据，可以将日期作为分区列，每天导入数据至一个子分区。示例语句如下。

    begin;
    create table insert_partition(c1 bigint not null, c2 boolean, c3 float not null, c4 text, c5 timestamptz not null) partition by list(c4);
    call set_table_property('insert_partition', 'orientation', 'column');
    commit;
    create table insert_partition_child1 partition of insert_partition for values in('20190707');
    create table insert_partition_child2 partition of insert_partition for values in('20190708');
    create table insert_partition_child3 partition of insert_partition for values in('20190709');
    
    select * from insert_partition where c4 >= '20190708';
    select * from insert_partition_child3;

• 选择合适的索引。
  Hologres支持设置多种索引，不同索引的作用不同。您可以根据业务场景选择合适的索引，提升查询性能。索引类型如下表所示。

  类型	名称	描述	使用建议	示例查询语句
  Clustering_key	聚簇索引	文件内聚簇索引，数据在文件内按该索引排序。 对于部分范围查询，Hologres可以直接通过聚簇索引的数据有序属性进行过滤。	将范围查询或Filter查询列作为聚簇索引列。索引过滤具备左匹配原则，建议设置不超过2列。	select sum(a) from tb1 where a > 100 and a < 200;
  Bitmap Columns	比特编码索引	文件内位图索引，数据在文件内按该索引列建立位图。 对于等值查询，Hologres可以按照数值对每一行的数据做编码，通过位操作快速索引到对应行，时间复杂度为O(1)。	将等值查询列作为Bitmap列。	select * from tb1 where a =100;
  Segment_key（也称为event_time_column）	分段键	文件索引，数据按Append Only方式写入文件，随后文件间按该索引键合并小文件。 Segment_key标识了文件的边界范围，您可以通过Segment Key快速索引到目标文件。 Segment_key是为时间戳、日期等有序，范围类数据场景设计的，因此与数据的写入时间有强相关性。	您需要先通过Segment_key进行快速过滤，再通过Bitmap或Cluster索引进行文件内范围或等值查询。具备最左匹配原则，一般只有1列。 建议将第一个非空的时间戳字段设置为Segment_key。	select sum(a) from tb1 where ts > '2020-01-01' and a < '2020-03-02';

  Cluster索引和Segment索引都需要满足传统数据库（例如MySQL）的最左前缀匹配原则，即按照Index书写的最左列排序进行索引。如果最左列为有序的场景，则按照左边第二列进行排序。示例如下。

  call set_table_property('tmp', 'clustering_key', 'a,b,c');
  select * from tmp where a > 1   --可以使用Cluster索引。
  select * from tmp where a > 1 and c > 2   --只有a可以使用Cluster索引。
  select * from tmp where a > 1 and b > 2   --a,b均可以使用Cluster索引。
  select * from tmp where a > 1 and b > 2 and c > 3 --a,b,c均可以使用Cluster索引。
  select * from tmp where b > 1 and c > 2   --b,c均不能使用Cluster索引。

  Bitmap Index支持多个列的and及or查询，示例如下。

  call set_table_property('tmp', 'bitmap_columns', 'a,b,c');
  select * from tmp where a = 1 and b = 2   --可以使用Bitmap索引。
  select * from tmp where a = 1 or b = 2   --可以使用Bitmap索引。

  说明 Bitmap索引可以在创建表后添加，Cluster索引和Segment索引则在创建表时已经指定，后续无法再添加。

查看是否使用index

begin;
create table tmp(a int not null, b int not null, c int not null);
call set_table_property('tmp', 'clustering_key', 'a');
call set_table_property('tmp', 'segment_key', 'b');
call set_table_property('tmp', 'bitmap_columns', 'a,b,c');
commit;

单阶段Agg优化为多阶段Agg

如果Agg算子耗时过高，您可以检查是否没有做Local Shard级别的预聚合。

set optimizer_force_multistage_agg = on;

优化SQL语句