Find Slow Django Queries with pg_stat_statements

Problem statement

The site is slow and the database is the suspect, but you do not know which queries matter. Slow query logs show individual outliers; APM traces show single requests. Neither tells you which statements consume the most database time overall, and that is where optimisation pays off: a 5 ms query run 2 million times a day costs more than a 3 second report run twice.

pg_stat_statements is a PostgreSQL extension that aggregates statistics for every normalised statement: how often it runs, total and average time, rows returned, and cache hits. This guide enables it, ranks queries for a Django app, maps them back to code, and fixes the top offenders.

1. Add pg_stat_statements to shared_preload_libraries and restart PostgreSQL (managed services: parameter group or flag).
2. Run CREATE EXTENSION IF NOT EXISTS pg_stat_statements; in the application database.
3. After representative traffic, list the top statements by total_exec_time, then by mean_exec_time and calls.
4. Identify the ORM code from table names, filters, and application_name; reproduce the queryset in manage.py shell.
5. Run queryset.explain(analyze=True, buffers=True) to see the plan.
6. Fix with indexes, select_related/prefetch_related, only(), pagination, or caching; reset stats and measure again.

Step-by-step solution

1. Enable the extension

Self-managed PostgreSQL:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top
pg_stat_statements.max = 10000

sudo systemctl restart postgresql
sudo -u postgres psql -d myapp -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Loading the library requires a restart; creating the extension makes the view available in that database. Managed services usually preload it already (Amazon RDS does by default); you still run CREATE EXTENSION once.

The overhead is small for typical Django workloads, which is why it is commonly left enabled permanently.

2. Rank by total time

SELECT round(total_exec_time::numeric / 1000, 1) AS total_s,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       left(regexp_replace(query, '\s+', ' ', 'g'), 150) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 15;

Column names are for PostgreSQL 13 and newer (total_exec_time, mean_exec_time); older versions use total_time and mean_time.

Read the result in three ways:

• High total, low mean, huge calls: an N+1 pattern or a query in a hot path. Fix by batching or caching.
• High mean: a slow individual query. Fix with an index or a rewrite.
• Low hit_pct: data not in memory; the query reads from disk. Often an index problem, sometimes memory sizing.

Statements are normalised: literal values become $1, $2, so all executions of the same ORM query shape are grouped together.

3. Map statements to Django code

Django's SQL is recognisable. "orders_order"."customer_id" = $1 points to Order.objects.filter(customer=...) or a reverse relation. LIMIT 21 is Model.objects.get(), which fetches up to 21 rows to detect multiple results. A burst of identical WHERE "catalog_product"."id" = $1 statements with millions of calls is usually a missing select_related in a list view.

To see which process sends it, label connections with application_name per process type (web, Celery, cron) and check current activity:

SELECT application_name, count(*) FROM pg_stat_activity
WHERE query ILIKE '%orders_order%' GROUP BY 1;

APM tools or OpenTelemetry tracing link SQL to views directly when you need precise attribution.

4. Look at the plan

Reproduce the queryset and ask PostgreSQL how it executes it:

# python manage.py shell
from orders.models import Order

qs = Order.objects.filter(status="pending", customer__country="DE").order_by("-created")[:50]
print(qs.explain(analyze=True, buffers=True))

analyze=True runs the query; do not run it on statements that write. Look for:

• Seq Scan on a large table with a selective filter: a missing index.
• Rows Removed by Filter much larger than rows returned: the index does not match the filter.
• A Sort on many rows followed by Limit: an index on the ordering column (or on filter plus ordering) avoids sorting.
• Estimated rows far from actual rows: stale statistics; run ANALYZE on the table.

5. Apply the fix

Add an index that matches the query:

class Order(models.Model):
    ...

    class Meta:
        indexes = [
            models.Index(fields=["status", "-created"], name="order_status_created_idx"),
        ]

For large, busy tables, create it without blocking writes:

from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models


class Migration(migrations.Migration):
    atomic = False
    dependencies = [("orders", "0031_previous")]
    operations = [
        AddIndexConcurrently("order", models.Index(fields=["status", "-created"], name="order_status_created_idx")),
    ]

Reduce query count: select_related for foreign keys, prefetch_related for reverse and many-to-many relations.

Reduce work per query: only() or values() for wide tables, pagination instead of loading everything, exists() instead of count() when you only need a yes or no, and caching for data that changes rarely.

6. Measure again

SELECT pg_stat_statements_reset();

Resetting (requires appropriate privileges) lets you compare a clean period after the fix with the numbers before it. Record the top statements before resetting so you have a baseline.

Verification check:

Rerun the ranking query after a representative period. The statement you fixed should have dropped in total time, and overall database CPU or latency should reflect it.

Explanation

Most database cost in a Django application comes from a small number of query shapes executed very often. pg_stat_statements groups executions by normalised statement, so the ORM's repetitive queries collapse into single rows with meaningful totals. Ranking by total time aligns optimisation with actual cost, and EXPLAIN ANALYZE shows why a specific shape is expensive. Together they turn "the database is slow" into a short, ordered list of fixes.

Edge cases / notes

Statement text and comments

The stored text is the first execution seen for that normalised statement. Tools that add SQL comments with the view name can help tracing, but pg_stat_statements groups by statement structure, so the comment you see belongs to only one of the callers.

Replicas

Statistics are per server. Queries routed to read replicas appear in the replica's pg_stat_statements, not the primary's.

When to automate this

Snapshot the top 20 statements daily into a table or your monitoring system, and alert when a new statement enters the top list after a deploy. That catches performance regressions from new code within a day.

Next steps

• Eliminate repeated queries with Find and Fix N+1 Queries in Django.
• Profile the Python side with Profile Slow Django Endpoints in Production with py-spy.
• Review database settings with the PostgreSQL Production Checklist for Django.
• Apply the findings to the admin with Fix a Slow Django Admin on Large Tables.

FAQ

How do I enable pg_stat_statements?

Add it to shared_preload_libraries, restart PostgreSQL, and run CREATE EXTENSION pg_stat_statements; in the database.

Should I sort by total time or mean time?

Start with total time to find what costs the most overall; then look at mean time for individually slow queries.

How do I find which Django code produced a query?

Match table and column names to models, set application_name per process type, and use tracing or APM for exact views.

Is pg_stat_statements safe to leave on in production?

Yes. Its overhead is small, and it is commonly enabled permanently on production databases.