Partition Pruning Fails Silently, So Prove It in the Plan

Partition pruning is the optimisation in which a database skips partitions that cannot contain rows matching a query. PostgreSQL describes it as the planner examining each partition's definition and proving that the partition "could not contain any rows meeting the query's WHERE clause", and the word that matters there is proving.

We treat pruning as something to confirm, not something to assume. A partitioned table that is not being pruned is just a large table with extra bookkeeping, and nothing in the result set will tell you so.

In this post we cover why the failure is silent, how engines decide, the first question that splits predicate problems from key problems, the evidence to read in four engines, four predicate patterns that defeat the proof, why a query that pruned yesterday may not today, how to detect a regression and when reading every partition is fine.

Pruning Failure Returns Correct Rows and Raises No Error

When pruning fails, the query still answers correctly. There is no warning in the logs and no error for monitoring to catch, so the only symptoms are indirect.

On a row-store database the symptom is a slow-burn latency or I/O regression as the table grows. On a cloud warehouse it is bytes scanned, which becomes a line on an invoice that someone in finance has to explain without being able to read a query plan.

The failure is also hidden at small scale. A table with a few partitions of modest size answers quickly with or without pruning, so the problem appears only when the data is large enough for the missing optimisation to matter.

Pruning Is a Proof the Engine Makes From the Query

MySQL states the idea plainly in its partition pruning documentation as "Do not scan partitions where there can be no matching values." The engine skips a partition only when it can show, from the partition definitions and the query's constants, that no matching row could be there.

The PostgreSQL partitioning documentation adds that this can happen at more than one stage. Some pruning happens during initialisation of the plan, for parameter values known then, and partitions removed there do not appear in EXPLAIN output, though a "Subplans Removed" property counts them. Other pruning happens during execution, for values from subqueries and from parameterised nested loop joins.

Two consequences follow for diagnosis. Pruning is driven by the constraints implied by the partition keys, "not by the presence of indexes", so adding an index is rarely the right first move. And the presence of a partition in a plan depends on the stage at which it was removed, so we read the plan with that in mind.

Decide If It Is a Predicate Problem or a Key Problem

Before reading any plan, we ask which kind of failure we are chasing. They look identical in latency graphs and need opposite fixes.

In a predicate problem the table is partitioned on the column the query filters on, but the filter is written in a way the engine cannot use. A rewrite fixes it. In a key problem the common queries never filter on the partition key, so no rewrite helps, and the schema does not match the workload.

The test is quick. List the most frequent queries by call count, write down the columns each one filters on, and compare with the partition key. If the key is missing from most of those lists, stop reading predicates and consider repartitioning or adding a secondary access path.

Prove Pruning Happened in Each Engine and Read the Evidence

Every engine exposes evidence of what it read, and the signals differ. A short table in the runbook saves the person on call from remembering four vocabularies.

Engine	Where to look	Success signal	Failure signal
PostgreSQL	EXPLAIN with ANALYZE	Few child scans under the Append node, or Subplans Removed	Every partition scanned under Append
MySQL	The partitions column of EXPLAIN	One or a few partition names	All partitions listed
BigQuery	A dry run before execution	Estimated bytes near the filtered range	Estimated bytes near the whole table
Snowflake	Query Profile table scan statistics	Partitions scanned a small fraction of total	Scanned and total roughly equal

• PostgreSQL

The documentation shows the same query planned with pruning off and on. With it off, the plan lists a sequential scan on every child under an Append node, and with it on only the matching child appears. We run EXPLAIN with ANALYZE and look at both the children listed and any "Subplans Removed" count.

For execution-time pruning, the documentation says to inspect the loops property, because subplans pruned every time may show as "never executed". A plan with many children that all say never executed is working as intended.

• MySQL

MySQL supports pruning for SELECT, DELETE and UPDATE statements, and the partitions column of EXPLAIN lists the partitions the statement would touch. If it names all of them, pruning did not happen for that statement.

The same page gives the conditions the optimiser can reduce, namely a partition column equal to a constant or in a list of constants, and notes that for tables partitioned by HASH or KEY, pruning works only on integer columns. A filter outside those forms leads to a full scan.

• BigQuery

The BigQuery documentation says "To estimate how many bytes a query will process, perform a dry run." That makes cost a pre-flight check, so a pruning failure can be caught before the query runs, not after the bill arrives.

It also describes a table option that rejects queries without a usable filter, with the error "Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination". We would turn it on for large partitioned tables that should never be scanned whole.

• Snowflake

The Query Profile documentation reports partitions scanned and partitions total for each table scan, and advises that "If the former is a small fraction of the latter, pruning is efficient. If not, the pruning did not have an effect."

We read those two numbers on any slow query against a large table. Equal numbers with a selective filter mean the filter is not helping the scan.

Capturing plans, scan statistics and slow-query logs together is easier when the evidence is gathered for you. It helps to connect it read-only to your database and logs so that root cause analysis can compare the plan with the query text and the deploy history at the moment the regression began.

Four Predicate Patterns That Stop the Engine From Proving Anything

If the key is right and the evidence shows no pruning, the filter is the first suspect. These patterns come from the engine documentation, and we check them in this order.

1. A Function or Expression on the Partition Column

BigQuery lists unsupported shapes, including functions such as FORMAT_DATE or EXTRACT applied to the partitioning column, and arithmetic like adding an interval to it. The fix is to leave the column bare and move the logic to the other side, so a filter on a date function becomes a range on the timestamp column itself.

2. A Comparison With Another Column

A predicate that compares the partition column with a second column cannot be reduced to constants. BigQuery names this case explicitly as one that prevents pruning, and the same logic applies wherever the engine needs known values to exclude a partition.

3. A Dynamic Value From a Subquery

Engines differ here, which is why the evidence matters. BigQuery says a filter on a subquery result prevents pruning, whereas PostgreSQL can prune at execution time using values from subqueries. We test the specific engine instead of generalising.

4. A Condition the Engine Cannot Reduce

MySQL's rules show the principle. If the condition cannot be reduced to equalities, lists or the supported ranges on the partition column, or the column type is not supported for the scheme, the optimiser keeps every partition. When a rewritten query prunes in the plan and the original does not, the difference is the predicate.

Our guide to Postgres query plan changes covers reading the plan for a related class of regressions.

Why a Query That Pruned Yesterday May Not Today

A regression with no change to the query text is possible, and we look for it deliberately. In PostgreSQL, the PREPARE documentation explains that by default "the first five executions are done with custom plans", after which a generic plan is created and used if its cost is not much higher.

A generic plan cannot use the parameter values at planning time, so pruning has to happen when the executor starts or while it runs. That is supported, and it is visible in different parts of the plan output, so a plan that looked pruned in the first five executions may need a different reading afterwards.

Other causes are mundane and still worth checking, starting with a code change that wrapped the key column in a function. We check the deploy history for the first bad day, because the query fingerprint and the release date usually explain the rest.

Detect a Pruning Regression Before the Invoice Does

Because pruning failures raise no error, detection has to rest on I/O signatures. In PostgreSQL, the pg_stat_statements module tracks calls, shared blocks read and rows per statement, so blocks read per call rising sharply while rows per call stay flat is the signature of a statement that started reading more for the same answer.

For warehouses we would trend bytes scanned per row returned for each query fingerprint. A sudden jump for one fingerprint points at a dropped or broken partition filter, and the dry-run and required-filter options above prevent the worst cases outright.

A related silent pattern, many cheap queries standing in for one good one, is covered in our guide to the N+1 query problem. The wider habit is the same as for other silent regressions. Our guide to p99 latency and tail regressions describes how to turn a slow drift into an alert.

When Reading Every Partition Is Fine

Not every full scan is a defect. We leave these cases alone.

• A report that needs the whole history, which legitimately touches every partition.
• A small table, which gains nothing from pruning.
• A maintenance job that scans all partitions on a schedule.

It is also wrong to repartition because one query failed to prune. If the common queries filter on the key and one does not, we fix that query. If most queries do not use the key, we accept the scan, add a secondary access path or budget for the cost, because the schema no longer matches the workload.

Prove Partition Pruning Before You Rewrite Anything

Read the engine's evidence first, decide whether the problem is the predicate or the key, and change one thing at a time. Many partition pruning failures end with a bare column, a constant filter and a plan that shows the partitions it skipped.

We keep the evidence with the incident record so the next regression is a lookup. Operate reads databases, logs and repositories through read-only adapters, and proposes any code fix as a patch file an engineer reviews and applies.