openskills.info
Course catalog
Course Preview

SQL Execution Plans and Query Tuning

An SQL execution plan is the database engine's chosen sequence of scans, joins, sorts, and other operations for a query. Query tuning compares the optimizer's estimates with measured work so you can change SQL, indexes, statistics, or data design for a faster and more stable plan.

itDatabases and data storage

Don't Panic: SQL Execution Plans and Query Tuning

An execution plan is the database engine's account of how it intends to get your SQL result: scans at the leaves, joins and sorts in the middle, and a result at the root. SQL asks for a result, which is admirably noncommittal. The engine must still move rows, and it must choose how without holding a committee meeting for every possible plan.

The useful idea is cardinality, the number of rows an operator expects to produce. That estimate helps choose an access path, join order, join type, and memory allocation. If the estimate goes wrong early, later choices inherit the bad news with the grim determination of office paperwork reproducing itself. Actual plans let you compare that prediction with rows, loops, timing, buffers, and spills observed during execution.

A scan is not guilty because it is a scan, and a nested loop is not guilty because it has a worrying name. A sequential scan can suit a query that needs much of a table. A nested loop can suit a small outer input and a selective inner probe. The surprise is that optimizer cost is not elapsed time: it is an engine-specific score for comparing candidates, not a stopwatch wearing a tiny database hat.

Start with the first serious difference between estimated and actual rows. Then look for repeated inner work, broad reads followed by selective filters, and sorts or hashes that spill. Those clues point back to statistics, predicates, indexes, data shape, or parameter values. Change one cause at a time, preserve the baseline, and measure again. Otherwise the plan changes, the query changes, the cache changes, and everyone receives a splendid mystery.

The Reference tab explains engine-specific plan formats and statistics. Slides give the row-flow map when a tree starts looking botanical. Cheatsheet is the compact list of operator signals, index checks, and safe experiment rules. The Quiz tests whether a plan observation supports a conclusion. For hands-on practice, use the Practice and Exercise tabs with a disposable PostgreSQL database, then return to the plan with evidence instead of optimism.

Where this skill leads

Relevant careers

See how this topic contributes to broader role-level skill maps.

Sources