Partition pruning behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Partition pruning with static query filters

Given a partitioned table sales partitioned by region, which query will prune partitions correctly and return only rows from the north region?

PostgreSQL

SELECT * FROM sales WHERE region = 'north';

A SELECT * FROM sales WHERE region LIKE '%north%';

B SELECT * FROM sales WHERE region IN ('north', 'south');

C SELECT * FROM sales WHERE region = 'north';

D SELECT * FROM sales WHERE region <> 'south';

💡 Hint

Partition pruning works best with exact matches on the partition key.

Only option C uses an exact equality condition on the partition key 'region', allowing PostgreSQL to prune partitions and scan only the 'north' partition. Other options use patterns or ranges that prevent pruning.

🧠 Conceptual

intermediate

Understanding partition pruning timing

When does PostgreSQL perform partition pruning during query execution?

A At query planning time for static filters and at execution time for parameterized queries.

B Only at query execution time regardless of query type.

C Only at query planning time for all queries.

D After the query finishes scanning all partitions.

💡 Hint

Think about how static values and parameters affect planning.

PostgreSQL prunes partitions at planning time when filters are static constants. For parameterized queries, pruning happens at execution time when parameter values are known.

📝 Syntax

advanced

Which query syntax enables effective partition pruning with a parameter?

Given a partitioned table orders partitioned by order_date, which query syntax will allow PostgreSQL to prune partitions when using a parameter for the date filter?

PostgreSQL

Prepare a query to select orders for a specific date using a parameter.

A SELECT * FROM orders WHERE order_date = $1;

B SELECT * FROM orders WHERE order_date::text = $1;

C SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = $1;

D SELECT * FROM orders WHERE order_date > $1 - INTERVAL '1 day';

💡 Hint

Partition pruning requires direct comparison on the partition key without transformations.

Option A uses a direct equality comparison on the partition key with a parameter, enabling pruning. Options A, B, and C apply transformations or functions that prevent pruning.

🔧 Debug

advanced

Why does this query scan all partitions despite a filter?

A table events is partitioned by event_type. The query SELECT * FROM events WHERE event_type = 'click' OR event_type = 'view'; scans all partitions. Why?

A Because the table is not actually partitioned.

B Because the OR condition prevents pruning; only single equality filters prune partitions.

C Because the query uses string literals instead of parameters.

D Because the partition key is not indexed.

💡 Hint

Consider how multiple conditions affect pruning.

Partition pruning in PostgreSQL works best with simple equality conditions on the partition key. Using OR with multiple values disables pruning, causing all partitions to be scanned.

❓ optimization

expert

Optimizing partition pruning for range partitions

Given a table logs partitioned by range on log_date, which query will maximize partition pruning efficiency for retrieving logs from January 2024?

A SELECT * FROM logs WHERE log_date BETWEEN '2024-01-01' AND '2024-01-31';

B SELECT * FROM logs WHERE TO_CHAR(log_date, 'YYYY-MM') = '2024-01';

C SELECT * FROM logs WHERE EXTRACT(MONTH FROM log_date) = 1 AND EXTRACT(YEAR FROM log_date) = 2024;

D SELECT * FROM logs WHERE log_date >= '2024-01-01' AND log_date < '2024-02-01';

💡 Hint

Partition pruning works best with direct range comparisons on the partition key.

Option D uses direct range conditions on the partition key, allowing PostgreSQL to prune partitions efficiently. Options A, B, and D use functions or inclusive ranges that may prevent pruning or scan extra partitions.