PostgreSQL Indexing Strategies That Will Make Your Queries 10x Faster

Introduction

If your PostgreSQL queries are slow, an appropriate index is often one of the most effective optimizations. The challenge isn't simply adding indexes—it's knowing which type of index best matches your query patterns.

This guide covers four indexing strategies commonly used in production, along with how to identify when they're needed using EXPLAIN ANALYZE.

Step 1: Diagnose with EXPLAIN ANALYZE

Before creating an index, inspect the query plan:

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

Look for:

• Seq Scan — PostgreSQL scans every row in the table.
• Index Scan — PostgreSQL uses an index to locate matching rows efficiently.

If you expected an index but see a sequential scan instead, investigate why before creating additional indexes.

Strategy 1: B-Tree Index (Default)

Best for:

• Equality lookups
• Range queries
• ORDER BY
• Primary lookup columns

-- Before: Sequential scan
SELECT *
FROM orders
WHERE user_id = 42;

-- Create an index
CREATE INDEX idx_orders_user_id
ON orders(user_id);

-- After: PostgreSQL can perform an Index Scan

B-tree indexes are the default index type and the best choice for most application queries.

Strategy 2: Composite Index

Best for:

Queries that filter using multiple columns together.

-- Query
SELECT *
FROM orders
WHERE user_id = 42
  AND status = 'pending';

-- Composite index
CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

Important

Column order matters.

A composite index on:

(user_id, status)

supports:

• ✅ WHERE user_id = ...
• ✅ WHERE user_id = ... AND status = ...

but not queries filtering only by:

status

Choose the column order based on your application's most common query patterns.

Strategy 3: Partial Index

Best for:

Queries that repeatedly filter using the same condition.

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

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

Instead of indexing every row, PostgreSQL indexes only rows matching the WHERE clause.

Benefits include:

• Smaller indexes
• Faster lookups
• Lower storage requirements
• Reduced maintenance overhead

Strategy 4: GIN Index

Best for:

• Full-text search
• JSONB
• Arrays

Full-text Search

CREATE INDEX idx_posts_search
ON posts
USING GIN (
  to_tsvector(
    'english',
    title || ' ' || body
  )
);

JSONB

CREATE INDEX idx_users_metadata
ON users
USING GIN(metadata);

SELECT *
FROM users
WHERE metadata @> '{"role":"admin"}';

GIN indexes significantly improve containment queries and full-text search operations.

Common Mistakes

Indexing Every Column

Indexes improve reads but make inserts, updates, and deletes more expensive.

Only index columns that are frequently queried.

Wrapping Indexed Columns in Functions

For example:

WHERE LOWER(email) = 'user@example.com'

A normal index on email cannot be used here.

Instead, create a functional index:

CREATE INDEX idx_users_lower_email
ON users (LOWER(email));

Forgetting CONCURRENTLY in Production

Creating an index normally locks writes to the table.

For production systems, consider:

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);

This allows the index to be built with minimal disruption, although it typically takes longer to complete.

Conclusion

The right index can reduce query execution times dramatically.

A good workflow is:

1. Run EXPLAIN ANALYZE.
2. Identify sequential scans or inefficient plans.
3. Choose the appropriate index type.
4. Re-run EXPLAIN ANALYZE to confirm the optimizer is using the new index.

Thoughtful indexing allows PostgreSQL to scale efficiently while avoiding unnecessary storage and write overhead.