Changing how much data exists

Why do data-reduction mechanisms dominate, and why is their limit the interface's vocabulary?

CaseConstructed case · Dev at Kiranamart

40 rows returned, 4.2 TB scanned

Kiranamart now exports the order data as Parquet files in object storage, for a second query engine that bills by bytes scanned. Dev builds a small page: "Yesterday at my store". It shows 40 rows: the top 40 products sold yesterday in one store. The query looks harmless.

SELECT p.name, SUM(oi.qty) AS units
FROM order_items oi
JOIN orders o   ON o.order_id = oi.order_id
JOIN stores s   ON s.store_id = o.store_id
JOIN products p ON p.product_id = oi.product_id
WHERE s.store_name = 'Koramangala'
  AND o.order_date = DATE '2025-03-10'
GROUP BY p.name
ORDER BY units DESC
LIMIT 40;

The answer is 40 rows. The engine's query report says it scanned 4.2 TB. The cloud bill for the dashboard is larger than the bill for the whole nightly load. Dev asks Ananya: "The optimizer chose a good join order. Why is it still reading everything?"

Nothing is wrong with the join order. The planning articles of this mechanism chose how to do the work. This article is about a different idea: making the work disappear.

How it ends →

The earlier articles chose an execution strategy for a computation whose input size was fixed. Every win was a constant factor. The constants were large, but they were constants. The mechanisms in this article are categorically different. They do not choose a strategy. They change how much data there is to process. That is why they dominate. A better join order may win 10×. Not reading 99% of the files wins 100×. The two wins multiply; they do not compete.

All the mechanisms in this article are the same act. Each one takes a fragment of the query (a predicate, a column list, a row limit, a set of join keys) and transports it downward across an interface until it reaches a layer that can decline to produce data.

Predicate pushdown moves a filter into the scan, then into the file decoder, then into a comparison against the file footer, then into a remote system.
Projection pushdown moves a column list into the file reader.
Top-N pushdown moves a bound into a heap, and then into a threshold.
Runtime filters create a predicate that did not exist in the query and send it sideways.

So the real subject of this article is not optimization. It is interface design. The limit is not physical and not informational. It is linguistic: what vocabulary does the layer below agree to understand?

Two questions that sort the mechanisms

A second distinction matters as much, because it separates mechanisms with very different risk. Ask: where does the information come from, and what does the mechanism do with it?

	Prove irrelevance (do not read)	Defer reading (read later, or less)
From the query text	Partition pruning, file and row-group skipping	Projection pushdown, LIMIT
From catalog metadata (the stored description of the tables)	Constraint-based elimination, metadata-only answers	—
From this query's own execution	Dynamic partition pruning, runtime filters	Top-N threshold feedback, late materialization

The left column is conservative and safe in a precise sense: it can only produce false positives. You may read a block that you did not need. You will never skip a block that you did need. The right column proves nothing. It changes when work happens, and its risk is that the reordering costs more than it saves.

This "safe direction" is the sharpest contrast with the planning articles. Cardinality estimation had no safe side, so its errors were unbounded. Data skipping has a safe side, so every engine adopts it aggressively. Nobody has ever been paged because a zone map (a stored min/max summary of a block) was too conservative.

Some terms used in this article

Parquet is a columnar file format. A file is split into row groups (for example 10,000 or 1,000,000 rows). Inside a row group, each column is stored on its own, in pages. A footer at the end of the file stores, for each row group and column: the row count, the minimum, the maximum and the null count. These are the statistics.
Pruning means skipping whole partitions or files. Skipping usually means skipping row groups or pages inside a file that you do open.
A predicate is a condition in a WHERE clause. A predicate is sargable when the engine can use it directly against stored values (dt = '2024-01-01') and not when the column is wrapped in a function (date(ts) = '2024-01-01').
A Bloom filter is a small bit-array structure that answers "is this value in the set?". It may say "yes" wrongly (a false positive), but it never says "no" wrongly. So a "no" is a proof.