Practical Database Indexing and Query Tuning for Scalable Applications
Understanding composite indexes, execution plans, index selectivity, and how to avoid costly table scans in SQL Server and PostgreSQL.
As application datasets grow, unindexed queries that previously ran in 5 milliseconds can easily degrade to multi-second operations, causing thread-pool exhaustion and high database CPU spikes.
Understanding Index Selectivity
An index is most effective when it filters down a large dataset to a very small percentage of rows (high selectivity). An index on a Gender or Status column with only 2 or 3 distinct values often won't be used by the query optimizer because a sequential scan is cheaper.
Composite Indexes and Column Order
The order of columns in a composite index matters immensely. Follow the Equality, Range, Sort rule:
1. Equality columns first (e.g. tenant_id = 42)
2. Range / Inequality columns next (e.g. created_at > '2026-01-01')
3. Covering columns via INCLUDE to avoid bookmark lookups
CREATE INDEX idx_orders_tenant_created
ON orders (tenant_id, created_at DESC)
INCLUDE (total_amount, status);