Optimize queries by using partition pruning

Partition pruning allows the query engine to skip partitions that cannot contain matching data, significantly reducing I/O and improving query latency. Learn how partition pruning works, what conditions enable or disable it, and how to verify that pruning is applied.

Partition design guidelines

Partition pruning effectiveness depends on how you design partitions when creating a table. A sound partition strategy is the foundation for pruning optimization.

Choose an appropriate partition column

Choose a column that frequently appears in WHERE conditions as the partition column. Time columns such as event_time or order_date are the most common choice.
The partition column should have sufficient cardinality so that query conditions can effectively narrow the scan range.

Use AUTO PARTITION (recommended)

AUTO PARTITION automatically creates partitions on demand during data ingestion. No pre-creation is required, making it ideal when the range of partition key values is unpredictable or the partition granularity needs to be flexible:

-- AUTO PARTITION example: automatically creates daily partitions on write
CREATE TABLE user_events (
    event_time DATETIME NOT NULL,
    user_id BIGINT NOT NULL,
    event_type VARCHAR(32),
    event_data TEXT
)
DUPLICATE KEY(event_time, user_id)
AUTO PARTITION BY RANGE (date_trunc(event_time, 'day')) ()
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO;

Use dynamic partitioning for lifecycle management

For time-series data with explicit lifecycle management needs (such as retaining the most recent N days and automatically dropping expired partitions), configure dynamic partitioning:

-- Dynamic partition example: retain 30 days and pre-create 3 days
CREATE TABLE user_events_dynamic (
    event_time DATETIME NOT NULL,
    user_id BIGINT NOT NULL,
    event_type VARCHAR(32),
    event_data TEXT
)
DUPLICATE KEY(event_time, user_id)
PARTITION BY RANGE(event_time) ()
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-30",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p"
);

Control partition granularity

Keep the total number of partitions per table within a few thousand. Excessively fine-grained partitions (such as per-second) increase metadata management overhead. Excessively coarse partitions (such as per-year) reduce pruning effectiveness, requiring queries to scan large amounts of data. For time-series data, partitioning by day or month is typical.

How partition pruning works

When a table is partitioned by a column (typically a date or timestamp), ApsaraDB for SelectDB maintains metadata about the value range of each partition. During query planning, the optimizer evaluates filter conditions against these ranges. Partitions whose ranges do not overlap with the filter condition are excluded from the scan plan entirely.

For a table with 365 daily partitions, a query that filters on a single day reads only 1/365 of the total data. This reduction in scan volume translates directly to lower latency and reduced resource consumption.

Conditions that enable partition pruning

Partition pruning takes effect when the WHERE clause contains predicates that reference the partition column directly with constant or deterministic values:

Equality predicates: WHERE event_date = '2024-06-15'
Range predicates: WHERE event_date BETWEEN '2024-06-01' AND '2024-06-30'
IN predicates: WHERE event_date IN ('2024-06-15', '2024-06-16', '2024-06-17')
Comparison operators: WHERE event_date >= '2024-06-01'
Compound conditions with AND: WHERE event_date >= '2024-06-01' AND event_date < '2024-07-01'

Conditions that disable partition pruning

Certain query patterns prevent the optimizer from applying partition pruning. Avoid these anti-patterns:

Functions applied to the partition column: WHERE YEAR(event_date) = 2024 wraps the column in a function, making the optimizer unable to map the result back to partition ranges.
Implicit type casting: WHERE event_date = 20240615 compares a DATE column to an integer, triggering an implicit cast that blocks pruning.
Expressions on the partition column: WHERE event_date + INTERVAL 1 DAY > '2024-06-15' transforms the column into an expression.
OR with non-partition conditions: WHERE event_date = '2024-06-15' OR user_id = 123 may prevent pruning because the OR branch does not constrain the partition column.

-- Anti-pattern: function on partition column (pruning disabled)
SELECT * FROM event_log WHERE YEAR(event_time) = 2024 AND MONTH(event_time) = 6;

-- Optimized: direct range comparison (pruning enabled)
SELECT * FROM event_log
WHERE event_time >= '2024-06-01' AND event_time < '2024-07-01';

Optimization tips

Reference the partition column directly: Always write filter conditions as partition_column op constant without wrapping the column in functions or expressions.
Match data types exactly: Ensure the literal value in the filter matches the data type of the partition column. Use date literals for DATE columns and timestamp literals for DATETIME columns.
Push partition filters into joined tables: When joining a partitioned table with another table, include the partition column filter explicitly in the WHERE clause rather than relying on predicate derivation from the join condition.
Avoid subqueries on partition columns: WHERE event_date IN (SELECT max_date FROM config) may not enable pruning if the subquery result is not deterministic at planning time. Use explicit constants when possible.

Verify partition pruning with EXPLAIN

Use the EXPLAIN statement to confirm that partition pruning is applied. In the OlapScanNode output, check the partitions field, which shows the number of partitions selected versus the total.

EXPLAIN
SELECT * FROM event_log
WHERE event_time >= '2024-06-01' AND event_time < '2024-07-01';

-- In the output, look for:
-- OlapScanNode
--   TABLE: event_log
--   PARTITIONS: 30/365    <-- Only 30 out of 365 partitions are scanned

If the PARTITIONS field shows all partitions being scanned (e.g., 365/365), review your filter conditions against the anti-patterns described above and rewrite the query accordingly.