Don't Panic — SQL Query Optimization for Analysts

SQL query optimization is the craft of asking for the same analytical answer while making the database do less carrying, sorting, joining, and general furniture-moving. SQL describes the result, which is delightful until the engine expresses its feelings about the request with a very long wait. The remedy is not a collection of lucky incantations. It is a way to make the work visible.

The first anchor is grain: what one row represents. One row per order is not one row per line item, customer, or month, however enthusiastically a join tries to make it so. If a rewrite changes that promise, it is not an optimization. It is a new query wearing the old query's coat. Keep columns, filters, null behavior, duplicate behavior, and required order in the answer contract before you start moving clauses around.

Then meet the execution plan, the engine's account of how it intends to turn the request into rows. Scans read data. Filters discard it. Joins combine it. Aggregates reduce it. Sorts establish order. In distributed systems, exchanges move it between workers, because apparently rows also enjoy travel. The plan and query profile reveal where width, scan depth, join growth, repetition, or shuffle become actual work.

The surprising bit is that familiar SQL shapes are not harmless decoration. SELECT * can read columns nobody needs. A row limit can still leave the scan large. DISTINCT and UNION can demand duplicate-elimination work. A missing join condition can make rows multiply with magnificent efficiency. A common table expression may be a readability boundary, not a promise that the database will materialize it. The engine gets choices; your SQL gives it the problem.

Start with the Intro for the full map of scans, joins, aggregation, sorting, windows, and reuse. The Slides compress that map into the work multipliers and diagnostic loop. The Cheatsheet is the compact companion when a profile has produced an alarming operator. The Practice Reference and exercise provide a controlled comparison of join fan-out and match existence. The Field Notes cover the uncomfortable operational facts that profiles alone do not settle.

Keep the loop small: preserve the answer, establish a baseline, inspect the evidence, change one cause, and measure again. Estimated plans are safer when execution is uncertain; actual profiles provide runtime facts but execute the statement. Once the query shape is clear, bring the measured bottleneck to whoever owns indexes, layout, statistics, memory, or capacity. That is not surrender. It is the database equivalent of bringing a map to a discussion about roads.