Database Indexing & Query Optimization in MongoDB & PostgreSQL (2026)

As applications scale to millions of records, unindexed database queries degrade from millisecond execution times to severe CPU-bound bottlenecks. Whether operating document-based databases (MongoDB) or relational systems (PostgreSQL), effective indexing and query execution planning are essential for maintaining sub-millisecond data retrieval speeds.

This guide analyzes indexing mechanics, composite query structures, and query execution diagnostic tools across MongoDB and PostgreSQL.

Indexing Mechanics: B-Tree vs. Specialized Indexes

Index Type	Database Support	Primary Use Case	Performance Trait
B-Tree / Single Field	MongoDB & PostgreSQL	Direct equality and range lookups (=, >, <, BETWEEN)	Logarithmic lookup complexity $O(\log N)$
Compound / Composite	MongoDB & PostgreSQL	Multi-field filtering, sorting, and aggregation constraints	Requires strict adherence to the ESR Rule
Partial / Filtered	MongoDB & PostgreSQL	Indexing a subset of documents that match a specific condition	Reduces index size and write overhead dramatically
GIN / Multikey	PostgreSQL (GIN) / MongoDB (Multikey)	Array fields, JSONB documents, and full-text search operators	Higher write overhead, optimized for containment queries

The Compound Index Golden Rule: ESR (Equality, Sort, Range)

When creating compound indexes for queries containing filtering and sorting directives, order fields in the index definition according to the ESR Rule:

1. E (Equality): Place exact-match filter fields first (e.g., status: 'ACTIVE', tenantId: 101).
2. S (Sort): Place fields used for ordering results second (e.g., sort({ createdAt: -1 })).
3. R (Range): Place range comparison fields last (e.g., age: { $gt: 25 }, price < 500).

1. MongoDB Index Optimization & Analysis

// 1. Create Compound Index using ESR Rule
db.orders.createIndex({
  tenantId: 1,    // Equality
  createdAt: -1,  // Sort
  amount: 1       // Range
});

// 2. Query utilizing the ESR Index
db.orders.find({
  tenantId: "t_4091",
  amount: { $gte: 100 }
}).sort({ createdAt: -1 });

// 3. Diagnose Query Plan using explain()
db.orders.find({
  tenantId: "t_4091",
  amount: { $gte: 100 }
}).sort({ createdAt: -1 }).explain("executionStats");

Key Metrics to Watch in MongoDB Execution Stats:

• stage: 'IXSCAN': Indicates an index scan (Good). Avoid stage: 'COLLSCAN' (Full Collection Scan).
• totalDocsExamined: Should match or closely equal nReturned. High values mean an inefficient index.

2. PostgreSQL EXPLAIN ANALYZE & Partial Indexes

In PostgreSQL, full table scans (Seq Scan) on massive tables spike I/O usage. Use Partial Indexes to index only high-frequency active query subsets.

-- 1. Create a Partial Index on Unprocessed Tasks only
CREATE INDEX idx_unprocessed_tasks 
ON tasks (priority DESC, created_at ASC) 
WHERE status = 'PENDING';

-- 2. Execute Query with EXPLAIN ANALYZE execution timing
EXPLAIN ANALYZE 
SELECT id, payload 
FROM tasks 
WHERE status = 'PENDING' 
ORDER BY priority DESC, created_at ASC 
LIMIT 50;

Deciphering PostgreSQL Query Plans:

• Index Scan using idx_unprocessed_tasks: Direct B-Tree traversal for matching rows.
• Bitmap Index Scan: Combines index pointers before reading heap pages (efficient for medium selectivity).
• Seq Scan: Evaluates every row in the table sequentially — indicates a missing or unused index.

Production Indexing Best Practices

• Avoid Over-Indexing: Every index adds overhead to write operations (INSERT, UPDATE, DELETE) because the database must update index trees synchronously.
• Covering Queries: Whenever possible, project only fields present within the index itself so the database engine returns data directly from the index tree without fetching heap/document pages.
• Monitor Unused Indexes: Periodically query pg_stat_user_indexes in PostgreSQL or run $indexStats in MongoDB to identify and drop unused or redundant indexes.