Indexes and Query Performance

A sequential scan reads every row in a table to find matches. On a million-row table, that means a million comparisons for every query. An index is a separate data structure that lets PostgreSQL jump directly to the rows that match — similar to a book’s index pointing you to the right page instead of making you read every page. Understanding when and how to add indexes is one of the highest-leverage performance skills in SQL.

Why Indexes Matter

Without an index, this query forces PostgreSQL to scan every row in orders, no matter how many there are:

SELECT * FROM orders WHERE customer_id = 42;

With an index on customer_id, PostgreSQL looks up 42 in a sorted B-tree structure and follows a pointer directly to the matching rows. The difference between a sequential scan and an index scan can be the difference between milliseconds and minutes on large tables.

B-Tree Indexes (the default)

B-tree is the default index type and handles the vast majority of use cases. It supports equality (=), range comparisons (<, >, BETWEEN), sorting, and prefix matching with LIKE. When someone says “add an index,” they almost always mean a B-tree index.

-- Basic index on a single column — supports =, <, >, BETWEEN, ORDER BY
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Index with explicit sort direction — supports ORDER BY customer_id DESC efficiently
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

B-tree indexes work well on columns with high cardinality (many distinct values). They are less useful on low-cardinality columns like a boolean is_active flag — in that case, a partial index (covered below) is a better fit.

Hash Indexes

Hash indexes are optimized purely for equality comparisons (=). They are smaller and faster than B-trees for equality-only lookups, but they can’t support ranges or sorting. Use them for columns like session tokens or UUIDs that are only ever queried with =.

-- Hash index: perfect for token lookups where range queries never occur
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);

Use hash indexes when a column is only ever queried with = and never with <, >, BETWEEN, or ORDER BY.

GIN Indexes for Full-Text Search, Arrays, and JSONB

GIN (Generalized Inverted Index) indexes multiple values within a single column. They’re essential for full-text search, array containment, and JSONB queries — scenarios where a single column contains many searchable values and a B-tree can’t express the relationship.

-- Full-text search index — enables fast document search
CREATE INDEX idx_articles_fts ON articles USING GIN (to_tsvector('english', body));

-- Array containment index — speeds up @> and ANY() queries
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- JSONB index — supports @>, ?, ?|, ?& operators across the entire document
CREATE INDEX idx_events_data ON events USING GIN (payload);

With a GIN index on tags, a query like WHERE tags @> ARRAY['sale', 'featured'] runs in microseconds instead of doing a full table scan.

GiST Indexes for Geometric and Range Types

GiST (Generalized Search Tree) indexes are designed for data types that don’t fit the B-tree model: geometric shapes, IP ranges, date ranges, and full-text search vectors. If you’re working with tsrange, daterange, geometry, or network address types, GiST is the index to reach for.

-- Range type index — enables fast overlap queries on booking periods
CREATE INDEX idx_bookings_period ON bookings USING GIST (period);

-- Find all bookings that overlap a date range
SELECT * FROM bookings
WHERE period && '[2024-12-20, 2024-12-27)'::daterange;

Partial Indexes

A partial index only indexes rows that satisfy a WHERE clause. This produces a smaller, faster index and is especially valuable for low-cardinality columns where you always query against a specific value. A partial index on status = 'pending' is far more useful than a full index on status, because pending orders are the minority of rows and the ones queries care about most.

-- Only index unprocessed orders — the common query pattern
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Only index non-deleted users — most queries filter on this anyway
CREATE INDEX idx_users_email_active ON users(email)
WHERE deleted_at IS NULL;

Covering Indexes with INCLUDE

A covering index satisfies an entire query from the index without touching the table (the heap). This “Index Only Scan” is the best case — it avoids random I/O to the heap entirely. Add non-key columns with INCLUDE to make an index covering without increasing the size of the indexed key.

-- Query: SELECT status, total FROM orders WHERE customer_id = 42
-- This index covers all columns needed — no heap access required
CREATE INDEX idx_orders_customer_covering
ON orders(customer_id)
INCLUDE (status, total);

Multi-Column Index Column Order

For multi-column indexes, column order matters significantly. The index is most useful when queries filter on the leading column(s) first. A query that skips the first column cannot use the index efficiently.

-- Good for: WHERE region = 'US' AND status = 'active'
-- Also good for: WHERE region = 'US' alone (uses the leading column)
-- Not useful for: WHERE status = 'active' alone (skips the leading column)
CREATE INDEX idx_orders_region_status ON orders(region, status);

Put the most selective column first (the one that eliminates the most rows), and the column used in ORDER BY last.

Reading EXPLAIN ANALYZE Output

EXPLAIN ANALYZE runs the query and shows the actual execution plan with real timing. This is the definitive tool for confirming whether your index is being used and identifying where time is being spent.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Key things to look for in the output:

• Seq Scan — full table scan; may need an index
• Index Scan — using an index; good
• Index Only Scan — covering index in use; best case
• Bitmap Heap Scan — index used for many rows; efficient for bulk reads
• actual time=0.023..1.45 — startup and total time in milliseconds
• rows=150 vs rows=10000 (estimated) — large discrepancy means stale statistics; run ANALYZE

Index Bloat and REINDEX

As rows are updated and deleted, index pages can accumulate dead entries (bloat). Bloated indexes are larger than necessary and slower to scan. Rebuild a bloated index without locking the table using CONCURRENTLY.

-- Rebuild index without locking the table — safe for production
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

When to Add an Index

Add an index when:

• A column appears frequently in WHERE, JOIN ON, or ORDER BY clauses
• The column has high cardinality (many distinct values)
• The table has more than a few thousand rows
• EXPLAIN ANALYZE shows a sequential scan on a large table

Avoid adding indexes on:

• Tables with heavy write workloads and infrequent reads
• Columns that are rarely queried
• Very small tables where a sequential scan is faster than an index lookup