EXPLAIN によるパーティションプルーニング診断とフルスキャン回避 - MaxCompute - Alibaba Cloud - MaxCompute

このトピックでは、パーティションプルーニングの有効性を評価する方法について説明します。

背景情報

MaxCompute では、パーティションテーブルを作成する際に、1 つ以上のフィールドをパーティション列として定義できます。データをクエリする際にパーティション名を指定すると、該当するパーティション内のデータのみを読み取れます。この方法によりフルテーブルスキャンを回避でき、処理効率の向上とコスト削減につながります。

パーティションプルーニングでは、パーティション列にフィルター条件を適用します。これにより、SQL クエリはフルテーブルスキャンを実行する代わりに、パーティションのサブセットを読み取れるため、リソースを節約し、クエリ効率を向上できます。ただし、パーティションプルーニングが失敗するケースはよくあります。

このトピックでは、パーティションプルーニングに関する次の2つの項目について説明します：

パーティションプルーニングが有効かどうかを判断する方法。
パーティションプルーニングが失敗するシナリオの分析。

パーティションプルーニングの有効性の判断

EXPLAIN コマンドを使用して SQL の実行計画を表示し、パーティションプルーニングが有効かどうかを判断できます。

パーティションプルーニングが無効な場合。
```
explain
select seller_id
from xxxxx_trd_slr_ord_1d
where ds=rand();
```
実行計画から、SQL クエリがテーブルの全 1,344 パーティションを読み取ることがわかります。

パーティションプルーニングが有効な場合。

explain
select seller_id
from xxxxx_trd_slr_ord_1d
where ds='20150801';

In Task M1 Stg1:
Data source:rd_slr_ord_id/dtrd_slr_ord_id/ds=20150801
TS: alias: rd_slr_ord_id/dtrd_slr_ord_id
    FIL: EQUAL(rd_slr_ord_id/dtrd_slr_ord_id.ds, '20150801')
        SEL: rd_slr_ord_id/dtrd_slr_ord_id.seller_id

実行計画から、SQL クエリがテーブルの 20150801 パーティションのみを読み取ることがわかります。

パーティションプルーニングが失敗するシナリオ

ユーザー定義関数による失敗
パーティション列に対するフィルター条件でユーザー定義関数 (UDF) または特定の組み込み関数を使用すると、パーティションプルーニングが失敗する場合があります。そのため、非標準の関数を使用してパーティション値をフィルタリングする場合は、EXPLAIN コマンドで実行計画を確認し、パーティションプルーニングが有効であることを確認してください。
```
explain
select ...
from xxxxx_base2_brd_ind_cw
where ds = concat(SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 1), SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 2))
```
説明
UDF は現在、パーティションプルーニングをサポートしています。詳細については、「WHERE 句 (WHERE_condition)」をご参照ください。
JOINの使用時の失敗
SQL ステートメントで JOIN を使用する場合：
- パーティションのフィルター条件がWHERE 句にある場合、パーティションプルーニングが有効になります。
- パーティションのフィルター条件がON 句にある場合、左外部結合では右テーブルでプルーニングが有効ですが、左側テーブルでは無効です。
このセクションでは、3 種類の JOIN における動作を説明します：
- LEFT OUTER JOIN
  - すべてのパーティションのフィルター条件がON 句にある場合
```
set odps.sql.allow.fullscan=true;
explain
select a.seller_id
    ,a.pay_ord_pbt_1d_001
from xxxxx_trd_slr_ord_1d a
left outer join
     xxxxx_seller b
on a.seller_id=b.user_id
and a.ds='20150801'
and b.ds='20150801';
```
    上記の explain ステートメントを実行すると、次のように実行計画が返されます。計画から、左側テーブルのデータソースが 1,770 個のパーティションをすべてスキャンしており、フィルター条件が FIL 演算子にプッシュダウンされていることが分かります：
```
In Task M1_Stg1:
  Data source: xxx/ds=trd_slr_ord_1d/ds=..., ... (total 1770)
  TS: alias: a
In Task M2_Stg1:
  Data source: xxx/ds=seller/ds=20150801
  TS: alias: b
In Task J3_1_2_Stg1:
    JOIN: a LEFT OUTER JOIN b
      FIL: And(EQUAL(a.ds, '20150801'), EQUAL(b.ds, '20150801'))
```
    上記の出力は、左側テーブルではフルテーブルスキャンが実行される一方、右テーブルではパーティションプルーニングが有効であることを示しています。
  - すべてのパーティションのフィルター条件がWHERE 句にある場合
```
set odps.sql.allow.fullscan=true;
explain
select a.seller_id
    ,a.pay_ord_pbt_1d_001
from xxxxx_trd_slr_ord_1d a
left outer join
    xxxxx_seller b
on a.seller_id=b.user_id
where a.ds='20150801'
and b.ds='20150801';
```
```
Task M2_Stg1:
  Data source: xxx/ds=seller/ds=20150801
  TS: alias: b
      FIL: EQUAL(b.ds, '20150801')
          RS: order: +
              optimizeOrderBy: False
              valueDestLimit: 0
              keys:
                  b.user_id
              values:
              partitions:
                  b.user_id
In Task J3_1_2_Stg1:
  JOIN: a LEFT OUTER JOIN unknown
      filter:
          0:
          1:
      SEL: a._col0, a._col20
      FS: output: None
In Task M1_Stg1:
  Data source: xxx/ds= trd slr ord 1d/ds=20150801
  TS: alias: a
```
    実行計画から、両方のテーブルでパーティションプルーニングが有効であることがわかります。
- RIGHT OUTER JOIN
  左外部結合と同様に、右外部結合のON 句にパーティションのフィルター条件がある場合、パーティションプルーニングが有効なのは左側テーブルのみです。条件がWHERE 句にある場合は、両方のテーブルでパーティションプルーニングが有効です。
- FULL OUTER JOIN
  完全外部結合では、パーティションのフィルター条件がWHERE 句にある場合にのみ有効です。条件がON 句にある場合、いずれのテーブルでもパーティションプルーニングは有効になりません。

注意事項

パーティションプルーニングの失敗は影響が大きく、検知が困難な場合が多いです。そのため、コードをコミットする前にこれらの失敗がないか確認してください。
ユーザー定義関数でパーティションプルーニングを使用するには、クラスを変更するか、SQL ステートメントの前に set odps.sql.udf.ppr.deterministic = true; を追加する必要があります。詳細については、「WHERE 句 (WHERE_condition)」をご参照ください。