Comparison
Predicate pushdownvsProjection pushdown
Predicate pushdown
the where-clause is handed to the file reader, which uses the min and max in each chunk's footer to skip most of them unread.
Pushing a query's filters down to the storage layer so entire blocks can be excluded before decoding. Its effectiveness depends entirely on physical layout: min/max statistics only help if the filtered column is correlated with the file's ordering, which is why sorting on the column you filter by is worth more than most query tuning. When someone says a filter 'did nothing for cost', this is the mechanism that failed.
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 →