A table events is range partitioned by event_date with monthly partitions. You want to query events in March 2023. Which query will maximize partition pruning?
hard📝 optimization Q15 of 15
PostgreSQL - Table Partitioning
A table events is range partitioned by event_date with monthly partitions. You want to query events in March 2023. Which query will maximize partition pruning?
ASELECT * FROM events WHERE event_date BETWEEN '2023-02-28' AND '2023-03-31';
BSELECT * FROM events WHERE EXTRACT(MONTH FROM event_date) = 3 AND EXTRACT(YEAR FROM event_date) = 2023;
CSELECT * FROM events WHERE TO_CHAR(event_date, 'YYYY-MM') = '2023-03';
DSELECT * FROM events WHERE event_date >= '2023-03-01' AND event_date < '2023-04-01';
Step-by-Step Solution
Solution:
Step 1: Understand pruning with range partitions
Range partitions work best with direct range conditions on the partition key.
Step 2: Evaluate each query
SELECT * FROM events WHERE event_date >= '2023-03-01' AND event_date < '2023-04-01'; uses a direct range filter on event_date, enabling pruning. Options B and C use functions, disabling pruning. SELECT * FROM events WHERE event_date BETWEEN '2023-02-28' AND '2023-03-31'; includes dates outside March, scanning extra partitions.
Final Answer:
SELECT * FROM events WHERE event_date >= '2023-03-01' AND event_date < '2023-04-01'; -> Option D
Quick Check:
Direct range filters maximize pruning [OK]
Quick Trick:Use direct range filters on partition keys for pruning [OK]
Common Mistakes:
Using functions disables pruning
Including extra dates scans more partitions
Assuming BETWEEN always prunes perfectly
Master "Table Partitioning" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
