How to Discuss Database Index Tuning in English

Proposing an index change well means explaining not just what you’re adding, but why the query planner will actually use it and what it costs on the write path — a proposal that only says “this will make queries faster” invites reasonable skepticism from anyone who’s seen an index made things worse.

Key Vocabulary

Query plan / execution plan — the database’s chosen strategy for running a specific query (a sequential scan, an index scan, a hash join), inspectable with EXPLAIN and the primary evidence for whether an index will actually help. “The query plan shows a sequential scan across two million rows — that’s the smoking gun for why this query is slow, and exactly what the new index should eliminate.”

Selectivity — how effectively a column (or combination of columns) narrows down the result set; a highly selective column is a strong index candidate, while a low-selectivity one (like a boolean) often isn’t worth indexing alone. “The status column has low selectivity — it’s one of three values across ten million rows, so indexing it alone won’t help much. Combining it with created_at in a composite index would.”

Composite index — an index spanning multiple columns, useful when queries consistently filter or sort on the same combination of fields, with column order mattering for which queries can actually use it. “We need a composite index on (customer_id, created_at), in that order, since every query filters by customer first and then sorts by date.”

Write amplification (index overhead) — the added cost every insert, update, and delete incurs from maintaining an index, the trade-off side of adding one that has to be weighed against the read-side benefit. “This table gets heavy write traffic, so a new index isn’t free — every insert now has to update three indexes instead of two, and we should measure that write amplification before committing to it.”

Covering index — an index that includes all the columns a query needs, letting the database satisfy the query entirely from the index without touching the underlying table (an “index-only scan”). “By including email in the index alongside the lookup key, this becomes a covering index for the login query — no need to hit the table at all.”

Common Phrases

• “What does the query plan show — a sequential scan, or is an existing index already being used inefficiently?”
• “Is this column selective enough to be worth indexing on its own?”
• “Should this be a composite index, and if so, what column order matches our actual query patterns?”
• “What’s the write amplification cost here, given how write-heavy this table is?”
• “Could we make this a covering index to avoid the extra table lookup entirely?”

Example Sentences

Proposing an index in a performance review: “The query plan confirms a full sequential scan on this table for a query we run constantly. Adding a composite index on (user_id, status) should convert that to an index scan — I’ve tested it in staging and it cut the query time from 800ms to 12ms.”

Pushing back on an over-eager indexing proposal: “Before we add this, let’s check selectivity — is_deleted is only true for a tiny fraction of rows, so an index on it alone probably won’t be used by the planner. A partial index might be the better fit.”

Explaining a trade-off to the team: “This index would help the read path significantly, but this table gets thousands of writes per second — the write amplification is a real cost. I’d want a benchmark before rolling it out to production.”

Professional Tips

• Always show the query plan, not just the observed latency, when proposing an index — “queries are slow” is unconvincing without the EXPLAIN output showing why.
• Discuss selectivity before proposing an index on a low-cardinality column — it prevents both wasted indexing effort and the “why didn’t this help” follow-up conversation.
• State the intended column order explicitly when proposing a composite index — order determines which queries can actually use it, and getting it wrong makes the index dead weight.
• Name the write amplification cost alongside the read-side benefit in any indexing proposal — a one-sided pitch that ignores write cost is an easy target for a skeptical reviewer, and rightly so.

Practice Exercise

1. Write a sentence explaining what a query plan reveals about index usage.
2. Describe selectivity in your own words with a low-cardinality example.
3. Explain the trade-off a composite index introduces on the write path.