PostgreSQL performance: finding the slow query

When a PostgreSQL database feels slow, the first guess is often that the database is too small or the server is underpowered. Sometimes that is true. More often, one query is reading far more rows than it needs, or it is waiting for another query to release a lock. This guide walks through the usual suspects, and then shows how to find the one query that is causing the trouble. If you want to try things out first, the two free tools at the end let you do that in your browser.

How to find the slow query

The search works best from the outside in. Start with the whole system, then narrow down to one statement. Reading the plan itself has its own guide: how to read a PostgreSQL EXPLAIN plan.

1. Find the queries that cost the most overall

The pg_stat_statements extension keeps a running record of every query: how often it ran, how long it took, and how many rows it returned. To use it, add the extension to shared_preload_libraries and create it in your database. Then run the query below. Sorting by total time matters, because a query that takes 5 ms but runs a million times a day can hurt more than one that takes 30 seconds and runs once.

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

2. See what is running right now

pg_stat_activity shows every connection and what it is doing at this moment. Filter out the idle ones and sort by how long each query has been running. The wait_event_type column tells you whether a query is waiting on a lock, on I/O, or on something else.

SELECT pid,
       now() - query_start AS running_for,
       state,
       wait_event_type,
       left(query, 100)    AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY running_for DESC;

3. Check whether something is blocking it

If a query is stuck waiting, the thing to fix is usually the query holding the lock, not the one waiting. pg_blocking_pids() tells you which process is holding up a given connection. Often the answer is a transaction someone forgot to commit or roll back.

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       left(query, 100)      AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

4. Let the server tell you about slow statements

You do not have to catch slow queries by hand. The setting log_min_duration_statement writes any statement that runs longer than a threshold to the server log. Something like 500 ms is a sensible starting point. The auto_explain module goes a step further and logs the plan of those slow statements, so you can see why they were slow without running them again.

5. Read the plan with EXPLAIN ANALYZE

Once you have a suspect query, ask PostgreSQL how it actually ran. EXPLAIN (ANALYZE, BUFFERS) runs the statement and prints the plan, with timings and row counts for each step, plus the number of buffer pages it read. Read the plan from the innermost step outward. A few things tend to stand out:

• A Seq Scan on a large table that discards most of its rows. An index on the filter column is worth testing.
• Estimated rows far from actual rows. That usually means the statistics need refreshing.
• A Sort that goes to disk, or a Nested Loop repeated thousands of times.
• A large Buffers: shared read figure compared with the number of rows returned. The query is doing far more I/O than the result justifies.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 4821
ORDER BY created_at DESC
LIMIT 20;

One warning before you run this on production. EXPLAIN ANALYZE really executes the statement. If the statement is an UPDATE or a DELETE, wrap it in a transaction and roll it back, so the test leaves your data alone.

6. Change one thing, then measure again

Once you know the cause, the usual suspects list shows the common fixes. Resist the urge to fix three things at once. Add one index, or rewrite one query, or run ANALYZE, and then run EXPLAIN ANALYZE again. Compare the time and the buffers. Keep in mind that an index speeds up reads but slows down every insert and update, because the index has to be maintained as well. Check the write path before you decide to keep it.

The usual suspects

Once you have the slow query and its plan, the plan usually points to one of the causes below. Read this section after the steps, when you know which symptom you are looking at.

A full scan on a big table. If the column you filter on has no index, PostgreSQL has to read every row and check each one. That is harmless on a table with a thousand rows and painful on one with ten million. In a query plan, it shows up as a Seq Scan that throws away most of the rows it reads.

An index that exists but is not used. This one surprises people. Wrapping the column in a function, as in WHERE lower(email) = ..., hides the plain column from a normal index. A similar thing happens when you compare a text column with a number. An expression index on lower(email) solves the first case, and matching the types solves the second.

Statistics that have gone stale. PostgreSQL picks a plan based on what it knows about the data. After a big import or a lot of deletes, that knowledge can be out of date, and the planner may pick a nested loop where a hash join would be much faster. Compare the estimated row counts in the plan with the actual ones. If they are far apart, run ANALYZE on the table and check again.

Sorts that spill to disk. A sort that does not fit in work_mem is written to a temporary file on disk. The plan says so explicitly with Sort Method: external merge Disk. Sometimes an index that already stores the rows in the right order removes the sort altogether.

Deep pagination. OFFSET 100000 does not skip rows for free. PostgreSQL still reads those 100,000 rows and throws them away. Keyset pagination, where the next page starts after the last id you saw, stays fast no matter how deep you go.

N+1 queries from an ORM. An object-relational mapper can quietly turn one request into hundreds of queries. It loads a list of orders, then runs one more query per order to fetch its items. Each query is quick, but together they are slow. The log gives it away: the same statement shows up again and again with different ids. The fix is usually a join or a single batched query.

Lock waits. One transaction that stays open for too long, especially one marked idle in transaction, can hold locks that other queries need. Those queries look slow, but really they are just waiting.

Too many connections. Every connection costs memory and CPU, and past a certain point, adding more makes everything slower. If the application opens hundreds of connections at once, a pooler such as PgBouncer, or a smaller pool inside the application, often helps more than any query tuning.

Bloat and missed vacuums. Updates and deletes leave old row versions behind until VACUUM cleans them up. A table full of dead rows is slower to read, and a long-running transaction can stop vacuum from doing its job.

Practise these steps with our free tools

You can learn most of this without a server. Both tools below run PostgreSQL inside your browser tab, and nothing you type is uploaded.

The PostgreSQL Playground runs PostgreSQL 16 with a small shop database of customers, products, orders and order items. It is a good place to try the queries in this guide, run EXPLAIN ANALYZE, and see how the rows are read. You can also import your own CSV if you want to work with data closer to your own. Keep in mind that it is a practice database, so it does not expose the statistics views of a real server.

The PostgreSQL Query Performance Lab is for the question of which index to build. Give it one SELECT, and it tries a set of candidate indexes one at a time: B-tree, hash, BRIN, GIN, multi-column indexes in both orders, partial and expression indexes. For each one it shows whether PostgreSQL used it, how long the query took, and how many rows and pages were read. It also draws each plan step by step, which makes terms like Seq Scan and Bitmap Heap Scan easier to picture. The lab also compares pgvector HNSW and IVFFlat indexes for nearest-neighbour search.

Here is one way to use them together. Take a slow query from your own system, paste a similar one into the Playground, and run EXPLAIN ANALYZE. Find the plan step that reads the most rows. Then open the Query Performance Lab with a similar filter and see which index removes that step.

A note on permissions and settings

Some of this needs the right privileges. pg_stat_statements and pg_stat_activity only show other users' queries to roles that are allowed to see them. On managed services, such as a cloud database, settings like shared_preload_libraries are usually changed in the provider's console rather than in postgresql.conf. The provider's documentation has the exact steps for your version.

Questions