Indexes and EXPLAIN ANALYZE

Introduction

Indexes speed up lookups and joins—like a book index instead of reading every page. This chapter creates B-tree, partial, and expression indexes, explains the leftmost prefix rule for composites, and reads EXPLAIN / EXPLAIN ANALYZE plans—Seq Scan vs Index Scan, Bitmap scans, and Index Only Scan. Compare with MySQL indexes and EXPLAIN.

Prerequisites

Constraints and Keys
SELECT Queries Basics
Enough rows to see plan differences (optional—concepts apply on small tables too)

Why Indexes Matter

Without an index on posts.user_id, finding posts for one user scans the whole table (sequential scan). With an index, PostgreSQL jumps to matching rows.

PostgreSQL stores rows in heap order—indexes are separate structures pointing to row locations (TIDs). PRIMARY KEY creates a unique B-tree index automatically.

B-Tree (Default)

text

         [50]
        /    \
    [25]      [75]
    /  \      /  \
  ...  ...  ...  ...

Code explanation:

Balanced tree—roughly O(log n) comparisons to find keys
Default for CREATE INDEX—also used for UNIQUE and PRIMARY KEY

Other index types (GIN for JSONB, GiST for geo) appear in JSONB and Advanced Types.

CREATE and DROP Index

On an existing table:

sql

CREATE INDEX idx_posts_user_id ON posts (user_id);
 
CREATE INDEX idx_posts_published_at ON posts (published_at);

Unique index (also enforces uniqueness):

sql

CREATE UNIQUE INDEX uk_users_email ON users (email);

Drop:

sql

DROP INDEX IF EXISTS idx_posts_published_at;

Concurrent build (production—avoids long write locks):

sql

CREATE INDEX CONCURRENTLY idx_posts_user_id ON posts (user_id);

CONCURRENTLY cannot run inside a transaction block—plan maintenance windows accordingly.

Composite Index and Leftmost Prefix

sql

CREATE INDEX idx_posts_user_published ON posts (user_id, published_at);

Efficient for:

sql

WHERE user_id = 1;
WHERE user_id = 1 AND published_at >= '2026-01-01';

Less efficient for:

sql

WHERE published_at >= '2026-01-01';  -- skips leading user_id

Code explanation:

Composite (A, B) supports filters on A or A + B, not B alone
Order columns by query patterns and selectivity

Partial Index (PostgreSQL)

Index only a subset of rows—smaller and faster when queries always filter the same way:

sql

CREATE INDEX idx_posts_published_only
ON posts (user_id, published_at)
WHERE published_at IS NOT NULL;

Code explanation:

Matches queries like WHERE published_at IS NOT NULL AND user_id = ?
MySQL has no direct equivalent—PostgreSQL strength for soft-delete and status flags

Expression Index

Index on an expression—common for case-insensitive email:

sql

CREATE UNIQUE INDEX uk_users_email_lower ON users (lower(email));

Query must use the same expression:

sql

SELECT id FROM users WHERE lower(email) = lower('Ada@Example.com');

Without lower() on both sides, the planner may not use this index.

Covering Index (Index-Only Scan)

When the index contains all columns the query needs, PostgreSQL may skip the heap:

sql

CREATE INDEX idx_posts_user_title ON posts (user_id, title);
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, title FROM posts WHERE user_id = 1;

Look for Index Only Scan in output—requires visibility map freshness (VACUUM helps)—Performance, VACUUM, and Monitoring.

EXPLAIN Basics

Show plan without executing:

sql

EXPLAIN
SELECT id, title
FROM posts
WHERE user_id = 1
ORDER BY published_at DESC
LIMIT 10;

Important nodes:

Node	Meaning
Seq Scan	Read whole table—OK for tiny tables, bad at scale
Index Scan	Walk B-tree, fetch matching heap rows
Bitmap Index Scan	Collect row locations, then batch heap fetch
Index Only Scan	Read index without heap when possible

Other fields:

Field	Meaning
cost	Planner estimate (arbitrary units)
rows	Estimated row count
width	Average row width in bytes
Filter	Rows removed after scan

EXPLAIN ANALYZE

Execute the query and show actual timings:

sql

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, title
FROM posts
WHERE user_id = 1;

Code explanation:

ANALYZE runs the query—use on dev, not blindly on destructive statements
BUFFERS shows cache hits—helps spot I/O-heavy plans
Compare estimated rows vs actual rows—large gaps suggest ANALYZE table stats are stale

Update statistics:

sql

ANALYZE posts;

Before and After Index Example

Before index on email lookup:

sql

EXPLAIN SELECT id FROM users WHERE email = 'ada@example.com';

If you see Seq Scan on a large users table, add:

sql

CREATE UNIQUE INDEX uk_users_email ON users (email);

Re-run EXPLAIN ANALYZE—expect Index Scan or Index Only Scan and lower actual time.

Index Anti-Patterns

Mistake	Why
Index every column	Slows writes, wastes disk
Wrong column order in composite	Leading column must match WHERE
Functions on indexed column without expression index	`WHERE lower(email) = ...` won't use plain `email` index
Duplicate indexes	`UNIQUE (email)` and `INDEX (email)`—pick one
Partial index without matching WHERE	Planner won't use it

When Not to Index

Tiny tables where Seq Scan is cheaper than index lookup
Columns rarely filtered or joined
Write-heavy tables where index maintenance dominates—measure first

FAQ

PRIMARY KEY vs UNIQUE index?

PRIMARY KEY adds NOT NULL and is the main row identity. Both create B-tree indexes in PostgreSQL.

Why Bitmap Index Scan?

PostgreSQL combines multiple index conditions or moderate selectivity by bitmap—common in multi-column filters.

EXPLAIN vs EXPLAIN ANALYZE?

EXPLAIN estimates only. EXPLAIN ANALYZE executes and shows real row counts and milliseconds.

MySQL EXPLAIN differences?

MySQL uses type, key, Extra columns; PostgreSQL uses a tree of nodes. Both answer: "Is it scanning the whole table?"

REINDEX when?

After corruption, major version upgrades, or bloat—REINDEX INDEX CONCURRENTLY idx_name; in production with care.

JSONB indexing?

Use GIN indexes—JSONB chapter.