9.6 How do you know whether a query uses an index?

In a book: You can't always tell from the outside how someone found a page. So you ask them: "did you use the index, or flip through?"

In the system: Ask the database with EXPLAIN. It shows the query plan: the steps the planner chose.

EXPLAIN QUERY PLAN SELECT id, total FROM orders WHERE user_id = 7; -- SQLite before the index: SCAN orders after the index: SEARCH orders USING INDEX idx_orders_user (user_id=?)

What to look for, in any database:

• Scan of a big table where you expected a search/seek: the index isn't being used.
• Index-only / covering scans: good.
• Sort steps on big results: a composite index might avoid them.
• Estimated vs actual rows (PostgreSQL's EXPLAIN ANALYZE actually runs the query and shows both): a big gap means the planner's statistics are stale or misleading, and it may be choosing badly.

Why the planner might skip your index: the table is small; the condition matches a large share of rows; statistics are out of date; or the query is written so the index can't be used (a function on the column, a leading %, a type mismatch; see 9.8 and the Level 4 bug).

Make it a habit: EXPLAIN every new query that runs often, and look at the slow-query log regularly.

Memory code: "Don't guess, EXPLAIN."

---