PostgreSQL Indexing Complete Guide: B-Tree, GIN, and Query Tuning

Introduction

Indexes are the primary tool for accelerating PostgreSQL queries—but the wrong index wastes disk, slows writes, and confuses the planner. Understanding B-tree defaults, composite column order, partial indexes, and specialized types (GIN, GiST, BRIN) lets you match indexes to real query patterns.

This guide explains how PostgreSQL uses indexes, how to read EXPLAIN ANALYZE, and practical patterns for production workloads from SaaS multitenant apps to analytics adjacency.

Table of Contents

• How PostgreSQL Uses Indexes
• B-Tree Indexes and Column Order
• Composite and Covering Indexes
• Partial and Expression Indexes
• GIN and Full-Text Search
• EXPLAIN ANALYZE Walkthrough
• Index Maintenance and Bloat
• Best Practices
• Common Mistakes
• Interview Questions
• Summary
• References

How PostgreSQL Uses Indexes

Without an index, PostgreSQL performs a sequential scan—reads every row. With a suitable index, it can index scan or bitmap index scan to fetch row locations (TIDs) and load heap pages.

CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  total       NUMERIC(12, 2)
);

CREATE INDEX idx_orders_user_id ON orders (user_id);

SELECT * FROM orders WHERE user_id = 42;
-- Likely: Index Scan using idx_orders_user_id

Primary keys automatically create unique B-tree indexes.

B-Tree Indexes and Column Order

Default CREATE INDEX builds B-tree—good for equality and range (=, <, >, BETWEEN, ORDER BY on indexed column).

Leftmost prefix rule for composites:

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Uses index well:
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 1;

-- May not use index efficiently:
SELECT * FROM orders WHERE status = 'paid';

Put high-selectivity or equality columns first; range columns often last.

Composite and Covering Indexes

Index-only scans occur when all needed columns are in the index:

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC)
  INCLUDE (total, status);

SELECT status, total, created_at
FROM orders
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 20;

INCLUDE columns are stored in the index leaf but are not part of the search key—useful for covering queries without bloating key width.

Partial and Expression Indexes

Partial index—index subset of rows:

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

Smaller index, faster for queries matching the predicate:

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at;

Expression index:

CREATE INDEX idx_users_email_lower ON users (lower(email));

SELECT * FROM users WHERE lower(email) = lower('Monu@Example.com');

GIN and Full-Text Search

GIN (Generalized Inverted Index) suits arrays, JSONB, and full-text:

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
-- tags is text[]

SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];

JSONB:

CREATE INDEX idx_data_metadata ON events USING GIN (metadata jsonb_path_ops);

SELECT * FROM events WHERE metadata @> '{"source": "api"}';

Full-text:

CREATE INDEX idx_articles_fts ON articles
  USING GIN (to_tsvector('english', title || ' ' || body));

SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'index & btree');

EXPLAIN ANALYZE Walkthrough

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10;

Look for:

• Seq Scan on large tables—missing or wrong index
• Rows Removed by Filter—index not selective enough
• High actual time on nested loops—bad join order or missing index on join key
• Buffers: shared hit/read—cache effectiveness

Run ANALYZE orders; after bulk loads so statistics are current.

Index Maintenance and Bloat

Indexes grow and can bloat under heavy updates. Monitor with:

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan;

Drop unused indexes (idx_scan = 0 over weeks). REINDEX or pg_repack for bloat. Autovacuum handles dead tuples—don't neglect it.

Best Practices

1. Index query patterns, not every column—measure with EXPLAIN.
2. Match predicate order in composite indexes to WHERE clauses.
3. Use partial indexes for hot subsets (active, unpublished, pending).
4. Avoid over-indexing—each index slows INSERT/UPDATE/DELETE.
5. Keep statistics fresh—ANALYZE after large data changes.
6. Consider BRIN for very large, naturally ordered tables (timestamps).

Common Mistakes

1. Indexing low-cardinality columns alone—status with 3 values may not help alone.
2. Wrong column order in composites—planner can't use index.
3. Functions on columns in WHERE without expression index—WHERE YEAR(created_at) = 2024.
4. Implicit type casts preventing index use.
5. Creating indexes before measuring—premature optimization.
6. Ignoring OR conditions—may need separate indexes or rewrite to UNION.

Interview Questions

1. When does PostgreSQL choose sequential scan over index scan?
2. Explain leftmost prefix rule for composite indexes.
3. Difference between B-tree, GIN, and GiST?
4. What is an index-only scan?
5. How do partial indexes reduce overhead?
6. How to find unused indexes in PostgreSQL?
7. What causes index bloat and how to fix it?
8. How does EXPLAIN ANALYZE differ from EXPLAIN?

Multicolumn Indexes and Sort Order

PostgreSQL can use indexes for ORDER BY when sort direction matches index definition:

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

SELECT * FROM orders
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 20;
-- Index scan avoids separate sort step

Mixed sort directions may need two indexes or NULLS FIRST/LAST awareness in planner choices.

Monitoring Slow Queries

Enable pg_stat_statements:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Set log_min_duration_statement in development to catch queries exceeding threshold before production.

Correlate application request logs with query_id when using prepared statements for faster root-cause analysis.

Index Design Workshop Process

1. Collect top 20 slow queries from stats
2. Run EXPLAIN ANALYZE on each with production-like data volume
3. Propose index—measure write overhead on staging
4. Deploy during low traffic; monitor bloat and scan counts
5. Review unused indexes quarterly

Index design is iterative—first deployment rarely optimal after real traffic patterns emerge.

Read Replicas and Cache Coherence

When PostgreSQL read replicas lag behind primary, cache-aside can serve stale data after write:

Write → Primary → (replication lag) → Replica still old
Read from replica → cache miss → stale read cached

Mitigations:

• Read your writes from primary for recently mutated entities
• Short TTL on hot keys after writes
• Cache invalidation message via Redis pub/sub on write path

Measure replica lag—alert above 5 seconds for user-facing read paths.

Index maintenance windows should be communicated to stakeholders—REINDEX on large tables locks writes or runs long depending on method chosen. Use REINDEX CONCURRENTLY where supported and monitor disk space; rebuilding indexes temporarily doubles storage requirements until old index files are reclaimed by vacuum processes completing successfully.

Summary

PostgreSQL indexing is query-pattern driven. Start with B-tree on foreign keys and filter columns; add composites, partial, and GIN indexes when EXPLAIN proves the need. Maintain statistics, monitor usage, and drop dead indexes. The planner is smart but not psychic—give it the right structures and accurate stats.

References

• PostgreSQL Docs — Indexes
• PostgreSQL Docs — EXPLAIN
• Use The Index, Luke!
• PostgreSQL Docs — GIN
• PgAnalyze — Index Guide