How do you confirm from an EXPLAIN plan that your query used the index you created for it?

## The question a plan can answer directly You added an index for a specific query. Did that query use it? This is one of the few things a plan answers unambiguously, because the access node names the index it went through. PostgreSQL: ```text Index Scan using idx_orders_customer on orders (cost=...) Index Cond: (customer_id = 42) ``` MySQL's tabular `EXPLAIN` reports the same fact in two columns: `possible_keys` lists the indexes considered usable, and `key` names the one actually chosen. `key` being NULL while your index sits in `possible_keys` is a distinct signal from your index not appearing at all — the first says "usable but not chosen", the second says "not applicable to this statement as written". ## Explain the real statement The most common self-inflicted error is explaining something that is not what runs in production: - **A simplified copy.** Removing a join or a predicate to "focus on the interesting part" can change the whole shape of the plan. Explain the full statement. - **Unrepresentative parameter values.** A value matching three rows and a value matching three million are different queries as far as access-path choice is concerned. Use values that resemble real traffic, and try both a typical and a worst-case value. - **Different text.** Whitespace and literal-versus-parameter differences aside, make sure the column list, the ordering and the limit match what the application sends; a `LIMIT` added "to keep it quick" can change the chosen path entirely. ## Realistic data or no conclusion On a tiny table there is nothing to save: reading the whole thing is genuinely cheap, so a plan captured on a near-empty development database tells you nothing about production. If you cannot test at scale, say so rather than drawing a conclusion — "the plan on my laptop scans the table" is not evidence that the index is useless. ## When the plan names a different index This is useful information, not a failure of the tool. Two author-side things to check first: whether the predicates in your statement really match the index you built (order of columns aside, the predicate must be expressed on the indexed columns in a form that can seek), and whether the query needs columns that make another index a better fit. If your statement is genuinely shaped the way you intended and the engine still prefers another path, you have crossed out of query authoring and into optimizer territory — how the engine estimates and chooses is a different discipline, and the plan has done its job by telling you where the boundary is. ## Re-check after every edit The discipline that pays: change one thing, re-read the plan. A rewrite plus a new index plus a statistics refresh applied together leaves you unable to say which one mattered, and you will keep the two that did nothing. Small steps also build the intuition of which edits move plans at all. ## Beyond the name Once you have confirmed the right index is used, the next thing to read on the same node is which of your predicates it used to position the scan and which are being applied afterwards to rows already fetched. That is what tells you whether the index is being used *well*, not merely used. ## What good sounds like in an interview "I read the access node and check the index name it reports; PostgreSQL prints it in the node line, MySQL puts it in the `key` column. Then I make sure I explained the exact statement with realistic parameters on realistic data, because a plan from a small dev table proves nothing. If it picks another index, I check whether my predicates really match the one I built."