How to analyze slow queries in PostgreSQL

A small number of repeated PostgreSQL statements can consume most of a busy server's execution time while the rest of the workload looks normal. Ranking statements by collected execution time shows which SQL deserves plan review before changing indexes, vacuum settings, or memory limits.

pg_stat_statements aggregates normalized SQL inside the server and records counters such as calls, total_exec_time, mean_exec_time, returned rows, buffer activity, and query text. Slow-query logs show individual events, while this extension shows which statement patterns dominated a measurement window.

Loading pg_stat_statements requires shared_preload_libraries and a PostgreSQL restart because the module uses shared memory. The extension must also be created in each database that should expose the view, and the no-argument reset function clears collected statement statistics for the whole instance. Tracking adds overhead and statement text may expose application details, so run the analysis as a superuser or a controlled role with pg_read_all_stats.

1. Confirm the active configuration file path before editing.

   $ sudo -u postgres psql -Atc "SHOW config_file;"
   /etc/postgresql/18/main/postgresql.conf

2. Add pg_stat_statements to shared_preload_libraries in the active postgresql.conf file.

   shared_preload_libraries = 'pg_stat_statements'

   If other libraries are already listed, append pg_stat_statements separated by a comma.

3. Restart PostgreSQL to load the preloaded extension.

   $ sudo systemctl restart postgresql

   Restarting drops active connections.

   Related: How to manage the PostgreSQL service with systemctl on Linux

4. Confirm that the library was loaded after the restart.

   $ sudo -u postgres psql -Atc "SHOW shared_preload_libraries;"
   pg_stat_statements

5. Create the extension in the database that should be monitored.

   $ sudo -u postgres psql -d appdb -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
   CREATE EXTENSION

   Replace appdb with the target database name, and repeat for each monitored database.

6. Reset collected statement statistics to start a clean measurement window.

   $ sudo -u postgres psql -d appdb -c "SELECT pg_stat_statements_reset();"
      pg_stat_statements_reset    
   -------------------------------
    2026-06-07 05:09:25.469603+00
   (1 row)

   The no-argument reset clears collected pg_stat_statements entries for the whole PostgreSQL instance, not just the current database.

7. Let the application workload or a controlled reproduction run long enough to collect representative statements.

   Use a short, named measurement window when a busy system has mixed traffic, so the ranked rows can be tied back to the incident, release, or load test being investigated.

8. List the slowest statements by total or average execution time.

   $ sudo -u postgres psql -d appdb -c "SELECT calls, round(total_exec_time::numeric,2) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms, rows, left(query,120) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
    calls | total_ms | mean_ms | rows |                                             query                                             
   -------+----------+---------+------+-----------------------------------------------------------------------------------------------
        3 |    70.70 |   23.57 |   30 | SELECT id, status, total FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT $2
        1 |    28.03 |   28.03 |    1 | SELECT count(*) FROM orders WHERE customer_id = $1
        1 |     0.09 |    0.09 |    1 | SELECT pg_stat_statements_reset()
   (3 rows)

   Use ORDER BY total_exec_time DESC to find cumulative cost, or switch to ORDER BY mean_exec_time DESC when rare but individually slow statements matter more. A high calls value with modest mean_ms can still dominate total database time.

9. Validate one top read statement with EXPLAIN (ANALYZE, BUFFERS).

   $ sudo -u postgres psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id, status, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;"
                                                                 QUERY PLAN                                                               
   ---------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=6810.75..6811.91 rows=10 width=64) (actual time=14.269..16.956 rows=10.00 loops=1)
      Buffers: shared hit=4093
      ->  Gather Merge  (cost=6810.75..7009.44 rows=1706 width=64) (actual time=14.268..16.953 rows=10.00 loops=1)
            Workers Planned: 2
            Workers Launched: 2
            Buffers: shared hit=4093
            ->  Sort  (cost=5810.72..5812.50 rows=711 width=64) (actual time=11.926..11.927 rows=10.00 loops=3)
                  Sort Key: created_at DESC
                  Sort Method: top-N heapsort  Memory: 25kB
                  Buffers: shared hit=4093
                  Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
                  Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
                  ->  Parallel Seq Scan on orders  (cost=0.00..5795.36 rows=711 width=64) (actual time=0.015..11.553 rows=1666.67 loops=3)
                        Filter: (customer_id = 42)
                        Rows Removed by Filter: 165000
                        Buffers: shared hit=4017
    Planning:
      Buffers: shared hit=92
    Planning Time: 0.465 ms
    Execution Time: 17.006 ms
   (20 rows)

   EXPLAIN (ANALYZE, BUFFERS) executes the statement; avoid running write queries on production data without an explicit rollback plan.

   Related: How to explain a query plan in PostgreSQL