What is Query Optimization?

The Core Problem: When you write a query like SELECT * FROM orders WHERE customer_id = 123, there are many ways the database could execute it. It could scan every single row in the table. It could use an index on customer_id. It could even scan a materialized view. Each approach gives the same answer but at dramatically different costs. Without optimization, a naive approach would scan a 10 TB table on cloud storage taking tens of seconds to minutes. Product teams typically expect interactive analytics under 1 to 3 seconds p95 for dashboards and under 100 milliseconds for operational reads. The gap between these expectations and raw scan performance is what query optimization bridges. The Three Stage Pipeline: First, parsing turns query text into a logical plan showing what operations to perform. Second, logical optimization rewrites this into an equivalent but cheaper form, like pushing filters closer to data sources or simplifying expressions. Third, physical optimization chooses actual algorithms, such as which index to use, what join order and algorithm to apply, how to partition work across compute nodes, and whether to use cached results or scan fresh data. Cost Based vs Rule Based: Modern engines primarily use cost based optimization. They collect statistics about your data, such as how many rows exist, how values are distributed, and how many distinct values appear in each column. The optimizer then estimates the cost of different execution plans in terms of Input/Output operations, CPU cycles, memory usage, and network transfers. It picks the plan with minimum estimated cost. Some systems also support rule based optimization or user hints when cost models become unreliable with complex schemas.