Hologres：EXPLAIN和EXPLAIN ANALYZE

執行計畫簡介

在Hologres中，最佳化器（Query Optimizer，QO）會為每一條SQL產生一個執行計畫，執行引擎（Query Engine，QE）會根據該執行計畫產生最終的執行計畫，然後執行並擷取SQL結果。執行計畫中會反映出SQL的統計資訊、執行運算元、運算元耗時等資訊，一個好的執行計畫，能夠用最少的資源更快的返回結果。因此對於日常開發來說，執行計畫至關重要，它可以反應出SQL的問題，從而去針對性最佳化。

Hologres相容Postgres，可以根據EXPLAIN和EXPLAIN ANALYZE文法瞭解SQL的執行計畫。

• EXPLAIN：代表最佳化器QO根據SQL特徵預估的SQL執行計畫，並非實際的執行計畫，對SQL的運行有一定參考意義。

• EXPLAIN ANALYZE：代表SQL真實的運行計劃，相比EXPLAIN會包含更多的實際運行資訊，能準確的反映出SQL的執行運算元和運算元耗時，可以根據運算元耗時去做針對性的SQL最佳化。

EXPLAIN

• 文法格式

  EXPLAIN可以反映出最佳化器的預估執行計畫，文法如下：

  EXPLAIN <sql>;

• 使用樣本

  以TPC-H中的SQL為例。

  說明

  該樣本引用TPC-H的SQL，但是不代表TPC-H的測試結果。

  EXPLAIN SELECT
          l_returnflag,
          l_linestatus,
          sum(l_quantity) AS sum_qty,
          sum(l_extendedprice) AS sum_base_price,
          sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
          sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
          avg(l_quantity) AS avg_qty,
          avg(l_extendedprice) AS avg_price,
          avg(l_discount) AS avg_disc,
          count(*) AS count_order
  FROM
          lineitem
  WHERE
          l_shipdate <= date '1998-12-01' - interval '120' day
  GROUP BY
          l_returnflag,
          l_linestatus
  ORDER BY
          l_returnflag,
          l_linestatus;

• 返回結果

  QUERY PLAN
  Sort  (cost=0.00..7795.30 rows=3 width=80)
    Sort Key: l_returnflag, l_linestatus
    ->  Gather  (cost=0.00..7795.27 rows=3 width=80)
          ->  Project  (cost=0.00..7795.27 rows=3 width=80)
                ->  Project  (cost=0.00..7794.27 rows=3 width=104)
                      ->  Final HashAggregate  (cost=0.00..7793.27 rows=3 width=76)
                            Group Key: l_returnflag, l_linestatus
                            ->  Redistribution  (cost=0.00..7792.95 rows=1881 width=76)
                                  Hash Key: l_returnflag, l_linestatus
                                  ->  Partial HashAggregate  (cost=0.00..7792.89 rows=1881 width=76)
                                        Group Key: l_returnflag, l_linestatus
                                        ->  Local Gather  (cost=0.00..7791.81 rows=44412 width=76)
                                              ->  Decode  (cost=0.00..7791.80 rows=44412 width=76)
                                                    ->  Partial HashAggregate  (cost=0.00..7791.70 rows=44412 width=76)
                                                          Group Key: l_returnflag, l_linestatus
                                                          ->  Project  (cost=0.00..3550.73 rows=584421302 width=33)
                                                                ->  Project  (cost=0.00..2585.43 rows=584421302 width=33)
                                                                      ->  Index Scan using Clustering_index on lineitem  (cost=0.00..261.36 rows=584421302 width=25)
                                                                            Segment Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                            Cluster Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
  

• 結果解釋

  執行計畫需要從下往上看，每個箭頭（->）代表一個節點，每個子節點會返回使用的運算元，以及預估的行數等。需要關注的運算元包括：

  參數	描述
  cost	運算元的預估耗時，父節點的cost也包含子節點的cost。包含預估啟動cost和預估總cost，中間使用..分割。 預估啟動cost：輸出階段開始之前的cost。 預估總cost：是假設運算元運行完成的總cost。 例如上述返回結果中的Final HashAggregate節點，預估啟動cost是0.00，總cost是7793.27。
  rows	運算元的預估輸出行數，主要基於統計資訊進行估算。 對於上述返回結果中Scan的預估值，預設值為1000。 說明 通常情況下如果出現rows=1000則說明表的統計資訊不正確，沒有根據統計資訊進行估算，可以通過執行analyze <tablename>命令對錶進行統計資訊更新。
  width	運算元的預估輸出平均寬度（單位：位元組），值越大說明列越寬。

EXPLAIN ANALYZE

• 文法格式

  EXPLAIN ANALYZE可以反應出SQL的實際執行計畫，以及對應的運算元耗時，從而協助診斷SQL效能。文法如下：

  EXPLAIN ANALYZE <sql>;

• 使用樣本

  以TPC-H中的SQL為例。

  EXPLAIN ANALYZE SELECT
          l_returnflag,
          l_linestatus,
          sum(l_quantity) AS sum_qty,
          sum(l_extendedprice) AS sum_base_price,
          sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
          sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
          avg(l_quantity) AS avg_qty,
          avg(l_extendedprice) AS avg_price,
          avg(l_discount) AS avg_disc,
          count(*) AS count_order
  FROM
          lineitem
  WHERE
          l_shipdate <= date '1998-12-01' - interval '120' day
  GROUP BY
          l_returnflag,
          l_linestatus
  ORDER BY
          l_returnflag,
          l_linestatus;

• 返回結果

  QUERY PLAN
  Sort  (cost=0.00..7795.30 rows=3 width=80)
    Sort Key: l_returnflag, l_linestatus
  [id=21 dop=1 time=2427/2427/2427ms rows=4(4/4/4) mem=3/3/3KB open=2427/2427/2427ms get_next=0/0/0ms]
    ->  Gather  (cost=0.00..7795.27 rows=3 width=80)
        [20:1 id=100003 dop=1 time=2426/2426/2426ms rows=4(4/4/4) mem=1/1/1KB open=0/0/0ms get_next=2426/2426/2426ms]
          ->  Project  (cost=0.00..7795.27 rows=3 width=80)
              [id=19 dop=20 time=2427/2426/2425ms rows=4(1/0/0) mem=87/87/87KB open=2427/2425/2425ms get_next=1/0/0ms]
                ->  Project  (cost=0.00..7794.27 rows=0 width=104)
                      ->  Final HashAggregate  (cost=0.00..7793.27 rows=3 width=76)
                            Group Key: l_returnflag, l_linestatus
                          [id=16 dop=20 time=2427/2425/2424ms rows=4(1/0/0) mem=574/570/569KB open=2427/2425/2424ms get_next=1/0/0ms]
                            ->  Redistribution  (cost=0.00..7792.95 rows=1881 width=76)
                                  Hash Key: l_returnflag, l_linestatus
                                [20:20 id=100002 dop=20 time=2427/2424/2423ms rows=80(20/4/0) mem=3528/1172/584B open=1/0/0ms get_next=2426/2424/2423ms]
                                  ->  Partial HashAggregate  (cost=0.00..7792.89 rows=1881 width=76)
                                        Group Key: l_returnflag, l_linestatus
                                      [id=12 dop=20 time=2428/2357/2256ms rows=80(4/4/4) mem=574/574/574KB open=2428/2357/2256ms get_next=1/0/0ms]
                                        ->  Local Gather  (cost=0.00..7791.81 rows=44412 width=76)
                                            [id=11 dop=20 time=2427/2356/2255ms rows=936(52/46/44) mem=7/6/6KB open=0/0/0ms get_next=2427/2356/2255ms pull_dop=9/9/9]
                                              ->  Decode  (cost=0.00..7791.80 rows=44412 width=76)
                                                  [id=8 dop=234 time=2435/1484/5ms rows=936(4/4/4) mem=0/0/0B open=2435/1484/5ms get_next=4/0/0ms]
                                                    ->  Partial HashAggregate  (cost=0.00..7791.70 rows=44412 width=76)
                                                          Group Key: l_returnflag, l_linestatus
                                                        [id=5 dop=234 time=2435/1484/3ms rows=936(4/4/4) mem=313/312/168KB open=2435/1484/3ms get_next=0/0/0ms]
                                                          ->  Project  (cost=0.00..3550.73 rows=584421302 width=33)
                                                              [id=4 dop=234 time=2145/1281/2ms rows=585075720(4222846/2500323/3500) mem=142/141/69KB open=10/1/0ms get_next=2145/1280/2ms]
                                                                ->  Project  (cost=0.00..2585.43 rows=584421302 width=33)
                                                                    [id=3 dop=234 time=582/322/2ms rows=585075720(4222846/2500323/3500) mem=142/142/69KB open=10/1/0ms get_next=582/320/2ms]
                                                                      ->  Index Scan using Clustering_index on lineitem  (cost=0.00..261.36 rows=584421302 width=25)
                                                                            Segment Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                            Cluster Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                          [id=2 dop=234 time=259/125/1ms rows=585075720(4222846/2500323/3500) mem=1418/886/81KB open=10/1/0ms get_next=253/124/0ms]
  
  ADVICE: 
  [node id : 1000xxx] distribution key miss match! table lineitem defined distribution keys : l_orderkey; request distribution columns : l_returnflag, l_linestatus; 
  shuffle data skew in different shards! max rows is 20, min rows is 0
  
  Query id:[300200511xxxx]
  ======================cost======================
  Total cost:[2505] ms
  Optimizer cost:[47] ms
  Init gangs cost:[4] ms
  Build gang desc table cost:[2] ms
  Start query cost:[18] ms
  - Wait schema cost:[0] ms
  - Lock query cost:[0] ms
  - Create dataset reader cost:[0] ms
  - Create split reader cost:[0] ms
  Get the first block cost:[2434] ms
  Get result cost:[2434] ms
  ====================resource====================
  Memory: 921(244/230/217) MB,  straggler worker id: 72969760xxx
  CPU time: 149772(38159/37443/36736) ms, straggler worker id: 72969760xxx
  Physical read bytes: 3345(839/836/834) MB, straggler worker id: 72969760xxx
  Read bytes: 41787(10451/10446/10444) MB, straggler worker id: 72969760xxx
  DAG instance count: 41(11/10/10), straggler worker id: 72969760xxx
  Fragment instance count: 275(70/68/67), straggler worker id: 72969760xxx

• 結果解釋

  EXPLAIN ANALYZE的執行結果反映的是真實執行路徑，其結果是一個由多個運算元組成的樹狀結構，會反映出每個階段每個運算元的詳細執行資訊。EXPLAIN ANALYZE的結果主要包括Query Plan、Advice、Cost耗時、Resource資源消耗情況。

QUERY PLAN

在QUERY PLAN中，會反映每個運算元的詳細執行資訊。同EXPLAIN一樣，EXPLAIN ANALYZE中的Query Plan需要從下往上看，每個箭頭（->）代表一個節點。

由於一條SQL語句可能涉及到多個運算元，下面將會有單獨章節對運算元進行詳細介紹，請參見運算元含義。

ADVICE

ADVICE中主要系統根據當前EXPLAIN ANALYZE的執行結果，自動產生的調優建議如下：

• 建議為表設定distribution key，clustering key，bitmap等，如Table xxx misses bitmap index。

• 表缺少統計資訊：Table xxx Miss Stats! please run 'analyze xxx';。

• 資料可能存在傾斜：shuffle data xxx in different shards! max rows is 20, min rows is 0。

Cost耗時

Cost代表Query的總耗時以及在每個階段啟動並執行詳細耗時，可以通過每個階段的耗時初步判斷效能瓶頸。

Total cost：代表Query執行的總耗時，單位毫秒（ms），其中：

• Optimizer cost：代表最佳化器（QO）產生執行計畫的耗時，單位毫秒（ms）。

• Build gang desc table cost：將查詢最佳化工具產生的執行計畫，轉換為執行引擎所需的資料結構所花費的時間，單位毫秒（ms）。

• Init gangs cost：將QO產生的執行計畫進一步預先處理，並將請求發送給執行引擎，從而開始Start Query階段的耗時，單位毫秒（ms）。

• Start query cost：從Init gangs步驟執行完成時開始計算，真正開始執行Query前的初始化階段，包括了加鎖，對齊Schema版本等過程，主要有以下幾個節點：

  • Wait schema cost：儲存引擎（Storage Engine，SE）和Frontend（FE）對齊Schema版本所耗時間。當表的Schema發生變化時，FE會更新節點版本，同理SE也會更新節點版本，如果FE和SE的版本不對齊，則會出現Schema延遲。如果延遲較高，一般原因是因為SE處理慢，尤其是分區父表的DDL較多時，延遲較高，導致資料寫入/查詢慢，建議針對性最佳化DDL頻率。

  • Lock query cost：Query加鎖所耗時間，如果耗時較高，則說明Query在等鎖。

  • Create dataset reader cost：建立索引資料讀取器的耗時，耗時較高有可能是未命中Cache。

  • Create split reader cost：開啟檔案消耗的時間，耗時高則說明檔案meta沒有命中緩衝，IO開銷較高。

• Get result cost：從Start Query階段結束開始計算，到所有結果均返回所花費的時間。Get result cost包含Get the first block cost，單位毫秒（ms）。

  • Get the first block cost：從Start Query階段結束開始計算，到返回第一批資料（即第一個record batch）為止的時間。某些情境下該指標會與比Get result cost指標非常接近或者一致。例如當查詢計劃的頂部是Hash Agg運算元，建立Hash Table做彙總運算需要依賴下遊運算元的全量資料。而對於普通帶過濾條件的查詢，資料流式進行計算和返回，此情境下，該指標通常會與Get result cost數值有較大的差距（取決於資料量）。

Resource資源消耗情況

Resource代表Query啟動並執行資源消耗，格式為：total(max/avg/min)，包含總的資源累加和消耗資源的最大值、平均值和最小值。

因為Hologres是分布式引擎，一個執行個體有多個Worker節點，每個Worker節點計算完成會進行結果合并，並最終返回給用戶端。因此在資源消耗中，會使用total(max worker/avg worker/min worker)的格式來反應整體消耗，其中：

• total：代表Query的總消耗。

• max：代表單個Worker節點消耗的最大值。

• avg：代表平均Worker節點的消耗（avg=total/worker節點數）。

• min：代表單個Worker節點消耗的最小值。

以下為各資源消耗指標的詳細解釋：

運算元含義

SCAN

• seq scan

  Seq Scan表示順序地從表中讀取資料，會進行全表掃描。on後對應的是所掃描的表名。

  樣本：對一張普通內表查詢，在執行計畫中會有Seq scan。

  EXPLAIN SELECT * FROM public.holo_lineitem_100g;

  返回結果：

  

  • 查詢分區表

    如果是分區表，執行計畫中則是Seq Scan on Partitioned Table，且會通過Partitions selected: x out of y來展示SQL中掃描了多少分區。

    樣本：查詢一個分區父表，且只掃描了一個分區。

    EXPLAIN SELECT * FROM public.hologres_parent;

    返回結果：

    

  • 查詢外部表格

    如果查詢的是外部表格，執行計畫中則會有Foreign Table Type區分外部表格的來源。type分類包括：MaxCompute、OSS、Hologres。

    樣本：查詢MaxCompute外部表格。

    EXPLAIN SELECT * FROM public.odps_lineitem_100;

    返回結果：

    

• Index Scan和 Index Seek

  如果掃描表有命中索引，根據表的儲存格式（行存或列存）不同，Hologres在底層使用的索引也不同，根據索引掃描時主要有Clustering_index和Index Seek（又名pk_index）兩種索引。其中：

  • Clustering_index：表示使用了列存表的索引（例如segment 、clustering等），只要查詢命中索引就會使用Clustering_index。Seq Scan Using Clustering_index通常會跟Filter一起出現，Filter是子節點，會列出命中的索引，包括clustering filter、segment filter、bitmap filter等，詳情請參見列存表原理。

    • 樣本1：查詢命中索引。

      BEGIN;
      CREATE TABLE column_test (
       "id" bigint not null ,
       "name" text not null ,
       "age" bigint not null 
      );
      CALL set_table_property('column_test', 'orientation', 'column');
      CALL set_table_property('column_test', 'distribution_key', 'id');
      CALL set_table_property('column_test', 'clustering_key', 'id');
      COMMIT;
      
      INSERT INTO column_test VALUES(1,'tom',10),(2,'tony',11),(3,'tony',12);
      
      EXPLAIN SELECT * FROM column_test WHERE id>2;

      返回結果：

      

    • 樣本2：假如查詢沒有命中索引，則不會使用clustering_index。

      EXPLAIN SELECT * FROM column_test WHERE age>10;

      返回結果：

      

  • Index Seek（又名pk_index）：表示使用了行存表的索引，主要是主鍵索引。一般情況下，基於主鍵的行存表點查是走Fixed Plan，但是未走Fixed Plan的行存表且帶有主鍵的查詢就會走pk_index，更多原理請參見行存表原理。

    樣本：查詢行存表。

    BEGIN;
    CREATE TABLE row_test_1 (
        id bigint not null,
        name text not null,
        class text ,
    PRIMARY KEY (id)
    );
    CALL set_table_property('row_test_1', 'orientation', 'row');
    CALL set_table_property('row_test_1', 'clustering_key', 'name');
    COMMIT;
    INSERT INTO row_test_1 VALUES ('1','qqq','3'),('2','aaa','4'),('3','zzz','5');
    
    BEGIN;
    CREATE TABLE row_test_2 (
        id bigint not null,
        name text not null,
        class text ,
    PRIMARY KEY (id)
    );
    CALL set_table_property('row_test_2', 'orientation', 'row');
    CALL set_table_property('row_test_2', 'clustering_key', 'name');
    COMMIT;
    INSERT INTO row_test_2 VALUES ('1','qqq','3'),('2','aaa','4'),('3','zzz','5');
    
    --pk_index
    EXPLAIN SELECT * FROM (SELECT id FROM row_test_1 WHERE id = 1) t1 JOIN row_test_2 t2 ON t1.id = t2.id;
    

    返回結果：

    

Filter

Filter代表將資料根據SQL條件進行過濾，一般會跟隨seq scan on table一起，是seq scan的子節點，表示掃描表時是否有過濾，以及過濾條件是否命中索引。主要包括以下幾種Filter：

• Filter

  如果執行計畫中，僅包含Filter，則說明過濾條件沒有命中任何索引。此時需要去檢查表索引，重新為SQL設定合理的索引，從而提升查詢效能。

  說明

  如果執行計畫中存在One-Time Filter: false，說明輸出結果為空白集。

  樣本：

  BEGIN;
  CREATE TABLE clustering_index_test (
   "id" bigint not null ,
   "name" text not null ,
   "age" bigint not null 
  );
  CALL set_table_property('clustering_index_test', 'orientation', 'column');
  CALL set_table_property('clustering_index_test', 'distribution_key', 'id');
  CALL set_table_property('clustering_index_test', 'clustering_key', 'age');
  COMMIT;
  
  INSERT INTO clustering_index_test VALUES (1,'tom',10),(2,'tony',11),(3,'tony',12);
  
  EXPLAIN SELECT * FROM clustering_index_test WHERE id>2;

  返回結果：

  

• Segment Filter

  Segment Filter表示查詢時命中了segment索引，與index_scan一起出現，詳情請參見Event Time Column（Segment Key）。

• Cluster Filter

  Cluster Filter表示查詢時命中了clustering索引，詳情請參見聚簇索引Clustering Key。

• Bitmap Filter

  Bitmap Filter表示查詢時命中了Bitmap索引，詳情請參見位元影像索引Bitmap。

• Join Filter

  join完後還需要對資料做一遍filter。

Decode

Decode表示對資料進行解碼或者編碼，以加速text等文本類資料的計算。

Local Gather和Gather

在Hologres中，資料會在shard內按照檔案的形式儲存。Local Gather代表資料從多個檔案合并到一個shard上。Gather則代表將多個shard的資料合併匯總成最終結果。

樣本：

EXPLAIN SELECT * FROM public.lineitem;

返回結果：其執行計畫如下，代表資料被掃描出來後，會先通過Local Gather在shard級匯總，然後通過Gather合并成最終資料。

Redistribution

Redistribution表示資料通過雜湊分布或者隨機分布，查詢時shuffle到一個或者多個shard。

• Redistribution運算元常見的情境：

  • 通常出現在join、count distinct（本質上也是join）以及group by的情境中，表的distribution key沒有設定，或者設定不合理，導致查詢時資料在多個shard間shuffle。尤其是在多表join的情境，如果出現redistribution，則說明沒有利用local join的能力，導致查詢效能不佳。

  • 當對應的key（如join key、group by key）涉及到運算式時，例如對欄位cast類型改變了原來的欄位類型等，則無法利用上local join的能力，會出現redistribution。

• 樣本：

  • 樣本1：兩表join，distribution key設定不合理導致出現redistribution。

    BEGIN;
    CREATE TABLE tbl1(
    a int not null,
    b text not null
    );
    CALL set_table_property('tbl1', 'distribution_key', 'a');
    CREATE TABLE tbl2(
    c int not null,
    d text not null
    );
    CALL set_table_property('tbl2', 'distribution_key', 'd');
    COMMIT;
    
    EXPLAIN SELECT * FROM tbl1  JOIN tbl2 ON tbl1.a=tbl2.c;

    返回結果：執行計畫如下，出現redistribution，說明distribution key設定不合理（SQL中join條件是tbl1.a=tbl2.c，而兩個表的distribution key分別為a和d，join時資料就會shuffle）。

    

    調優建議：如果出現redistribution運算元，建議關注表的distribution key設定是否合理。更多關於redistribution的情境和distribution key的設定請參見分布鍵Distribution Key。

  • 樣本2：如下執行計畫中，join key涉及到運算式，改變了原欄位的類型，無法使用local join的能力，出現redistribution。

    調優建議：盡量不使用運算式。

Join

與標準資料庫的定義一致，多表關聯（Join）根據SQL的書寫方式又分為hash join、nested loop和merge join。

• Hash Join

  hash join是指兩個表或者多表join時，基於其中一個表（一般為小表）在記憶體中構建一個hash表，並把join的列值進行hash計算後放進hash表中，之後逐行的讀取另外的表，計算出其hash值並在hash表中尋找，最終返回匹配的資料。根據hash join的細分類，還可以繼續劃分，如下表：

  分類	描述
  Hash Left Join	多表關聯時，根據join條件從左表中返回所有滿足條件的行，然後匹配右表，如果沒有匹配到資料，則返回null。
  Hash Right Join	多表關聯時，返回右表的全部行和左邊滿足join條件的行，如果右表的行在左表中沒有匹配資料，那麼左表則返回null。
  Hash Inner Join	多表關聯時，只返回滿足join條件的行。
  Hash Full Join	多表關聯時，從左表和右表返回所有的行，如果其中一個表的資料在另外一個表中沒有匹配，則無法匹配的表返回null。
  Hash Anti Join	僅返回未匹配到的資料，多用於not exists查詢條件。
  Hash Semi Join	有任意匹配則返回一項，通常是由exists查詢，結果不會產生重複項。

  當查看hash join相關的執行計畫時，還需要關注其子節點：

  • hash cond：代表join的關聯條件，如hash cond（tmp.a=tmp1.b）。

  • hash key：一般出現在group by情境中，表示將group by的key在多個shard上進行hash計算。

  當出現hash join時，我們需要額外關注join表中的小表（資料量較小的表）是否是做了hash表，可以通過如下幾種方式查看：

  • 執行計畫中，有hash字樣的表是hash表。

  • 執行計畫中，從下往上看，最下面的表則是hash表。

  調優建議：

  • 更新統計資料

    hash join的核心調優思路就是儘可能的將小表作為hash表，如果是大表作為hash表，因為要在記憶體中構建hash表，那麼就會消耗較多的資源，大多數情況都是因為表的統計資訊沒有更新導致最佳化器QO將大表作為了hash表。

    樣本：因為統計資訊未更新（rows=1000），兩表join時大表hash_join_test_2作為hash表（tbl2資料量有100萬，tbl1資料量只有1萬），導致查詢效率較低。

    BEGIN ;
    CREATE TABLE public.hash_join_test_1 (
        a integer not null,
        b text not null
    );
    CALL set_table_property('public.hash_join_test_1', 'distribution_key', 'a');
    CREATE TABLE public.hash_join_test_2 (
        c integer not null,
        d text not null
    );
    CALL set_table_property('public.hash_join_test_2', 'distribution_key', 'c');
    COMMIT ;
    
    INSERT INTO hash_join_test_1 SELECT i, i+1 FROM generate_series(1, 10000) AS s(i);
    INSERT INTO hash_join_test_2 SELECT i, i::text FROM generate_series(10, 1000000) AS s(i);
    
    EXPLAIN SELECT * FROM hash_join_test_1 tbl1  JOIN hash_join_test_2 tbl2 ON tbl1.a=tbl2.c;

    執行計畫中大表hash_join_test_2作為hash表，如下：

    如果統計資訊沒有及時更新，可以手動執行analyze <tablename>更新統計資料。樣本如下：

    ANALYZE hash_join_test_1;
    ANALYZE hash_join_test_2;

    更新後的執行計畫如下，小表hash_join_test_1變成了hash表，且最佳化器預估的行數正確。

  • 調整join order

    通常情況下，更新統計資料能解決大部分的join關聯問題。但是當SQL比較複雜且是多表關聯時（至少5張表），Hologres最佳化器（QO）的預設機制會根據SQL選擇更加合理的執行計畫，導致最佳化器在選擇執行計畫上會耗費較多時間。我們可以通過如下GUC控制join order，以降低QO的耗時。

    SET optimizer_join_order = '<value>'; 

    value參數的取值如下：

    value取值	描述
    exhaustive（預設）	通過演算法進行Join Order轉換，會產生最優的執行計畫，但多表關聯時會導致最佳化器開銷變高。
    query	按照SQL的方式產生執行計畫，最佳化器不做任何改變，僅適用於多表關聯且表資料量不大（低於億級）的情境，從而降低QO開銷。同時不建議將該參數設定成DB層級，否則會影響其餘join的效能。
    greedy	通過貪心演算法產生Join Order，最佳化器開銷適中。

• Nested Loop Join和Materialize

  Nested Loop代表嵌套迴圈串連，多表關聯時，先從一張表中讀取資料，成為外層表，再將外層驅動表的每條資料遍曆另外的表（即內層表），然後內外層表嵌套迴圈進行Join，相當於計算笛卡爾積。在執行計畫中第一內層表通常有Materialize運算元。

  最佳化建議：

  • Nested Loop的原理是內層表被外層表驅動，外層表返回的每一行都要在內層表中檢索與之匹配的行，因此返回的結果集不能太大，否則會消耗較多資源，盡量將返回結果較小的表作為外層表。

  • 非等值的join通常會產生Nested Loop join，在SQL書寫中盡量避免非等值join。

  • Nested Loop join的樣本如下：

    BEGIN;
    CREATE TABLE public.nestedloop_test_1 (
        a integer not null,
        b integer not null
    );
    CALL set_table_property('public.nestedloop_test_1', 'distribution_key', 'a');
    CREATE TABLE public.nestedloop_test_2 (
        c integer not null,
        d text not null
    );
    CALL set_table_property('public.nestedloop_test_2', 'distribution_key', 'c');
    COMMIT;
    
    INSERT INTO nestedloop_test_1 SELECT i, i+1 FROM generate_series(1, 10000) AS s(i);
    INSERT INTO nestedloop_test_2 SELECT i, i::text FROM generate_series(10, 1000000) AS s(i);
    
    EXPLAIN SELECT * FROM nestedloop_test_1 tbl1,nestedloop_test_2 tbl2 WHERE tbl1.a>tbl2.c;

    從下述執行計畫中可以看出存在Materialize和Nested Loop運算元，說明該SQL走的是Nested Loop join路徑。

• Cross Join

  從V3.0版本開始，Cross Join作為Nested Loop Join的一種實現最佳化，主要用於處理包含小表的非等值Join等情境。與Nested Loop Join每次從外層迴圈提取一行資料並遍曆內層迴圈所有資料，然後重設內層迴圈子查詢狀態的計算流程相比，Cross Join直接將小表資料全部載入到記憶體中，然後依次與流式讀取的大表資料進行Join計算，從而顯著提升計算效能。然而，相較於Nested Loop Join，Cross Join會佔用更多的記憶體資源。

  您可以查看查詢計劃，如果出現如下Cross Join運算元，表示使用了Cross Join。

  

  如需禁用Cross Join，可以使用以下SQL語句進行關閉。

  -- session層級關閉
  SET hg_experimental_enable_cross_join_rewrite = off;
  
  -- database層級關閉，建立串連生效
  ALTER database <database name> hg_experimental_enable_cross_join_rewrite = off;

Broadcast

Broadcast指通過廣播的方式將資料分發到各個shard，通常用在Broadcast Join的情境中，一般是小表join大表。下發SQL時，最佳化器QO會比較redistribution和Broadcast的代價，然後根據演算法產生執行計畫。

調優建議：

• 當查詢表的資料量較小且執行個體的shard數比較少時（比如shard count=5）時，走Broadcast比較划算。

  樣本：兩表join，broadcast_test_1和broadcast_test_2的資料量差異比較明顯。

  BEGIN;
  CREATE TABLE broadcast_test_1 (
      f1 int, 
      f2 int);
  CALL set_table_property('broadcast_test_1','distribution_key','f2');
  CREATE TABLE broadcast_test_2 (
      f1 int,
      f2 int);
  COMMIT;
  
  INSERT INTO broadcast_test_1 SELECT i AS f1, i AS f2 FROM generate_series(1, 30)i;
  INSERT INTO broadcast_test_2 SELECT i AS f1, i AS f2 FROM generate_series(1, 30000)i;
  
  ANALYZE broadcast_test_1;
  ANALYZE broadcast_test_2;
  
  EXPLAIN SELECT * FROM broadcast_test_1 t1, broadcast_test_2 t2 WHERE t1.f1=t2.f1;

  返回結果：

  

• 如果不是小表但又產生了Broadcast運算元，通常情況是因為統計資訊未及時更新導致（比如統計資訊中rows=1000，實際掃描100萬），需要及時更新統計資料，執行analyze <tablename>。

Shard prune和Shards selected

• Shard prune

  表示擷取Shard的方式，包括：

  • lazaily：根據節點中的Shard ID先標記對應的Shard，在後續計算時選擇對應的Shard。

  • eagerly：根據命中的Shard選擇對應的Shard，不需要的Shard則不需要選擇。

  最佳化器會根據執行計畫來自動匹配Shard prume的方式，無需手動調節。

• Shards selected

  Shards selected表示選中了多少個Shard，例如1 out of 20表示在20個Shard中選中了一個Shard。

ExecuteExternalSQL

如Hologres的產品架構介紹，Hologres的計算引擎會分為HQE、PQE、SQE等，其中PQE是原生Postgres引擎，部分Hologres自研引擎HQE還沒有支援的運算元和函數，會通過PQE執行，相比於HQE，PQE的執行效率會更低。當我們在執行計畫中看到有ExecuteExternalSQL運算元，說明有函數或者運算元走了PQE。

• 樣本1：走PQE的SQL樣本如下。

  CREATE TABLE pqe_test(a text);
  INSERT INTO pqe_test VALUES ('2023-01-28 16:25:19.082698+08');
  EXPLAIN SELECT a::timestamp FROM pqe_test;

  執行計畫如下：有ExecuteExternalSQL說明::timestamp運算元走的是PQE。

  

• 樣本2：將::timestamp改寫成to_timestamp，可以走HQE，SQL樣本如下。

  EXPLAIN SELECT to_timestamp(a,'YYYY-MM-DD HH24:MI:SS') FROM pqe_test;

  執行計畫如下，結果中沒有ExecuteExternalSQL，表明沒有走PQE。

調優建議：通過執行計畫找到SQL走了PQE的函數或者運算元，並通過改寫使其走到HQE，提升查詢效率。常見的運算元改寫列表請參見最佳化查詢效能。

EXPLAIN SELECT l_shipdate FROM public.lineitem ORDER BY l_shipdate;

EXPLAIN SELECT * FROM public.lineitem limit 1;

相關文檔

HoloWeb可視化查看執行計畫，請參見查看執行計畫。