Postgres Partition Pruning: How the Planner Skips Partitions at Plan and Execution Time

Partitioning a Postgres table looks like an obvious win on paper: split a large table into smaller pieces, query the smaller pieces, get faster results. The reality is that partitioning only helps when the planner can identify which partitions a query needs and skip the others. If the planner has to scan every partition for every query, you have paid the operational cost of partitioning (more complicated migrations, foreign key restrictions, separate vacuum cycles) without getting the performance benefit.

The mechanism that decides which partitions get scanned is called partition pruning. It comes in two flavors—plan-time and execution-time—with different behaviors, different requirements, and different failure modes. Understanding both is the difference between partitioning that pays off and partitioning that costs without benefit.

Plan-time pruning

Plan-time pruning is the older form, working in Postgres since version 10 when declarative partitioning was introduced. The planner examines the WHERE clause of the query, compares the predicates against the partition boundaries, and produces a plan that only references the partitions whose rows could possibly match.

For a range-partitioned table on a date column, a query with WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01' produces a plan that touches only the January 2026 partition, regardless of how many years of partitions exist. The query plan visible in EXPLAIN includes only the matched partitions, and the plan is the same regardless of what data is in the partitions.

Plan-time pruning requires that the WHERE-clause predicates be evaluable at plan time. Constants work; literal date strings work; expressions involving immutable functions work. Expressions involving the current time (now(), CURRENT_TIMESTAMP) work because the planner can evaluate them once and use the result. Expressions involving parameters in prepared statements work for plan-time pruning if the parameter value is known when the plan is built and the planner chooses a custom plan rather than a generic plan.

The failure modes of plan-time pruning are predicates the planner cannot evaluate. Joins, where the partition-key value comes from another table, defeat plan-time pruning entirely—the planner does not know which values will arrive from the joined table until execution. Subqueries that return partition-key values have the same problem. Function calls that the planner cannot prove are immutable, including most user-defined functions, prevent plan-time pruning.

Execution-time pruning

Execution-time pruning was added in Postgres 11 and refined in subsequent versions. It runs at execution time, after the planner has produced a plan that touches multiple partitions, and skips partitions that the actual runtime values prove cannot match. This is the form that handles the cases plan-time pruning cannot: parameterized queries, nested-loop joins, subqueries.

In a nested-loop join between an unpartitioned outer table and a partitioned inner table, the inner table's partition is selected for each outer row based on the actual joined value. The EXPLAIN output shows all partitions in the plan with a marker indicating that some will be pruned at execution time; EXPLAIN ANALYZE shows which partitions were actually scanned and how many were skipped.

Execution-time pruning is most visible in the Subplans Removed counter that appears in EXPLAIN ANALYZE output. A query plan that targets 100 partitions but actually executes against 3 of them will show Subplans Removed: 97, indicating that pruning eliminated 97 of the planned scans before they executed.

The performance cost of execution-time pruning is small but nonzero. The planner has to produce a plan that includes all possibly-needed partitions, and the executor has to do per-row pruning decisions during execution. For most cases this overhead is negligible compared to the savings from not scanning the pruned partitions, but for very small queries against very large partition counts, the planning overhead can be measurable.

Reading EXPLAIN for partition pruning

The way to verify that partition pruning is working is reading EXPLAIN output. A query with effective plan-time pruning shows only the targeted partitions in the plan—the unmatched partitions do not appear at all. A query with effective execution-time pruning shows all possibly-needed partitions in the plan but reports Subplans Removed in EXPLAIN ANALYZE output. A query that is not benefiting from pruning shows all partitions in the plan and zero Subplans Removed.

The diagnostic pattern is to take a representative query and compare expected partitions vs. actual scanned partitions. If a query that should target one month of data is scanning twelve months of partitions, partition pruning is not working. The common causes are: the WHERE clause does not reference the partition key, the WHERE clause uses an expression the planner cannot evaluate, or the partition key type does not match the predicate type (a common subtle bug where a timestamptz partition key is compared against a timestamp value, producing implicit conversions that defeat pruning).

The constraint exclusion alternative

Before declarative partitioning, Postgres supported partitioning through table inheritance plus CHECK constraints, with the planner using constraint exclusion to skip partitions. Constraint exclusion is still controlled by the constraint_exclusion parameter (default value partition, meaning constraint exclusion applies only to inheritance partitions, not declarative ones).

Declarative partitioning uses a fundamentally different pruning mechanism—the planner has direct access to partition bounds without needing to read CHECK constraints—and is faster than constraint exclusion at high partition counts. Teams migrating from inheritance-based partitioning to declarative partitioning routinely see substantial planning-time improvements, especially for tables with hundreds of partitions.

Three patterns that fail

First, the type-mismatch trap. A range-partitioned table on timestamptz with a query using a string literal like WHERE created_at >= '2026-01-01' may or may not prune depending on the implicit conversion. The reliable pattern is to use a typed literal: WHERE created_at >= '2026-01-01'::timestamptz or WHERE created_at >= timestamp '2026-01-01' AT TIME ZONE 'UTC'. The clean way to verify is to read EXPLAIN output and confirm pruning is happening.

Second, the function-in-predicate trap. A query like WHERE created_at >= date_trunc('month', now()) works for plan-time pruning because date_trunc and now() are both stable functions the planner can evaluate at plan time. A query like WHERE created_at >= some_user_function() may not prune if the function is not marked IMMUTABLE or STABLE. The fix is to mark user-defined functions with the correct volatility classification.

Third, the OR-predicate trap. A query with WHERE created_at = '2026-01-15' OR user_id = 42 in a table partitioned on created_at cannot use partition pruning effectively because the OR-clause means rows matching the second predicate could be in any partition. The planner produces a plan that scans all partitions. The fix is to restructure the query as a UNION ALL of two separately-pruning queries, or to partition on a different key, or to accept that this query pattern does not benefit from partitioning.

What partition pruning does not do

Partition pruning skips entire partitions but does not reduce the work within a scanned partition. If a partitioned table has a billion rows in one partition that matches the query predicates, partition pruning has not saved anything—the query still has to scan or index-seek a billion rows. Partition pruning is a coarse-grained optimization that pairs with normal indexing for fine-grained selectivity.

Partition pruning does not help with INSERT or UPDATE operations on the partition key. INSERTs go to the partition determined by the partition-key value; UPDATEs that change the partition key require a delete-and-reinsert internally. The pruning logic only applies to SELECT and the WHERE-clause filtering in UPDATE/DELETE.

Partition pruning does not solve the foreign-key problem with partitioned tables: foreign keys can reference a partitioned table only at the per-partition level in older versions, and only as a single declaration in newer versions with some restrictions. The pruning logic is a SELECT-time optimization, not a schema-level capability change.

Where we stand

The four DocuMint, CronPing, FlagBit, and WebhookVault products are SQLite-based, which does not have declarative partitioning. SQLite supports attached databases, which can simulate partitioning at the application layer with the application deciding which database to query for each request—a workable but more manual approach than Postgres declarative partitioning.

The Postgres migration plan for the four products includes partitioning candidates: CronPing's monitor_checks table, FlagBit's evaluation log, WebhookVault's captured_requests and delivery_attempts tables, and DocuMint's generation_log. All of these are time-series tables that grow without bound, with most queries targeting recent data. They are textbook partitioning candidates and the migration plan includes monthly range partitioning with automated partition maintenance via pg_partman.

The discipline we plan to follow during migration is partition-pruning verification as part of every partitioning rollout. Every query that touches a partitioned table will be checked via EXPLAIN to confirm pruning is happening. The verification gate prevents the common failure mode where partitioning is enabled but no queries actually benefit because of subtle pruning-defeat patterns. The point of partitioning is the partition pruning; without verified pruning, partitioning is just operational overhead.

The deeper observation about Postgres partition pruning is that it is one of the cleaner cases where a feature has two related but distinct implementations (plan-time and execution-time) each with different requirements and different failure modes. Teams that learn only the plan-time version end up confused when their parameterized queries do not prune as expected. Teams that learn only the execution-time version end up confused when their EXPLAIN output looks scary even though pruning is happening. Understanding both, and knowing which form should fire for which query shape, is the foundation for using partitioning effectively in production.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.