Partition pruning behavior in PostgreSQL - Step-by-Step Execution

Concept Flow - Partition pruning behavior

Query with WHERE clause

↓

Planner analyzes query

↓

Identify relevant partitions

↓

Exclude irrelevant partitions

↓

Execute query only on needed partitions

↓

Combine results from partitions

↓

Return final result

The query planner uses the WHERE clause to find which partitions to scan, skipping others to improve speed.

Execution Sample

PostgreSQL

CREATE TABLE sales (
  id INT,
  region TEXT,
  amount INT
) PARTITION BY LIST (region);

SELECT * FROM sales WHERE region = 'north';

This query only scans the 'north' partition, skipping others.

Execution Table

Step	Action	Partitions Considered	Partitions Pruned	Partitions Scanned	Result
1	Parse query	All partitions: north, south, east, west	None yet	None yet	Query ready for planning
2	Planner analyzes WHERE region = 'north'	north, south, east, west	south, east, west	north	Only 'north' partition scanned
3	Execute scan on 'north' partition	north	south, east, west	north	Rows from 'north' partition returned
4	Combine results	north	south, east, west	north	Final result set returned
5	End	north	south, east, west	north	Query complete

💡 Query ends after scanning only the 'north' partition; others pruned due to WHERE clause

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
Partitions Considered	north, south, east, west	north, south, east, west	north	north
Partitions Pruned	none	south, east, west	south, east, west	south, east, west
Partitions Scanned	none	none	north	north

Key Moments - 2 Insights

Why are some partitions not scanned even though they exist?

Because the planner uses the WHERE clause to prune partitions that cannot contain matching rows, as shown in step 2 of the execution_table where south, east, and west are pruned.

Does partition pruning happen before or after scanning partitions?

Partition pruning happens before scanning. The planner decides which partitions to scan (step 2) and then scans only those (step 3).

Visual Quiz - 3 Questions

Look at the execution_table, at which step are partitions south, east, and west pruned?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Check the 'Partitions Pruned' column in the execution_table rows.

According to variable_tracker, which partitions are scanned after step 3?

A north, south

B all partitions

C north only

D none

💡 Hint

Look at 'Partitions Scanned' after step 3 in variable_tracker.

If the WHERE clause was removed, how would the 'Partitions Pruned' column change at step 2?

A All partitions pruned

B No partitions would be pruned

C Only one partition pruned

D Partitions pruned after scanning

💡 Hint

Without a WHERE clause, the planner cannot exclude any partitions before scanning.

Concept Snapshot

Partition pruning lets PostgreSQL skip scanning partitions that can't match the query.
The planner uses WHERE conditions to decide which partitions to scan.
Only relevant partitions are scanned, improving query speed.
Pruning happens during query planning, before execution.
Without pruning, all partitions are scanned.

Full Transcript

Partition pruning behavior in PostgreSQL means the query planner looks at the query's WHERE clause to decide which partitions to scan. It excludes partitions that cannot have matching rows, so the query runs faster by scanning fewer partitions. For example, if a table is partitioned by region and the query filters for region = 'north', only the 'north' partition is scanned. The planner prunes other partitions like 'south', 'east', and 'west' before execution. This pruning happens during query planning, not after scanning. If no WHERE clause filters partitions, all partitions are scanned. This behavior helps improve performance by reducing unnecessary data reads.

Practice

1. What is the main purpose of partition pruning in PostgreSQL?

easy

A. To merge all partitions into one table

B. To create new partitions automatically

C. To skip scanning partitions that cannot contain matching rows

D. To backup partitions separately

Solution

1. Step 1: Understand partition pruning concept

   Partition pruning means the database avoids scanning partitions that do not match the query filter.

2. Step 2: Identify the main benefit

   This skipping reduces query time by focusing only on relevant partitions.

3. Final Answer:

   To skip scanning partitions that cannot contain matching rows -> Option C

4. Quick Check:

   Partition pruning = skip irrelevant partitions [OK]

Hint: Partition pruning skips irrelevant partitions to speed queries [OK]

Common Mistakes:

• Thinking pruning merges partitions
• Assuming pruning creates partitions
• Confusing pruning with backup

2. Which of the following WHERE clauses will enable partition pruning on a table partitioned by column region?

easy

A. WHERE region = 'US'

B. WHERE UPPER(region) = 'US'

C. WHERE LENGTH(region) > 2

D. WHERE region LIKE '%US%'

Solution

1. Step 1: Identify pruning conditions

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

2. Step 2: Analyze each option

   WHERE region = 'US' uses a direct equality on region, enabling pruning. Options A, B, and D use functions or patterns, preventing pruning.

3. Final Answer:

   WHERE region = 'US' -> Option A

4. Quick Check:

   Simple equality on partition key enables pruning [OK]

Hint: Use simple column = value filters on partition keys [OK]

Common Mistakes:

• Using functions on partition keys disables pruning
• Using LIKE patterns disables pruning
• Assuming any WHERE clause prunes partitions

3. Given a table sales partitioned by year with partitions for 2021 and 2022, what will the query below scan?

SELECT * FROM sales WHERE year = 2021;

medium

A. Only the 2021 partition

B. Both 2021 and 2022 partitions

C. No partitions, query returns empty

D. All partitions plus a full table scan

Solution

1. Step 1: Understand partition pruning with equality filter

   The query filters on year = 2021, which matches exactly one partition.

2. Step 2: Determine scanned partitions

   PostgreSQL will prune and scan only the 2021 partition, skipping 2022.

3. Final Answer:

   Only the 2021 partition -> Option A

4. Quick Check:

   Filter on partition key = value scans matching partition only [OK]

Hint: Equality on partition key scans only matching partition [OK]

Common Mistakes:

• Assuming all partitions scan regardless of filter
• Thinking query returns empty if filter matches a partition
• Believing full table scan always happens

4. You wrote this query on a partitioned table orders partitioned by order_date:

SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023;

Why might partition pruning NOT occur?

medium

A. Partition pruning only works with numeric columns

B. Using a function on the partition key disables pruning

C. The query syntax is invalid

D. Partition pruning requires an index on order_date

Solution

1. Step 1: Identify pruning limitation

   Partition pruning requires direct use of the partition key in filters without wrapping functions.

2. Step 2: Analyze the query

   The query uses EXTRACT(YEAR FROM order_date), a function on the partition key, preventing pruning.

3. Final Answer:

   Using a function on the partition key disables pruning -> Option B

4. Quick Check:

   Functions on partition keys disable pruning [OK]

Hint: Avoid functions on partition keys for pruning [OK]

Common Mistakes:

• Thinking pruning needs indexes
• Believing pruning works with any filter
• Assuming query syntax error causes pruning failure

5. 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

A. SELECT * FROM events WHERE event_date BETWEEN '2023-02-28' AND '2023-03-31';

B. SELECT * FROM events WHERE EXTRACT(MONTH FROM event_date) = 3 AND EXTRACT(YEAR FROM event_date) = 2023;

C. SELECT * FROM events WHERE TO_CHAR(event_date, 'YYYY-MM') = '2023-03';

D. SELECT * FROM events WHERE event_date >= '2023-03-01' AND event_date < '2023-04-01';

Solution

1. Step 1: Understand pruning with range partitions

   Range partitions work best with direct range conditions on the partition key.

2. 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.

3. Final Answer:

   SELECT * FROM events WHERE event_date >= '2023-03-01' AND event_date < '2023-04-01'; -> Option D

4. Quick Check:

   Direct range filters maximize pruning [OK]

Hint: 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