How to Master PostgreSQL Indexing: B-Tree, Composite, and Partial Indexes

Prerequisites

  • PostgreSQL 14+
  • A database with at least 10,000 rows in a test table for meaningful benchmarks
  • Basic SQL knowledge

Why Indexing Matters

Without indexes, PostgreSQL performs sequential scans — it reads every row to find matches. This is linear time O(n). With an index, lookups become O(log n), often reducing query time from seconds to milliseconds.

But indexes aren’t free. They consume disk space and slow down writes (INSERT, UPDATE, DELETE) because the index must be updated alongside the table.

B-Tree Indexes (Default)

B-tree is PostgreSQL’s default index type. It handles equality and range queries:

-- Create a B-tree index
CREATE INDEX idx_users_email ON users(email);

-- Now these queries use the index:
SELECT * FROM users WHERE email = '[email protected]';
SELECT * FROM users WHERE email > 'a';
SELECT * FROM users ORDER BY email;

When B-tree works:

  • =, >, <, >=, <=, BETWEEN, IN
  • ORDER BY on the indexed column
  • LIKE 'prefix%' (but NOT LIKE '%suffix')

When B-tree doesn’t work:

  • LIKE '%middle%' — needs trigram index (GIN + pg_trgm)
  • Full-text search — needs GIN with tsvector
  • JSON containment — needs GIN with jsonb_ops

Composite Indexes

When queries filter on multiple columns:

-- Query pattern:
SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';

-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Column order rule: Put the most selective column first (the one that filters out the most rows).

-- Good: user_id is very selective
CREATE INDEX ON orders(user_id, status);

-- Bad: status has few values ('pending', 'shipped', 'cancelled')
CREATE INDEX ON orders(status, user_id);

Leftmost prefix rule: A composite index on (a, b, c) can serve queries on:

  • WHERE a = ? ✅
  • WHERE a = ? AND b = ? ✅
  • WHERE a = ? AND b = ? AND c = ? ✅
  • WHERE b = ? AND c = ? ❌ (skips leading column)

Partial Indexes

Index only a subset of rows:

-- Only index active users (5% of the table)
CREATE INDEX idx_active_users ON users(email)
  WHERE active = true;

-- Only index unshipped orders
CREATE INDEX idx_unshipped_orders ON orders(created_at)
  WHERE status != 'shipped';

Benefits:

  • Smaller index — less disk space, faster writes
  • More efficient queries when the WHERE clause matches the partial index condition

Using EXPLAIN ANALYZE

Always verify your indexes are being used:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Key output to examine:

Index Scan using idx_users_email on users  (cost=0.29..8.31 rows=1 width=100)
  (actual time=0.012..0.013 rows=1 loops=1)
  • Index Scan — using the index (good)
  • Seq Scan — reading all rows (bad for large tables)
  • Bitmap Index Scan — combining multiple indexes (usually good)
  • cost — estimated cost in arbitrary units (lower = better)
  • actual time — real execution time in ms

Common Indexing Pitfalls

1. Indexes on low-cardinality columns

-- Bad: boolean column with only 2 values
CREATE INDEX ON users(is_admin);

-- PostgreSQL will still sequential scan — the index isn't selective enough

2. Function calls prevent index usage

-- Index on email won't help here:
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Fix: create a functional index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

3. Unused indexes

-- Find unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Drop indexes that are never used — they waste disk and slow writes.

4. Over-indexing

Every index adds overhead to INSERT/UPDATE/DELETE. Monitor write performance after adding indexes.

Verification

-- Create test data
CREATE TABLE test_data AS
SELECT generate_series(1, 100000) AS id,
       md5(random()::text) AS email;

-- Query without index
EXPLAIN ANALYZE SELECT * FROM test_data WHERE email = 'abc';
-- Seq Scan ... actual time ~30ms

-- Add index
CREATE INDEX ON test_data(email);

-- Query with index
EXPLAIN ANALYZE SELECT * FROM test_data WHERE email = 'abc';
-- Index Scan ... actual time ~0.05ms
-- 600x faster!

Summary

  • Use B-tree as the default — it covers 90% of needs
  • Create composite indexes for multi-column queries; order columns by selectivity
  • Use partial indexes to index only frequently-queried subsets
  • Always verify with EXPLAIN ANALYZE
  • Drop unused indexes to save disk and write performance

References

Advertisement