Skip to main content
VeloDB Cloud 26.x is based on Apache Doris 4.x. See Version Mapping.

Optimize Table Scans with Partition Pruning

Partition pruning uses query filters to eliminate partitions that cannot contain matching rows.

Pruning can reduce I/O and computation when a filter excludes part of the table. Use EXPLAIN to check the selected partitions before tuning execution parallelism.

Applicability checklist:

  • The table is partitioned by a business column (such as date).
  • The query conditions include filters on the partition column (for example, WHERE date BETWEEN ...).
  • You want to reduce the number of partitions scanned and lower I/O.
  • You need to verify with EXPLAIN whether pruning takes effect.

Example: Prune a sales table by date​

The following example creates a test table in your current database. You need privileges to create a table and run queries. The output is illustrative and can vary with the engine version.

1. Create a table: range partitioning by date​

Goal: Create sales with range partitions. p2, p3, and p4 cover January, February, and March 2023. p1 contains all dates before January 2023.

Command:

CREATE TABLE sales (
date DATE,
product VARCHAR(50),
amount DECIMAL(10, 2)
)
PARTITION BY RANGE(date) (
PARTITION p1 VALUES LESS THAN ('2023-01-01'),
PARTITION p2 VALUES LESS THAN ('2023-02-01'),
PARTITION p3 VALUES LESS THAN ('2023-03-01'),
PARTITION p4 VALUES LESS THAN ('2023-04-01')
)
DISTRIBUTED BY HASH(date) BUCKETS 16;

Description: The partition column is date. The four upper bounds are exclusive. p1 has no lower date bound.

2. Query: with a filter on the partition column​

Goal: Query the total sales amount between January 15, 2023 and February 15, 2023.

Command:

SELECT SUM(amount) AS total_amount
FROM sales
WHERE date BETWEEN '2023-01-15' AND '2023-02-15';

Description: The WHERE clause contains a range filter on the partition column date, which is the key to triggering partition pruning.

3. Understand which partitions are selected​

StepVeloDB Cloud behaviorResult
1Analyze the partition column date in the query conditionsIdentify the date range 2023-01-15 to 2023-02-15
2Compare the query conditions with the partition definitionsHit partitions p2 and p3
3Automatically skip irrelevant partitionsSkip p1 and p4
4Run the scan and aggregation only on the hit partitionsReturn the result quickly

4. Verify pruning with EXPLAIN​

Goal: Use the EXPLAIN command to view the execution plan and confirm the actual number of partitions scanned.

Command:

EXPLAIN SELECT SUM(amount) AS total_amount
FROM sales
WHERE date BETWEEN '2023-01-15' AND '2023-02-15';

Key output:

| 0:VOlapScanNode(212) |
| TABLE: cir.sales(sales), PREAGGREGATION: ON |
| PREDICATES: (date[#0] >= '2023-01-15') AND (date[#0] <= '2023-02-15') |
| partitions=2/4 (p2,p3) |

Description: The partitions=2/4 (p2,p3) field on the OlapScanNode indicates that only 2 out of 4 partitions (p2 and p3) are scanned, which means partition pruning has taken effect.

Compare plans with and without partition elimination​

DimensionPartition pruning effectivePartition pruning not effective
Query conditionsInclude a filter from which the optimizer derives a partition rangeNo usable partition range can be derived, or the range legitimately covers every partition
Number of partitions scannedOnly the hit partitions are scannedAll partitions are scanned
I/O overheadLowHigh
EXPLAIN outputpartitions=N/M (N < M)partitions=M/M

FAQ​

Q1: The query is slow and I suspect partition pruning is not effective. how do I confirm this?​

Run EXPLAIN <query> and check the partitions=N/M field on the OlapScanNode. If N == M, no partition was eliminated. Check whether the requested range should cover all partitions before treating this as a problem.

Q2: Why does the query still scan all partitions even though WHERE includes the partition column?​

Common reasons:

  • A function around the partition column prevents the optimizer from deriving a range. Where possible, compare the column directly with correctly typed boundary values.
  • Type mismatch (for example, the partition column is DATE while the filter value is a string that cannot be implicitly converted).
  • An OR includes a branch that can match rows in any partition, so excluding those partitions would change the result.

Q3: What is the difference between partition pruning and bucket pruning?​

  • Partition Pruning: prunes partitions based on the PARTITION BY column.
  • Bucket Pruning / Tablet Pruning: prunes tablets based on equality conditions on the DISTRIBUTED BY HASH column. The two can be combined to further reduce the amount of data scanned.

Summary​

  • Partition pruning automatically identifies the mapping between query conditions and partitions, and scans only the necessary partitions.
  • Key prerequisites: the table is partitioned by a business column, and the query includes pushdown-capable filter conditions on the partition column.
  • The partitions=N/M field in EXPLAIN lets you quickly verify whether pruning takes effect.
  • Compare the chosen partitions with the requested range. Scanning every partition can be correct for a query that needs all the data.

Drop the test table with DROP TABLE sales; when it is no longer needed.