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.