Query performance — indexes, execution plans and common problems
Why some queries take milliseconds and others minutes, how indexes work, how to read an execution plan, and the habits that keep analytical SQL fast.
This lesson builds on: Window functions — ranking, LAG/LEAD, running totals and moving averages.
- Explain how an index speeds up lookups and what it costs
- Read a basic execution plan (scan vs index search)
- Recognise sargable and non-sargable conditions
- Apply practical optimisation habits to analytical queries
Why performance matters to analysts
Slow queries delay decisions, time out dashboards, and cost money on cloud warehouses that bill per data scanned. You do not need to be a database administrator, but you must avoid writing queries that force the database to do unnecessary work.
How an index works
Without an index, finding all orders for customer 42 means reading every row — a full table scan. An index is a separate sorted structure (usually a B-tree) of a column's values with pointers to rows, like the index at the back of a book. The database can jump straight to customer 42.
Costs: indexes take storage and slow down inserts/updates (every index must be maintained). Index columns used often in WHERE, JOIN and ORDER BY — typically foreign keys and dates.
Cloud warehouses (BigQuery, Snowflake, Redshift, Fabric) store data by column and rarely use classic indexes; instead they rely on partitioning and clustering by date or key. The principles below still apply: read fewer rows and fewer columns.
Execution plans
The execution plan is the database's step-by-step strategy for a query. Every DBMS can show it: EXPLAIN (PostgreSQL, MySQL), EXPLAIN QUERY PLAN (SQLite), "Display Estimated Execution Plan" (SQL Server).
The practice database has an index on orders.customer_id:
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42;| id | parent | notused | detail |
|---|---|---|---|
| 3 | 0 | 61 | SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?) |
SEARCH … USING INDEX means the index is used. Now filter on a column without an index:
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE payment_method = 'bnpl';| id | parent | notused | detail |
|---|---|---|---|
| 2 | 0 | 216 | SCAN orders |
SCAN orders = full table scan. Fine for 38,000 rows; painful for 380 million.
Sargable conditions
A condition is sargable (Search-ARGument-able) if the database can use an index for it. Wrapping the indexed column in a function usually prevents index use:
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE customer_id + 0 = 42;| id | parent | notused | detail |
|---|---|---|---|
| 3 | 0 | 207 | SCAN orders USING COVERING INDEX idx_orders_customer_id |
The plan changes from SEARCH to SCAN: because of the arithmetic, the database can no longer jump to 42 and must read every entry (SQLite reads the whole index instead of the whole table, but it is still a full scan). The same applies to WHERE YEAR(order_date) = 2025 in SQL Server or WHERE DATE(created_at) = '2025-03-01' — rewrite them as ranges on the raw column: order_date >= '2025-01-01' AND order_date < '2026-01-01'.
Common performance problems
| Problem | Symptom | Fix |
|---|---|---|
| Accidental cross join (missing ON condition) | Row counts explode, query never finishes | Check every join has a correct key condition |
| Fan-out then DISTINCT to "fix" duplicates | Slow and often wrong | Aggregate before joining |
SELECT * on wide tables |
Excess I/O, especially in columnar warehouses | Select only needed columns |
| Functions on filtered columns | Full scans | Use ranges on raw columns |
| Correlated subquery on large tables | Runs once per row | Rewrite with JOIN or window function |
Huge IN (…) lists |
Slow parsing/planning | Join to a table of values |
| Sorting enormous results | Spills to disk | Aggregate first; sort only the final output |
- Is the result correct? 2. Is it filtering early (
WHEREon indexed/partitioned columns)? 3. Is it reading only needed columns? 4. Only then look at the plan. Premature cleverness makes SQL unreadable for little gain.
Practice
Which filter can use an index on order_date?
A query joining three tables returns 2.4 billion rows when you expected about 80,000. The most likely cause?
Summary
- Indexes turn scans into searches but cost storage and write speed.
- Read plans: SCAN = read everything, SEARCH USING INDEX = targeted lookup.
- Keep filters sargable (no functions on indexed columns); use date ranges.
- Most slow analytical SQL comes from cross joins, fan-out,
SELECT *and per-row subqueries.