实时数仓Hologres：优化查询性能

查看统计信息是否正确

通过查看执行计划：

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

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

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

更新统计信息

analyze tmp;
analyze tmp1;

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

推荐更新统计信息的场景

推荐在以下情况下运行analyze <tablename>命令。

• 导入数据之后。

• 大量的INSERT、UPDATE以及DELETE操作之后。

• 内部表、外部表均需要ANALYZE。

• 分区表针对父表做ANALYZE。

• 如果遇到以下问题，您需要先执行analyze <tablename>，再运行导入任务，可以系统地优化效率。

  • 多表JOIN超出内存OOM。

    通常会产生Query executor exceeded total memory limitation xxxxx: yyyy bytes used报错。

  • 导入效率较低。

    在Hologres查询或导入数据时，效率较低，运行的任务长时间不结束。

更新统计信息

如上述查看统计信息中，参与Join的表如果未及时更新统计信息，可能会导致数据量大的表做了Hash表，从而导致Join效率变低。因此可以通过更新表的统计信息，提升SQL性能。

analyze <tablename>;

选择合适的分布列（Distribution Key）

分布列（Distribution Key）用于将数据划分到多个Shard，划分均衡可以避免数据倾斜。多个相关的表设计为相同的Distribution Key，可以起到Local Join的加速效果。创建表时，您可以通过如下原则选择合适的分布列：

• Distribution Key设置建议

  • 选择Join查询时的连接条件列作为分布列。

  • 选择Group By频繁的列作为分布列。

  • 选择数据分布均匀离散的列作为分布列。

  • 更多关于Distribution Key的原理和使用详情请参见分布键Distribution Key。

• 设置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算子被优化（上图中红框内的算子），表明数据不会重新分布，从而避免了冗余的网络开销。

使用Runtime Filter

从V2.0版本开始，Hologres开始支持Runtime Filter，通常应用在多表Join（至少2张表），尤其是大表Join小表的场景中，无需手动设置，优化器和执行引擎会在查询时自动优化Join过程的过滤行为，使得扫描更少的数据量，从而降低IO开销，以此提升Join的查询性能，详情请参见Runtime Filter。

优化Join Order算法

• 当SQL Join关系比较复杂时，或者Join的表多时，优化器（QO）消耗在连接关系最优选择上的时间会更多，调整Join Order策略，在一定场景下会减少Query Optimization的耗时，设置优化器Join Order算法语法如下。

  set optimizer_join_order = '<value>'; 

• 参数说明

  参数	说明
  value	优化器Join Order算法，有如下几种。 exhaustive2（V2.2及以上版本默认值）：升级优化的动态规划算法。 exhaustive（早期版本默认值）：通过动态规划算法进行Join Order转换，会生成最优的执行计划，但优化器开销最高。 query：不进行Join Order转换，按照SQL书写的连接顺序执行，优化器开销最低。 greedy：通过贪心算法进行Join Order的探索，优化器开销适中。

• 补充说明

  使用默认的exhaustive2算法可以全局探索最优的执行计划，但对于很多表的Join（例如表数量大于10），优化耗时可能较高。使用query或者greedy算法可以减少优化器耗时，但无法生成最优的执行计划。

优化Broadcast等Motion算子

目前Hologres包含四种Motion Node，分别对应四种数据重分布场景，如下表所示。

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

• 如果Motion算子耗时较高，则您可以重新设计分布列。

• 如果统计信息错误，导致生成Gather Motion或Broadcast Motion，则您可以通过analyze <tablename>命令将其修改为更高效的Redistribute Motion分布方式。

• Broadcast Motion只有在Shard数较少，且广播表的数量较少的场景下有优势。所以如果是小表Broadcast的场景，建议您将表的Shard数量减少（尽量保持Shard Count与Worker数量成比例关系），从而提高查询效率。Shard Count详情请参见Shard Count。

采用Fixed Plan

Fixed Plan适用于高吞吐场景，通过简化的执行路径，实现数倍性能和吞吐的提升，配置方式和使用方法请参考Fixed Plan加速SQL执行。

PQE算子改写

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

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

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

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

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

select name,unnest(string_to_array(age,',')) from demo;

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;

select regexp_replace(c1::text,'-','0') from t2;

select replace(c1::text,'-','') from t2;

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;

select cast(c1 as timestamp) from t2;

select to_timestamp(c1, 'yyyyMMdd hh24:mi:ss') from t2;

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的优化手段

当实例计算内存不足时通常会出现OOM，常见的报错如下。产生OOM的原因有多种，比如计算复杂、并发量高等，可以根据不同的原因进行针对性优化，从而减少OOM。详情请参见OOM常见问题排查指南。

Total memory used by all existing queries exceeded memory limitation. 
memory usage for existing queries=(2031xxxx,184yy)(2021yyyy,85yy)(1021121xxxx,6yy)(2021xxx,18yy)(202xxxx,14yy); Used/Limit: xy1/xy2 quota/sum_quota: zz/100

Order By Limit场景优化

在Hologres V1.3之前版本，对Order By Limit场景不支持Merge Sort算子，生成执行计划时，在最后输出时还会做一次排序，导致性能相对较差。从1.3版本开始，引擎通过对Order By Limit场景优化，支持Merge Sort算子，实现多路归并排序，无需再进行额外的排序，提升了查询性能。

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优化

  Hologres从V2.1版本开始，针对Count Distinct场景做了非常多的性能优化（包括单个Count Distinct、多个Count Distinct、数据倾斜、SQL没有Group By字段等场景），无需手动改写成UNIQ，即可实现更好的性能。如果想要提升Count Distinct性能，建议您将Hologres实例升级至V2.1及以上版本。

Group By优化

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

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

数据倾斜处理

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

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;

• 示例

  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级别的预聚合。

set optimizer_force_multistage_agg = on;

选择存储类型

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

选择数据类型

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

• 尽量选用存储空间小的类型。

  • 优先使用INT类型，而不是BIGINT类型。

  • 优先使用精确确定的DECIMAL/NUMERIC类型，明确数值精度（PRECISION，SCALE），且精度尽量小，减少使用FLOAT/DOUBLE PRECISION等非精确类型，避免统计汇总中的误差。

  • GROUP BY的列不建议使用FLOAT/DOUBLE等非精确类型。

  • 优先使用TEXT，适用范围更广，当使用VARCHAR(N)和CHAR(N)，N的取值尽量小。

  • 日期类型使用TIMESTAMPTZ、DATE，避免使用TEXT。

• 关联条件使用一致的数据类型。

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

• UNION或GROUP BY等操作避免使用FLOAT/DOUBLE等非精确类型。

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

选择主键

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

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

选择分区表

选择索引

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索引。

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索引。

查看是否使用Index

创建tmp表并指定索引字段，语句如下。

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;

• 查看是否使用Cluster Index，语句如下。

  explain select * from tmp where a > 1;

  

• 查看是否使用Bitmap Index，语句如下。

  explain select * from tmp where c = 1;

  

• 查看是否使用Segment Key，语句如下。

  explain select * from tmp where b > 1;