Retrieve 2023 Sales Data with Efficient Partition Pruning

Query Data using Partition Pruning

Write a PostgreSQL query to retrieve all sales records from the year 2023 in a range-partitioned Sales table.

Solution:

-- Fetch sales records from 2023.
SELECT * FROM Sales 
-- Filter records where sale_date is between January 1, 2023 and December 31, 2023.
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

Explanation:

• Purpose of the Query:
• Ensures PostgreSQL only scans the relevant partition (Sales_2023).
• Key Components:
• WHERE sale_date BETWEEN ...: Uses date filtering for automatic partition pruning.
• Real-World Application:
• Increases query efficiency by avoiding unnecessary partitions.

Notes:

• Works best if the partition key is included in the WHERE clause.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to fetch records from a partitioned table using a date range that exactly matches partition boundaries.
• Write a PostgreSQL query to retrieve data from a partitioned table using a function on the partition key to enforce partition pruning.
• Write a PostgreSQL query to fetch records using an inequality condition on the partition key to trigger partition pruning.
• Write a PostgreSQL query to retrieve records from a partitioned table with a complex WHERE clause that still allows for partition pruning.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Query Data from a Specific Partition.