Query Execution Plan
SQL · Extra Concepts
Theory
When you send a query, the database does not run it literally. A component called the optimizer considers different ways to execute it (scan the table or use an index, which table to read first, which join algorithm) and picks the one it estimates is cheapest, using statistics about table sizes and value distributions. The chosen strategy is the execution plan.
You can see it by putting EXPLAIN before the query in PostgreSQL and MySQL, EXPLAIN QUERY PLAN in SQLite, or viewing the estimated/actual plan in SQL Server. A plan is a tree of steps such as SCAN employees, SEARCH employees USING INDEX idx_dept (dept=?) or a hash join. PostgreSQL's EXPLAIN ANALYZE actually runs the query and shows real row counts and times next to the estimates. Reading plans is how you find out why a query is slow: a full scan on a huge table, a missing index, or a big gap between estimated and actual rows (stale statistics) are the usual suspects.
Why we use it
Two queries that look similar can run very differently. The execution plan shows exactly how the database will do the work, so you can find the slow step instead of guessing.
When to use it
Check the plan whenever a query is slow, after adding an index (to confirm it is used) and before running heavy queries in production. Use EXPLAIN ANALYZE carefully on UPDATE or DELETE, since it really executes them.
How it works
- Submit the SQL text
- Parser checks syntax and resolves table/column names
- Optimizer compares candidate plans using statistics
- Useful index for the filter? Yes: Plan SEARCH USING INDEX. No: Plan a full table SCAN.
- Executor runs the chosen plan
- EXPLAIN shows that plan without (or with ANALYZE, with) running it
Syntax
EXPLAIN QUERY PLAN
SELECT ...;Example
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE department = 'IT';Key points
- EXPLAIN (Postgres/MySQL) or EXPLAIN QUERY PLAN (SQLite) reveal the plan
- Look for full table scans on large tables as a red flag
- EXPLAIN ANALYZE (PostgreSQL) runs the query and shows actual rows and time
- Big gaps between estimated and actual rows often mean stale statistics
- Read plans after adding an index to confirm it is actually used