Database
Intermediate 14 min read Database

Indexing & Query Optimization

Turn slow scans into fast lookups with indexes. Learn how btree indexes work, how to design composite and covering indexes, and how to read EXPLAIN plans to prove a fix worked.

indexbtreeEXPLAINquery plancomposite indexcovering indexoptimization
Prerequisites: SQL Fundamentals Database Design & Normalization
Introduction

What is this?

An index is an auxiliary structure the database maintains to find rows without scanning the whole table. The default btree index keeps keys sorted with pointers to rows, turning an O(n) scan into an O(log n) lookup. Because every index costs writes and storage, the planner weighs index scans, sequential scans and bitmap scans using cost estimates built from table statistics.

Motivation

Why do you need it?

  • Indexes are the highest-leverage read-latency fix: one CREATE INDEX can cut a query from seconds to milliseconds.
  • They serve both point lookups and range queries because btree keys are sorted.
  • UNIQUE and PRIMARY KEY constraints are implemented as indexes, so they also enforce integrity.
  • A covering index can answer a query entirely from the index pages, skipping the table entirely.
  • Understanding indexes lets you read EXPLAIN plans and influence what the cost-based planner decides.
Think about it

Mental model

Imagine

The index at the back of a book

Think of

A database index

You jump straight to the right page instead of flipping through the whole book. The book works without the index — searching is just slower. Every reprint (write) must keep the index accurate too.

Mechanics

How it works

  • CREATE INDEX builds a btree of key → row pointer (TID) pairs, sorted by key value.
  • A query filtering on the indexed column descends the tree with O(log n) comparisons.
  • The planner estimates selectivity from statistics (pg_class.reltuples, pg_stats) to choose index vs seq scan.
  • For range or multi-row matches, the engine reads a contiguous slice of leaf pages and fetches the matching heap rows.
  • Every INSERT/UPDATE/DELETE must maintain the index, trading write cost for read speed.
  • ANALYZE refreshes planner statistics so cost estimates stay accurate as data changes.

How a btree index turns a full scan into a point lookup.

SELECT ... WHERE email = 'ada@x.io'
Index Scan on idx_users_email
Descend internal nodes
Leaf node match
Heap fetch
Result row returned
Reference

Key commands

CREATE INDEX idx_users_email ON users (email);

Create a btree index for equality lookups on email.

EXPLAIN SELECT ...

Show the query plan without executing it.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

Execute the query and report actual timings, buffers and rows.

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

Create a composite index matching a filter-plus-sort pattern.

CREATE INDEX idx_orders_active ON orders (user_id) WHERE status = 'active';

Create a partial index covering only hot rows.

\di

List all indexes in the current database.

Hands-on

Examples

Before and after an index, with EXPLAIN

Before and after an index, with EXPLAIN
-- Slow: the planner must read every row of the table
EXPLAIN SELECT * FROM users WHERE email = 'ada@example.com';
-- Seq Scan on users  (cost=0.00..1234.56 rows=1 width=448)
 
CREATE INDEX idx_users_email ON users (email);
 
-- Fast: an index scan finds the key in O(log n)
EXPLAIN SELECT * FROM users WHERE email = 'ada@example.com';
-- Index Scan using idx_users_email on users  (cost=0.28..8.29 rows=1)
--   Index Cond: (email = 'ada@example.com'::text)

Before the index, the planner commits to a Seq Scan across the whole table. After CREATE INDEX, it switches to an Index Scan that descends the btree to the matching key and fetches only that row. The cost figures in parentheses are estimates; ANALYZE keeps them honest.

Real world

In production

  • Foreign-key columns are indexed so JOIN lookups are cheap instead of nested-loop scans.
  • Composite indexes on (user_id, created_at) power 'recent activity' feeds in O(log n).
  • Partial indexes on recent or active rows keep the index small for hot subsets.
  • Covering indexes serve reporting queries straight from the index, removing heap access.
Guidelines

Best practices

  • Index the columns in WHERE, JOIN and ORDER BY clauses — but only after measuring that the query is slow.
  • Order composite index columns equality-first, then range or sort columns.
  • Prove the win with EXPLAIN (ANALYZE, BUFFERS) before and after adding an index.
  • Keep write-heavy tables lean — every extra index slows INSERT and grows bloat.
  • Prune unused indexes by querying pg_stat_user_indexes for idx_scan counts.
Watch out

Common mistakes

  • Indexing low-cardinality columns where a sequential scan is already fast, wasting write work.
  • Wrapping an indexed column in a function (WHERE lower(email) = ...) without a matching functional index.
  • Creating several single-column indexes when one composite index would serve several queries.
  • Adding indexes to patch a query that a schema fix or a missing WHERE clause would solve better.
Root cause analysis

Common issues

Error
Seq Scan on users (cost=0.00..1234.56 rows=1) despite a selective filter
Root cause

No index exists on the filtered column, or statistics are stale.

Fix

CREATE INDEX on the column, run ANALYZE, and re-check with EXPLAIN.

Error
Planner picks a bad plan after a bulk load
Root cause

pg_statistic is stale and the estimates no longer match reality.

Fix

Run ANALYZE (autovacuum does this eventually) and check fillfactor for bloat.

Error
Plan shows Bitmap Heap Scan instead of Index Scan
Root cause

The planner estimates many matches and prefers bitmap mode.

Fix

This is usually correct behavior; investigate only if the plan is measurably slow.

When things break

Debugging tips

  • Run EXPLAIN (ANALYZE, BUFFERS) and compare estimated vs actual rows to spot stale statistics.
  • Run ANALYZE after bulk loads so the planner sees the current distribution.
  • Check pg_stat_user_indexes.idx_scan for indexes that are never used.
  • A Seq Scan on a huge table with a restrictive filter is the classic missing-index smell.
Interviews

Interview checkpoint

Practice

Hands-on lab

Making a Slow Query Fast
30 min · intermediate
In progress
Objectives
  • Generate a large dataset
  • Measure a slow query with EXPLAIN (ANALYZE, BUFFERS)
  • Design and create the right composite index
  • Prove the improvement with before/after plans

Making a Slow Query Fast

30 min · intermediate

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

Quick quiz

0/3
1

Which query shape benefits most from a btree index?

2

Every index speeds up all queries at zero ongoing cost.

3

After adding an index, INSERT throughput on a hot table drops noticeably. What is the most likely reason?

Quick reference

Cheat sheet

Index commands
CREATE INDEXBuild a btree index
CREATE UNIQUE INDEXEnforce uniqueness
CREATE INDEX ... WHEREPartial index on a subset
DROP INDEXRemove an index
ANALYZERefresh planner statistics
Reading a plan
Seq ScanReads the whole table
Index ScanPoint lookup via btree
Bitmap Heap ScanMany matches via bitmap
Nested LoopPer-outer-row inner lookup
Hash JoinHash one side, probe the other
Merge JoinSorted inputs merged
Go deeper

Official documentation

PostgreSQL Indexes
docs
CREATE INDEX reference
docs
EXPLAIN reference
docs
Use The Index Luke
docs