Partition Pruning

Both queries return identical rows. One considers a thousand path strings and opens three files; the other opens every file in the table. Nothing in the SQL, the result or the pipeline status distinguishes them — only the plan does.

1-- 1. FUNCTION ON THE PARTITION COLUMN
2--    The planner cannot invert an arbitrary function, so it cannot decide
3--    which partition values could satisfy the condition.
4WHERE date_trunc('month', event_date) = DATE '2026-08-01'          -- scans all
5WHERE event_date >= DATE '2026-08-01'
6  AND event_date <  DATE '2026-09-01'                              -- prunes
7 
8-- 2. TYPE MISMATCH -> AN IMPLICIT CAST, WHICH IS A FUNCTION
9--    A cast around the column has exactly the effect of a function
10--    around the column.
11WHERE event_date = '2026-08-25'                                    -- may cast
12WHERE event_date = DATE '2026-08-25'                               -- prunes
13 
14-- 3. A PREDICATE THE PLANNER CANNOT SEE AT PLAN TIME
15--    The bound comes from another table, so there is no literal to fold
16--    unless the engine implements dynamic pruning for this shape.
17SELECT sum(e.amount)
18FROM   events e
19JOIN   report_days d ON e.event_date = d.report_date;               -- may scan all
20 
21--    Resolve the bound first and inject it as a literal:
22SELECT sum(e.amount)
23FROM   events e
24WHERE  e.event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-25';
25 
26-- 4. THE PARTITION COLUMN INSIDE AN OR WITH A NON-PARTITION COLUMN
27--    A row in any partition could satisfy the second branch, so no
28--    partition can be excluded.
29WHERE event_date = DATE '2026-08-25' OR customer_id = 'c-991'       -- scans all
30 
31--    If both branches are genuinely wanted, bound the partition on both:
32WHERE event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-25'
33  AND (event_date = DATE '2026-08-25' OR customer_id = 'c-991');

The rewrite is the same move every time: turn the condition into a bare-column comparison or range against a literal. Note that none of the left-hand versions is wrong — each returns exactly the right rows, which is precisely why nobody catches them.

1. Partition value derived by the writer

   holds The result of deriving a partition key from a record — a date extracted from an event timestamp, in a specific time zone.

   could corrupt A time-zone or parsing difference routes rows into the neighbouring day, or into a catch-all null partition. Every subsequent step then works perfectly on data that is in the wrong place.

   ↑ reads from
2. Path written to storage

   holds The encoded column=value segment that makes the value knowable without reading.

   could corrupt Inconsistent encoding — zero-padding, casing, a different column name — produces partitions some engines see and others do not.

   ↑ reads from
3. Partition registered in the catalog or manifest

   holds The authoritative list of partitions the planner will consider.

   could corrupt A partition present on storage and absent here is excluded from every query with complete confidence. This is a missing-rows incident that presents as a fast query (Missing Rows).

   ↑ reads from
4. Predicate as written by the consumer

   holds The condition the planner will try to fold into a comparison against partition values.

   could corrupt A function, a cast, a disjunction or a join-derived bound leaves nothing foldable, and every partition survives.

   ↑ reads from
5. Planner's elimination decision

   holds The set of partitions that could contain a matching row.

   could corrupt Engine version differences, particularly for dynamic pruning, change this set without any change on either side of it.

   ↑ reads from
6. Tasks scheduled over surviving files

   holds The actual work: one task per file, reading and filtering.

   could corrupt One partition far larger than the rest turns a well-pruned query into a single straggler task (Data Skew).

Two nodes cause slowness and two cause missing data. The ones that cause missing data — a mis-derived value and an unregistered partition — both make queries faster, which is why nobody investigates them.

The first check is the one people build and the second is the one that catches an incident. A pruning ratio protects the bill; a storage-versus-catalog reconciliation protects the number.