Skip to content
DBAExperts
PostgreSQL Diagnostics

How to find slow queries in PostgreSQL with pg_stat_statements

Before optimizing, you have to measure. pg_stat_statements tells you which queries consume the most total time; EXPLAIN ANALYZE shows you why a specific query is slow.

  1. 1

    Enable the pg_stat_statements extension

    This requires adding the library to shared_preload_libraries in postgresql.conf and restarting. Then you create the extension.

    -- en postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
-- tras reiniciar:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  2. 2

    List queries by total time

    Sort by total_exec_time to see where the server time goes. Sometimes a fast but very frequent query weighs more than an isolated slow one.

    SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
  3. 3

    Analyze the problem query with EXPLAIN ANALYZE

    EXPLAIN ANALYZE actually runs the query and shows times and rows per node. For write queries, wrap it in a transaction with ROLLBACK so you do not modify data.

    EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pedidos WHERE cliente_id = 42;
  4. 4

    Reset the statistics when you change something

    After applying an improvement, clear the counters to measure the effect over a fresh window and compare clearly.

    SELECT pg_stat_statements_reset();

// common mistake

Reading EXPLAIN without ANALYZE shows the estimated plan, not the real one: the costs are theoretical. And running EXPLAIN ANALYZE on a DELETE or UPDATE outside of a transaction with ROLLBACK actually executes the change.

// when it's worth an expert

When total time is spread across dozens of queries and it is not obvious where to start, manual analysis gets long. At dba.mx we do evidence-based tuning: we measure, prioritize by impact, and validate each change, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.