17.00 - 17.05 - 部分的GROUP BYブロックの最適化 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLリクエストおよびトランザクション処理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
ユーザー ガイド
Publication ID
B035-1142-170K-JPN
Language
日本語 (日本)

最適化ルーチンによる部分的GROUP BYブロックの最適化について

適切な場合、結合の集約は、結合前に実行される部分と、結合後に実行される部分に分割されます。こうすると、結合の作業を少なくするだけでなく、結合後の集約作業も少なくすることができます。

GROUP BY (またはDISTINCT)句があるクエリーの結合クエリー計画では、部分的GROUP BY (PGB)操作により、基本テーブル、結合インデックス、および中間スプールのカーディナリティを減らし、結合クエリー計画の実行に必要な処理を減らすことができます。さらに効率性を高めるために、中間の部分的GROUP BY操作、最終のGROUP BY操作、またはその両方を含む特定の部分的GROUP操作を、特定の事前定義条件に応じて排除することができます。

部分的GROUP BYによる最適化は、結合計画のほとんどすべてのステップで実行できます。初期の段階でGROUP BYを実行することにより、最適化プロセス中の基礎リレーションまたは中間結合リレーションの行数が減少します。ただし、GROUP BY操作およびマージ結合操作では、かなりの行が減少するため、最低のコスト計画で使用する際に互いに競合する傾向があります。このため、最適化ルーチンにとって、代替計画のコスト計算は非常に重要です。

部分的GROUP BYの最適化によって、通常のリクエストの処理時間および入出力時間を短縮できます。テーブルが保有する重複グループ化値が多いほど、入出力処理が大幅に減少し、場合によっては、1/100にまで減らすことができます。

部分的GROUP BY最適化の例

次の一般的な問合わせについて考えてみます。

SELECT b1, c1, SUM(t1.float_1), SUM(t2.float_2), SUM(t3.float_3)
FROM t1,t2,t3
WHERE b1=b2 AND b2=b3
GROUP BY b1,c1;
部分的GROUP BYが有効でない場合、最適化ルーチンは次の結合計画の1つを選択します。
  • [(t1 X t2) X t3]'
  • [(t2 X t3) X t1]'
  • [(t1 X t3) X t2]'

ここでXは結合を表わし、[ ]'は、リレーションに適用されるGROUP BY演算子を意味します。

部分的GROUP BYが有効な場合、次のようないくつかの追加の結合計画が検討されます。
  • [([t2]' X t3) X t1]'
  • [(t1 X [t3]') X t2]'
  • [([t2]' X [t3]') X t1]]'
  • [([t1 X t3]' X [t2]'
  • ..。

最適化ルーチンに、実行可能な結合計画の大きなセットの中から、より低いコストの結合計画を見つける機会が提供されます。検討対象の結合計画が他にある場合でも、その検索時にはごくわずかなCPU時間が消費されるだけで、リクエストの実行時間は大幅に短縮されます。

次の例について考えてみます。

SELECT ps_partkey, SUM(l_quantity), SUM (ps_supplycost)
FROM partsupp, lineitem
WHERE l_partkey = ps_partkey
GROUP BY 1;

部分的GROUP BYが有効でない場合、最適化ルーチンは結合列l_partkeyでlineitemを再分散し、partsuppとの結合を実行して、結合の結果を集約します。1テラバイトのデータベースでは、60億行を再分散してpartsuppとの結合を実行し、集約が実行される60億行の結果セットを生成します。

部分的GROUP BYが有効な場合、最適化ルーチンは最初にリレーションpartsuppとlineitemを個別に集約してから、それらの集約の結果を結合します。

変換なしの場合、約850億行の読取りおよび書込みを実行する必要があります。これは主に、60億行のlineitemリレーションが、8億行のpartsuppリレーションと結合されて、集約用に240億行の結合結果スプールを生成するという結合のためです。

変換がある場合、lineitemリレーションはソートと再分散操作の一部として集約され、2億行のスプールを生成します。partsuppリレーションは、ローカルに集約され、2億行のスプールを別に生成します。これらの2つのスプールは結合されて2億行の結果が生成されます。このとき、行の読み取りおよび書き込み数が全体で約1/3に削減されます。

EXPLAINレポート テキストにおける部分的GROUP BY操作の特定

部分的GROUP BYを使用して行をまとめるには、次の2つの方法があります。
  • 部分的SUMステップ
  • Sort/Groupステップ

部分SUMステップがある初期段階でのGROUP BY

このセクションでは、EXPLAIN句の部分SUMについて説明します。

次の問合わせについて考えてみます。

SELECT l_partkey,SUM(l_quantity),SUM(ps_supplycost)
FROM partsupp, lineitem
WHERE l_partkey = ps_partkey
GROUP BY 1;

部分的GROUP BYが有効になっていないと、結合計画は[partsupp x lineitem]'になります。部分的GROUP BYを有効にすると、結合計画は[partsupp]' x [lineitem]'になります。

次のEXPLAINテキストは、このクエリーの2つのExplanationを示しています。最初のテキストは部分的GROUP BYが無効である場合のExplanation、2番目のテキストは部分的GROUP BYが有効である場合のExplanationです。

これは、部分的GROUP BYが無効な部分的EXPLAINテキストです。

...
4) We do an all-AMPs RETRIEVE step from TPCD50G.lineitem by way of an all-rows scan
   with no residual conditions into Spool 4 (all_amps), which is redistributed by hash 
   code to all AMPs. Then we do a SORT to order Spool 4 by row hash. The result spool 
   file will not be cached in memory. The size of Spool 4 is estimated with high 
   confidence to be 300,005,811 rows. The estimated time for this step is 2 hours and
   51 minutes.
5) We do an all-AMPs JOIN step from TPCD50G.partsupp by way of a RowHash match scan with 
   no residual conditions, which is joined to Spool 4 (Last Use). TPCD50G.partsupp and
   Spool 4 are joined using a merge join, with a join condition of
   ("L_PARTKEY = TPCD50G.partsupp.PS_PARTKEY").
   The input table TPCD50G.partsupp will not be cached in memory, but it is eligible for
   synchronized scanning. The result goes into Spool 3 (all_amps), which is built-
   locally on the AMPs. The result spool file will not be cached in memory. 
   The size of Spool 3 is estimated with low confidence to be 1,200,023,244 rows. 
   The estimated time for this step is 2 hours and 46 minutes.
6) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows
   scan, and the grouping identifier in field 2. Aggregate Intermediate Results are
   computed locally, then placed in Spool 5. The aggregate spool file will not be
   cached in memory. The size of Spool 5 is estimated with low confidence to be 
   1,200,023,244 rows. The estimated time for this step is 10 hours and 6 minutes.

これは、部分的GROUP BYが有効な部分的EXPLAINテキストです。

...
4) We do an all-AMPs partial SUM step to aggregate from TPCD50G.partsupp by way of
   an all-rows scan with no residual conditions, and the grouping identifier in field 1.
   Aggregate Intermediate Results are computed locally, then placed in Spool 6.  
   The input table will not be cached in memory, but it is eligible for synchronized
   scanning.  The aggregate spool file will not be cached in memory.  The size of Spool 6
   is estimated with low confidence to be 10,000,000 rows.  The estimated time for
   this step is 12 minutes and 36 seconds.
5) We execute the following steps in parallel.
   1) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of an all-rows scan
      into Spool 5 (all_amps), which is built locally on the AMPs.  Then we do a SORT
      to order Spool 5 by row hash.  The result spool file will not be cached in
      memory. The size of Spool 5 is estimated with low confidence to be 
      10,000,000 rows.
   2) We do an all-AMPs partial SUM step to aggregate from TPCD50G.lineitem by
      way of an all-rows scan with no residual conditions, and the grouping identifier
      in field 1.  Aggregate Intermediate Results are computed globally, then placed 
      in Spool 10.  The input table will not be cached in memory, but it is eligible
      for synchronized scanning. The size of Spool 10 is estimated with low confidence 
      to be 10,000,000 rows. The estimated time for this step is is 3 hours and 
      39 minutes.
6) We do an all-AMPs RETRIEVE step from Spool 10 (Last Use) by way an all-rows scan
   into Spool 9 (all_amps), which is redistributed by hash code to all AMPs.  Then we do
   a SORT to oder SPOOL 9 by row hash.  The result spool file will not be cached in
   memory. The size of Spool 9 is estimated with no confidence to be 10,000,000 rows. 
7) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of a RowHash match scan,
   which is joined to Spool 9 (Last Use).  Spool 5 and Spool 9 are joined using a
   merge join, with a join condition of ("L_PARTKEY = PS_PASRTKEY").
   The result goes into Spool 3, (all_amps), which is built locally on the AMPs.
   The result spool file will not be cached in memory.  The size of Spool 3 is
   estimated with low confidence to be 993,739,248 rows.  The estimated time for this
   step is 2 hours and 16 minutes.

部分的GROUP BYが無効な場合、SUMステップは、カーディナリティが削減されるステップ6で実行されます。 対照的に、部分的GROUP BYが有効な場合、部分SUMステップは、ステップ4と5.1で実行されます。

部分的GROUP BYによる最適化は結合の前に実行されるので、EXPLAINテキストでは、最適化を簡単に識別できるように、SUMステップの前に部分句が追加されています。

部分的GROUP BYが有効である場合でも、最適化ルーチンは、この最適化を使用しない結合計画を選択できることに注意してください。例えば、小さいグループの多さが原因で、コストの集約でこれを選択しても意味がない場合などです。

SORT/GROUPステップと部分的GROUP BY最適化

この節では、GROUP BYの識別としてのSORT/GROUP句について説明します。次のリクエストには、外部結合を指定する内部問合わせを含む、入れ子の副問合せがあります。内部および外部問い合わせの両方にGROUP BY句があります。

SELECT c_count, COUNT(*) AS custdist
FROM (SELECT c_custkey, COUNT(o_orderkey)
      FROM customer LEFT OUTER JOIN ordertbl
                    ON  c_custkey = o_custkey
                    AND o_comment NOT LIKE '%special%requests%'
      GROUP BY c_custkey) AS c_orders (c_custkey, c_count)
GROUP BY c_count
ORDER BY custdist desc, c_count DESC;

これは、部分的GROUP BYが有効な部分的EXPLAINテキストです。

...
4) We do an all-AMPs RETRIEVE step from TPCD50G.ORDERTBL by way of an all-rows scan
   with a condition of ("NOT(TPCD50G.ORDERTBL.O_COMMENT LIKE '%special%requests%')")
   into Spool 5 (all_amps), which is redistributed by hash code to all AMPs.
   Then we do a SORT/GROUP to order Spool 5 by row hash and non-aggregate
   fields grouping duplicate rows. The result spool file will not be cached in memory.
   The size of Spool 5 is estimated with no confidence to be 67,500,000 rows.
5) We do an all-AMPs JOIN step from TPCD50G.CUSTOMER by way of a RowHash match scan
   with no residual conditions, which is joined to Spool 5 (Last Use).
   TPCD50G.CUSTOMER and Spool 5 are left outer joined using a merge join, 
   with a join condition of ("TPCD50G.CUSTOMER.C_CUSTKEY = O_CUSTKEY"). 
   The input table TPCD50G.CUSTOMER will not be cached in memory. The result goes 
   into Spool 3 (all_amps), which is built locally on the AMPs. The result spool file
   will not be cached in memory. The size of Spool 3 is estimated with low confidence
   to be 74,954,952 rows. The estimated time for this step is 15 minutes and
   32 seconds.
6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan
   into Spool 1 (all_amps), which is built locally on the AMPs. The result spool file
   will not be cached in memory. The size of Spool 1 is estimated with low confidence
   to be 74,954,952 rows. The estimated time for this step is 11 minutes and 6 seconds.
7) We do a SUM step to aggregate from Spool 1 (Last Use) by way of an all-rows scan,
   and the grouping identifier in field 3. Aggregate Intermediate Results are computed
   globally, then placed in Spool 10. The size of Spool 10 is estimated with no
   confidence to be 8,658 rows. The estimated time for this step is 1 minute and
   26 seconds.

このExplanationでは、 SORT/GROUP句はステップ4に出現します。これは、部分的GROUP BYが、カーディナリティを減らすために初期の段階で使用されていることを意味します。

最後の部分的GROUP BY集約の排除によるパフォーマンス向上

次に、最後のGROUP BY操作を必要としない結合計画の例を示します。

TPC-Hベンチマーク用の次のクエリーを送信するとします。

SELECT l_suppkey,SUM(l_quantity),SUM(ps_availqty)
FROM lineitem,partsupp
WHERE l_suppkey=ps_suppkey
GROUP BY 1;

このクエリーでは、結合計画[lineitem]' x [partsupp]'が使用されます。

次のリクエストについて考えてみます。

SELECT b1, c1, SUM(t1.float_1), SUM(t2.float_2), SUM(t3.float_3)
FROM t1, t2, t3
WHERE b1=b2
AND   b2=b3
GROUP BY 1,2;

部分的GROUP BYがない場合、このクエリーは、最終的なGROUP BY操作で結合計画[[t2xt3]'x t1']'を使用します。列は結合列をカバーするので、最後のGROUP BYはスキップ可能です。また、結合計画は[t2xt3']'xt1'に最適化できます。

次のリクエストでは、最後のGROUP BY操作を実行する必要はありません。

SELECT c_custkey, c_name,
       SUM(l_extendedprice*(1-l_discount)(FLOAT))(DECIMAL(18,2))
         AS revenue, c_acctbal, n_name, 
         c_address,c_phone,c_comment
FROM customer, ordertbl, lineitem, nation
WHERE c_custkey = o_custkey
AND   l_orderkey = o_orderkey
AND   o_orderdate >= '1993-10-01'
AND   o_orderdate < DATE '1993-10-01' + INTERVAL '3' MONTH
AND   l_returnflag = 'R'
AND   c_nationkey = n_nationkey
GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address,
         c_comment
ORDER BY revenue DESC;
このクエリーには、システム構成によって、2つの結合計画が考えられます。どちらの計画も最後のGROUP BY操作を必要としません。2つの結合計画は次のとおりです。
  • ((lineitem X ordertbl)' customer) X nation
  • ((ordertbl X customer) X lineitem)' X nation

次のリクエストをTPCHパフォーマンス ベンチマーク データベースに対して実行依頼するとします。

SELECT c_custkey,c_name,SUM(l_extendedprice*(1-l_discount)(FLOAT))
       (DECIMAL(18,2)) AS revenue, c_acctbal, n_name, c_address,
       c_phone,c_comment
FROM customer, ordertbl, lineitem, nation
WHERE c_custkey = o_custkey
AND   l_orderkey = o_orderkey
AND   o_orderdate >= '1993-10-01'
AND   o_orderdate < DATE '1993-10-01' + INTERVAL '3' MONTH
AND   l_returnflag = 'R'
AND   c_nationkey = n_nationkey
GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address,
         c_comment
ORDER BY revenue DESC;

nationテーブルの定義がn_nameではなくn_key上のプライマリ インデックスを定義するように変更されるとき、部分的GROUP BYのない結合計画を有効にすると、次のように最後のGROUP BYが含まれます: (((lineitem x ordertbl)' x customer) x nation)'。結合計画の指定の最後にある'GROUP BY演算子に注意してください。

部分的GROUP BY最適化では、最後のGROUP BYを削除して、プライマリ インデックス定義のこの変更を補正します。これにより、計画は((lineitem x ordertbl)' x customer) x nationとなります。

部分的GROUP BY最適化を可能にする統計の収集

最適化ルーチンが、GROUP BY演算子を効果的に適用できる時期を認識するためには、通常のリクエストのすべての結合列とGROUP BY列で統計を収集します。

例えば、次のクエリーでは、l_orderkey、o_orderkey、o_orderdate、c_custkey、c_nationkey、およびn_nationkeyで統計を収集する必要があります。

SELECT c_custkey, c_name,
       SUM(l_extendedprice*(1-l_discount)(FLOAT))(DECIMAL(18,2))
         AS revenue, c_acctbal, n_name,
         c_address,c_phone,c_comment
FROM customer, ordertbl, lineitem, nation
WHERE c_custkey=o_custkey
AND   l_orderkey=o_orderkey
AND   o_orderdate>='1993-10-01'
AND   o_orderdate<DATE '1993-10-01' + INTERVAL '3' MONTH
AND   l_returnflag='R'
AND   c_nationkey=n_nationkey
GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address,
         c_comment
ORDER BY revenue DESC;

別のリクエスト例として、次のクエリーではl_partkeyとps_partkeyで統計を収集する必要があります。

SELECT l_partkey, SUM(l_quantity), SUM(ps_supplycost)
FROM partsupp, lineitem
WHERE l_partkey=ps_partkey
GROUP BY 1;