PostgreSQL Indexing Deep Dive: Query Optimization for Production Databases

Why Indexes Are the Most Impactful Database Optimization#

A poorly indexed database can be 100x-1000x slower than a well-indexed one. Adding the right index often transforms a query that takes 10 seconds into one that takes 10 milliseconds — without changing any application code. Understanding PostgreSQL's indexing system is one of the highest-ROI skills a backend engineer can develop.

This guide covers the index types available in PostgreSQL, when to use each, how to analyze query performance, and the common indexing mistakes that cause production performance problems.

How Indexes Work: B-Tree Internals#

PostgreSQL's default index type is B-Tree (Balanced Tree). A B-Tree index maintains a sorted tree structure of the indexed column's values alongside pointers to the actual table rows (heap pointers).

When PostgreSQL executes a query with a WHERE clause on an indexed column, it navigates the B-Tree to find matching values in O(log n) time, then fetches the specific rows using heap pointers. Without an index, PostgreSQL performs a Sequential Scan — reading every row in the table, which is O(n).

• Sequential scan: 10,000,000 row reads
• B-Tree index scan: ~23 reads (log₂ of 10,000,000)

Index Types and When to Use Each#

B-Tree (Default) Supports equality (`=`), range (`<`, `>`, `BETWEEN`), ordering (`ORDER BY`), and `IS NULL` queries. This covers the vast majority of production query patterns.

Create: CREATE INDEX idx_users_email ON users(email);

Hash Indexes Optimized for equality comparisons only — faster than B-Tree for pure equality checks on large values, but does not support range queries or ordering. Used when a column is only queried with `=`.

GIN (Generalized Inverted Index) Designed for multi-valued types: JSONB, arrays, full-text search (`tsvector`). GIN indexes each element of a composite value, enabling efficient queries like `jsonb_column @> '{"key": "value"}'` or `array_column && '{"tag1", "tag2"}'`.

GiST (Generalized Search Tree) Extensible index for complex data types: geometric shapes, PostGIS geography, and range types (`daterange`, `tstzrange`). Used for nearest-neighbor searches and overlap queries.

BRIN (Block Range Index) A very small index that stores min/max values for each block range of the table. Extremely compact but only effective for columns where values are physically ordered in the table (insert-time sequential data like timestamps or auto-increment IDs). Ideal for time-series and logging tables.

Composite Indexes and Column Order#

Composite indexes span multiple columns and can satisfy queries on the leading columns without additional indexes:

CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at);

• WHERE user_id = 5 ✅
• WHERE user_id = 5 AND status = 'pending' ✅
• WHERE user_id = 5 AND status = 'pending' ORDER BY created_at ✅
• WHERE status = 'pending' ❌ (cannot use leading index columns are skipped)

The most selective column (the one that filters out the most rows) should be first — unless a specific query pattern requires a different ordering.

Partial Indexes: Index Only What You Query#

A partial index covers only a subset of table rows, making it smaller and faster:

This is ideal when queries almost always filter on status = 'pending' — the index only contains pending orders, making it much smaller and faster than a full index on created_at.

Expression Indexes#

Expression indexes index the result of a function or expression, allowing queries that use the same expression to use the index:

Now WHERE lower(email) = 'user@example.com' uses the index. Without this, PostgreSQL would perform a sequential scan because the index on email doesn't help when lower() is applied.

Reading EXPLAIN ANALYZE Output#

EXPLAIN ANALYZE is the primary tool for understanding query execution:

• Seq Scan vs Index Scan: Sequential Scan indicates no index was used (or it was cheaper not to use one)
• rows= estimated vs actual: Large discrepancies indicate stale statistics — run ANALYZE orders to update
• Execution Time: Total wall-clock time
• Buffers hit/read: Cache hits (fast) vs disk reads (slow)

• Sequential scan on a large table with a restrictive WHERE clause → Add an index
• Very high row estimate vs low actual → Run ANALYZE or increase statistics target
• Nested Loop with large inner relation → May benefit from a Hash Join or additional index

Index Maintenance Considerations#

Indexes have costs that must be weighed against their query benefits:

• Every INSERT, UPDATE, and DELETE must also update all indexes on the table — adding indexes increases write latency
• Indexes consume disk space — a B-Tree index is typically 30-50% of the indexed data size
• Unused indexes should be removed: pg_stat_user_indexes shows idx_scan counts
• VACUUM and AUTOVACUUM clean up dead index entries from deleted rows — monitor autovacuum health on large tables