Indexes from the query side — EXPLAIN and query plans

The system design domain covered why indexes exist conceptually. This is the practitioner's side — actually creating one, and reading EXPLAIN's output to confirm whether a slow query is using it, instead of guessing.

Advanced

4 min read

Creating an index: the actual statement

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

This is the concrete version of the system design domain's database-indexes-basics lesson: CREATE INDEX builds a separate, sorted structure (typically a B-tree) that maps customer_id values to the rows that have them, so WHERE customer_id = 5 can jump almost directly to matching rows instead of scanning the whole table. The index has to be created explicitly — a column isn't indexed just because it's frequently queried; someone has to decide it's worth the write-cost/read-benefit trade covered in that earlier lesson and actually create it.

The tool that tells you whether an index is actually helping: EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

Index Scan using idx_orders_customer_id on orders  (cost=0.29..8.31 rows=1 width=48)
  Index Cond: (customer_id = 5)

EXPLAIN shows the database's actual query plan — the concrete steps it intends to take to run a query, without running it (or EXPLAIN ANALYZE, which does actually run it and reports real timing alongside the plan). This is the only reliable way to confirm whether a query is actually using an index — guessing based on whether an index exists is not the same as confirming the query planner chose to use it, and the planner's decision can genuinely surprise you.

Reading the two most common outcomes

-- Using the index (fast, for a selective filter):
Index Scan using idx_orders_customer_id on orders (cost=0.29..8.31 rows=1 width=48)

-- NOT using the index (scanning every row instead):
Seq Scan on orders (cost=0.00..2400.00 rows=50000 width=48)
  Filter: (customer_id = 5)

Index Scan means the database used the index — it went almost directly to the matching rows. Seq Scan (sequential scan) means it checked every row in the table, one by one, exactly the full-table-scan behavior the indexes-basics lesson described indexes as fixing — a Seq Scan on a query that has a matching index available is the concrete signal something's wrong: either the index isn't actually helping for this specific query, or the query isn't written in a way that lets the planner use it.

Why a query can fail to use an index that clearly exists

-- Index exists on customer_id, but this doesn't use it:
SELECT * FROM orders WHERE customer_id + 0 = 5;    -- wrapping the column breaks index usage
 
-- This does use it — the column is compared directly, unmodified:
SELECT * FROM orders WHERE customer_id = 5;

A B-tree index is built on the column's raw values — the moment the column is wrapped in a function or expression (customer_id + 0, UPPER(name), CAST(...)), the index no longer directly matches what's being compared, and most databases fall back to a full scan rather than the index. This is a genuinely common, easy-to-miss real bug: a filter that "looks like" it should use an index but doesn't, because the column isn't being compared in its raw form. EXPLAIN is what actually reveals this — the query still returns correct results either way, so nothing about the output signals the missing index usage, only the query plan does.

The planner sometimes chooses NOT to use an index, on purpose

-- Selective filter (few matching rows) -> index scan makes sense
WHERE customer_id = 5           -- maybe 3 matching rows out of 50,000

-- Non-selective filter (most rows match) -> a full scan can genuinely be faster
WHERE active = true              -- maybe 48,000 matching rows out of 50,000

An index only pays off when it narrows the search meaningfully — if a filter matches most of the table anyway, jumping through an index to fetch nearly every row individually is often slower than just reading the table straight through sequentially. The query planner estimates this using stored statistics about the table's data distribution, and it genuinely can and does choose a Seq Scan over an available index when its cost estimate says the index wouldn't actually help — this isn't a planner bug, it's the same reasoning the indexes-basics lesson covers about indexes not being free, applied at query-plan-decision time instead of at index-creation time.

The workflow this lesson is actually teaching

This is the concrete, practical version of "does this query need an index" — not guessing from first principles, but reading what the database itself intends to do, checking that against expectations, and only then acting. Creating an index and never confirming with EXPLAIN that it's actually being used is a real, common way indexes get created that provide zero benefit.