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;