Postgres will tell you why it is slow, if you ask it properly

The most common performance conversation we have with a new client goes: "the app is slow", "which part", "all of it". It is almost never all of it. It is usually four queries, and Postgres will name them for you.

Find them first

pg_stat_statements is in every managed Postgres and off by default in most self-hosted ones. Turn it on.

SELECT calls, mean_exec_time, total_exec_time, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Order by total_exec_time, not mean_exec_time. A 2ms query called four million times is a bigger problem than a 900ms report run twice a day, and it is the one nobody looks at because it is individually fast.

Then read the plan

EXPLAIN (ANALYZE, BUFFERS) on the offenders. Four shapes cover most of what we find:

Sequential scan on a large table. Missing index, or an index that cannot be used because the predicate wraps the column in a function. WHERE lower(email) = $1 will not use an index on email — it needs an index on lower(email).

Nested loop with a high row estimate error. Look for rows=12 next to actual rows=48000. The planner chose a nested loop because it expected twelve rows. Stale statistics, or a correlation the planner cannot see. ANALYZE first; extended statistics if the columns are genuinely correlated.

Sort spilling to disk. Sort Method: external merge Disk: 84032kB. Either work_mem is too low for this query, or you are sorting far more rows than you return — the classic ORM ORDER BY before a LIMIT across a join that multiplies rows.

The N+1 that is not in the plan at all. Each individual query looks perfect. There are four thousand of them per request. This is the most common one in Django codebases and EXPLAIN will never show it — django-debug-toolbar or pg_stat_statements call counts will.

The Django-specific traps

select_related for forward foreign keys, prefetch_related for reverse and many-to-many. Getting this wrong is the single largest source of N+1 we find.

.count() on a queryset you are about to iterate anyway runs a second query. len() on an evaluated queryset does not.

.exists() instead of if queryset: when you only need the boolean — it adds a LIMIT 1 and skips fetching rows.

Serializer methods that touch a related object break your prefetch if they filter in Python versus the database. [t for t in obj.technologies.all() if t.is_active] uses the prefetch; obj.technologies.filter(is_active=True) issues a fresh query per object and quietly undoes the whole thing.

That last one has bitten us in production more than any other item on this list. It looks more correct.

Index discipline

Add indexes from query plans, not from intuition. Every index costs write throughput and disk. We have removed more unused indexes from client databases than we have added — pg_stat_user_indexes with idx_scan = 0 after a representative period is a list of things to drop.