优化内部表的性能

• 扩容后，因业务需要，原有业务有规模增长，需要提高原有业务的查询效率。此时，您需要创建新的Table Group，并为其设置更大的Shard数。原有的表和数据仍然在旧的Table Group中，您需要将数据重新导入新的Table Group中，完成Resharding的过程。
• 扩容后，需要上线新业务，但已有业务并不变化。此时，建议您创建新的Table Group，并为其设置适合的Shard数，并不调整原有表的结构。

• Distribution Key设置建议
  • 选择JOIN查询时的连接条件列作为分布列。
  • 选择Group By频繁的列作为分布列。
  • 选择数据分布均匀离散的列作为分布列。
• 设置Distribution Key场景示例
  例如设置Distribution Key，表tmp和tmp1做Join，通过执行explain SQL语句看到执行计划中有Redistribution Motion，说明数据有重分布，没有Local Join，导致查询效率比较低。您需要重新建表并同时设置Join Key为Distribution Key，避免多表连接时数据重分布带来的额外开销。 重新建表后两个表的DDL示例语句如下。

  begin;
  create table tmp(a int, b int, c int);
  call set_table_property('tmp', 'distribution_key', 'a');
  commit;
  
  begin;
  create table tmp1(a int, b int, c int);
  call set_table_property('tmp1', 'distribution_key', 'b');
  commit;
  
  -- 设置分布列为Join Key。
  select count(1) from tmp join tmp1 on tmp.a = tmp1.b ;

  通过重新设置表的Distribution Key，再次执行explain SQL语句，可以看到执行计划中，红框内的算子被优化掉了，数据按照相同的Hash Key分布于Shard中。因为数据分布相同，Motion算子被优化（上图中红框内的算子），表明数据不会重新分布，从而避免了冗余的网络开销。 Hologres包含四种Motion Node，分别对应四种数据重分布场景，如下表所示。

  类型	描述
  Redistribute Motion	数据通过哈希分布或随机分布，Shuffle到一个或多个Shard。
  Broadcast Motion	复制数据至所有Shard。 仅在Shard数量与广播的表的数量均较少时，Broadcast Motion的优势较大。
  Gather Motion	汇总数据至一个Shard。
  Forward Motion	用于联邦查询场景。外部数据源或执行引擎与Hologres执行引擎进行数据传输。

  结合explain SQL语句执行结果您可以注意如下事项：

  • 如果Motion算子耗时较高，则您可以重新设计分布列。
  • Broadcast Motion只有在Shard数较少，且广播表的数量较少的场景下有优势。
  • 如果统计信息错误，导致生成Gather Motion或Broadcast Motion，则您可以通过analyze tablename命令将其修改为更高效的Redistribute Motion分布方式。
  • 您可以设置如下参数，禁止生成Motion算子，再对比查询耗时，示例语句如下。

    -- 禁止生成Broadcast Motion。
    set optimizer_enable_motion_broadcast = off; 
    
    -- 禁止生成Redistribute Motion。
    set optimizer_enable_motion_redistribute = off; 

• Group By优化
  Group By Key会导致数据在计算时按照分组列的Key重新分布数据，如果Group By耗时较高，您可以将Group By的列设置为分布列。

  -- 数据如果按照a列的值进行分布，将减少数据运行时重分布，充分利用shard的并行计算能力。
  select a, count(1) from t1 group by a; 

• 数据倾斜处理
  数据在多个Shard上分布不均匀会导致查询速度较慢，您可以通过如下语句判断数据分布是否存在倾斜。

  -- hg_shard_id是每个表的内置隐藏列，描述对应行数据所在shard
  select hg_shard_id, count(1) from t1 group by hg_shard_id;

  如果数据存在显著倾斜，则需要更改distribution_key，选择数据分布均匀离散的列作为分布列。

  说明 更改distribution_key需要重新创建表并导入数据。

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;

• 采用Fixed Plan优化SQL写入和更新。

  Fixed Plan是Hologres针对高吞吐数据写入、更新、删除场景的特定优化，通过简化的执行路径，实现数倍性能和吞吐的提升，配置方式和使用方法参考文档Fixed Plan加速SQL执行。

• PQE算子改写。

  Hologres底层有原生引擎HQE（Hologres Query Engine，向量引擎）和PQE（Postgres Query Engine，分布式Postgres引擎）等多个执行引擎，如果SQL语句中包含HQE不支持的算子，则系统会将该算子发送至PQE执行。此时查询的性能未能足够优化，需要修改相关查询语句。

  通过执行计划（explain SQL）查询，若执行计划中出现External SQL（Postgres）则说明这部分的SQL是在PQE中执行的。

  具体示例如下：HQE不支持 not in，则会将 not in操作转到外部查询引擎PQE执行。建议将 not in重写为 not exists。优化前的SQL语句如下。

  explain select * from tmp where a not in (select a from tmp1);

  External算子代表该部分SQL语句是在外部引擎Postgres中执行的。
  优化后的SQL语句如下，不再使用外部查询引擎。

  explain select * from tmp where not exists (select a from tmp1 where a = tmp.a);

  
  通过改写函数，将算子运行在HQE引擎中，以下为函数改写建议。

  Hologres原生引擎（HQE）不支持的函数	建议改写的函数	样例	备注
  not in	not exists	select * from tmp where not exist (select a from tmp1 where a = tmp.a);	不涉及。
  regexp_split_to_table(string text, pattern text )	unnest(string_to_array）	select name,unnest(string_to_array(age,',')) from demo;	regexp_split_to_table支持正则表达式。
  substring	extract(hour from to_timestamp(c1, 'YYYYMMDD HH24:MI:SS'))	select cast(substring(c1, 13, 2) as int) AS hour from t2; 改写为： select extract(hour from to_timestamp(c1, 'YYYYMMDD HH24:MI:SS')) from t2;	Hologres部分V0.10版本及更早版本不支持substring。V1.3版本及以上版本中，HQE已支持substring函数的非正则表达式入参。
  regexp_replace	replace	select regexp_replace(c1::text,'-','') from t2; 改写为： select replace(c1::text,'-','') from t2;	replace不支持正则表达式。
  at time zone 'utc'	删除at time zone 'utc'	select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS') at time zone 'utc') from t2 改写为： select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS') ) from t2;	不涉及。
  cast(text to timestamp)	to_timestamp	select cast(c1 as timestamp) from t2; 改写为： select to_timestamp(c1, 'yyyyMMdd hh24:mi:ss') from t2;	不涉及。
  timestamp::text	to_char	select c1::text from t2; 改写为： select to_char(c1, 'yyyyMMdd hh24:mi:ss') from t2;	不涉及。

• 避免模糊查询。

  模糊查询（例如Like操作）不会创建索引，会退化为全表扫描。

• 结果缓存对查询的影响
  Hologres会默认对相同的查询、子查询结果进行缓存，重复执行会命中缓存结果。若要关闭缓存对性能测试的影响，请使用如下命令语句。

  set hg_experimental_enable_result_cache = off;

• OOM优化

  命令	含义	参数取值范围	参数建议取值
  set hg_experimental_scan_node_dop=<number>;	设置单个Shard内scan算子扫描的并发度，最大为单个Shard内扫描表的文件数。默认为性能最高的设置。适用于写入时出现OOM的场景。	0-512 说明 并发度太大可能造成OOM ，导入或查询失败，甚至实例重启，以至于服务不可用。并发度太小会造成查询性能很差。	使用show hg_experimental_scan_node_dop命令查看当前的并发度。 得到的结果除以2作为hg_experimental_scan_node_dop的参数值。 说明 也可自行决定并发度值减小的比例。 也可自行决定并发度值减小的比例。
  set hg_experimental_dml_bulkload_dop=<number>;	设置BulkLoad写入或更新的并发度，最大为单个Shard内写入表的文件数，最小为1。默认为性能最高的设置。适用于写入时出现OOM的场景。	0-512 说明 并发度太大可能造成OOM ，导入或查询失败，甚至实例重启，以至于服务不可用。并发度太小会造成查询性能很差。	使用show hg_experimental_dml_bulkload_dop命令查看当前的并发度。 得到的结果除以2作为hg_experimental_dml_bulkload_dop的参数值。 说明 也可自行决定并发度值减小的比例。

• Order By Limit场景优化

  在Hologres V1.3之前版本，对Order By Limit场景不支持Merge Sort算子，生成执行计划时，在最后输出时还会做一次排序，导致性能相对较差。从1.3版本开始，引擎通过对order by limit场景优化，支持Merge Sort算子，实现多路归并排序，无需再进行额外的排序，提升了查询性能。若您的Hologres版本低于V1.3，您可以通过搜索（钉钉群号：32314975）加入实时数仓Hologres交流群申请升级。

  优化示例如下。

  • 建表DDL

    begin;
    create table test_use_sort_1
    (
              uuid           text not null,
              gpackagename   text not null,
              recv_timestamp text not null
    );
    call set_table_property('test_use_sort_1', 'orientation', 'column');
    call set_table_property('test_use_sort_1', 'distribution_key', 'uuid');
    call set_table_property('test_use_sort_1', 'clustering_key', 'uuid:asc,gpackagename:asc,recv_timestamp:desc');
    commit;
    
    --插入数据
    insert into test_use_sort_1 select i::text, i::text, '20210814' from generate_series(1, 10000) as s(i);
    
    --更新统计信息
    analyze test_use_sort_1;

  • 查询命令

    select uuid from test_use_sort_1 order by uuid limit 5;

  • 执行计划对比
    • Hologres V1.3之前版本（V1.1）的执行计划如下。
    • Hologres V1.3版本的执行计划如下。
    从执行计划对比中可以看出，Hologres V1.3版本在最后输出会少一个排序，直接多路归并，提升了查询性能。
• Count Distinct优化
  • 改写为APPROX_COUNT_DISTINCT

    Count Distinct是精确去重，需要把相同key的记录shuffle到同一个节点去去重，比较耗费资源。Hologres实现了扩展函数APPROX_COUNT_DISTINCT，采用HyperLogLog基数估计的方式进行非精确的COUNT DISTINCT计算，提升查询性能。误差率平均可以控制在0.1%-1%以内，可以根据业务情况适当改写，详情请参见APPROX_COUNT_DISTINCT。

  • 使用UNIQ函数

    Hologres从 V1.3版本开始，支持UNIQ精确去重函数，在GROUP BY KEY的KEY基数较高时，比Count Distinct性能更好，更节省内存。当使用Count Distinct出现OOM时，可以使用UNIQ做替换，详情请参见UNIQ。

  • 设置合适的Distribution Key

    当有多个Count Distinct且是key是同一个并且数据离散均匀分布，建议将Count Distinct的key设置成Distribution Key，这样相同的数据可以分布相同的Shard，避免数据Shuffle。

  • 多个Count Distinct优化
    当SQL中含有多个Count Distinct时，会先将每个Count Distinct单独求值，再基于join进行关联，而当from子句是subquery时，会导致subquery的重复计算，消耗资源较大、性能较差。从1.3版本开始，针对多个Count Distinct场景进行了优化，当Count Distinct数量大于1个时，会自动改写为 UNIQ实现（结果和语义和当前实现是一样的，对上层透明），以减少基于当前模式下不必要的subquery重复计算，提升查询性能。若您的Hologres实例版本低于V1.3版本，请先 通过搜索（钉钉群号：32314975）加入实时数仓Hologres交流群申请升级实例。示例如下。

    • 建表DDL

      create table test_count_distinct_implementation
      (
          id int
          ,dim1 int
          ,dim2 text
          ,dim3 int8
          ,userid text
          ,deviceid text
          ,price float8
          ,ds text
      );
      
      insert into test_count_distinct_implementation select i, i % 17, (i % 13)::text, i % 37, 'user_' || round(i % 97723)::text, 'device_' || floor(i % 179357)::text, (sqrt(i % 24658)), '1' from generate_series(1, 5000)i;
      insert into test_count_distinct_implementation select i, i % 19, (i % 13)::text, i % 37, 'user_' || round(i % 87723)::text, 'device_' || floor(i % 139557)::text, (sqrt(i % 38658)), '2' from generate_series(1, 4000)i;
      
      analyze test_count_distinct_implementation;

    • 查询命令

      select count(1), count(distinct userid), count(distinct deviceid), sum(distinct price::numeric) from test_count_distinct_implementation;

    • 执行计划对比
      • Hologres V1.3之前版本（V1.1）的执行计划如下。
      • Hologres V1.3版本的执行计划如下。
      从执行计划的对比中可以看出，Hologres V1.3版本减少了对Count Distinct的重复计算，可以很高效的算出结果。
• With表达式优化（Beta）
  Hologres兼容PostgreSQL ，支持CTE（ Common Table Expression），常用在with递归查询，其实现原理同PostgreSQL，都是基于Inlining展开的，所以当有多次使用CTE时会造成重复计算。在HologresV1.3版本中，可以通过如下GUC参数支持CTE Reuse（复用），这样CTE只需计算一次而被多次引用，用以节省计算资源，提升查询性能。若您的Hologres实例版本低于 V1.3，升级实例。

  set optimizer_cte_inlining=off;

  说明

  • 该功能当前还处于Beta阶段，默认没有开启（默认会将CTE全部Inline展开，重复计算），可手动设置GUC后开启使用。
  • CTE Reuse开启后，依赖Shuffle阶段的Spill功能，因为下游用户消费CTE的进度是不同步的，所以数据量大时候会影响性能。

  • 示例

    create table cte_reuse_test_t
    (
        a integer not null,
        b text,
        primary key (a)
    );
    
    insert into cte_reuse_test_t values(1, 'a'),(2, 'b'), (3, 'c'), (4, 'b'), (5, 'c'), (6, ''), (7, null);
    
    
    set optimizer_cte_inlining=off;
    
    explain with c as (select b, max(a) as a from cte_reuse_test_t group by b)
    select a1.a,a2.a,a1.b, a2.b
    from c a1, c a2
    where a1.b = a2.b
    order by a1.b
    limit 100;
                                        

  • 执行计划对比
    • Hologres V1.3之前版本（V1.1）的执行计划如下。
    • Hologres V1.3版本的执行计划如下。
    从执行计划的对比中可以看出Hologres V1.3之前版本会有多个AGG计算（HashAggregate），Hologres V1.3版本只需计算一次就被结果复用，提升了性能。
• 单阶段Agg优化为多阶段Agg

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

  通过在单个Shard内先进行本地的Agg操作，可以减少最终聚合操作的数据量，提升性能。具体如下：

  • 三阶段聚合：数据先进行文件级别的聚合计算，再聚合单个Shard内的数据，最后汇总所有Shard的结果。
  • 两阶段聚合：数据先在单个Shard内进行聚合计算，再汇总所有Shard的结果。
  您可以强制Hologres进行多阶段聚合操作，语句如下。

  set optimizer_force_multistage_agg = on;