4 Ways to Find the Top Queries in PostgreSQL

The database is slow, CPU is at 90%, and someone asks, “Which query is doing this?”

PostgreSQL can tell you, as long as you know where to look. This guide shows you how to find:

• The queries running right now and how long they’ve been going.
• The queries that cost the most over time, which is usually where the real problem is.
• The query behind a busy process you spotted in Linux top.

First: “Top Query” Can Mean Two Different Things

Picture two queries:

• Query A is a big report. It takes 10 seconds and runs once a day.
• Query B is a small lookup. It takes 2 milliseconds but runs 5 million times a day.

Which one is your “top query”?

Query A is the slowest. Query B uses about 10,000 seconds of database time a day, roughly a thousand times more than Query A. Speeding up Query B by just 1 ms would save more time than deleting Query A completely.

So when you look for top queries, check both:

• Total time: which query uses the most database time overall.
• Average time: which query is the slowest each time it runs.

Method 1: See What’s Running Right Now (pg_stat_activity)

This works right away. You don’t need to install anything:

SELECT pid,
       usename,
       datname,
       state,
       now() - query_start AS running_for,
       wait_event_type,
       wait_event,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY running_for DESC;

Example output:

  pid  | usename  | datname |        state        |   running_for   | wait_event_type | wait_event |                   query
-------+----------+---------+---------------------+-----------------+-----------------+------------+-------------------------------------------
 48211 | report   | shop    | active              | 00:12:41.27     | IO              | DataFileRead | SELECT c.name, sum(o.total) FROM orders o JOIN customers c ...
 48390 | app_user | shop    | idle in transaction | 00:03:05.81     | Client          | ClientRead | UPDATE inventory SET qty = qty - 1 WHERE product_id = $1
 48502 | app_user | shop    | active              | 00:00:00.42     |                 |            | SELECT * FROM orders WHERE customer_id = $1

How to read it:

• running_for: how long the current query has been running. Twelve minutes on a web app database is a red flag.
• state:
  • active means it’s running right now.
  • idle in transaction means a transaction was opened and then left sitting there. These are sneaky: they hold locks and stop VACUUM from cleaning up. Look closely at any that last more than a few seconds.
• wait_event_type / wait_event: what the query is waiting on. IO / DataFileRead means it’s reading from disk. Lock means it’s blocked by another session.

Stopping a Runaway Query

Found the culprit? Try the gentle option first:

See also: Mastering the Linux Command Line — Your Complete Linux Training Course

SELECT pg_cancel_backend(48211);

This cancels the current query but keeps the connection open. If that doesn’t work:

SELECT pg_terminate_backend(48211);

This closes the whole connection. Any open transaction is rolled back.

Why You Might See <insufficient privilege>

Regular users can only see the query text for their own sessions. To see everyone’s, connect as a superuser, or grant the built-in role:

GRANT pg_read_all_stats TO monitoring_user;

Method 2: Find the Heaviest Queries Over Time (pg_stat_statements)

pg_stat_activity only shows the present. Once a query finishes, it’s gone. To see which queries have cost the most over hours or days, you need the pg_stat_statements extension. It ships with PostgreSQL, but it’s turned off by default.

Turn It On

1. Add it to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

If something is already listed there, separate the entries with commas.

2. Restart PostgreSQL. A reload isn’t enough for this setting:

sudo systemctl restart postgresql

3. Create the extension in the database you’ll query it from:

CREATE EXTENSION pg_stat_statements;

On Amazon RDS and most other managed services, it’s usually already loaded. You just need step 3.

From now on, PostgreSQL keeps statistics for every query. Similar queries are grouped together: WHERE id = 5 and WHERE id = 9 both become WHERE id = $1, so you see one row per query shape, not millions of separate rows.

Top 10 Queries by Total Time

This is the most useful query in this guide:

SELECT round(total_exec_time::numeric)                              AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2)                            AS avg_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       left(query, 70)                                              AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

 total_ms |  calls  | avg_ms | pct  |                                query
----------+---------+--------+------+-----------------------------------------------------------------------
  8421337 | 4920113 |   1.71 | 61.2 | SELECT * FROM orders WHERE customer_id = $1 AND status = $2
  2104552 |     212 | 9927.13 | 15.3 | SELECT c.name, sum(o.total) FROM orders o JOIN customers c ON ...
   980441 |  610447 |   1.61 |  7.1 | UPDATE inventory SET qty = qty - $1 WHERE product_id = $2
   ...

The pct column shows each query’s share of all database time. In this example, one simple lookup accounts for 61% of all database time, because it runs almost 5 million times. It’s exactly the “Query B” case from earlier. An index on (customer_id, status) would likely fix most of the load.

Top 10 by Average Time (Slowest per Run)

SELECT round(mean_exec_time::numeric, 2) AS avg_ms,
       calls,
       left(query, 70) AS query
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 10;

The calls > 10 filter skips one-off queries, like a migration someone ran once by hand.

Top 10 by Disk Reads

If the server is held back by I/O rather than CPU, sort by blocks read from disk:

SELECT shared_blks_read,
       shared_blks_hit,
       round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
       left(query, 70) AS query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

A low cache_hit_pct means the query keeps reading data that isn’t in memory. That often points to a missing index or a large sequential scan.

Start Fresh

Statistics keep adding up until you reset them. After fixing something, reset them so you can measure the improvement cleanly:

SELECT pg_stat_statements_reset();

Version note: on PostgreSQL 12 and older, the columns are called total_time and mean_time instead of total_exec_time and mean_exec_time.

Method 3: From Linux top to the Exact Query

PostgreSQL runs every connection as its own Linux process. So when top shows a postgres process eating CPU, you can find out exactly what it’s doing.

Run top and filter to the postgres user:

top -c -u postgres

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
48211 postgres  20   0  4.2g   1.1g  1.0g R  98.7   7.0  12:38.44 postgres: report shop 10.0.0.15(51234) SELECT

Copy the PID, 48211, and look it up:

SELECT pid, usename, now() - query_start AS running_for, query
FROM pg_stat_activity
WHERE pid = 48211;

Now you know the user, how long it’s been running, and the full SQL. The process title in top already gives you a hint (user report, database shop, command SELECT), but pg_stat_activity gives you the whole picture.

Method 4: Log Slow Queries Automatically

To keep a record of every slow query with the exact values used, turn on slow query logging:

ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

This doesn’t need a restart. Any statement that takes longer than 500 ms is now written to the PostgreSQL log:

LOG:  duration: 1843.221 ms  statement: SELECT * FROM orders WHERE created_at > '2026-01-01' ORDER BY total DESC

Start with a high threshold and lower it carefully. Setting it to 0 logs every statement, and on a busy server that can fill up your disk fast.

Found It. Now What?

Once you know which query is the problem, ask PostgreSQL how it runs that query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid';

Look for Seq Scan on big tables, row estimates that are far from the actual row counts, and large numbers of buffer reads.

Be careful: EXPLAIN ANALYZE actually runs the query. For UPDATE or DELETE, wrap it in a transaction you roll back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK;

Summary

Question	Tool
What’s running right now?	pg_stat_activity
Which queries cost the most overall?	pg_stat_statements, sorted by total_exec_time
Which queries are slowest per run?	pg_stat_statements, sorted by mean_exec_time
What is this busy Linux process doing?	top, then look up the PID in pg_stat_activity
Keep a history of slow queries	log_min_duration_statement

If you only set up one thing, make it pg_stat_statements. The next time someone asks which query is slowing everything down, you’ll be able to answer with numbers instead of guesses.