最適化ルーチンによる部分的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;
- [(t1 X t2) X t3]'
- [(t2 X t3) X t1]'
- [(t1 X t3) X t2]'
ここでXは結合を表わし、[ ]'は、リレーションに適用される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操作の特定
- 部分的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;
- ((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;