Bird
0
0

Consider the following partitioned table:

medium📝 query result Q5 of 15
PostgreSQL - Table Partitioning
Consider the following partitioned table:
CREATE TABLE logs (id SERIAL, log_date DATE) PARTITION BY RANGE (log_date);
CREATE TABLE logs_2022 PARTITION OF logs FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE logs_2023 PARTITION OF logs FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
SELECT COUNT(*) FROM logs WHERE log_date >= '2023-06-01' AND log_date < '2023-07-01';
Which partitions will PostgreSQL scan to answer this query?
ABoth logs_2022 and logs_2023 partitions
BOnly logs_2023 partition
COnly logs_2022 partition
DAll partitions including parent table
Step-by-Step Solution
Solution:
  1. Step 1: Analyze query date filter

    The query filters log_date between '2023-06-01' and '2023-07-01'.
  2. Step 2: Match filter to partition ranges

    Only logs_2023 covers dates from '2023-01-01' to '2024-01-01', so only this partition is scanned.
  3. Final Answer:

    Only logs_2023 partition -> Option B
  4. Quick Check:

    Partition pruning uses date filter = logs_2023 only [OK]
Quick Trick: Partition pruning scans only matching date ranges [OK]
Common Mistakes:
  • Assuming all partitions are scanned
  • Including parent table in scan
  • Confusing partition ranges

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More PostgreSQL Quizzes