The optimiser's whole job is to move your WHERE clause as close to the disk as it can get it.
Telling the librarian which shelf you want before they start carrying books to your desk, instead of after.
The difference between a 2 GB scan and a 4 TB scan is usually one clause written in a way the optimiser can use.
You write a query as a logical statement: read this, join that, filter, group. The engine is free to execute it any way that produces the same answer, and the biggest wins come from doing less I/O. Predicate pushdown moves filters down past joins and into the scan, so rows are eliminated before they are read rather than after. Projection pushdown does the same for columns. Partition pruning skips whole directories. Together they decide whether a query reads two gigabytes or two terabytes — and the reason to understand them is that a few common query patterns silently disable them.
Predicate pushdown moves filters down the plan — past joins, into the file scan — so the engine skips row groups and partitions instead of reading and discarding rows. Projection pushdown reads only referenced columns. Both depend on the filter being expressible against the storage layer, which is why wrapping a column in a function, or filtering on a computed alias, quietly turns a pruned scan into a full one.
Query Optimization | SQL Query Optimization with Examples — Gate Smashers, 9:44