Sign in Subscribe
3 min read Django

Most Django Indexes Are Useless. Here’s How to Fix Them.

TL;DR: Most Django developers rely on default indexes (like those on foreign keys), but PostgreSQL (and most RDBMSs) only uses one index per query. If you filter on multiple fields, especially together, a composite index is usually required. Index for how you query, not just how your models look.

1. What Django Gives You by Default

Django will automatically add indexes for:

  • Primary keys (the id field)
  • Foreign keys (behind the scenes with db_index=True)

It does not automatically index:

  • Regular fields you filter on (CharField, BooleanField, etc.)
  • Fields used in .order_by() or pagination
  • Fields used together in queries (composite indexes)

Example:

class Invoice(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)  # FK = auto-indexed
    created_at = models.DateTimeField()
    status = models.CharField(max_length=20)  # Not indexed by default

This model will have indexes on id and user_id, but not on status.

The presence of ForeignKey fields and Django’s automatic primary keys can give a false sense of security. It’s easy to assume that if a field is indexed, the query will be fast. But indexing isn’t about whether a field can be queried — it’s about how fields are used together in your queries. Relying only on what Django gives you by default often leads to missed performance wins.

2. The More Field Indexes, the Merrier - Right?

It’s often believed that adding db_index=True to all fields you might filter on is enough. But this is where things get counterintuitive:

PostgreSQL only uses one index per table scan.

If your query filters on multiple fields, the planner won’t merge several single-column indexes. It’ll pick the one it believes is most selective and apply the other filters in memory.

Example query:

Invoice.objects.filter(user_id=1, status="paid")

Even with indexes on both user_id and status, Postgres might still do a sequential scan:

Example .explain() output:

Seq Scan on invoice  (cost=0.00..1204.50 rows=1000 width=48)
  Filter: ((user_id = 1) AND (status = 'paid'))

This means no index was used effectively. For large tables, this becomes a performance bottleneck.

3. How to Index for Composite Queries

You need to design indexes that reflect how your data is used, not just how it’s structured.

Fixing the example:

class Meta:
    indexes = [
        models.Index(fields=["user", "status"]),
    ]

This allows Postgres to filter efficiently when both fields are queried together.

Important: Composite indexes are only used when the leading column(s) in the index are part of the query. An index on ("user", "status") will not help if you're only filtering by status. The query must include user to make use of the index.

Also note: using models.Index(fields=["field"]) provides the same underlying result as db_index=True, but it can be more readable and easier to evolve, especially if you plan to later modify or combine indexes.

4. The Trade-Offs

Indexes come with costs:

  • They consume disk space
  • They slow down writes (INSERT, UPDATE, DELETE) because each index must also be updated

Avoid blindly creating composite indexes for every possible combination of fields. Instead, focus on what's frequent, slow, or scaling poorly — and back your decisions with .explain() output and real-world usage.

Indexing is only effective when it aligns with your actual query patterns. Adding indexes for every field often leads to bloat and diminishing returns.

5. Reviewing Indexes in Practice

When reviewing model or migration PRs, we ask:

  • Is this field part of a common filter?
  • Are these fields used together in filters?
  • Are we paginating or sorting by this field?
  • Does this need a composite or partial index instead of multiple single-field ones?

A common red flag: db_index=True on three different fields — all used together — but no composite index in sight.

Good indexing is less about marking every field as "indexable" and more about being precise, intentional, and aware of how queries behave in the real world.

6. Final Thoughts

Indexes are one of the most powerful tools in your performance toolbox — but only if they’re actually used.

Don’t rely on Django’s defaults. Don’t assume that db_index=True is sufficient. And don’t expect PostgreSQL to combine indexes for you — it won’t.

Index for how your data is used, not just how it’s structured.

Member discussion