An index is a sorted structure you trade writes for

An index is a separate data structure the database maintains alongside a table so it can find rows without reading all of them. A B-tree — the default and the one that matters most — keeps entries sorted by its key columns, so the engine can descend to a value in logarithmic time and then walk forward in order.

That sorted property is the whole story. It explains what an index can do: equality lookups, range scans, prefix matches, and supplying rows already ordered so a sort can be skipped. It also explains what it cannot: a B-tree cannot help with LIKE '%term', because there is no prefix to descend to, and a plain index on the base column cannot directly seek transformed values; an expression index on that transformation may.

Indexes add write work: inserts add entries, deletes eventually remove them, and updates that change indexed values must update the affected indexes. An insert writes to the table and to each index; each index consumes storage and adds vacuum work. So an index is a trade, and the question for any index is not "does this help a query" but "does this help enough to be worth taxing every write on this table".

This is the shape of the interview question. Asked "this query is slow, what do you do," the weak answer jumps straight to "add an index." The strong answer reads the plan first, works out why the engine chose the path it chose, and only then decides whether an index is the fix — and says what it will cost. Interviewers probe exactly that discipline, and the follow-ups come in a predictable order: why didn't the planner use the index you expected? What does that index cost on writes? What happens when the table is ten times bigger? If you can answer those without notes, the rest of this guide is the detail behind the answers.

Sargability: whether a predicate can be sought

A predicate is sargable when the engine can turn it into a range over an index's sorted keys. The mechanical requirement is that the indexed column appears bare on one side, compared against something the engine can evaluate independently of the row.

The classic defeats are all forms of wrapping the column:

• WHERE YEAR(created_at) = 2026 applies a function per row, so the index on created_at cannot be sought. Rewritten as a half-open range — created_at >= '2026-01-01' AND created_at < '2027-01-01' — it returns identical rows and seeks.
• WHERE LOWER(email) = :value has the same problem. Fixes: store a normalised column, build an expression index on LOWER(email), or use a case-insensitive collation.
• WHERE amount * 12 > 1000 should be amount > 1000 / 12.
• WHERE description LIKE '%term%' cannot use a B-tree at all. Genuine substring or full-text search needs a different index type — a trigram index for substrings, a full-text index with a tsvector for word search.
• WHERE status = 'open' OR priority = 1 cannot seek a single index on either column, because the union of two ranges is not one range. The planner will often fall back to a bitmap OR of two separate indexes if both exist, or a sequential scan if they don't. Sometimes the right fix is a composite index on the column pair, sometimes a rewrite to UNION of two seekable branches.

The subtle one is implicit type conversion. Mismatched parameter or literal types can make an engine cast the indexed column rather than the value, silently defeating an otherwise plausible index path. PostgreSQL may instead reject unsupported type pairs, so inspect the resolved expression and plan rather than assuming a coercion direction. It behaves like a hidden function call.

Sargability is necessary but not sufficient. A perfectly sargable predicate matching most of the table will still get a sequential scan — correctly, because reading most rows in physical order beats millions of random index lookups. An index helps when it is selective. This is the most common follow-up to "why isn't my index used": the planner isn't broken, it did the arithmetic and the scan won. A weak answer blames the planner; a strong answer estimates the selectivity.

Composite indexes and column order

For an index on (a, b, c), entries are sorted by a, then within equal a by b, then by c. That ordering dictates what it can serve.

It efficiently supports predicates on a; on a and b; and on a, b and c. Leading columns normally determine efficient scan bounds. PostgreSQL 18 can sometimes use skip scan, or scan a larger part of the index, for a predicate on b alone, but a dedicated b-leading index is usually far more effective for an important column-first lookup. Treat the leftmost-prefix idea as a cost guideline, not an absolute eligibility rule.

A useful starting heuristic is equality predicates first, then a range or ordering column, because leading equality conditions usually narrow the scan most effectively. A range stops later columns being usable for seeking, because once the scan spans many values of b, the c values within it are no longer in a single sorted run. So for WHERE tenant_id = ? AND created_at > ? ORDER BY created_at DESC, the index is (tenant_id, created_at DESC) — equality first, then the range column which also serves the sort.

An index on (a) is a redundancy candidate when (a, b) exists, but the narrower index may still be cheaper to scan or differ in uniqueness, operator class, collation, predicate or included columns. Compare definitions, sizes and measured usage before removing it.

The interview version of this is usually a scenario: "queries by tenant and date are slow, what index do you build?" The weak answer picks the most selective column first. The strong answer asks what the predicates look like — equality on tenant, range on date — and orders by the query pattern, not by raw cardinality.

Beyond the plain B-tree

Partial indexes cover a subset: CREATE INDEX ... WHERE processed = false on a queue table indexes only the rows anyone queries, staying tiny even as the table grows. They also enforce subset uniqueness — one active row per user while keeping history — which a plain unique constraint cannot express.

Expression indexes index a computed value, restoring seekability for a predicate that must transform the column.

Covering indexes let a query be answered from the index alone. If every column a query needs is in the index, the heap need not be visited at all — an index-only scan. PostgreSQL's INCLUDE clause adds payload columns without making them part of the sort key. The caveat is that index-only scans depend on the visibility map: if a page has recently-modified rows, the heap must still be checked, so a heavily updated table gets less benefit than the plan suggests until vacuum catches up.

Other index types exist for what B-trees cannot do: GIN for containment queries over JSON and arrays and for full-text search; GiST for geometric and range types, and for exclusion constraints; BRIN for very large naturally-ordered tables, where it stores per-block summaries and is dramatically smaller than a B-tree at the cost of coarser filtering; hash for equality only, rarely worth choosing over a B-tree.

Reading a plan

EXPLAIN shows the planner's chosen plan and its estimates. EXPLAIN ANALYZE actually runs the query and reports real timings and row counts — which is what you need, because the estimates are what may be wrong.

The first thing to look at is the gap between estimated and actual rows. A plan that expected 50 rows and got 50,000 chose its strategy on bad information: it may have picked a nested loop that is catastrophic at the real cardinality. Causes include stale statistics, correlated columns the planner models as independent, and expressions it cannot estimate through. Refreshing statistics sometimes fixes the entire problem without touching the query.

Then the shapes worth recognising. A sequential scan on a large table under a selective predicate suggests a missing or unusable index. A nested loop over a large outer input suggests the planner underestimated. A sort spilling to disk suggests either a missing index that could supply the order, or insufficient working memory. A filter removing most rows after an index scan means the index got you to the right area but the selective condition was not in it.

Read plans inside out: the innermost nodes execute first, and their timings are cumulative in the parents.

In an interview, the diagnostic order matters as much as the conclusion. Weak answers name a node type and stop. Strong answers walk the plan: here is the estimate, here is the actual, here is where the time went, here is what I'd change and how I'd verify the change worked. Expect the follow-up "how do you know it's the statistics and not the index?" — the answer is the estimate/actual gap, not a guess.

Costs and habits

Indexes are not free and the costs are worth stating plainly. Write amplification on inserts and deletes, and on updates that touch indexed values or cannot use a heap-only update. Storage, often substantial. Vacuum work — more indexes mean more dead tuples to clean up per delete, and on heavily churned tables that feeds bloat and keeps autovacuum busy. Slower bulk loads — dropping indexes before a large load and rebuilding after is a standard technique. And more choices for the planner, which occasionally makes a worse one.

"Add an index for every slow query" is the wrong answer, and interviewers set that trap deliberately. The right posture: create the structural indexes with the schema (primary keys and unique constraints are automatic; foreign key columns are not, and missing those makes every parent delete scan the child table). Add indexes for known critical queries. Add the rest against measured evidence. Then audit periodically for unused and redundant indexes, which most mature databases accumulate.

On a live table, build with CREATE INDEX CONCURRENTLY. A plain build takes a lock that blocks writes for its duration, which on a large table is an outage. The concurrent build is slower and can fail leaving an invalid index behind, which must then be dropped and retried — a fair trade against blocking production.

Worked example: LOWER(email) is not sargable

10M users. Filter WHERE LOWER(email) = 'a@x.com'. Planner expected 50 rows, actual 1.

access path	p99
sequential scan	22 s
btree on email (case-sensitive)	22 s (predicate still wraps the column)
btree on (LOWER(email))	4 ms

The index has to match the expression the predicate actually evaluates. EXPLAIN ANALYZE is the argument, not a named index.