Use EXPLAIN to Diagnose Partition Pruning & Avoid Full Scans - MaxCompute

Partition pruning allows MaxCompute to skip partitions that don't match a query's filter conditions, so SQL reads only the partitions you need instead of scanning the entire table. When partition pruning fails, queries fall back to full table scans — increasing cost and degrading performance. This topic explains how to verify that partition pruning is working and identifies the scenarios where it commonly fails.

Verify partition pruning with EXPLAIN

Run the EXPLAIN statement to view the query execution plan and confirm which partitions are read.

Partition pruning not effective — using a non-constant expression as the partition filter:

explain
select seller_id
from xxxxx_trd_slr_ord_1d
where ds=rand();

The execution plan shows that all 1,344 partitions of table xxxxx_trd_slr_ord_1d are read, meaning partition pruning did not take effect.

Partition pruning effective — using a constant value as the partition filter:

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

Partition pruning

The execution plan shows that only partition 20150801 of table xxxxx_trd_slr_ord_1d is read.

Scenarios where partition pruning does not take effect

Improper use of UDFs

Using user-defined functions (UDFs) or certain built-in functions in the partition filter expression may prevent partition pruning from taking effect.

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

Run EXPLAIN on any query that uses UDFs in the WHERE clause to confirm whether partition pruning is active.

For details on UDF-based partition pruning, see the "WHERE" section in WHERE.

Improper use of joins

Where you place partition filter conditions — in the ON clause or the WHERE clause — determines which tables benefit from partition pruning:

`WHERE` clause: Partition pruning is effective for all joined tables.
`ON` clause: Partition pruning is effective only for the secondary (right) table; the primary (left) table is still fully scanned.

The following table summarizes partition pruning behavior across JOIN types:

JOIN type	Filter in `ON` clause	Filter in `WHERE` clause
LEFT OUTER JOIN	Right table only	Both tables
RIGHT OUTER JOIN	Left table only	Both tables
FULL OUTER JOIN	No pruning	Both tables

LEFT OUTER JOIN examples

Filter in the `ON` clause — pruning is effective only for the right table:

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

LEFT OUTER JOIN ON clause execution plan

The execution plan shows that partition pruning is effective for the right table but not the left table.

Filter in the `WHERE` clause — pruning is effective for both tables:

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

LEFT OUTER JOIN WHERE clause execution plan

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

Recommendations

Run EXPLAIN to verify partition pruning before committing SQL code. If partition pruning fails silently, query cost and performance can degrade significantly.
To enable UDF-based partition pruning, either modify the UDF classes or add set odps.sql.udf.ppr.deterministic = true; before your SQL statements. For details, see WHERE.