Mastering Database Query Optimization & Indexing in PostgreSQL and MySQL

1. Understanding the Cost of Full Table Scans

As relational databases grow past hundreds of thousands of records, unindexed queries quickly degrade application performance. A query performing a Sequential Scan (Seq Scan) reads every block from disk, leading to massive I/O spikes and thread contention.

2. Designing High-Performance Composite B-Tree Indexes

When filtering and sorting across multiple columns simultaneously, single-column indexes are often insufficient. A composite index must follow the Equality, Range, Sort (ESR) design principle:

-- Creating an optimized composite index for multi-column filtering and ordering
CREATE INDEX idx_orders_customer_status_created 
ON orders (customer_id, status, created_at DESC);

Tip: Always place exact equality columns first in the composite index definition, followed by range filters and ORDER BY columns.

3. Diagnosing Queries with EXPLAIN ANALYZE

Use execution plan inspection to verify that the query engine uses an Index Scan rather than a Sequential Scan:

EXPLAIN ANALYZE
SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 4921 AND status = 'completed'
ORDER BY created_at DESC
LIMIT 20;