Comparison
Partition pruningvsProjection pushdown
Partition pruning
you filtered on the partition column and the engine opened four folders instead of nine hundred.
Skipping whole partitions because the query's filter proves they cannot contain matching rows. It is the coarsest and by far the most valuable form of skipping, which is why partition column choice is the most consequential physical decision in a warehouse. It fails silently when the filter is on a derived expression the engine cannot see through, so a function wrapped around the partition column can turn a two-second query into a full scan.
Full entry →Projection pushdown
you replaced select star with the six columns you needed and the bytes scanned fell by ninety percent.
Reading only the columns a query actually references, which columnar storage makes possible and which select-star defeats. It is the cheapest optimisation available in an analytical stack, and it is invisible in the query plan of an engineer who has never had to pay per byte. It also makes wide tables affordable, which is the reason one-big-table designs are viable at all.
Full entry →