Why is my PostgreSQL query slow? A checklist that starts with EXPLAIN

Short answer: run EXPLAIN (ANALYZE, BUFFERS) on the query and look for where the time goes. Most slow queries come from a missing or unusable index, a bad row estimate from stale statistics, returning far more rows than you need, a lock wait that is not really about the query, or a connection pool that is starved. Find which one before you change anything.

How do you find the slow queries first?

Enable the pg_stat_statements extension. It records time and call counts per normalised query.

SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 1)  AS mean_ms,
       left(query, 80)                    AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

A query that takes 5 ms and runs a million times costs more than one that takes two seconds and runs twice. Sort by total time to find what matters.

How do you read EXPLAIN ANALYZE?

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Read from the innermost node outward. For each node, compare three things:

• Estimated rows vs actual rows. rows=10 estimated against actual rows=250000 means the planner is working from wrong statistics and will choose badly.
• Actual time, which is per loop. Multiply by loops for the real cost.
• Buffers: shared read is data fetched from disk, shared hit from memory. Heavy reads point to a cold cache or a scan reading too much.

EXPLAIN ANALYZE actually runs the query. Wrap data-changing statements in a transaction you roll back.

Is a sequential scan always bad?

No. For a small table, or a query that returns a large fraction of it, a sequential scan is the fastest plan. It is a problem when a large table is scanned to return a few rows. That is where an index helps.

Why is my index not used?

• Function on the column: WHERE lower(email) = ... cannot use a plain index on email. Create an index on lower(email).
• Type mismatch forcing a cast on the column.
• Leading wildcard: LIKE '%abc' cannot use a normal B-tree. Use a trigram index with pg_trgm.
• Wrong column order in a composite index. An index on (a, b) helps WHERE a = ? and WHERE a = ? AND b = ?, not WHERE b = ? alone.
• Low selectivity: the planner correctly decides the index is not worth it.
• Stale statistics: run ANALYZE tablename.

What does a good fix look like?

For the query above, a composite index that matches both the filter and the sort.

CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

CONCURRENTLY avoids locking writes on a live table. It takes longer and cannot run inside a transaction block.

What if the query is fine but the app is slow?

• N+1 queries: the app runs one query per row of a list. The logs show thousands of tiny identical statements. Fetch in one query with a join or WHERE id = ANY($1).
• Lock waits: check pg_stat_activity for rows where wait_event_type = 'Lock'. A long-running transaction left open by a worker can block everything else.
• Pool exhaustion: the database is idle but requests queue in the app waiting for a connection. See connection pooling.
• Returning too much: SELECT * on wide rows with large text columns. Select only what you need.
• Row-level security policies adding joins to every query. Test the plan as the application role.

What about bloat and autovacuum?

Updates and deletes leave dead rows. Autovacuum cleans them. If a hot table is updated heavily and autovacuum falls behind, tables and indexes grow and scans slow down. Check n_dead_tup in pg_stat_user_tables and the last_autovacuum time. Long-running transactions prevent cleanup, which is another reason to find and fix them.

A short routine

• Find the top queries by total time.
• Run EXPLAIN (ANALYZE, BUFFERS) and compare estimated and actual rows.
• Fix the cause: index, query shape, statistics.
• Measure again. Keep the before and after numbers.

Change one thing at a time. Otherwise you will not know what helped.