How an index helps
A B-tree index keeps values sorted so the engine can seek instead of scanning every row. It helps equality lookups, range scans, and ORDER BY on the indexed column.
A composite index on (a, b) can serve predicates on a, or a and b together — but generally not b alone. Left-to-right prefix order is the rule, and getting the column order wrong is the most common indexing mistake.
A covering index includes every column a query needs, so the engine never touches the table. Indexes are not free: every write must maintain them.
What kills index use
Wrapping the column in a function: WHERE YEAR(order_date) = 2024 cannot use an index on order_date. Rewrite as a range: >= '2024-01-01' AND < '2025-01-01'. Or build a functional/expression index if the engine supports one.
A leading wildcard: LIKE '%abc' cannot seek. LIKE 'abc%' can.
Implicit type casts, e.g. comparing a varchar column to a number.
Low selectivity: on a boolean where half the rows match, a scan is genuinely cheaper and the planner is right to choose it.
Reading a plan
EXPLAIN shows the plan; EXPLAIN ANALYZE (Postgres) or SET STATISTICS (SQL Server) runs it and reports actuals.
What to look for:
- Seq Scan / Table Scan on a big table with a selective filter — usually a missing or unusable index
- Nested Loop with a large outer row count — often better as a hash join
- Estimated vs actual rows wildly apart — stale statistics; run ANALYZE
- Sort spilling to disk — needs more work memory or an index providing the order
Read the deepest, most indented node first: plans execute inside-out.
Before you add an index
Ask whether the query is the problem. Selecting columns you do not need, an accidental fan-out, or a function on a filtered column will all outweigh any index.
And check whether a suitable index already exists — over-indexing slows writes and confuses the planner. In a warehouse, partitioning and clustering often matter more than traditional indexes.