EXPLAIN ANALYZE: The PostgreSQL Command Every Django Developer Should Know

Source: Dev.to

Introduction

If you have never read a query execution plan, you are flying blind on database performance. Django’s ORM is excellent at writing SQL, but it does not tell you whether that SQL is fast. To understand performance you need to look at the database’s execution plan.

Using EXPLAIN ANALYZE in Django

PostgreSQL’s EXPLAIN ANALYZE shows the SQL a query produces, how the engine plans to execute it, how long it actually takes, and where the time is spent.

Django 3.2 added QuerySet.explain(), giving direct access to the execution plan:

# In the Django shell (python manage.py shell_plus):
qs = Order.objects.filter(status='paid', user_id=42).select_related('user')
print(qs.explain(verbose=True, analyze=True))

For more control you can run raw SQL:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute(
        '''
        EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
        SELECT * FROM orders_order
        WHERE status = %s AND user_id = %s
        ''',
        ['paid', 42]
    )
    for row in cursor.fetchall():
        print(row[0])

Example Output

Seq Scan on orders_order  (cost=0.00..4821.00 rows=3912 width=52)
                          (actual time=0.05..28.4 rows=3912 loops=1)
  Filter: ((status = 'paid') AND (user_id = 42))
  Rows Removed by Filter: 48231
Planning Time: 0.8 ms
Execution Time: 28.9 ms

Interpreting the Plan

Adding Indexes in Django

Once you identify a missing index, add it in models.py:

# models.py
class Order(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    status = models.CharField(max_length=20)

    class Meta:
        indexes = [
            # Composite index: supports queries filtering on both columns
            # Also supports queries on 'status' alone (leftmost prefix rule)
            models.Index(fields=['status', 'user'], name='order_status_user_idx'),

            # Partial index: only index active orders
            # Smaller, faster, more selective than a full‑table index
            models.Index(
                fields=['user', 'created_at'],
                condition=Q(status__in=['pending', 'paid']),
                name='order_active_user_created_idx',
            ),
        ]

Best Practices

• Run EXPLAIN ANALYZE on any query that will be called frequently in production.
• Watch for:
  • Seq Scans on large tables
  • High “Rows Removed by Filter” ratios
  • Unexpected actual times
• Use django-debug-toolbar during development to see query counts and execution times on each page. Reserve EXPLAIN ANALYZE for queries flagged as slow or unusually frequent.

Conclusion

Database performance is not a black art. Mastering a few fundamentals—reading EXPLAIN ANALYZE, recognizing problematic Seq Scans, selecting appropriate indexes, and measuring before optimizing—will dramatically improve your Django applications.

Further Reading

The concepts above are expanded in Professional Django Engineering (Chapter 7 – Database Performance). The book covers 16 chapters on production‑grade Django development, including architecture, ORM performance, REST APIs, security, testing, caching, async, and deployment.

▶ Available on Amazon KDP (link in comments).