SQL Indexes & Query Performance

What an Index Is

A separate data structure (usually a B-tree) storing column values plus pointers back to the actual table rows — trading write overhead and storage space for read speed.

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);  -- no table lock — safe in production

Index Types

B-Tree (default) — good for equality, range (>, <, BETWEEN), ORDER BY, prefix LIKE 'x%'. A function call wrapped around the indexed column (WHERE LOWER(name) = 'alice') defeats a plain B-tree index — see the functional-index fix below.
Hash — equality only, faster than B-tree for = but useless for ranges; rarely the right default choice.
GIN / GiST (PostgreSQL) — GIN for full-text search, arrays, JSONB containment; GiST for geometric/range types.

Composite Index — the Left-Prefix Rule

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Query	Uses the index?
`WHERE user_id = 5`	Yes — leading column
`WHERE user_id = 5 AND status = 'active'`	Yes — both columns
`WHERE status = 'active'` (alone)	No — not the leading column

Column order matters and should be deliberate: put the most selective column first, and equality conditions before range conditions (idx(user_id, status, created_at) supports WHERE user_id=5 AND status='active' AND created_at > X fully; reversing the order wouldn't).

Partial Index

CREATE INDEX idx_active_orders ON orders(user_id) WHERE status = 'active';

Indexes only the rows matching the predicate — smaller, faster for the targeted query pattern, but only helps queries filtering on that same predicate.

Covering Index (Index-Only Scan)

CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (status, total);

Includes every column the query needs, so the database never touches the main table (heap) at all for a matching query — the fastest possible index usage.

Functional / Expression Index

CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- now WHERE LOWER(email) = 'alice@example.com' can use the index

`EXPLAIN ANALYZE` — Reading a Query Plan

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

Term	Meaning
`Seq Scan`	Full table scan — a red flag on a large table
`Index Scan`	Uses the index, then fetches matching heap rows
`Index Only Scan`	Uses a covering index — fastest, no heap fetch
`rows=X` vs `actual rows=Y`	A large gap between estimated and actual means stale statistics — run `ANALYZE`
`cost=X..Y`	Estimated cost, startup..total

When Indexes Silently Aren't Used

WHERE YEAR(created_at) = 2026                                       -- function on indexed column defeats it
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'      -- range equivalent — DOES use the index

WHERE name LIKE '%alice%'   -- leading wildcard — can't use a B-tree
WHERE name LIKE 'alice%'    -- prefix match — CAN use a B-tree

WHERE is_deleted = FALSE    -- if 95% of rows are FALSE, low selectivity — a seq scan may be cheaper anyway

Index Trade-offs

Benefit	Cost
Faster SELECT/JOIN/ORDER BY	Slower INSERT/UPDATE/DELETE — the index itself must be maintained on every write
—	Extra disk space + memory for index pages

Rule: index foreign keys and columns actually used in WHERE/JOIN/ORDER BY. Don't reflexively over-index a high-write table.

The N+1 Problem

# Bad: 1 query for users, then N more — one per user
for user in users:
    orders = query("SELECT * FROM orders WHERE user_id = %s", user.id)

# Fix 1: one JOIN
# SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id;

# Fix 2: batch load
# SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4, ...);

This is a query-count problem, not a query-correctness problem — the N+1 pattern is functionally correct but issues far more round trips than necessary; both fixes collapse N+1 queries into a small constant number.

Interview Drills

Why doesn't WHERE LOWER(name) = 'alice' use a plain B-tree index on name? — The index stores raw name values; wrapping the column in a function at query time means the database would need to evaluate LOWER() on every row to compare, which defeats the point of the index. Fix: a functional index built on LOWER(name) directly, or store a pre-lowercased column.
EXPLAIN ANALYZE shows a huge gap between estimated and actual row counts. What does this signal, and what's the fix? — Stale table statistics — the query planner's cost estimates (and therefore its plan choice, like index vs seq scan) are based on outdated statistics; running ANALYZE on the table refreshes them.
Why is idx(status, user_id) worse than idx(user_id, status) for a query filtering WHERE user_id = 5 AND status = 'active'? — The left-prefix rule means the index is only useful starting from its leading column — with status leading, a query that only filters user_id gets no benefit at all, and even the combined query benefits less if status (typically lower selectivity — few distinct values) is the leading column instead of the more selective user_id.

Cross-links

[[SQL Transactions & ACID]] · [[SQL Constraints & Schema DDL]] · [[Consistent Hashing]]