Engineering

SQL Indexing Explained Simply: How to Make Slow Queries 100x Faster

Understand B-Trees, composite indexes, query execution plans, and why adding random indexes often hurts database performance.

Techoverxe TeamNovember 2, 20262 min read

Nothing brings a production application to a grinding halt faster than an unindexed database query scanning millions of rows on every HTTP request.

When response times spike from 25ms to 4,000ms, junior developers often panic and start slapping indexes onto every column in sight. But database indexing is not a magic fix-all: every index comes with write overhead, memory consumption, and disk footprint.

Here is a clear, visual explanation of how SQL indexes actually work, how to read an execution plan, and how to design indexes that make queries 100x faster.

1. The Problem: Sequential Scans (Table Scans)

Imagine a telephone book with 10 million names printed in completely random order. If you want to find "John Doe", you have to read the book page by page from the first line to the very end. That is a Sequential Scan (O(N) complexity).

In relational databases (PostgreSQL, MySQL), a sequential scan means the database engine reads every single 8KB disk block into memory, checking if each row matches your WHERE condition. On a 10-million-row table, this can take seconds of heavy I/O.

2. The Solution: The B-Tree Index

When you create an index:

CREATE INDEX idx_users_email ON users(email);

The database creates a balanced search tree (B-Tree) on disk. A B-Tree maintains sorted values in a multi-level hierarchy:

               [ Root Node: M ]
              /                \
      [ Node: D - H ]     [ Node: Q - U ]
      /      |      \     /      |      \
   Leaf    Leaf    Leaf Leaf   Leaf    Leaf
 (Data)   (Data)  (Data)

Searching a balanced tree takes O(log N) time. On a table with 10,000,000 rows, a B-Tree lookup requires at most 3 to 4 disk page reads to locate the exact row pointer (ctid). Your query goes from 3,500ms down to 1.2ms.

3. The Power of Composite Indexes & Column Ordering

Queries rarely filter on a single column. Consider this search query:

SELECT id, title, price 
FROM products 
WHERE category = 'electronics' AND status = 'active' 
ORDER BY created_at DESC 
LIMIT 20;

If you index category and status separately, the database engine must scan two separate indexes and perform a costly Bitmap Index Scan to intersect the row IDs.

Instead, create a single Composite Index (Multi-column Index):

CREATE INDEX idx_products_cat_status_created 
ON products(category, status, created_at DESC);

The Golden Rule of Index Ordering (Equality, Range, Sort - ESR):

  1. Place columns used in exact equality matches (WHERE col = 'xyz') first.
  2. Place columns used in sorting (ORDER BY col DESC) next.
  3. Place range filters (WHERE price > 100) last.

If your query filters on (status, created_at) but your index starts with category, the index cannot be used because B-Trees can only be traversed from left to right!

4. How to Read EXPLAIN ANALYZE

Never guess whether an index is working. Ask the database directly:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE customer_id = 9842;

Look for these key indicators in the output:

  • Seq Scan on orders (Bad): The database scanned the entire table. Your index was ignored or doesn't exist.
  • Index Scan using idx_orders_customer_id (Great): The database traversed the B-Tree and fetched matching rows directly from disk blocks.
  • Index Only Scan (Best!): All columns requested in SELECT were satisfied entirely by the index itself; the database never touched the underlying table data pages.

5. The Hidden Cost of Too Many Indexes

Indexes are not free:

  • Write Penalties: Every INSERT, UPDATE, and DELETE must rewrite every corresponding index tree. Tables with 15+ indexes suffer massive write slowdowns.
  • RAM Bloat: Indexes must fit into the database buffer pool (shared_buffers) to stay fast. Huge unused indexes evict active table pages from memory.

Audit unused indexes in PostgreSQL periodically:

SELECT relname, indexrelname, idx_scan 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

Deepen your database skills with our SQL & Databases Roadmap and PostgreSQL Technology Guide.

SQL
Databases
PostgreSQL
Performance
Backend

Ready to put this into practice?

Browse real AI training & evaluation jobs, or follow a guided roadmap to go from beginner to job-ready.