Why does a top-N-per-group window function cost more than the N rows it returns?

## The order of operations is the whole story The idiomatic top-N-per-group query computes a ranking in a subquery and filters it outside: ```sql SELECT * FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) AS rn FROM orders o ) t WHERE rn = 1; ``` The filter on `rn` cannot be applied before the ranking exists, and the ranking is defined over the full ordered partition. So the physical plan does the expensive things first: hash every row on `customer_id`, ship it across the network, sort each partition by `order_ts`, number the rows, and only then discard all but one per customer. On a billion-row table returning ten million customers, roughly 99% of the shuffled and sorted bytes are thrown away. This is why the query "feels" cheap — the result is small — and is not. Result size is a terrible predictor of window cost; input size and partition size are the predictors. ## Why the engine cannot reduce early on its own Aggregation is *partially reducible*: each worker can compute a local partial result per group and send only that, so the exchange carries groups rather than rows. A general window function is not, because it produces one output row per input row; there is no smaller intermediate that preserves the answer. The top-N shape is a special case where reduction *is* possible in principle: a worker only needs its own local top N per key before the exchange, since a row that is not in the local top N of its partition-fragment cannot be in the global top N. Some optimisers implement exactly this — often called a partial or pushed-down top-N — recognising a `ROW_NUMBER`/`RANK` window whose only consumer is a filter such as `rn <= 3`. When it fires, the exchange carries N rows per key per worker instead of everything, and the query becomes cheap. When it does not fire, or when the filter is written in a way the optimiser does not recognise (buried behind other operators, combined with unrelated predicates, or applied to a saved intermediate), you pay the full price. The only reliable move is to read the plan and look for the row count entering the exchange. ## The aggregate rewrite Where the optimisation is absent, express the intent as an aggregation: ```sql WITH latest AS ( SELECT customer_id, max(order_ts) AS order_ts FROM orders GROUP BY customer_id ) SELECT o.* FROM orders o JOIN latest l ON o.customer_id = l.customer_id AND o.order_ts = l.order_ts; ``` The aggregation pre-reduces on every worker before the shuffle, so the network sees one row per customer per worker rather than every order. The join then fetches the payload. Three caveats: - **Ties duplicate.** If two orders share the maximum timestamp, both come back. `ROW_NUMBER` picks exactly one arbitrarily; the aggregate form does not. Add a deterministic tiebreaker, or accept and document the duplicate. - **It reads the table twice** — once for the aggregate, once for the join. That is usually still far cheaper than a full sort of every partition, but it is not free, and for a very narrow table the window form may win. - **Dialects with an argmax-style aggregate** (a function returning the value of one column at the row where another is maximal) collapse this into a single pass with no self-join. Where the dialect offers one, it is the best form; where it does not, the join is the portable answer. For N greater than one, the aggregate rewrite becomes clumsier — you need per-key top-N collection — and a well-optimised window with pushed-down top-N is usually the better tool. That is exactly the case where confirming the optimisation in the plan is worth the minute it takes. ## Shrink the input regardless Orthogonal to the operator choice: most "latest row per key" queries have a time bound in reality. If the newest order per customer is always within the last 90 days for active customers, filtering to that range before the window cuts the input by whatever fraction history represents, and any remaining customers can be handled by a second, small query. Partition pruning on a time-partitioned table makes this nearly free. ## Reading the plan The diagnostic is one number: rows entering the exchange below the window. If it equals the table's row count, no reduction happened and the rewrite is worth doing. If it is close to keys times workers times N, the optimiser has already done the work for you and the query is as good as it gets.