Query Optimization

Reviewed & published by Brayan K

By the end of this lesson you'll be able to open up any slow query with EXPLAIN, read the plan line by line, understand how the cost-based optimiser decided what to do, and rewrite the query so it uses your indexes instead of scanning whole tables. This is the skill that separates "it works" from "it's fast at scale".

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Read EXPLAIN and EXPLAIN ANALYZE output line by line
• Tell a seq scan from an index scan — and why it matters
• Recognise nested loop vs hash vs merge joins
• Understand how the optimiser turns statistics into cost
• Rewrite queries to be sargable and index-friendly
• Spot stale statistics and apply optimiser hints safely

The Big Picture

A query is a destination; the optimiser is a satnav. You say where you want to go (the SQL); the optimiser works out how to get there — which roads (scans), which interchanges (joins), in which order. EXPLAIN prints the chosen route before you drive it. EXPLAIN ANALYZE drives it and logs the real travel time at every junction. Your job in this lesson is to read that route sheet and spot where the satnav took the slow road because it had a bad map (stale statistics) or because you wrote the address in a way it couldn't match to a fast road (a non-sargable predicate).

1. EXPLAIN — Print the Plan Before You Run It

The query planner (also called the optimiser) is the part of the database that turns your SQL into a concrete execution plan — an ordered tree of steps. EXPLAIN prints that tree without running the query. EXPLAIN ANALYZE actually executes it and adds the real timings and real row counts next to each step, so you can compare guess against reality.

Read a plan bottom-up and inside-out: the most-indented node runs first, and its output rows flow up into the node above it. The top line is the final result.

-- engine: postgres — this whole lesson is about reading query plans, and
-- EXPLAIN ANALYZE is PostgreSQL syntax that SQLite does not have at all
-- (its nearest equivalent is EXPLAIN QUERY PLAN, which prints something
-- quite different). Pressing Run on this site uses SQLite, so run these
-- against a real PostgreSQL server to see the plans below.
--
-- The table this lesson uses. Every later block queries it, and the
-- "Try it Yourself" button carries this setup along so each snippet runs.
-- manager_id points at another row's id; the CEO's is NULL, which is what
-- makes the org chart walkable.
CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       TEXT,
    title      TEXT,
    manager_id INTEGER,
    salary     INTEGER,
    hire_date  TEXT,
    email      TEXT
);

INSERT INTO employees (id, name, title, manager_id, salary, hire_date, email) VALUES
    (1, 'Ada',   'Chief Executive',  NULL, 180000, '2019-02-11', '[email protected]'),
    (2, 'Brian', 'VP Engineering',      1, 140000, '2020-06-01', '[email protected]'),
    (3, 'Carla', 'VP Sales',            1, 135000, '2021-09-20', '[email protected]'),
    (4, 'Dan',   'Engineer',            2,  75000, '2024-01-15', '[email protected]'),
    (5, 'Eve',   'Junior Engineer',     4,  62000, '2024-07-08', '[email protected]');

-- EXPLAIN shows the plan the optimiser CHOSE — it does not run the query.
EXPLAIN
SELECT name, salary
FROM employees
WHERE salary > 70000;

-- EXPLAIN ANALYZE actually RUNS the query and adds the real timings,
-- so you can compare what the optimiser guessed against what happened.
EXPLAIN ANALYZE
SELECT name, salary
FROM employees
WHERE salary > 70000;

-- Read a plan bottom-up and inside-out: the most indented node runs first,
-- and its rows flow up into the node above it.

2. Seq Scan vs Index Scan

The first thing to find in a plan is how each table is read. A Seq Scan (sequential scan) reads every row in the table and throws away the ones that don't match — fine for tiny tables or when you genuinely want most rows. An Index Scan uses an index to jump straight to the matching rows — far less work when you want only a few.

-- The SAME query can be answered two very different ways.

-- (1) No useful index on salary -> the engine reads every row:
EXPLAIN SELECT * FROM employees WHERE salary > 70000;
-- Plan: Seq Scan on employees  (cost=0.00..1850.00 rows=1200 width=64)
--         Filter: (salary > 70000)
-- "Seq Scan" = sequential scan = read the whole table, throw away non-matches.

-- (2) Add an index that matches the predicate:
CREATE INDEX idx_emp_salary ON employees (salary);

EXPLAIN SELECT * FROM employees WHERE salary > 70000;
-- Plan: Index Scan using idx_emp_salary on employees
--         (cost=0.42..96.30 rows=1200 width=64)
--         Index Cond: (salary > 70000)
-- "Index Scan" jumps straight to the matching rows. Far less work.

-- Rule of thumb: a Seq Scan on a big table that returns FEW rows is a red flag.

Line-by-line: Seq Scan on employees (cost=0.00..1850.00 rows=1200 width=64)

• Seq Scan on employees — the access method and the table.
• cost=0.00..1850.00 — estimated cost to get the first row (0.00) and to finish (1850.00).
• rows=1200 — the optimiser's estimate of how many rows survive the filter.
• width=64 — average bytes per returned row.
• Filter: (salary > 70000) — the condition applied to every row read. On an Index Scan this line becomes Index Cond, meaning the index itself did the filtering.

3. Cost, Rows & Width — How the Optimiser Decides

The optimiser builds several candidate plans, assigns each a numeric cost, and keeps the cheapest. Cost is an abstract unit — not milliseconds — derived from a few tunable constants for disk reads and CPU work. The key insight: every estimate ultimately traces back to the row count the optimiser expects, which it gets from table statistics.

-- Every node is labelled cost=startup..total  rows=estimate  width=bytes.
EXPLAIN SELECT * FROM orders WHERE total > 500;
-- Seq Scan on orders  (cost=0.00..1250.00 rows=5000 width=64)
--   Filter: (total > 500)
--
-- cost=0.00..1250.00  -> 0.00 to return the FIRST row, 1250.00 for ALL rows.
-- rows=5000           -> the optimiser's GUESS of how many rows match.
-- width=64            -> average bytes per row (drives memory + I/O estimates).

-- Those numbers are abstract "cost units", not milliseconds. PostgreSQL bases
-- them on a handful of tunable constants:
--   seq_page_cost        = 1.0    (read one page in order)
--   random_page_cost     = 4.0    (read one page at a random spot — disks seek)
--   cpu_tuple_cost       = 0.01   (process one row)
--   cpu_index_tuple_cost = 0.005  (process one index entry)
-- Total cost is roughly: (pages read x page_cost) + (rows x cpu_cost).
-- The optimiser builds several candidate plans and keeps the CHEAPEST total.

4. Estimated Rows vs Actual Rows

This is the single most useful diagnostic in the whole lesson. EXPLAIN ANALYZE prints the estimated rows (from statistics) right beside the actual rows (from running the query). When they're close, the optimiser had a good map and its plan choice was sound. When they're off by 10x or more, the optimiser was flying blind — and almost certainly chose the wrong plan.

-- EXPLAIN ANALYZE prints BOTH the estimate and the reality. Compare them.
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';
-- Seq Scan on orders
--   (cost=0.00..1850.00 rows=200 width=72)            <- estimated 200 rows
--   (actual time=0.05..6.90 rows=48000 loops=1)       <- ACTUALLY 48000 rows
--   Filter: (status = 'shipped')
--   Rows Removed by Filter: 12000
--
-- estimate 200 vs actual 48000 = a 240x miss. The optimiser thought almost
-- nothing matched, so it skipped the index. That mismatch is the classic
-- symptom of STALE STATISTICS (covered in section 6).
-- loops=1 means the node ran once; in a nested loop it can run many times,
-- and the printed row count is PER loop.

What the extra ANALYZE numbers mean

• actual time=0.05..6.90 — real milliseconds to first row and to last row for this node.
• rows=48000 (in the actual line) — rows this node really produced.
• loops=1 — how many times the node ran. In a nested loop the inner node runs many times; the printed time and rows are per loop, so multiply by loops for the true total.
• Rows Removed by Filter: 12000 — work the engine did and then threw away. Large numbers here mean a better index or predicate could avoid the wasted reads.

Your Turn: make it sargable

A predicate is sargable (Search-ARGument-able) when the engine can use an index for it. Wrapping the indexed column in a function like YEAR() destroys that. Rewrite the year test as a half-open range on the bare column. The expected plan is in the comments so you can check yourself.

-- 🎯 YOUR TURN — make this query "sargable" (able to use an index).
-- The index on hire_date is useless here because YEAR() hides the raw column:
--   SELECT * FROM employees WHERE YEAR(hire_date) = 2024;
--
-- Rewrite it as a half-open RANGE on the bare column so the index can be used.

SELECT * FROM employees
WHERE hire_date >= ___          -- 👉 first instant of 2024
  AND hire_date <  ___;         -- 👉 first instant of 2025 (exclusive upper bound)

-- ✅ Expected: predicate uses hire_date directly, so EXPLAIN now shows
--    "Index Scan ... Index Cond: (hire_date >= '2024-01-01' AND hire_date < '2025-01-01')"
--    Fill the blanks with '2024-01-01' and '2025-01-01'.

5. Join Methods — Nested Loop, Hash, Merge

When two tables are joined, the optimiser picks one of three physical strategies. Each wins in a different situation, and the plan tells you which one it chose.

Nested Loop: for each row on the outer side, look up matches on the inner side. Best when the outer side is tiny and the inner side is indexed; awful for large-by-large because it's roughly outer × inner work. Hash Join: build a hash table from the smaller input, then stream the larger one through it — the default for big equality joins. Merge Join: sort both inputs on the join key and walk them together like a zip — best when both inputs already arrive sorted (both columns indexed).

-- A JOIN can be executed three ways. The optimiser picks based on table
-- sizes, indexes and available memory. Learn to recognise each in a plan.

EXPLAIN ANALYZE
SELECT c.name, o.total
FROM customers c                       -- small: ~1,000 rows
JOIN orders o ON c.id = o.customer_id  -- large: ~1,000,000 rows
WHERE o.total > 100;

-- You will see ONE of these as the join node:
--
-- Nested Loop      -> for each outer row, look up matches in the inner table.
--                     Great when the outer side is tiny AND the inner side is
--                     indexed. Terrible for large x large (it is O(n x m)).
--
-- Hash Join        -> build an in-memory hash table from the SMALLER input,
--                     then stream the larger input through it. The default for
--                     big equality joins when neither side is pre-sorted.
--
-- Merge Join       -> sort BOTH inputs on the join key, then walk them in step
--                     like a zip. Wins when both inputs already arrive sorted
--                     (e.g. both sides have an index on the join column).

6. Query Rewrites That Unlock Indexes

You influence the plan far more by how you write the query than by tweaking the optimiser. These five rewrites remove the most common reasons an index goes unused.

-- Five rewrites that routinely turn a slow plan into a fast one.

-- 1) SARGABLE PREDICATE — no function on the indexed column.
-- ❌ index on hire_date cannot be used:
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;
-- ✅ bare column + range -> index usable:
SELECT * FROM employees
WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';

-- 2) SELECT ONLY THE COLUMNS YOU NEED — lets an index "cover" the query.
-- ❌ forces a trip back to the table for every row:
SELECT * FROM orders WHERE customer_id = 42;
-- ✅ if (customer_id, total) is indexed, this reads the index ALONE:
SELECT customer_id, total FROM orders WHERE customer_id = 42;

-- 3) REPLACE OR (across columns) WITH UNION — each branch uses its own index.
-- ❌ an OR over two columns usually defeats both indexes:
SELECT * FROM products WHERE category = 'Electronics' OR brand = 'Apple';
-- ✅ split so each side is a clean, index-friendly lookup:
SELECT * FROM products WHERE category = 'Electronics'
UNION
SELECT * FROM products WHERE brand = 'Apple';

-- 4) EXISTS vs IN — EXISTS can stop at the first match.
-- IN materialises the whole subquery; EXISTS short-circuits as soon as one
-- matching row is found, which is faster for "does any related row exist?":
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- 5) AGGREGATE ONCE, NOT PER ROW — kill correlated subqueries.
-- ❌ runs the COUNT once for EVERY customer row:
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) FROM customers c;
-- ✅ one grouped pass over the data:
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

Your Turn: fix the seq scan

The plan below reads all 2,000,000 rows to return just 12. The predicate is already sargable (a bare customer_id), so the issue isn't the query — there's simply no index to use. Write the one statement that fixes it.

-- 🎯 YOUR TURN — EXPLAIN below shows a Seq Scan returning only 12 of 2,000,000
-- rows. Reading the whole table to find 12 rows is the problem. Choose the fix.
--
--   EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
--   Seq Scan on orders  (cost=0.00..36000.00 rows=12 width=64)
--     Filter: (customer_id = 42)
--
-- Write the ONE statement that lets a future run use an Index Scan instead:

___   -- 👉 create an index on the column used in the WHERE filter

-- ✅ Expected: CREATE INDEX idx_orders_customer ON orders (customer_id);
--    Re-running EXPLAIN then shows:
--    "Index Scan using idx_orders_customer ... Index Cond: (customer_id = 42)".

7. Statistics — The Optimiser's Map of Your Data

Every cost estimate ultimately rests on statistics: how many rows a table has, how many distinct values a column holds, which values are most common, and a histogram of the distribution. The database samples these periodically. After a big bulk load, delete, or import, the sample is out of date — stale — and the estimates (remember the 200-vs-48000 miss) go badly wrong, taking the plan with them.

-- The optimiser's row estimates come from STATISTICS it samples per table:
-- how many rows, how many distinct values, the most common values, histograms.
-- If the data changed a lot since the last sample, the estimates go stale and
-- the plans go wrong (remember the 200-vs-48000 miss above).

-- PostgreSQL: refresh statistics by hand (autovacuum also does this for you):
ANALYZE orders;

-- See when each table was last analysed and how big it is:
SELECT relname, last_analyze, last_autoanalyze, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- For a skewed column, store a finer histogram, then re-analyse:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- MySQL is similar:  ANALYZE TABLE orders;
-- After a big bulk load or delete, ANALYZE first — THEN trust EXPLAIN.

8. Optimiser Hints (Use Sparingly)

Sometimes the optimiser still gets it wrong and you need to override it with a hint. There's no SQL standard for hints, so the syntax differs by engine — and a hint that helps today's data can hurt tomorrow's. Fix indexes, predicates and statistics first; treat hints as a last-resort splint.

-- "Hints" let you override the optimiser. SYNTAX VARIES BY ENGINE — there is
-- no standard, so a hint that works on one database is ignored or rejected on
-- another. Reach for them only after fixing indexes, predicates and statistics.

-- PostgreSQL has no inline hints; you nudge the planner with session flags:
SET enable_seqscan = off;   -- discourage sequential scans for this session
EXPLAIN SELECT * FROM employees WHERE salary > 70000;  -- see what it picks now
SET enable_seqscan = on;    -- always turn it back on afterwards

-- MySQL / Oracle use comment-style hints right after SELECT:
--   SELECT /*+ INDEX(orders idx_orders_customer) */ * FROM orders WHERE customer_id = 42;
--   SELECT /*+ NO_INDEX(t idx_x) */ ...

-- SQL Server uses an OPTION clause:
--   SELECT * FROM orders WHERE customer_id = 42 OPTION (RECOMPILE);

-- Treat hints as a temporary splint. A hint that fixes today's data often
-- becomes tomorrow's bug when the data distribution shifts.

Common Errors (and the fix)

• Function on an indexed column kills the index: WHERE YEAR(hire_date) = 2024 or WHERE UPPER(name) = 'ANA' forces a Seq Scan. Rewrite as a range on the bare column, or build a matching expression index on YEAR(hire_date).
• Leading-wildcard LIKE can't use a B-tree: WHERE name LIKE '%son' scans the whole table because the index is sorted left-to-right. LIKE 'son%' (anchored at the start) can use the index.
• Stale statistics produce wild estimates: if EXPLAIN ANALYZE shows estimated rows far from actual rows, run ANALYZE your_table; before blaming the query.
• SELECT * defeats a covering index: if an index covers (customer_id, total) but you ask for every column, the engine must visit the table anyway. Select only the columns you need so the index alone can answer.
• Reading the plan top-down: plans execute the most-indented node first. Read bottom-up, or you'll misjudge which step is the real bottleneck.

Frequently Asked Questions

Q: Is a Seq Scan always a problem?

No. If your query returns most of the table, one sequential pass is cheaper than millions of random index look-ups, so the optimiser correctly picks a Seq Scan. It's only a smell when a large table is scanned to return a few rows.

Q: Are the cost numbers in milliseconds?

No — they're abstract units derived from page-read and CPU constants. Use them to compare two plans, not as a stopwatch. For real time, use EXPLAIN ANALYZE and read the actual time values.

Q: EXISTS or IN — which is faster?

For "does any related row exist?", EXISTS can stop at the first match, while IN may materialise the whole subquery. On modern optimisers they're often planned identically, but EXISTS is the safer default for correlated existence checks.

Q: My estimate and actual rows are wildly different. What now?

Run ANALYZE your_table; to refresh statistics, then re-check. If a column is very skewed, raise its statistics target (SET STATISTICS 1000) and analyse again so the histogram captures the distribution.

Q: Do these plans look the same in MySQL or SQL Server?

The concepts (scans, joins, cost, statistics) are universal, but the wording and exact numbers differ. MySQL's EXPLAIN formats results differently and uses comment-style hints; SQL Server shows graphical plans and uses OPTION. Learn the ideas here and the engine-specific syntax maps over easily.

Mini-Challenge: Optimise the Slow Report

Now do it with the support removed — a brief, a blank canvas, and the expected shape in the comments. Make the date test sargable, add one supporting index, and explain how the plan changes. Then paste it into a playground to confirm.

-- 🎯 MINI-CHALLENGE — optimise a slow report, then explain the plan change.
--
-- The query below is slow on a 5,000,000-row orders table:
--
--   SELECT *
--   FROM orders
--   WHERE YEAR(created_at) = 2024
--     AND status = 'shipped';
--
-- EXPLAIN shows: Seq Scan on orders, Filter on both conditions, ~9s.
--
-- 1. Rewrite the date test to be SARGABLE (range on the bare created_at).
-- 2. Add ONE index that supports both conditions, most selective column first.
-- 3. In a comment, say which scan node you now expect and why it is faster.
--
-- ✅ Expected (one correct shape):
--    CREATE INDEX idx_orders_status_created ON orders (status, created_at);
--    SELECT * FROM orders
--    WHERE status = 'shipped'
--      AND created_at >= '2024-01-01' AND created_at < '2025-01-01';
--    -- Plan flips Seq Scan -> Index Scan (Index Cond on status + created_at):
--    -- it jumps to the 'shipped' block and range-reads only 2024, instead of
--    -- scanning and filtering all 5,000,000 rows.

-- your solution here

🎉 Lesson Complete

• ✅ EXPLAIN shows the plan; EXPLAIN ANALYZE runs it and adds real times
• ✅ A Seq Scan reads everything; an Index Scan jumps to matching rows
• ✅ Cost, rows and width drive every decision — and all rest on statistics
• ✅ Estimated-vs-actual rows is your fastest stale-stats diagnostic
• ✅ Nested loop, hash and merge joins each win in different situations
• ✅ Sargable predicates and column-only SELECTs unlock your indexes
• ✅ Next: Advanced JOIN Patterns — semi-joins, anti-joins, and lateral joins

Practice quiz

Continue this course

• Previous: Indexing Internals: B-Trees, Hash Indexes, Bitmap Indexes & When to Use Them
• Next: Advanced JOIN Patterns: Anti-Joins, Semi-Joins, Cross & Lateral Joins — Use NOT EXISTS, EXISTS, CROSS APPLY, and LATERAL for complex join patterns
• Quick reference: SQL cheat sheet