Evaluate partition pruning effectiveness

Background

In MaxCompute, you can define one or more fields as partition columns when creating a partitioned table. When you query the data, you can specify a partition name to read only the data in the corresponding partition. This approach avoids a full table scan, improving processing efficiency and reducing costs.

Partition pruning involves applying filter conditions to partition columns. This enables an SQL query to read a subset of partitions instead of performing a full table scan, saving resources and improving query efficiency. However, it is common for partition pruning to fail.

Determine if partition pruning is effective

Partition pruning failure scenarios

• Failure caused by a user-defined function

  Partition pruning can fail if the filter condition on a partition column uses a user-defined function (UDF) or certain built-in functions. Therefore, if you use a non-standard function to filter partition values, use the EXPLAIN command to view the execution plan and confirm that partition pruning is effective.

  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))

  Note

  UDFs now support partition pruning. For more information, see the description in WHERE clause (WHERE_condition).

• Failure when using JOINs

  When you use JOIN in an SQL statement:

  • If the partition filter conditions are in the WHERE clause, partition pruning takes effect.

  • If partition filter conditions are in the ON clause, pruning is effective for the right table in a LEFT JOIN, but not for the left table.

  This section describes the behavior for three types of JOINs:

  • LEFT OUTER JOIN

    • All partition filter conditions are in the ON clause

      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';

      After you run the preceding explain statement, the execution plan is returned as follows. The plan shows that the data source of the left table scans all 1,770 partitions, and the filter condition is pushed down to the FIL operator:

      In Task M2_Stg1:
        Data source: xxx ller/ds=20101001, xxx seller/ds=20101002, xxx seller/ds=20101003...(total 1770)
        xxx
            RS: order: +
                optimizeOrderBy: False
              valueDestLimit: 0
              keys:
                   b.user_id
              values:
                   b.ds
              partitions
                b.user_id
      In Task J3_1_2_Stg1:
          JOIN: a LEFT OUTER JOIN b
            filter:
                0:
                1:
            FIL: And(EQUAL(a. col96, '20150801'), EQUAL(b. col209, '20150801'))
          SEL: xxx
            FS: output: None

      The preceding output shows that a full table scan is performed on the left table, while partition pruning is effective for the right table.

    • All partition filter conditions are in the WHERE clause

      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

      The execution plan shows that partition pruning is effective for both tables.

  • RIGHT OUTER JOIN

    Similar to a LEFT OUTER JOIN, if the partition filter conditions are in the ON clause of a RIGHT OUTER JOIN, partition pruning is effective only for the left table. If the conditions are in the WHERE clause, partition pruning is effective for both tables.

  • FULL OUTER JOIN

    For a FULL OUTER JOIN, partition filter conditions are effective only if they are in the WHERE clause. If they are in the ON clause, partition pruning is not effective for either table.

Precautions

• Partition pruning failures can have a significant impact and are often difficult to detect. Therefore, check for these failures before committing your code.

• To use partition pruning with a user-defined function, you must either modify the class or add set odps.sql.udf.ppr.deterministic = true; before your SQL statement. For more information, see WHERE clause (WHERE_condition).