Use QuerySet.explain() and EXPLAIN ANALYZE to Speed Up Django Queries

Problem statement

You found a slow Django view and the query behind it, but you do not know why it is slow. Adding indexes by guesswork sometimes helps and often does nothing. PostgreSQL will tell you exactly how it runs a query: which tables it scans, which indexes it uses, how many rows it expected versus found, and where the time goes. Django exposes this through QuerySet.explain(), so you can read plans for ORM queries without copying SQL by hand.

1. Print the plan: print(qs.explain(analyze=True, buffers=True)).
2. Look for Seq Scan on large tables with selective filters: usually a missing index.
3. Compare rows= estimates with actual rows=: big gaps mean stale statistics (ANALYZE table) or correlated columns.
4. Look for expensive Sort or Hash nodes spilling to disk, and nested loops with many iterations.
5. Run analyze=True on production only for read queries, inside a transaction you roll back if the query writes.

Step-by-step solution

1. Get the plan from the ORM

qs = (Order.objects
      .filter(customer__country="DE", status="paid", created_at__gte=since)
      .select_related("customer")
      .order_by("-created_at")[:50])
print(qs.explain(analyze=True, buffers=True, verbose=False))

analyze=True executes the query and reports actual times and row counts; without it you get estimates only. buffers=True shows how many pages came from memory (shared hit) versus disk (read). On MySQL, explain() supports analyze=True on MySQL 8.0.18+ with different output.

2. Read the plan from the inside out

Limit  (cost=0.56..1520.33 rows=50 width=180) (actual time=0.081..2210.437 rows=50 loops=1)
  ->  Nested Loop  (actual time=0.080..2210.390 rows=50 loops=1)
        ->  Index Scan Backward using orders_order_created_at_idx on orders_order
              (actual time=0.030..2195.120 rows=50 loops=1)
              Filter: ((status = 'paid') AND (created_at >= '2026-09-01'))
              Rows Removed by Filter: 1843920
        ->  Index Scan using customers_pkey on customers_customer
              Filter: (country = 'DE')
Buffers: shared hit=12034 read=48210

The time is spent walking the created_at index backwards and discarding 1.8 million rows that fail the filter. An index matching both filter and order, such as (status, created_at DESC), or a partial index on created_at where status = 'paid', lets PostgreSQL find the 50 rows directly.

3. Fix estimates

When rows= (estimate) and actual rows= differ by orders of magnitude, the planner chooses badly. Refresh statistics:

ANALYZE orders_order;

For columns that are correlated (for example country and currency), create extended statistics:

CREATE STATISTICS order_country_currency (dependencies) ON country, currency FROM orders_order;
ANALYZE orders_order;

Autovacuum normally keeps statistics fresh; large bulk loads may need a manual ANALYZE.

4. Fix the queryset when the plan shows waste

Some plans reveal ORM-level problems rather than missing indexes:

• Large width and many columns: use .only() or .values() to fetch fewer columns.
• count() on huge tables for pagination: use keyset pagination or an estimated count.
• OR across different columns preventing index use: split into two queries with union().
• __icontains causing sequential scans: use trigram indexes or full-text search.

5. Run EXPLAIN safely on production

EXPLAIN ANALYZE actually executes the query. For SELECT, that is safe but costs the query's runtime. For updates and deletes, wrap it in a transaction and roll back:

from django.db import transaction

with transaction.atomic():
    print(Order.objects.filter(status="stale").explain(analyze=True))   # select only
    transaction.set_rollback(True)

Run heavy explains on a replica or during low traffic. auto_explain can log plans of slow queries automatically in production.

Explanation

PostgreSQL's planner turns SQL into a tree of operations and picks the cheapest according to its cost model, which depends on table statistics. EXPLAIN shows that tree; ANALYZE adds what actually happened when it ran; BUFFERS shows memory and disk access. Django's explain() compiles the queryset to SQL and passes these options through, so you see the plan for exactly the query the ORM generates, including joins from select_related and filters from related lookups.

Edge cases / notes

• Parameters matter. Plans for status='paid' and status='refunded' can differ; test with realistic values.
• Cold cache. The first run reads from disk; repeat to see warm-cache timing, but remember production may be cold.
• Visual tools. Paste explain(format="json", analyze=True) output into a plan visualizer to read large plans more easily.

Next steps

• Turn findings into indexes with A Practical Database Indexing Strategy for Django.
• Find candidates with Find Slow Django Queries With pg_stat_statements.
• Remove repeated queries with Fix Django N+1 Queries.

FAQ

Does QuerySet.explain() run the query?

Only with analyze=True. Without it, PostgreSQL returns an estimated plan.

Is it safe to run EXPLAIN ANALYZE in production?

For SELECT queries, yes, but it costs the query's runtime. For writes, run it in a transaction you roll back.

What does Rows Removed by Filter mean?

Rows the database read and then discarded. A large number means the index does not match the filter.

Why does the planner ignore my index?

Statistics may be stale, the filter may not be selective, or the index columns may not match the query's filter and order.