Design patterns and optimization · Intermediate

Exclude irrelevant partitions with partition pruning

Compare pruning on and off in disposable PostgreSQL, verifying equal results while distinguishing excluded partitions from filtered scan output.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Exclude irrelevant partitions before scanning them
  2. Align conditions with half-open partition bounds
  3. Execute SQL only in a disposable database
  4. Read scans rather than only final counts
  5. Consider non-prunable queries and partition cost
  6. Match per-node row counts to the fixed calendar input
  7. Solve the zero-output scan exercise
  8. Ask Logeia about this articleCheck your understanding

Exclude irrelevant partitions before scanning them

For February sales in monthly partitions, reading every month and filtering afterward performs unnecessary work. Partition pruning uses bounds and query conditions to exclude irrelevant partitions during planning or execution. Read indexes and query processing first. Distinguish not scanning a partition from reading its rows and discarding them with a filter. Partitioning alone does not improve every query; usable conditions matter.

The example ran on disposable PostgreSQL 18.6 with three temporary monthly partitions and 9,000 rows over ninety days, one hundred per day. February has 2,800 rows. Enable and disable pruning while checking the same result. No index is created, isolating the partition-exclusion question. SQLite is not used as substitute evidence for PostgreSQL, and the example needs no production database access.

Align conditions with half-open partition bounds

Bounds use [2026-02-01,2026-03-01). The query uses the same lower-inclusive, upper-exclusive date condition. This date-only example has no subsecond boundary. Timestamp extensions need explicit timezone and day semantics. A last-day 23:59:59 condition can omit values when precision changes; using the next period's start makes the intended boundary clearer.

Functions around the partition key or values unavailable during planning can change when exclusion is possible. Check product version and actual plans instead of assuming logically similar conditions receive identical optimization. Index access is another question: bounds can exclude partitions without an index, while an indexed query can still visit many partitions. Keep those mechanisms distinct when interpreting a result. The experiment's absent indexes make its observed pruning easier to attribute to partition metadata and predicates.

Execute SQL only in a disposable database

Run psql -X -v ON_ERROR_STOP=1 -f partition.sql against a verified disposable local database. BEGIN through ROLLBACK contains temporary tables and transaction-local settings. Stop-on-error prevents partial output from being mistaken for a completed experiment. Closing the connection also removes temporary resources. The script does not modify a production sales table or persistent planner configuration.

Generate fixed data, analyze it and save both EXPLAIN ANALYZE results. ANALYZE prepares statistics, while EXPLAIN ANALYZE actually executes the SELECT. In general, that execution distinction also matters for statements with effects. JSON permits structural comparison. Do not require identical fractional costs or timings across environments. Match relation names, row counts, filters and loop counts to the property being tested rather than converting one plan's exact numbers into a universal template.

sql
BEGIN;
CREATE TEMP TABLE phase3_sales(id integer, day date, amount integer) PARTITION BY RANGE(day);
CREATE TEMP TABLE phase3_sales_jan PARTITION OF phase3_sales FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TEMP TABLE phase3_sales_feb PARTITION OF phase3_sales FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TEMP TABLE phase3_sales_mar PARTITION OF phase3_sales FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
INSERT INTO phase3_sales SELECT g,DATE '2026-01-01'+(g%90),1 FROM generate_series(0,8999) g;
ANALYZE phase3_sales;
SET LOCAL enable_partition_pruning=on;
SELECT count(*) AS expected_2800 FROM phase3_sales WHERE day>=DATE '2026-02-01' AND day<DATE '2026-03-01';
EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT count(*) FROM phase3_sales WHERE day>=DATE '2026-02-01' AND day<DATE '2026-03-01';
SET LOCAL enable_partition_pruning=off;
SELECT count(*) AS expected_2800 FROM phase3_sales WHERE day>=DATE '2026-02-01' AND day<DATE '2026-03-01';
EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT count(*) FROM phase3_sales WHERE day>=DATE '2026-02-01' AND day<DATE '2026-03-01';
ROLLBACK;

Read scans rather than only final counts

With pruning enabled, the recorded plan scans only phase3_sales_feb and returns 2,800 rows. With it disabled, Append contains jan, feb and mar; jan and mar output zero after filtering. Zero output does not mean the partition was never read. Result count alone cannot identify pruning. Compare scanned relation names and rows removed by filtering.

Both queries return 2,800. First verify equal answers, then identify removed work. Timings of tiny temporary data do not predict a large production speed ratio. BUFFERS includes local information for temporary relations; zero shared blocks does not mean no data was accessed. Also distinguish cached access from physical reads using the documented fields for this version. The evidence establishes the observed plan structure, not a universal optimizer choice for every schema and dataset.

Consider non-prunable queries and partition cost

Without a partition-key restriction, a full-period query may need every partition. Excessive partition count adds planning, metadata and maintenance cost. Choose boundaries from query periods, retention, indexes and update distribution. Partitioning cannot justify altered result semantics. Include default partitions or unusual values when they exist.

A real assessment should cover boundaries, NULL, parameters, prepared statements and settings under the actual schema. This example tests constant date predicates, not every dynamic case. Match production SQL and statistics before adoption. An ordinary index can be the simpler adequate choice. Evidence links the executed SQL hash with PostgreSQL version; changed procedures need a separately labeled run. This keeps one successful case from being reported as broad coverage it did not provide.

Match per-node row counts to the fixed calendar input

January and March each contain 3,100 rows in this dataset. With pruning disabled, removing 3,100 rows from each leaves 2,800 of the original 9,000. This arithmetic cross-check connects filter evidence to known input rather than relying only on one final count. The Aggregate node itself returns one row containing the count; its actual output cardinality is not 2,800 sales rows. Identify the operator level whenever reporting rows. That discipline also prevents mistaking an empty scan output for absence of scanning. Plan trees describe work and cardinality at several boundaries, and the same word rows can refer to different quantities unless the node is named.

The count result is a value inside one aggregate output row. Retain that distinction when exporting plan summaries or plotting work, so an apparent one-row plan is not described as scanning one sale. Operator-local cardinality and table population answer different questions.

Solve the zero-output scan exercise

A disabled-pruning plan shows zero actual output from jan. That alone does not prove no scan: its filter can discard all rows. Inspect the scan, removed rows and buffers, contrasting it with a plan where the jan scan is absent. Keep estimated and actual row counts separate too.

Expand the predicate to February and March. Both relevant partitions are needed while jan may be excluded; verify the actual plan and compute a new expected count from fixed input rather than reusing 2,800. Record success through ROLLBACK separately from partial failure. The learning outcome aligns predicate, bounds, answer and scanned relations.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 12

Choose a learning path