When a Database Index Helps

How to decide whether an index will help a slow query 1. Start with the execution plan, not intuition. Run EXPLAIN (or EXPLAIN ANALYZE) on the slow query. The plan tells you what the engine actually does, and that decides whether an index can help. An index only helps if the plan is doing something an index can change. 2. What to inspect in the plan - Scan type. This is the single most important line. You want to see whether or not the engine is doing a full table scan (Seq Scan in Postgres, TABLE ACCESS FULL in Oracle, Clustered Index Scan / Table Scan in SQL Server, full scan in MySQL). A full scan over a big table is the classic sign an index might help. - Estimated vs actual rows. If the planner estimates 1,000 rows but the actual is 1,000,000, statistics are stale and the plan is bad regardless of indexes. Run ANALYZE first, then re-check. - Filter vs Access predicates. In the plan, look for which conditions are used to locate rows (access predicate, e.g. "Index Cond" in Postgres) versus which are applied after the row is fetched ("Filter"). An index only helps the access part. If your WHERE column is only in the Filter line, your index isn't being used. - Sort / Group operations. If you see a Sort node or a Hash Aggregate over many rows, an index on the ORDER BY / GROUP BY columns can eliminate the sort. - Nested loop vs hash join. A nested loop join that re-scans a big table for each outer row is a candidate for an index on the join column of the inner table. - Index usage. If the plan shows an index scan, check whether it's actually selective. An index that returns 80% of the table is worse than a seq scan; the planner should have chosen the seq scan, and if it didn't, that's a cost-model problem, not an index problem. 3. The decision rule An index helps when it lets the engine touch far fewer rows or skip an expensive operation (sort, join re-scan, covering the query). Concretely, an index helps when: - The WHERE clause filters on a column (or column prefix) with high selectivity — the condition matches a small fraction of rows. - The query needs rows in a specific order and the index provides that order for free. - The query joins on a column that is repeatedly probed. - The index can cover the query (all needed columns inside the index), avoiding table lookups entirely. It does not help when the condition matches most of the table, when the column is wrapped in a function (see mistakes), or when the table is tiny (a seq scan of 100 rows beats any index). 4. Small example Slow query: SELECT order_id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC; EXPLAIN shows: Seq Scan on orders, then Sort, filtering 2,000,000 rows down to ~30. Decision: customer_id is selective (30 of 2M rows) and the ORDER BY needs a sort. Create: CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC) INCLUDE (total); Now the plan becomes an Index Scan using idx_orders_customer_created: it seeks to customer_id = 42, reads rows in created_at order (no Sort node), and total is in the index (no table lookup). Verify with EXPLAIN ANALYZE that the Seq Scan and Sort nodes are gone and latency dropped. If they're still there, the index isn't being used — investigate why (stale stats, wrong column order, function-wrapped column). 5. Mistakes that make indexes hurt writes - Every index is a write tax. Each INSERT/UPDATE/DELETE must maintain every index on the table. More indexes = slower writes, more log traffic, more page splits, more bloat. A table with 5 indexes can see writes 2-5x slower than an unindexed table. Only add an index that demonstrably fixes a measured slow query. - Over-indexing. Adding indexes speculatively "just in case" is the most common mistake. Each one costs writes forever and may never be used. Add indexes only after a plan shows a real need. - Indexing low-cardinality columns. An index on a column with few distinct values (a status column with 3 values, a boolean) is nearly useless for reads but still costs every write. The planner will ignore it for most queries, so you pay write cost for nothing. - Wrong column order in a composite index. An index on (a, b) helps WHERE a = ? but NOT WHERE b = ?. Leading with the low-selectivity column makes the index useless for the queries you actually run, while still taxing writes. - Function-wrapped or cast columns. WHERE UPPER(name) = 'X' or WHERE date_col::date = ... cannot use an index on name or date_col. The engine must evaluate the function on every row. The index is dead weight on writes. Fix by indexing the expression (functional index) or rewriting the predicate to be sargable. - Redundant indexes. (a, b) and (a) overlap — the single-column one is fully redundant and only adds write cost. Drop it. - Ignoring index maintenance. Heavily updated indexes fragment and bloat; the planner may stop using them. Rebuild/reindex periodically, but that itself is a write-time cost to budget for. Bottom line: decide from the plan, not the query text. Add an index only when the plan shows a full scan or sort over many rows that a selective, correctly-ordered index can eliminate — then prove it with EXPLAIN ANALYZE before and after. Every index you add is a permanent write penalty, so the bar is "this index fixes a measured problem," not "this column appears in a WHERE clause."