PostgreSQL Indexing Strategies That Will Make Your Queries 10x Faster

Introduction

If your PostgreSQL queries are slow, the answer is almost always an index. But knowing which index to add — and where — is what separates senior engineers from juniors.

This guide covers the four indexing strategies I use most in production, with real EXPLAIN ANALYZE output so you understand exactly what's happening.

Step 1: Always diagnose first with EXPLAIN ANALYZE

Before adding any index, run this:

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'harsh@example.com';

Look for Seq Scan in the output — that means PostgreSQL is scanning every row. That's the problem. After adding an index, you want to see Index Scan instead.

Strategy 1: B-Tree index (the default)

Best for: equality checks, range queries, ORDER BY on a single column.

-- Before: Seq Scan on 1M rows
SELECT * FROM orders WHERE user_id = 42;

-- Add a B-Tree index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- After: Index Scan, 10x+ faster

Strategy 2: Composite index

Best for: queries that filter on multiple columns together. Column order matters — put the most selective column first.

-- Query filtering on two columns
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Composite index (user_id first — more selective)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

A composite index on (user_id, status) also covers queries that only filter on user_id — but not the other way around.

Strategy 3: Partial index

Best for: queries that always filter on a specific condition. Creates a smaller, faster index.

-- You always query for pending orders
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';

-- Only index pending orders
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

Instead of indexing all 10 million orders, you index only the 50K pending ones. Dramatically faster.

Strategy 4: GIN index for full-text search and JSONB

Best for: full-text search, array contains, JSONB queries.

-- Full-text search on a tsvector column
CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', title || ' ' || body));

-- JSONB containment
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- Now this query uses the index
SELECT * FROM users WHERE metadata @> '{"role": "admin"}';

Common mistakes to avoid

• Indexing every column — indexes slow down writes. Only index what you query.
• Wrapping indexed columns in functions — WHERE LOWER(email) = 'x' won't use the index on email. Use a functional index instead.
• Forgetting CONCURRENTLY on production — CREATE INDEX CONCURRENTLY builds without locking the table.

Conclusion

The right index can turn a 30-second query into 30 milliseconds. Always start with EXPLAIN ANALYZE, identify the sequential scans, and choose the right index type for your query pattern. Your database will handle millions of rows without breaking a sweat.