Projection pushdown and Top-N pushdown

When does reading fewer columns save I/O, and why does Top-N remove spilling and sometimes slow a query?

Projection (column) pushdown

The derivation is short, and the details matter. The cost is proportional to the bytes of the columns you actually read. So projecting k of m columns cuts I/O by about k/m, but only if the layout lets you read a column without reading its neighbours.

In a row store, projection pushdown saves zero I/O. The page comes in whole. It saves only later materialization work. The common advice "select only the columns you need" is a statement about columnar storage, applied to storage that does not have that property.
The reduction is not k/m. It is measured in bytes, and columns differ in width and compressibility by more than an order of magnitude. Take a 40-column table where one free-text column holds 95% of the bytes. Dropping 39 narrow columns gives almost nothing. Dropping that one gives almost everything. The right unit is per-column size. Most optimizers do not have per-column size statistics and use the column count as a proxy. This is a gap in current practice, not a permanent limit. Table formats that record per-column sizes in their manifests make a better estimate possible, but optimizers have been slow to use it.
The step that silently costs the most in practice is pushdown into nested structures. Reading a.b.c from a deeply nested struct without reading all of a needs two things: the reader must address sub-fields independently, and the optimizer must prune the schema. Support for this arrived late, and was off by default for a long time in several engines. Where it is missing, a query that selects one leaf field reads and decodes the entire nested column. The plan looks correct and does 50× the I/O.

In one sentence: the value of projection pushdown is entirely set by the layout's column independence; the unit is bytes and not column count; and nested-field pruning is where it most often fails without any warning.

Limit and Top-N pushdown

A bare LIMIT pushes down trivially: stop producing rows. ORDER BY ... LIMIT k is the interesting case, and it changes the cost in two ways at once.

Naively, Top-N is a full sort followed by a cut: O(n log n) time and O(n) memory. Pushed into a bounded heap (a structure that always gives the largest or smallest item quickly) of size k, it becomes O(n log k) time and O(k) memory. The time gain is real but secondary. The memory change is the prize. A pipeline-breaking operator (one that must read all its input before it outputs anything) with unbounded state becomes a streaming operator with constant state. This removes spilling (writing intermediate data to disk when memory is full) and therefore a whole failure mode. A query that would have spilled 200 GB now uses a few megabytes. The difference is not a percentage.

import heapq
def top_k(rows, k, key):
    heap = []                        # min-heap of the k best seen so far
    for r in rows:
        if len(heap) < k:
            heapq.heappush(heap, (key(r), r))
        elif key(r) > heap[0][0]:    # better than the current k-th best
            heapq.heapreplace(heap, (key(r), r))
    return sorted(heap, reverse=True)

Then comes the elegant part. Once the heap is full, the k-th value is a predicate. The query contained no filter. But execution has created sort_key > current_kth, and this synthetic predicate can be fed straight back into row-group skipping. Engines that do this can abandon whole files in the middle of a scan. The effect compounds: each qualifying row tightens the threshold, a tighter threshold skips more, and skipping finds the good rows sooner. This positive feedback converges quickly when the data is clustered on the sort key and does nothing when it is not.

Two hazards.

LIMIT cannot generally be pushed through a join, because a join can remove rows. The first k input rows are not the first k output rows. It is legal only through a join that is proven to keep every left row. That needs an enforced foreign key (a declared and checked link between tables) or an outer join.
The classic pathology: LIMIT makes the query slower. With a LIMIT, the optimizer reasons that it only needs to scan until k rows qualify. It estimates how many rows it must scan as k divided by the filter's selectivity, assuming values are spread evenly. So it prefers an index scan on the sort column over "filter, then sort". If the filter and the sort order are correlated (all matching rows are at the end), the scan reads almost everything before it finds k rows. Here LIMIT turns "rows scanned before k qualify" into the key estimate, and that is the estimate most sensitive to correlation between two columns. This is the independence assumption failing in its most painful operational form. Removing the LIMIT makes the query fast, which is why this problem produces so many confused bug reports.

In one sentence: Top-N turns O(n) state into O(k) state and so removes spilling; its threshold manufactures a predicate out of nothing; and LIMIT makes a correlation-sensitive estimate load-bearing, which is why it can invert performance.