In a job's step graph, which filters may the engine move below a grouping step, and which must stay above it?

## The rule behind the rewrite A **plan rewriter** - the engine component that edits the declared graph into an equivalent, cheaper graph before running it - may reorder steps only where the reorder is *equivalence-preserving*: the rewritten graph produces the same answer for every input. Cheapness is the motive; equivalence is the permission. Moving a filter below a grouping step is the clearest place to see the two come apart, because the cheap move and the correct move are not always the same move. Why the engine wants the move at all: a grouping step needs records that other workers are holding, so records must be sent across the network first. Every record removed before that point is a record that is never moved, never held in the grouping's working memory, and never combined. Filtering earlier is one of the largest single wins available to a rewriter. ## The three cases | the condition names | may it move below the grouping? | why | |---|---|---| | only grouping keys | yes | a record's key decides its group, so rejecting records by key removes entire groups and changes nothing about the groups that survive | | an aggregate result (a total, a count, a maximum) | no | the value does not exist before the grouping runs, and removing its input records would change the number being tested | | a non-key column of the record | usually no | it would drop part of a group, so every aggregate over that group would be computed from a different set of records | The middle row is the one candidates get wrong in both directions. Someone who says 'filters always move down' publishes wrong totals. Someone who says 'filters can never cross a grouping step' gives up the single best rewrite available, and also mis-explains why: keys can cross precisely because they cannot split a group. The third row has exceptions worth naming rather than glossing: if the condition on a non-key column happens to be implied by the aggregate being computed, or if the grouping is over records the condition cannot reject, the move is still equivalent. A rewriter that cannot prove the implication will not attempt it, which is the normal outcome. ## What blocks the move even when it is valid - **A step the rewriter cannot look inside.** If the condition calls an author-supplied body, the rewriter can call it but cannot reason about it - it cannot establish that the body behaves the same on every record regardless of position, so it leaves the condition where it was written. - **Missing values.** A condition that rejects records whose key is absent behaves differently above and below a grouping that treats absent keys as one group of their own. A careful rewriter refuses the move rather than guess. - **An engine that does not attempt it.** A model that runs one grouping step at a time and writes every intermediate result to disk before the next begins rewrites essentially nothing, so the author's written order is the executed order. There, the early filter is something the author writes, not something the engine grants. ## What an author should do about it The practical answer, and the one a good candidate volunteers: 1. **Write key conditions early yourself.** It costs nothing if the rewriter would have moved them, and it is the whole win if it would not. 2. **Accept that an aggregate condition runs last**, and reduce what reaches the grouping by other means - narrowing columns, rejecting records on keys, folding partial results per worker before the records move. 3. **Do not assume a rewrite happened.** The engine's own rendering of what it intends to run will show where the condition ended up; reading it is a separate skill and belongs with the plan-reading material, but the habit of checking is part of this one. ## The trap in the vocabulary 'The filter moved down' is said about two different edits. One is this one: past a grouping step, into the part of the graph that runs before records are redistributed. The other is the condition reaching the read itself, so rejected rows are never produced. They travel together in the common case - a key condition moves below the grouping and then continues to the read - but they are separate moves with separate conditions, and only the second can be blocked by an input that has no way to accept a condition.