PostgreSQL indexes explained: EXPLAIN, B-tree and the N+1 hell

The query working perfectly in dev crawls in production. Almost always three suspects: a missing index, the planner choosing badly, or your ORM firing 500 queries unnoticed. This article gives you the kit to diagnose all three with confidence.

Without an index: search means full scan

A PostgreSQL table is physically a heap of rows. A SELECT ... WHERE email = 'x' without an index forces reading every row one by one (Sequential Scan). With 100 rows, instant; with 10 million, seconds — multiplied by every concurrent request.

A B-tree index conceptually reorganizes a column's values into a balanced tree: finding a value costs O(log n) — 10 million rows ≈ 23 hops. The difference between flipping through a whole dictionary and using its index.

CREATE INDEX idx_users_email ON users (email);

Golden rule: index columns appearing in WHERE, JOIN ... ON, ORDER BY and unique constraints. Don't index everything: each index slows writes (must be updated) and consumes space.

Reading EXPLAIN ANALYZE without fear

The command answering "what is my query actually doing":

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created DESC LIMIT 20;

Typical output before the index:

Sort  (cost=... rows=20) (actual time=1842.1..1842.2 rows=20)
  Sort Key: created DESC
  Sort Method: top-N heapsort
  -> Seq Scan on orders (actual time=0.4..1780.5 rows=84112)
       Filter: (customer_id = 42)
       Rows Removed by Filter: 9915888

Read it like this:

• Seq Scan = walks the WHOLE table. Millions of rows plus selective filter equals the crime.
• Rows Removed by Filter: 9915888 = read 10 million to return 84 thousand. Brutal ratio.
• actual time=1780 = there are your 1.8 seconds.
• Top node (Sort) sorts in memory after gathering everything.

After CREATE INDEX idx_orders_customer ON orders (customer_id, created DESC):

Limit (actual time=0.31..0.85 rows=20)
  -> Index Scan using idx_orders_customer on orders (rows=20)
       Index Cond: (customer_id = 42)

0.85 ms. Three orders of magnitude. Note the fine detail: including created DESC as second index column makes Postgres read rows ALREADY sorted and the Sort node disappears — that's designing the index for the concrete query, not just the filter.

Composite indexes: order matters

An (a, b) index serves filters on a, on a AND b, and ORDER BY a — but NOT filtering by b alone (can't jump into the middle of the tree). Think phone book sorted by last+first name: finding all "García" is instant; locating all "José" requires walking it entirely (that would be another index).

Most selective column first (the one discarding most), ordering columns after. Covering indexes go further: CREATE INDEX ... INCLUDE (select_columns) answers from the index itself without touching the table (Index Only Scan).

The N+1: the silent ORM killer

The most common slowness pattern isn't one slow query but TWO HUNDRED fast ones:

# Innocent code
orders = Order.objects.all()[:50]
for o in orders:
    print(o.customer.name)   # ← one query PER order!

51 queries where 1 sufficed (hence N+1). In development with local SQLite it goes unnoticed; in production every database round-trip adds network latency: 50 × 5ms = 250ms extra minimum, scaling with traffic.

Diagnosis: enable query logging in development and count per request (django-debug-toolbar, rack-mini-profiler, Prisma logging $extends, Apollo tracing...). Solution: explicit eager loading — select_related/prefetch_related (Django), includes (Rails/Prisma), join fetch (JPA) — turning the graph into JOINs or a second IN (...) query.

Practical rule: rendering a list while accessing relations inside the loop? Suspect N+1 automatically.

When an index ISN'T used

Classic symptoms confusing everyone:

• Functions over the column: WHERE lower(email) = ... ignores the email index. Fix: functional index CREATE INDEX ON users (lower(email)).
• LIKE with leading wildcard: '%text' can't use B-tree. For suffix or free-text search: pg_trgm or full-text search.
• Low selectivity: an index on an is_active boolean with 98% trues doesn't help — the planner is right to ignore it.
• Stale statistics: the planner decides from statistics; after bulk loads run ANALYZE table.
• Type mismatches: comparing text column against numeric parameter forces casts invalidating the index.

And formatting those complex queries for calm review is exactly what our SQL formatter does — readability first, diagnosis after.

FAQ

How many indexes is too many? No magic number: each costs on INSERT/UPDATE and space. Practical rule: an index must justify itself via measured real queries (pg_stat_user_indexes shows usage counts — zero-read indexes are removal candidates).

Periodic REINDEX? Modern versions almost never need it; heavy bloat usually signals misconfigured autovacuum. Occasional REINDEX CONCURRENTLY after mass deletes does help.

How do I find slow queries before complaints? pg_stat_statements: official extension accumulating total time, mean and frequency per normalized query. Sort by total_exec_time for your real prioritized list.

Format your queries for actual readability with our SQL Formatter, free and right in your browser.