SQL Mastery
Advanced 16 min read SQL Mastery

Indexes and Query Performance

Trade write cost for read speed, deliberately

Indexes turn table scans into point lookups — B-trees, composite, partial and expression indexes — each with a write-cost and a size budget.

indexB-treecomposite indexpartial indexwrite amplification
Prerequisites: Explain and Query Plans
Before we start

What you will learn

  • Explain how a B-tree index accelerates lookups and ranges
  • Design composite indexes to match filter + sort predicates
  • Choose partial and expression indexes for hot paths
Introduction

What is this?

An index is a sorted structure (usually B-tree) mapping key values to row locations, letting the engine answer equality and range predicates without scanning the table. Each index taxes writes (maintenance) and disk; good indexing is a deliberate trade of storage/write cost for read latency.

Motivation

Why do you need it?

  • A Seq Scan over 40M rows pays for a report someone clicks daily — one good index removes it.
  • Composite and partial indexes shrink that cost further when predicates are predictable.
  • Unused or duplicate indexes are pure write-tax — a defensible index set is a measured one.
Think about it

Mental model

Imagine

An encyclopedia's index volume

Think of

A database index

The index lists topics and page numbers so you don't read every page to find 'volcano'. It costs extra shelves (storage) and time to write (maintain), but lookups become instant.

Mechanics

How it works

  • B-tree balance keeps inserts/lookups at O(log n) regardless of table order.
  • Equality on the leading column uses the index directly; range scans walk sorted leaves.
  • Composite (a, b) serves predicates on a, and a+b together — but leading-column absence degrades it.
  • Partial indexes index a WHERE subset; expression indexes index func(column) for computed filters.
  • The planner chooses indexes via cost; ANALYZE statistics keep cost estimates correct.

Indexing decision flow.

Hot slow query
Equality + range filters
Narrow row subset
Computed predicate
Re-measure
Reference

Key commands

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

Build an index without blocking writes.

ANALYZE orders;

Refresh planner statistics after bulk loads.

pg_stats... / SHOW INDEX FROM orders;

Inventory what already exists.

Hands-on

Examples

From Seq Scan to Index Scan

From Seq Scan to Index Scan
-- before: Seq Scan on orders (cost goes up with rows)
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending' AND user_id = 42;
 
CREATE INDEX idx_orders_status_user ON orders (status, user_id);
-- after: Index Scan using idx_orders_status_user

A composite index on the exact predicate pair lets the planner use the index directly.

Real world

In production

  • A customer-360 report's slow query gets a composite (customer_id, created_at) index, cutting p95 from 4s to 40ms.
  • A partial index on active-only rows serves a hot 'pending orders' view that is 1% of the table.
  • A unique index backs the natural-key dedup so duplicated loads fail at insert, not at report time.
Guidelines

Best practices

  • Index for the queries, not the columns: match the WHERE + ORDER BY shapes.
  • Build with CONCURRENTLY (Postgres) in maintenance windows to avoid blocking writes.
  • Refresh ANALYZE after bulk loads so the planner sees fresh cardinality.
  • Audit yearly with pg_stat_user_indexes to drop unused and duplicated indexes.
Watch out

Common mistakes

  • Indexing every column 'just in case', then paying write-tax and disk for nothing.
  • Leading-column mismatch: index (a, b) used with only b in the predicate is useless.
  • Forgetting expression indexes when filters are on lower(email) or extract(month).
  • Building on 100M rows without CONCURRENTLY, locking the table through the build.
Root cause analysis

Common issues

Error
Index created but EXPLAIN still Seq Scans
Root cause

Cardinality stats stale, or the predicate shape differs (func(col), leading column missing)

Fix

ANALYZE the table, then reassess the predicate/column order.

Error
Write-heavy table slowed after indexing
Root cause

Too many maintenance-heavy indexes on an insert-heavy store

Fix

Measure write duration before/after and drop rarely-used indexes.

When things break

Debugging tips

  • When the planner still scans despite an index, check ANALYZE freshness and predicate forms.
  • Inspect pg_stat_user_indexes for idx_scan counts — zero scans = dead weight.
  • Look at FROM, WHERE and ORDER BY to derive the minimal composite columns in the right order.
Interviews

Interview checkpoint

Practice

Hands-on lab

Create a Composite Index and Prove It with EXPLAIN
30 min · advanced
In progress
Objectives
  • Generate a large seeded table
  • Observe a Seq Scan in EXPLAIN QUERY PLAN
  • Create a composite index and confirm an Index Scan

Create a Composite Index and Prove It with EXPLAIN

30 min · advanced

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

An index (a, b) most directly accelerates which predicate set?

2

Every index added to a write-heavy table costs write performance.

Go deeper

Official documentation

PostgreSQL indexes
docs
SQLite indexing
docs