🗄️ PostgreSQL Indexing: Why Your Django Queries Are Slow

🗄️ PostgreSQL does not guess. Without an index, it scans every row. That is not a bug — it is the default behavior.

🔍 The Problem: Sequential Scans

When Django ORM runs orders.filter(user_id=42), PostgreSQL builds a query plan. If user_id has no index, PostgreSQL runs a Sequential Scan. It reads every row in the orders table, compares user_id to 42, and keeps matches. Time complexity is linear — O(n). At 10,000 rows this is slow. At 100,000 rows it is broken. At a million rows it is unacceptable.

Many developers assume Django ORM is slow by design. It is not. The ORM executes SQL. If the SQL lacks indexes, performance collapses regardless of framework.

✅ The Solution: B-Tree Indexes

PostgreSQL indexes use B-trees by default. A B-tree maps column values to physical row locations. Instead of scanning 100,000 rows, PostgreSQL traverses the tree in O(log n) operations — roughly 17 steps instead of 100,000. The same filter query drops from 3000ms to under 200ms.

In Django, this is one parameter:

user = models.ForeignKey(User, on_delete=models.CASCADE, db_index=True)

But adding db_index=True to every field is wrong. Indexes speed reads but slow writes. Each INSERT updates the B-tree. Every index adds overhead. Target only columns used in WHERE, JOIN, or ORDER BY conditions.

📊 Verification: Measure Before You Fix

Always profile before adding indexes. Use Django Debug Toolbar or PostgreSQL's pg_stat_statements extension to find slow queries. Then verify with SQL:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Look for Seq Scan — that is the problem. After adding the index, you should see Index Scan or Bitmap Index Scan. Measure the execution time before and after. Numbers tell the truth, not assumptions.

🎯 Key Takeaway

Indexing is not an optimization step. It is baseline engineering. Profile first, index selectively, verify with EXPLAIN ANALYZE, and measure the result. A single targeted index on a frequently filtered ForeignKey often changes a broken query into a fast one.