Skip to content
←Data & Databases

// Data & Databases · 01

Indexes

Full table scanleads toJump straight to rows

A sorted lookup structure so queries skip reading every row.

How it fits together

Indexes structureNo indexComposite index«client»Orders querycustomer_id, statusORDER BY created_atFull table scanreads 2M rowsComposite indexcustomer_idstatuscreated_atIndex seekFew rows returnedno index1 lookup2 seek3 fetch
fails / error pathuses / calls· numbered steps replay in order
  • Orders query fails / error path Full table scan (no index)
  • Orders query uses / calls Composite index (1 lookup)
  • Composite index uses / calls Index seek (2 seek)
  • Index seek uses / calls Few rows returned (3 fetch)

Spot it

  • A query gets slower as the table grows
  • The plan shows a Seq Scan on a big table
  • Simple lookups spike CPU and disk reads

It costs

Every index speeds some reads but slows every write and costs storage.

In code

CREATE INDEX ix_orders_customer_status_created    ON orders (customer_id, status, created_at);​-- Equality columns first, the sort column last: rows come back already orderedSELECT id, status, total, created_atFROM ordersWHERE customer_id = @customer_id  AND status = @statusORDER BY created_at DESC;
Go deeperThe full explanation and what to ask for · 1 min readExpand

An index is a separate sorted structure, usually a B-tree, that maps key values to rows. The planner uses it only when it expects to read a small fraction of the table; for unselective filters a scan is genuinely cheaper.

The classic mistake is indexing every column on its own. A query filtering on two columns wants one composite index, equality columns first and the range or sort column last. Unused indexes still cost on every INSERT, UPDATE and DELETE.

Read the plan, not your intuition: EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL, the actual execution plan in SQL Server. On large tables build indexes online (PostgreSQL CREATE INDEX CONCURRENTLY, or ONLINE = ON where the edition allows) so writes are not blocked.

Ask for it

  • B-tree index
  • composite index
  • covering index (INCLUDE)
  • selectivity
  • index seek vs scan
  • execution plan

This query on orders filters by customer_id and status and sorts by created_at. Read the execution plan, propose one composite index with the column order justified, and show the plan and timing before and after.

Connects to