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
EXPLAINwhether 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
| Step | VeloDB Cloud behavior | Result |
|---|---|---|
| 1 | Analyze the partition column date in the query conditions | Identify the date range 2023-01-15 to 2023-02-15 |
| 2 | Compare the query conditions with the partition definitions | Hit partitions p2 and p3 |
| 3 | Automatically skip irrelevant partitions | Skip p1 and p4 |
| 4 | Run the scan and aggregation only on the hit partitions | Return 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
| Dimension | Partition pruning effective | Partition pruning not effective |
|---|---|---|
| Query conditions | Include a filter from which the optimizer derives a partition range | No usable partition range can be derived, or the range legitimately covers every partition |
| Number of partitions scanned | Only the hit partitions are scanned | All partitions are scanned |
| I/O overhead | Low | High |
| EXPLAIN output | partitions=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
DATEwhile the filter value is a string that cannot be implicitly converted). - An
ORincludes 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 BYcolumn. - Bucket Pruning / Tablet Pruning: prunes tablets based on equality conditions on the
DISTRIBUTED BY HASHcolumn. 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/Mfield inEXPLAINlets 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.