What Is Database Indexing? Boost Query Performance 100x

Are your database queries slow? Searching millions of rows taking seconds? Indexes can dramatically improve your query performance.

What Is an Index?

A database index is like a book's index. Instead of reading the entire book, you jump to the relevant page. Without an index, the database scans every row (Full Table Scan).

Without index: 1,000,000 rows → Scan all → 2.5 seconds
With index:    1,000,000 rows → Index lookup → 0.003 seconds

How Indexes Work

B-Tree Index (Default)

The most common index type. Uses a balanced tree structure:

                    [M]
                   /   \
              [D,H]     [R,V]
             / | \     / | \
          [A-C][E-G][I-L][N-Q][S-U][W-Z]

• Ideal for =, <, >, BETWEEN, LIKE 'abc%'
• O(log n) complexity — only ~20 comparisons for 1 million rows

Hash Index

• Only for equality checks (=)
• O(1) complexity — very fast but no range queries

Index Types

1. Primary Key

CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- Auto-indexed
    email VARCHAR(255)
);

2. Unique Index

CREATE UNIQUE INDEX idx_users_email ON users(email);

3. Composite Index

CREATE INDEX idx_orders_customer_date 
ON orders(customer_id, order_date DESC);

-- Speeds up: WHERE customer_id = 42
-- Speeds up: WHERE customer_id = 42 AND order_date > '2026-01-01'
-- Does NOT speed up: WHERE order_date > '2026-01-01' (without first column)

4. Partial Index

CREATE INDEX idx_active_orders ON orders(customer_id)
WHERE status = 'active';
-- Only indexes active orders — smaller and faster

EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Good result:

Index Scan using idx_orders_customer on orders
  Index Cond: (customer_id = 42)
  Actual time: 0.025..0.031 ms

Bad result (Full Table Scan):

Seq Scan on orders
  Filter: (customer_id = 42)
  Actual time: 125.432..130.567 ms

When to Create Indexes

✅ Create:

• Columns frequently used in WHERE clauses
• Foreign key columns used in JOIN
• Columns used in ORDER BY
• Columns that must be unique

❌ Don't Create:

• Small tables (<1000 rows)
• Frequently updated columns
• Low cardinality columns (booleans)
• Every column — each index adds write overhead

Index Cost

SELECT:   Index → Faster ✅
INSERT:   Index → Slower ❌ (index must be updated)
UPDATE:   Index → Slower ❌ (index must be updated)
DELETE:   Index → Slower ❌ (index must be updated)

Best Practices

1. Use EXPLAIN ANALYZE to profile queries
2. Composite index ordering matters — most selective column first
3. Drop unused indexes — They slow down writes
4. Use partial indexes — Index only the subset you need
5. Run VACUUM and ANALYZE regularly — Keep statistics fresh
6. Consider covering indexes — Use INCLUDE to add extra columns

Conclusion

The right indexing strategy can boost your database performance 10x-100x. But every index adds write overhead. Measure with EXPLAIN ANALYZE, clean up unused indexes, and design indexes that match your data access patterns.

Learn database optimization and indexing on LabLudus.