How to Find Slow Queries From the Command Line the Right Way

"The database is slow" usually means a handful of queries, and the work is finding which ones. Both PostgreSQL and MySQL keep the evidence, if you have asked them to, and the command line is enough to read it. The method is the same for both: rank by total time, look at the worst few, then look at their plans. This post gives the setup and the queries. It follows debugging high load and uses the clients from psql and the mysql client. Examples use PostgreSQL 17 and MySQL 8.4.

PostgreSQL: pg_stat_statements

sudo -u postgres psql -c "alter system set shared_preload_libraries = 'pg_stat_statements'"
sudo systemctl restart postgresql
psql -d app -c "create extension if not exists pg_stat_statements"

The module must be preloaded, which needs a restart. If shared_preload_libraries already has entries, append to the list instead of replacing it. Afterward the view collects statistics for each normalized query (constants replaced by placeholders).

psql -d app -x -c "
select calls, round(total_exec_time::numeric/1000, 1) as total_s,
       round(mean_exec_time::numeric, 2) as mean_ms, rows, left(query, 90) as query
from pg_stat_statements order by total_exec_time desc limit 5"

calls   | 412008
total_s | 1892.4
mean_ms | 4.59
rows    | 412008
query   | select * from sessions where token = $1

Order by total_exec_time first. The top row here is a 4.6 ms query run 400,000 times, which costs more in total than any slow report, and likely needs an index on token. Then order by mean_exec_time to find the individually slow ones. Reset with select pg_stat_statements_reset() after a change to measure its effect over a clean period.

PostgreSQL: the log and live view

psql -c "alter system set log_min_duration_statement = '500ms'" -c "select pg_reload_conf()"
psql -d app -c "select pid, now() - query_start as running, state, left(query, 80) from pg_stat_activity where state = 'active' and now() - query_start > interval '5 seconds' order by running desc"

With log_min_duration_statement, any statement over 500 ms is written to the server log with its duration and the full text and parameters. pg_stat_activity shows what is running now; a long list of active queries is a problem in progress, and the same view helps with killing long-running queries.

MySQL: the slow query log

mysql -e "set global slow_query_log = 1; set global long_query_time = 0.5; set global log_queries_not_using_indexes = 0"
mysql -e "show variables like 'slow_query_log_file'"
sudo mysqldumpslow -s t -t 10 /var/lib/mysql/web1-slow.log

Count: 18241  Time=0.62s (11310s)  Lock=0.00s (2s)  Rows=1.0 (18241), app[app]@localhost
  SELECT * FROM orders WHERE customer_id = N AND status = 'S'

mysqldumpslow groups queries with the same shape (numbers become N and strings 'S') and sorts by total time (-s t), count (-s c) or average (-s at). Percona's pt-query-digest gives a fuller report. Settings made with set global last until restart; add them to the config file to keep. A very low long_query_time on a busy server floods the log, so use it for short windows. The Performance Schema offers a live table: select digest_text, count_star, sum_timer_wait/1e12 as total_s from performance_schema.events_statements_summary_by_digest order by sum_timer_wait desc limit 5.

Read the plan

psql -d app -c "explain (analyze, buffers) select * from sessions where token = 'abc123'"
mysql app -e "explain analyze select * from orders where customer_id = 42 and status = 'shipped'"

Look for a sequential scan (Seq Scan in PostgreSQL, type: ALL in MySQL) on a large table with a selective filter, a large gap between estimated and actual rows, and sorts that spill to disk. ANALYZE actually runs the statement, so do not use it on a destructive statement without a transaction and rollback. Adding the index and re-running the plan shows the effect, and the statistics view shows it fleet-wide.

Where this does not apply

Statistics describe the past; a problem that is happening now needs pg_stat_activity or SHOW FULL PROCESSLIST. Managed databases expose the same data through their own consoles, with some settings locked. The cause of slowness is sometimes not a query at all, but a lock wait, a connection pool or the disk; check wait events (wait_event in pg_stat_activity). Normalized text hides parameters, so one slow value can hide inside a fast average.

Finding the queries that cost the most

The first question is not which query is slow, but which queries cost the most in total. A query that takes 20 milliseconds and runs a million times an hour uses more database time than one that takes ten seconds and runs twice a day. In PostgreSQL the pg_stat_statements extension tracks this: it must be listed in shared_preload_libraries, which needs a restart, and created in the database with CREATE EXTENSION. Then a query on the view, sorted by total_exec_time descending, shows the top consumers, with calls, mean_exec_time and the normalized query text with parameters replaced by placeholders. The column names changed in PostgreSQL 13, when total_time became total_exec_time, so check the version. Reset the counters after a change with pg_stat_statements_reset() to measure its effect over a clean window. Sorting by mean time instead finds the individual slow statements, and sorting by calls finds the chatty ones.

A high total with a low mean points at a query that is fine alone and called too often, and the fix is in the application, batching or caching, and not in the database.

Logging and live views

For individual slow statements with their parameters, set log_min_duration_statement to a threshold such as 500 milliseconds, and PostgreSQL logs every statement slower than that, with the duration. The log is the place to see actual values and the context, and reading it with a tool such as pgBadger turns it into a report. The auto_explain module logs the plan of slow queries automatically, which captures the plan at the moment of slowness and not a later one that differs. For what is running now, query pg_stat_activity for active statements ordered by how long they have been running, including the wait event, which tells you whether a query is working or waiting on a lock or on disk. A query that is slow because it is blocked looks different from one that is slow because it is expensive, and the wait columns separate them. Check how much logging costs: a threshold of zero on a busy server writes enormous logs and slows it down.

MySQL's slow log and reading the plan

MySQL's equivalent is the slow query log, enabled with slow_query_log and a long_query_time threshold in seconds, which accepts fractions. It records the time, the lock time, the rows examined and the rows returned, and a large ratio of rows examined to rows returned is a clear sign of a missing index. The mysqldumpslow script summarizes the log, and pt-query-digest from the Percona toolkit does it better, ranking queries by total time. Performance schema tables give the same information live. To see why a statement is slow, read its plan. EXPLAIN shows the plan without running it, and EXPLAIN ANALYZE, in PostgreSQL as EXPLAIN (ANALYZE, BUFFERS) and in MySQL 8.0.18 and later, runs the statement and reports actual times and row counts beside the estimates. Because it really executes, use it inside a transaction that you roll back for statements that write. Large differences between estimated and actual rows mean the statistics are stale and an ANALYZE is due.

Acting on what you find

A ranked list is the start. For each of the top few statements, read the plan, look for a sequential scan on a large table, a sort that spills to disk or a join that estimates a handful of rows and returns millions, and fix the cause: an index, a rewritten condition, a changed join order, fresher statistics, or a smaller result. Test the change on a copy with realistic data, since a query that is fast on a small table can behave differently on a large one, and measure again in the statistics view after deploying. Add an index only when the plan shows it will be used, because every index slows writes and uses space. Keep a note of what you changed and the before and after times, so the next person who sees a regression can tell what was already tried.

See the pg_stat_statements documentation and the MySQL slow query log.