When a Postgres query is slow, the answer is almost always one of two things: you’re missing an index, or you have an index but the planner isn’t using it. Both problems are diagnosable with EXPLAIN ANALYZE and fixable in a single line of SQL — once you know what to look for.

This post is the “level up your Postgres skills” guide I wish someone had handed me earlier. We’ll cover what an index actually does, how to read query plans, when each index type pays off, and the patterns I reach for in production.

Quick refresher: what an index is

An index is a separate data structure that lets Postgres find rows without scanning the whole table. The default is a B-tree — a sorted, balanced tree that supports =, <, >, BETWEEN, sorting, and prefix matching on text.

CREATE INDEX idx_users_email ON users(email);

Now WHERE email = ? is O(log n) instead of O(n). The cost: indexes take disk space, slow down writes (the index has to be updated too), and the planner has to choose between them.

The implication: index things you query, not everything.

EXPLAIN and EXPLAIN ANALYZE

EXPLAIN <query> shows what Postgres plans to do. EXPLAIN ANALYZE <query> actually runs the query and shows real timings.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

Sample output:

Index Scan using idx_orders_user_id on orders  (cost=0.43..8.45 rows=1 width=120) (actual time=0.052..0.054 rows=1 loops=1)
  Index Cond: (user_id = 42)
Planning Time: 0.187 ms
Execution Time: 0.082 ms

The keys to read this:

  • Index Scan — Postgres used an index. Good.
  • Seq Scan — Postgres read every row in the table. On a big table, this is your problem.
  • cost=A..B — estimated startup cost..estimated total cost. Arbitrary units; lower is better.
  • actual time=A..B — real time in milliseconds (only with ANALYZE).
  • rows=N — estimated row count. If this is wildly off from the actual row count, run ANALYZE <table> to update statistics.
  • loops=N — how many times this node ran. For nested loop joins, this can multiply.

The decisions: when to add an index

Add a B-tree index when you have a selective query — one that filters down to a small fraction of rows. Index scans only beat sequential scans when they reduce the work significantly.

A few good signs:

  • The column is in a WHERE clause that’s run often.
  • The column has many distinct values (high cardinality).
  • The query returns < 10% of the table.

Bad signs (don’t bother indexing):

  • Boolean columns with two values (active / inactive) where one value covers most rows. The whole-table scan is cheaper than reading the index and then fetching most rows from the table.
  • Tiny tables (a few hundred rows). Postgres just reads the whole thing.
  • Columns you almost never query.

Composite indexes — left-prefix rule

If you often query on multiple columns together:

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

This index helps:

  • WHERE user_id = ? AND status = ?
  • WHERE user_id = ?

It does not help:

  • WHERE status = ? (alone)

The leftmost column has to be in your WHERE clause for the index to be useful. So order matters: put the column you filter on most often first, and the most selective column second.

Partial indexes — index a slice

When you almost always query with a fixed filter, build a smaller index that only covers those rows:

CREATE INDEX idx_orders_active ON orders(user_id) WHERE status = 'active';

This index is smaller, faster to scan, and faster to maintain than a full one. Great for “active” / “pending” / “open” filters.

Covering indexes — INCLUDE

Sometimes you can answer the whole query from the index alone, never touching the table. Tell the index to carry extra columns:

CREATE INDEX idx_orders_user_id_inc ON orders(user_id) INCLUDE (total, created_at);

Now SELECT total, created_at FROM orders WHERE user_id = ? can be answered as an Index Only Scan — the fastest plan. Don’t include columns you don’t need; bigger indexes are slower.

Index types beyond B-tree

GIN — for arrays, JSONB, full-text search

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- Now this is fast:
SELECT * FROM posts WHERE tags @> ARRAY['python'];

GIN (“Generalized Inverted Index”) is what makes JSONB and full-text search performant. We’ll go deeper on full-text in the next post .

GiST — for geometric and range types

Used heavily by PostGIS for spatial data. Less common in everyday CRUD apps.

BRIN — for huge, naturally-ordered tables

For tables where rows are roughly sorted by some column (think: a created_at column on an append-only log table), a Block Range INdex stores per-block min/max values:

CREATE INDEX idx_events_created_at_brin ON events USING BRIN (created_at);

BRIN indexes are tiny (often 1000× smaller than the equivalent B-tree) and fast for range scans on huge sorted tables. Don’t reach for them unless your table is at least millions of rows.

Hash — for equality only (mostly skip)

Postgres has hash indexes, but B-tree handles equality just fine and supports more operations. You’ll rarely need a hash index.

Sorting and indexes

If you ORDER BY a column, an index can serve the sort for free:

CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

EXPLAIN ANALYZE
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;

You’ll see an Index Scan (no Sort node). The 20 newest rows come right out of the index in order.

If you sort on a combination, your composite index needs to match:

CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);

-- Both filter AND sort served by the index:
SELECT * FROM posts WHERE user_id = ? ORDER BY created_at DESC LIMIT 20;

Reading harder plans

Real plans get nested. The structure is a tree, where the deepest node runs first. Example:

Limit  (cost=0..50 rows=10)
  ->  Nested Loop  (cost=0..600 rows=120)
        ->  Index Scan using idx_orders_user_id on orders o  (cost=0..40 rows=120)
              Index Cond: (user_id = 42)
        ->  Index Scan using items_pkey on items i  (cost=0..4.7 rows=1)
              Index Cond: (id = o.item_id)

Read it bottom-up:

  1. Scan orders by user_id = 42 → 120 rows.
  2. For each, look up items by primary key.
  3. Stop after 10 rows (because of LIMIT).

Nested Loop joins are great when the outer side is small. For bigger joins, watch for Hash Join (build a hash of one side, probe with the other) and Merge Join (sort both sides and walk in lockstep).

When the planner ignores your index

Frustrating, but it happens. Common reasons:

  • Table is too small — Postgres knows a sequential scan is faster.
  • Stale statistics — run ANALYZE <tablename>;.
  • Index doesn’t match the predicate — maybe you indexed lower(email) but query with email.
  • Wrong data type — WHERE id = '123' (string) when id is an integer; the index isn’t used.
  • OR in the predicate can defeat composite indexes; use a UNION instead.
  • Functions in WHERE prevent index use unless you have an expression index: CREATE INDEX ... ON users(lower(email)).

When in doubt, run EXPLAIN ANALYZE with and without SET enable_seqscan = off; (only in a debug session — never in prod) to see what the alternative plan looks like.

Maintenance

Indexes need a little upkeep:

  • ANALYZE updates the planner’s statistics. Postgres autovacuum runs this periodically, but after a bulk import, run it manually.
  • REINDEX rebuilds bloated indexes. Modern Postgres rarely needs this, but on huge tables with lots of updates, it can reclaim significant disk.
  • pg_stat_user_indexes tells you which indexes are actually being used. Indexes with zero scans are pure overhead — drop them.
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

The bottom of that list is your “consider dropping” pile.

A real-world tuning workflow

  1. Identify the slow query (logs, APM, pg_stat_statements).
  2. EXPLAIN ANALYZE it.
  3. Look for Seq Scan on big tables, or Sort nodes on big inputs.
  4. Add the index that would change the plan to Index Scan.
  5. Re-run EXPLAIN ANALYZE to confirm. Look for the actual time dropping.
  6. If unsure, repeat in production with a small subset.

This is the loop. It will fix 90% of slow queries.

Conclusion

Postgres indexing is not magic, and EXPLAIN ANALYZE is the tool that takes the guessing out of it. Learn to read a plan, learn the difference between B-tree and GIN, and you’ve made yourself ten times more useful any time the database starts groaning.

If you want the broader Postgres tour, see PostgreSQL Fundamentals . For the next layer of Postgres power, see PostgreSQL Full-Text Search .

Happy querying!

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .