Querying Across Partitions Efficiently
Write queries that benefit from partition pruning, and verify pruning with EXPLAIN.
Filter on the Partition Key
Pruning only works when the WHERE clause includes the partition key:
-- Prunes (uses date partitioning):
SELECT * FROM events WHERE ts >= '2024-03-01' AND ts < '2024-04-01';
-- No pruning — scans all partitions:
SELECT * FROM events WHERE user_id = 42;Composite Pruning
The planner can prune by multiple keys in multi-level partitioned tables:
SELECT * FROM events
WHERE ts >= '2024-03-01' AND ts < '2024-04-01'
AND user_id = 42;
-- Prunes by date AND by hash partition.All lessons in this course
- Why Partition: Pruning, Maintenance
- Range, List and Hash Partitioning
- Detaching and Attaching Partitions
- Querying Across Partitions Efficiently