Your Query Still Touched Every Partition: A Cross-Engine Decision Procedure for Proving Whether Pruning Actually Happened

TL;DR Partition pruning is a query optimization technique that skips irrelevant data partitions to reduce I/O and latency. If your query returns correct rows but scans the entire table anyway, you likely have a non-sargable predicate (like a type mismatch) or a structural mismatch between your partition key and access patterns.

The query is correct. It just read forty times more than it needed to.

When partition pruning fails, it fails silently. There is no error message, no warning in the logs, and the result set is perfectly accurate. In PostgreSQL and MySQL, this manifests as a slow-burn latency regression. In BigQuery, Snowflake, or Athena, it surfaces as a "bytes scanned" line item on a cloud invoice—often routed to a finance lead who cannot read a query plan.

As noted in the recent practitioner writeup "I Partitioned 1M Rows and the Query Still Touched Every Partition" (Medium, 2026-09-19), the failure is particularly dangerous because it is invisible at small scales. The author observed that their query was still "fast" at a million rows despite scanning every partition. It is only when that table hits a billion rows that the silent lack of pruning becomes a production-ending event.

First question: is this a predicate problem or a partition key problem?

Before diving into syntax, you must identify which fork of the failure you are facing.

1. Predicate Problem: Your table is partitioned on created_at, and your query filters on created_at, but the optimizer cannot prove the filter matches the bounds. This is a software defect that can be fixed with a rewrite.
2. Partition Key Problem: Your table is partitioned on created_at, but your top ten most frequent queries filter exclusively on user_id. No amount of query tuning will fix this. The schema is structurally incompatible with the workload.

The One-Line Test: List your top ten queries by call count and the columns they filter on. Compare this to your partition key. If the key isn't in the filter list, stop looking at predicates. You need to repartition or add a secondary access path.

Pruning is a proof, not an optimization you enable

To troubleshoot effectively, you must adopt the engine's mental model: Pruning is a proof. An engine only skips a partition when it can mathematically prove, based on metadata and query constants, that no matching row could possibly exist in that slice.

Key Takeaway: Partition pruning is not an "optional" boost you toggle; it is the result of the optimizer successfully proving that specific data blocks are irrelevant to your query filters.

Pruning occurs at three stages:

• Plan Time: The optimizer sees a constant (e.g., WHERE date = '2026-01-01') and excludes partitions immediately.
• Initialisation Time: Parameters are bound, and the engine excludes partitions before execution starts.
• Execution Time: In cases like nested loop joins, the engine prunes partitions dynamically as it receives values from the outer loop.

According to the official PostgreSQL documentation, both enable_partition_pruning and constraint_exclusion settings affect this behavior, though the former is the modern standard for partitioned tables.

Prove it: reading the plan in each engine

To diagnose a failure, you must know what "success" looks like in your specific engine.

The predicate failures, in the order they actually occur

If you have the right key but pruning still fails, one of these common pitfalls is usually responsible:

1. Function or Cast on the Partition Key

If your key is created_at (a timestamp), and you write WHERE DATE(created_at) = '2026-09-20', pruning will fail in most engines. The optimizer sees a function call, not a raw column, and cannot map it to the partition bounds. The Fix: Rewrite to a range: WHERE created_at >= '2026-09-20 00:00:00' AND created_at < '2026-09-21 00:00:00'.

2. Type Mismatch (The ORM Trap)

You might pass a string literal '123' to a numeric partition key. While the query returns the correct rows due to implicit casting, the optimizer may refuse to prune because the cast makes the predicate non-sargable. This is common when an ORM driver maps a parameter to the wrong wire type.

3. OR Branches and Inclusivity

A query like WHERE key = 1 OR other_col = 'a' often forces a full scan because the engine must check other_col across all partitions. In PostgreSQL, using UNION ALL between two pruned queries is often more efficient than a single OR.

The correction: statistics do not decide pruning

A common misconception, repeated on several community troubleshooting pages, is that stale statistics cause partition pruning to fail. This is technically incorrect. As documented by both Oracle and PostgreSQL, pruning is decided based on partition bounds (the DDL defining the ranges), not the selectivity statistics gathered by ANALYZE.

However, stale statistics can cause the optimizer to choose a join strategy (like a Hash Join instead of a Nested Loop) that prevents dynamic partition pruning from triggering. The pruning didn't "fail" to see the bounds; the optimizer simply chose a path where pruning wasn't applicable.

Detecting that a query stopped pruning, without waiting for the bill

Detection is the gap in most SRE stacks. Because pruning failures don't error, you need a regression detector based on I/O signatures.

• For PostgreSQL: Monitor pg_stat_statements. The signature of a pruning regression is a sharp spike in shared_blks_read per call while rows per call remains constant. You can query the delta:

  SELECT query, shared_blks_read / calls as blocks_per_call
  FROM pg_stat_statements
  WHERE calls > 100
  ORDER BY blocks_per_call DESC;
  

• For Data Warehouses: Trend "Bytes Scanned per Row Returned." If this ratio jumps 10x for a specific query fingerprint, a partition filter has likely been dropped or broken by a code change.

Why this matters: The regression causes

Why does a query that pruned yesterday stop pruning today?

• Generic vs. Custom Plans: In Postgres, after five executions of a prepared statement, the optimizer may switch to a "generic plan" that covers all partitions if it thinks it will be cheaper.
• The Default Partition: If you use a DEFAULT partition, it can become a "catch-all" that grows so large that any scan touching it negates the benefits of partitioning elsewhere.
• Driver Upgrades: A minor version bump in your database driver might change how timestamptz is sent to the server, breaking the engine's ability to match the parameter to the partition key.

What to do once you know which case you are in

If it’s a Predicate Case, rewrite the query to avoid functions on the key and ensure type alignment. If it’s a Key-Mismatch Case, you have three honest choices: repartition the table, add a secondary index (which is an I/O trade-off), or accept the scan and adjust your budget.

At Operate, we see these silent regressions as the most dangerous kind of technical debt. The signal exists in your metadata—pg_stat_statements and query profiles already have the data—but humans aren't paid to watch every fingerprint for I/O drifts. Operate watches these signatures proactively, opening an investigation when a query's "blocks per call" spikes while its results stay flat. Unlike tools that might automatically repartition your data, Operate provides the evidence and a suggested fix as a PR, keeping the final decision in the hands of the engineer.

FAQ

What is partition pruning? It is a performance optimization where the database skip-reads entire partitions that cannot possibly contain data matching the query's WHERE clause.

What is the difference between predicate pushdown and partition pruning? Predicate pushdown moves filters closer to the data source (like reading a Parquet file), while partition pruning uses table metadata to avoid opening the file or partition entirely.

How do I force partition pruning in PostgreSQL? Ensure enable_partition_pruning is on and that your filters use the exact column and data type defined in your partition key without wrapping them in functions.

Sources & further reading

• According to the PostgreSQL DDL Documentation, pruning can be performed at plan time and execution time.
• As detailed in the MySQL 8.4 Reference Manual, pruning is supported for range, list, and hash partitioning but is limited by the specific functions used.
• Research via Ubersuggest (2026-09-20) confirms that diagnostic queries for "partition pruning not working" are rising across nine different database engines.
• OneUptime notes that adding an index is often the wrong first step when pruning fails; the focus should be on sargability.