A table with 40 million rows. A dashboard query taking 14 seconds. A frustrated product manager asking why the reports page "is always slow." Sound familiar?

The instinct is to slap an index on whatever column sits in the WHERE clause and redeploy. Sometimes that works. More often it shaves a second off and leaves the real problem intact — or worse, adds write overhead without helping reads at all.

The engineers who consistently get dramatic performance improvements treat indexing as a diagnostic exercise, not a guess. They start with the query plan, not the schema.

Start With EXPLAIN ANALYZE, Not Intuition

Before touching a single CREATE INDEX statement, run your slow query prefixed with EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT). The output tells you exactly where time is being spent.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.created_at, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC;

The two node types that signal a missing or misused index are:

  • Seq Scan — PostgreSQL is reading every row in the table. On large tables, this is almost always a problem.
  • Hash Join with high cost — often a sign that the join key on one side lacks an index, forcing a full scan before the hash build.

Look at the actual time, rows, and Buffers: shared hit/read values. High shared read (disk I/O) on a node you expected to be fast is your first clue that the planner isn't finding cached, indexed data.

Choosing the Right Index Type

PostgreSQL ships with several index access methods. Picking the wrong one is as bad as picking no index at all.

B-tree: The Default Workhorse

B-tree indexes support equality (=), range (<, >, BETWEEN), and ORDER BY operations. They are the correct choice for most scalar columns — timestamps, integers, UUIDs, short strings used in exact lookups.

For the query above, a composite B-tree index on (status, created_at DESC) turns a sequential scan into an Index Scan, because the planner can satisfy both the WHERE filter and the ORDER BY in a single index traversal without a sort step.

CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

Before: Seq Scan, actual time ~12,400 ms, 41 M rows examined. After: Index Scan, actual time ~38 ms, 1,240 rows examined.

Column order in composite indexes matters enormously. Put the equality-filter column first (status), then the range or sort column. The planner can use a prefix of an index but not a suffix.

GIN: When You're Searching Inside Values

General Inverted Indexes are built for containment and membership queries — full-text search, JSONB fields, and array columns. A B-tree index on a jsonb column is nearly useless for querying nested keys; a GIN index is purpose-built for it.

-- JSONB column storing feature flags per user
CREATE INDEX idx_users_flags_gin
ON users USING GIN (feature_flags);

-- Now this query uses an index bitmap scan instead of Seq Scan
SELECT id FROM users WHERE feature_flags @> '{"beta_dashboard": true}';

GIN indexes are larger and slower to update than B-tree, so apply them deliberately. They pay back the cost on read-heavy JSONB or tsvector columns where containment queries run frequently.

BRIN: For Naturally Ordered, Append-Only Data

Block Range INdexes store min/max values for ranges of physical disk blocks rather than per-row entries. They are tiny — sometimes 200x smaller than an equivalent B-tree — and work spectacularly on time-series or event-log tables where rows are written in roughly chronological order and old data is rarely updated.

CREATE INDEX idx_events_occurred_brin
ON events USING BRIN (occurred_at);

If your events table is append-only and occurred_at correlates with physical insert order, BRIN can replace a B-tree at a fraction of the storage cost. The trade-off: BRIN is coarser. It eliminates block ranges rather than pointing to individual rows, so it is only efficient when data correlation between physical order and column value is high.

Run SELECT correlation FROM pg_stats WHERE tablename = 'events' AND attname = 'occurred_at'; — a value above 0.9 is a green light for BRIN.

Partial Indexes: Index Less, Win More

A partial index is built over a filtered subset of rows. If 95% of your orders rows have status = 'completed' and your slow queries only ever filter on status = 'pending', indexing all statuses wastes space and write overhead.

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

This index is smaller, faster to maintain, and the planner will prefer it for queries that match the partial condition. Partial indexes are underused by most teams and frequently deliver the biggest bang-per-byte of any indexing decision.

Diagnosing Index Bloat and Dead Indexes

Adding indexes is easy. Managing them over time is where teams slip up. Every index you create adds overhead to every INSERT, UPDATE, and DELETE. Two tools help you audit what you actually have:

  • pg_stat_user_indexes — shows idx_scan counts. An index with zero scans in the past 30 days in production is dead weight.
  • pgstatindex() (from the pgstattuple extension) — reveals index bloat percentage. An index with 40%+ bloat should be rebuilt with REINDEX CONCURRENTLY.
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan ASC;

Drop indexes that haven't been scanned. Then re-run your critical query plans to confirm nothing regressed.

The Systematic Workflow

The process that consistently produces results follows four steps:

  1. Profile — Identify the slowest queries via pg_stat_statements sorted by total_exec_time.
  2. Plan — Run EXPLAIN (ANALYZE, BUFFERS) and locate Seq Scans or high-cost nodes on large row sets.
  3. Select — Choose index type based on the access pattern: B-tree for ranges/equality, GIN for containment, BRIN for correlated time-series, partial for selective filters.
  4. Verify — Re-run the query plan after CREATE INDEX CONCURRENTLY and confirm the planner picks up the new index. Check idx_scan weekly to prove it earns its keep.

Reactive indexing treats symptoms. This workflow treats causes.

Why This Matters for Your Project

Whether you are building a multi-tenant SaaS on a shared Postgres cluster or scaling a mobile app backend into the millions of rows, poor indexing strategy is one of the highest-leverage problems you can fix — and one of the most commonly deferred. The difference between a 12-second report query and a 40-millisecond one is not more hardware; it is a composite index in the right column order and a partial index that eliminates 95% of irrelevant rows before the planner even starts working. Getting this right early keeps infrastructure costs predictable and user experience fast as your data grows.