PostgreSQL Indexes Explained: When B-tree Helps, When It Hurts

What a B-tree index is paying for

A B-tree index is a sorted structure Postgres maintains beside the heap table. Point lookups and range scans on the indexed columns become logarithmic instead of sequential. The cost: every INSERT, UPDATE, and DELETE must update matching indexes. On a busy events table we saw insert latency climb from about 2ms to about 7ms p95 after blindly indexing six columns just in case.

Low-cardinality columns such as is_active boolean alone rarely deserve a standalone B-tree. Postgres may still seq-scan if half the rows match. Composite or partial indexes are usually smarter.

I keep a spreadsheet of candidate indexes with query from APM, current p95, estimated rows, proposed definition, and write-path impact. If we cannot fill those cells, we do not create the index yet.

EXPLAIN ANALYZE before and after

I never argue about indexes without plans. EXPLAIN ANALYZE BUFFERS on a production-sized copy or anonymized dump shows actual time and whether an Index Scan or Bitmap Index Scan appeared.

Watch for Seq Scan on multi-million-row tables with a selective WHERE, and for Index Scan that still filters most rows because the index does not match the predicate well. Also compare shared hit versus read buffers — an index that lives in RAM behaves differently under cold cache.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 48291
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 50;

Composite order matters

For the query above, customer_id then created_at DESC matches equality then sort or range. created_at then customer_id is weaker for that filter pattern. I think of leftmost prefix rules the same way I did with MySQL years ago — still valid for default B-tree.

After adding CREATE INDEX CONCURRENTLY on orders for customer_id and created_at DESC, that query dropped from 180ms to 4ms on a 12M-row table. CONCURRENTLY mattered because a blocking CREATE INDEX locked writes for minutes in staging.

INCLUDE columns helped another report query avoid heap fetches: we included status and total_cents so the index-only scan answered the list view. Measure heap fetches in EXPLAIN before celebrating.

-- Prefer concurrent builds in production
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

-- Partial index: only open tickets (smaller, faster)
CREATE INDEX CONCURRENTLY idx_tickets_open_assignee
  ON tickets (assignee_id)
  WHERE status = 'open';

When indexes hurt

Write-heavy tables with indexes unused by queries are pure overhead. Functions wrapping columns such as LOWER(email) cannot use a plain email index unless you create an expression index on LOWER(email). Leading wildcards in LIKE will not use a normal B-tree; consider pg_trgm when search needs it.

Too many overlapping indexes confuse the planner and waste cache. I audit with pg_stat_user_indexes for idx_scan equals zero after a meaningful period of weeks, not hours.

On the events table, dropping three unused indexes brought insert p95 from about 7ms back to about 3ms and freed roughly 18GB of disk. Writes improved more than any read path suffered.

• Measure with EXPLAIN ANALYZE on realistic data volumes
• Match composite column order to filter plus sort patterns
• Prefer partial indexes for common status filters
• Build CONCURRENTLY in prod to avoid long write locks
• Drop unused indexes after watching idx_scan

Finding dead indexes in production

After two weeks of real traffic, I query pg_stat_user_indexes joined to pg_class for size. Anything with zero scans and non-trivial size goes on a drop candidate list. I exclude brand-new indexes and unique constraints that enforce correctness even if rarely used for reads.

I drop one at a time during low traffic, watch APM for a week, then continue. Never bulk-drop six indexes before a holiday weekend.

SELECT
  schemaname || '.' || relname AS table,
  indexrelname AS index,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_class ON pg_class.oid = indexrelid
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Unique constraints are indexes too

A UNIQUE constraint creates a unique B-tree. I do not also add a redundant non-unique index on the same columns. I have reviewed migrations that created both and paid double write cost for no gain.

Partial unique indexes are excellent for soft-delete: unique on email where deleted_at is null lets you reuse emails after delete without complicated application checks. That pattern replaced a flaky app-level race we had for years.

CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

Maintenance I schedule

Autovacuum usually keeps things healthy; bloated indexes after bulk deletes sometimes need REINDEX CONCURRENTLY. We alert when table bloat estimates cross a threshold rather than reindexing on a fixed calendar.

Foreign keys deserve indexes on the referencing column — not only for reads, but so ON DELETE cascades do not seq-scan children. Missing FK indexes caused a 40-second delete of a parent org row in one tenancy cleanup job.

Staging with production-shaped data

Plans flip with scale. On 50k rows almost everything looks fine. On 20M rows the planner chooses differently. I refresh a sanitized staging DB from production weekly for index experiments, with PII scrubbed.

I run EXPLAIN ANALYZE at least three times and discard the coldest run when comparing. Then I test under a light write load because an index that helps a read-only session can still hurt the API when inserts contend on the same pages. Uniform random foreign keys produce unrealistically perfect indexes; real customers cluster.

Monitoring after ship

After creating an index I watch query p95 from APM, insert and update p95 on the table, index size growth, and vacuum activity. A week of green metrics earns a note in the runbook; a write regression earns a revert even if the read looks prettier.

I also check whether the planner actually uses the new index. Creating an index that never gets selected is pure cost. Sometimes statistics are stale — running ANALYZE on the table fixed a stubborn seq scan after a bulk import more than once.

A small decision framework

Slow query in APM, capture SQL, run EXPLAIN ANALYZE, if seq scan on large table and selective filter then design minimal index, create concurrently on staging, compare plans and write p95, then ship. If the filter returns more than 20 to 30 percent of the table, rethink; a seq scan may already be optimal.

Ship the smallest index that changes the plan. You can always add INCLUDE columns later; removing a wide unused index under write load is harder socially than technically. After creating an index I watch query p95, insert p95, index size growth, and whether the planner actually uses it — ANALYZE after bulk imports if plans look stuck.

Key takeaways

• Indexes speed reads and tax writes — prove the trade with EXPLAIN ANALYZE.
• Composite column order should follow equality filters then range or sort.
• Partial and expression indexes beat scattershot single-column indexes.
• Use CREATE INDEX CONCURRENTLY in production.
• Audit idx_scan and remove indexes that never earn their keep.
• Index foreign key columns so deletes and joins do not seq-scan children.