Lesson 10 of 14 · 8 min · SQL for applications

Indexes and reading a query plan

Covers: PostgreSQL 18 syntax; MySQL 8 differences noted; plan numbers in the examples are illustrative. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • What an index is and what it costs
  • Create single-column and composite indexes
  • The left-most prefix rule
  • Why a query may not use your index
  • Read the key lines of EXPLAIN

In simple words

A thick textbook has an index at the back. To find "photosynthesis" you do not read every page, you look it up in the index and jump to the page. A database index does the same for a column. Without it, the database reads the whole table to find your rows. With it, it jumps straight there. The price is extra space and a little extra work every time data changes.

Words to know

  • Index: a separate sorted structure that lets the database find rows without scanning the whole table.
  • Sequential scan: reading every row of a table.
  • B-tree: the usual index structure, sorted so it can search quickly and also handle ranges.
  • Query plan: the database's step-by-step strategy for running a query, shown by EXPLAIN.

Why indexes matter

Take a table with ten million orders and the query:

SELECT * FROM orders WHERE customer_id = 4821;

With no index on customer_id, the database performs a sequential scan: it reads all ten million rows to find the few that match. With an index it follows a sorted tree down to the matching entries in a handful of steps. Primary key and unique constraints create an index automatically; foreign key columns usually do not in PostgreSQL (MySQL's InnoDB does create one), so you should add them yourself for joins and filters.

CREATE INDEX idx_orders_customer ON orders (customer_id);

Indexes are not free:

  • Writes get slower: every INSERT, UPDATE and DELETE must also update each index on the table.
  • They use disk space and memory.
  • Too many indexes slow the application as much as too few.

Index the columns you often filter (WHERE), join (ON), or sort (ORDER BY) on, in tables large enough to matter. On a table with a few hundred rows, a sequential scan is just as fast and the database will happily choose it.

Composite indexes and the left-most rule

An index can cover several columns, and the order matters:

CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);

This index is sorted by customer_id first, then by order_date within each customer. It helps:

  • WHERE customer_id = 7 (uses the first column)
  • WHERE customer_id = 7 AND order_date >= '2025-10-01' (uses both)
  • WHERE customer_id = 7 ORDER BY order_date (rows come out already sorted)

It does not help a query that filters only on order_date, because the index is not sorted by date alone. This is the left-most prefix rule. Put the column you filter by equality first, and range or sort columns after it.

A covering index contains every column a query needs, so the database never visits the table. In PostgreSQL: CREATE INDEX ... ON orders (customer_id) INCLUDE (amount); makes SELECT amount WHERE customer_id = ... an "index-only scan".

A unique index (CREATE UNIQUE INDEX) also enforces that no two rows share the value, which is how a UNIQUE constraint works.

Why an index may be ignored

The database chooses a plan by estimated cost; it may skip your index for good reasons or bad habits:

Query Why the index is not used
WHERE LOWER(email) = 'a@b.com' a function on the column hides it from the plain index (fix: an index on LOWER(email))
WHERE name LIKE '%kumar' a leading wildcard cannot use a B-tree (a trailing one, 'kumar%', can)
WHERE customer_id = '4821' against an integer column in some setups a type mismatch can force a conversion
WHERE status = 'paid' when 90 percent of rows are paid reading most of the table is cheaper with a sequential scan
the table is tiny a scan is cheaper than the index lookup

Also remember OR across different columns, and arithmetic on the column (WHERE amount + 10 > 100), which defeat plain indexes.

Reading EXPLAIN

EXPLAIN shows the plan without running the query; EXPLAIN ANALYZE runs it and adds real timings (careful: it really executes UPDATE and DELETE statements, so wrap those in a transaction you roll back).

EXPLAIN SELECT * FROM orders WHERE customer_id = 4821;

Without the index, the output looks like (numbers vary):

Seq Scan on orders  (cost=0.00..180000.00 rows=20 width=60)
  Filter: (customer_id = 4821)

After creating idx_orders_customer:

Index Scan using idx_orders_customer on orders  (cost=0.43..95.20 rows=20 width=60)
  Index Cond: (customer_id = 4821)

What to look for:

  • Node type: Seq Scan (whole table), Index Scan, Index Only Scan, Bitmap Heap Scan; join methods Nested Loop, Hash Join, Merge Join.
  • cost=start..total: the planner's estimate in arbitrary units; compare plans, do not read as milliseconds.
  • rows: the estimated rows. In EXPLAIN ANALYZE you also see actual rows; a large gap means stale statistics (run ANALYZE).
  • Filter versus Index Cond: a Filter after a scan means rows were read and thrown away.

MySQL has EXPLAIN too, with columns such as type (ALL is a full scan, ref and range use an index), key (the index used) and rows.

A tuning routine

  1. Find the slow query (the application's logs, pg_stat_statements in PostgreSQL, or the slow query log in MySQL).
  2. Run EXPLAIN ANALYZE and find the expensive node.
  3. Add or adjust one index, run ANALYZE, and check the plan again.
  4. Confirm the speed-up and that writes did not become unacceptably slow.
  5. Drop indexes that nothing uses.

Common mistakes

  • Indexing every column "just in case".
  • A composite index in the wrong column order.
  • Wrapping an indexed column in a function or doing arithmetic on it.
  • Not indexing foreign keys used in joins (PostgreSQL).
  • Judging by a tiny test table, where a sequential scan always wins.

Try it yourself

  1. Create a table with 100,000 rows (generate_series in PostgreSQL) and run EXPLAIN ANALYZE on a filter before and after adding an index.
  2. Create (customer_id, order_date) and test three queries: by customer, by date only, by both. Which use the index?
  3. Try LIKE 'ash%' and LIKE '%ash' and compare the plans.
  4. Write down which of your own tables' foreign keys have no index.

Check yourself

What is the cost of adding an index?

Extra disk space and slower writes, because inserts, updates and deletes must maintain the index.

Why will an index on (customer_id, order_date) not help WHERE order_date = '2025-10-01'?

The left-most prefix rule: the index is sorted by customer_id first, so it cannot be searched by order_date alone.

Why might the database ignore an index and do a sequential scan?

The table is small, the filter matches most rows, a function wraps the column, or the pattern starts with a wildcard.

What does EXPLAIN ANALYZE add to EXPLAIN?

It actually runs the query and reports real timings and row counts next to the estimates.

Interview questions

What is the difference between a clustered and a non-clustered index?

A clustered index determines the physical order of the table's rows (one per table, the primary key in MySQL InnoDB). A non-clustered index is a separate structure that points back to the rows.

How do you find out why a query is slow?

Run EXPLAIN ANALYZE, find the costly node (often a sequential scan, a bad join or a sort), then check indexes, statistics and the query shape.

When should you not add an index?

On small tables, on columns with very few distinct values that rarely help, on write-heavy tables where the maintenance cost outweighs the read benefit, or on columns never used in filters, joins or sorts.

Summary

  • An index lets the database jump to rows instead of scanning the table; it costs space and write speed.
  • Composite indexes follow the left-most prefix rule.
  • Functions on columns, leading wildcards and tiny tables defeat or bypass an index.
  • EXPLAIN shows the plan; check scan type, estimated versus actual rows, and re-measure after each change.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.