Understanding SQL Indexes and Query Performance

Reading an Execution Plan

An execution plan (EXPLAIN in Postgres/MySQL/SQLite, EXPLAIN ANALYZE for one that actually runs the query and reports real timings rather than estimates, or SQL Server's graphical plan) is the authoritative way to find out what a query is actually doing — guessing from the SQL text alone is unreliable, since the optimizer's actual choice depends on statistics you can't see just by reading the query.

EXPLAIN QUERY PLAN
SELECT * FROM Employees WHERE dept = 'Sales';

What to look for, in rough order of importance:

• Scan type per table — does it say some form of "seek"/"index" (good, for a selective condition) or "scan"/"full scan" (potentially fine on a tiny table, potentially a real problem on a large one)?
• Estimated vs. actual row counts (only visible with the "analyze" variant that actually executes) — a huge gap between what the optimizer expected and what actually came back is a strong signal of stale statistics or a query shape the optimizer is misjudging.
• Where the time is actually going — a plan with several steps often has one dominant, expensive step; optimizing anything else first is wasted effort.
• Key lookups / bookmark lookups appearing per row — a sign a covering index might help, as discussed above.

Treat EXPLAIN as the tie-breaker for any performance question in this entire reference — every rule above (SARGability, composite index order, selectivity) is a prediction about what the optimizer will do; the execution plan is the actual answer for your specific data and engine.