1. Anatomy of a B-Tree Database Index

When queries lack an appropriate index, relational database engines (PostgreSQL, MySQL, Oracle) must read every data block on physical storage through a Sequential Table Scan (Seq Scan). On a table with millions of transaction records, this converts a sub-5ms query into a multi-second I/O nightmare.

A B-Tree (Balanced Tree) index structures indexed column values in a self-balancing hierarchical tree of root, branch, and leaf pages. Searching through 10,000,000 rows requires only 3 to 4 pointer hops from the root page to the target leaf node, reducing computational complexity from O(N) to O(log N).

2. Composite Index Column Ordering: The Leftmost Prefix Rule

Creating multiple single-column indexes rarely solves complex filtering queries. Instead, production applications rely on Composite (Multi-Column) Indexes.

However, column ordering is critical. Relational databases can only utilize a composite index if the query filters match the Leftmost Prefix. An index created on (tenant_id, status, created_at) will accelerate queries filtering on tenant_id or tenant_id + status, but will be ignored if the query filters solely on status or created_at.

-- Diagnosing execution plan before indexing
EXPLAIN ANALYZE 
SELECT order_id, customer_id, total_price 
FROM customer_orders 
WHERE status = 'COMPLETED' 
  AND created_at >= '2026-01-01' 
ORDER BY created_at DESC;

-- Optimized: Creating a Composite Covering Index
CREATE INDEX CONCURRENTLY idx_orders_status_date_covering
ON customer_orders (status, created_at DESC)
INCLUDE (customer_id, total_price);

3. Common Index Invalidation Pitfalls

Developers often assume that having an index guarantees it gets used. However, simple syntax mistakes invalidate indexes at runtime:

• Wrapping Columns in Functions: Writing WHERE LOWER(email) = 'user@example.com' invalidates a standard index on email. Use an expression index instead: CREATE INDEX idx_user_lower_email ON users (LOWER(email)).
• Leading Wildcard LIKE Clauses: Writing WHERE username LIKE '%deep' forces a sequential scan because B-Trees cannot search substrings without known prefixes.
• Implicit Type Coercion: Comparing a string column against an unquoted integer parameter causes the query planner to cast every row dynamically, bypassing index trees.