SQL query performance

The idea

When you send SQL to a database, you describe what you want, not how to get it. A component called the query planner decides the how: it picks an access path for reaching your rows.

With no help, the planner reads every row and checks each one: a sequential scan, whose cost grows with the whole table. If a useful index exists, it can instead seek straight to the matching rows. Identical SQL, wildly different speed: the difference is the path the planner chose.

See it work

How it works

Every database has a planner (or optimizer). It estimates a numeric cost for each candidate plan, roughly how many rows it expects to touch, and runs the cheapest one. You can see its decision with EXPLAIN.

Cost is driven by selectivity: how small a fraction of the table the predicate keeps. customer_id = 42 might match a handful of rows out of millions. A sequential scan still reads all N rows to find them; an index scan walks a small B-tree (O(log n)) and fetches only the matches. The planner compares the estimates and picks the cheaper path.

-- Before: nothing to seek with, so it reads every row
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
  Seq Scan on orders  (cost=0.00..1850.00 rows=3 width=24)
    Filter: (customer_id = 42)
    -- rows removed by filter: ~99,997

-- Add a B-tree index on the filtered column
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- After: it seeks straight to the matches
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
  Index Scan using idx_orders_customer on orders
        (cost=0.42..12.10 rows=3 width=24)
    Index Cond: (customer_id = 42)

The estimated cost dropped from ~1850 to ~12: the planner now expects to touch a tiny fraction of the table.

Cost / trade-offs

An index is not free: it speeds reads but taxes writes and uses storage. Index the columns you actually filter and join on, not every column.

Watch out for

• Stale statistics. The planner estimates costs from sampled stats. After a big load, run ANALYZE: outdated stats lead to a bad plan, e.g. a seq scan where a seek would win.
• Non-sargable predicates. Wrapping the column in a function or arithmetic, WHERE LOWER(email) = 'a@b.com' or WHERE customer_id + 0 = 42, hides it from the index and forces a scan. Keep the column bare on one side, or build a matching expression index.
• Implicit type casts. Comparing an indexed bigint to a string (WHERE customer_id = '42') can make the engine cast the column instead of the literal, disabling the index. Match the literal's type to the column.
• SELECT * defeating a covering index. An index that holds only the filter column still needs a heap lookup per match to fetch other columns. Selecting just the columns you need can let an index cover the query and skip the heap.
• N+1 queries. A fast single query run once per row in a loop is slow in aggregate. Fetch in one set-based query (a join or IN (...)) instead of many round trips.

Worked example

A nightly report runs SELECT * FROM orders WHERE customer_id = 42 over a 100,000-row table and takes ~420 ms. You check why:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
  Seq Scan on orders  (cost=0.00..1850.00 rows=3 width=24)
                      (actual time=0.31..418.7 rows=3 loops=1)
    Filter: (customer_id = 42)
    Rows Removed by Filter: 99997
  Execution Time: 419.4 ms

The plan is a Seq Scan that reads 100,000 rows to return 3. The predicate is highly selective, so an index should pay off. You add one and refresh stats:

CREATE INDEX idx_orders_customer ON orders (customer_id);
ANALYZE orders;

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
  Index Scan using idx_orders_customer on orders
        (cost=0.42..12.10 rows=3 width=24)
        (actual time=0.04..0.07 rows=3 loops=1)
    Index Cond: (customer_id = 42)
  Execution Time: 0.12 ms

The plan flipped from Seq Scan to Index Scan, rows examined fell from 100,000 to ~3, and latency dropped from ~420 ms to under 1 ms: a roughly 3,000× speedup from one well-chosen index.

Check yourself