PostgreSQL has no single profiler. Profiling a slow query means using three built-in tools in order, each answering a different question: pg_stat_statements tells you which statements cost the most time, auto_explain captures the plan the server actually used when one was slow, and EXPLAIN (ANALYZE, BUFFERS) lets you reproduce and measure that plan. Then you change one thing and check the same numbers again.

1. Rank statements by total time

Start with the workload, not the query someone complained about. pg_stat_statements groups statements by normalized text and accumulates calls, execution time, rows and buffer use since the last reset:

SELECT queryid, calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 1)  AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       shared_blks_read, temp_blks_written,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

A fast statement that runs 20,000 times often outweighs a slow report that runs twice. High shared_blks_read points at I/O, and temp_blks_written at sorts or hashes spilling past work_mem (measure what the spills cost before raising it). To see what changed during an incident, snapshot the view before and after and compare the difference. The full method, including setup on RDS and Aurora, is in finding slow queries with pg_stat_statements.

If total execution time is low but the application is still slow, the time is going somewhere else: waiting for locks, waiting for a connection from the pool, or in the network. Sample pg_stat_activity for wait_event_type and state, or use Database Insights on RDS, before you tune any plan.

2. Capture the plan the server actually used

Running EXPLAIN later shows the plan for today's statistics and your parameter values, which may not be the plan that was slow. auto_explain logs the plan at the moment a statement exceeds a threshold:

# postgresql.conf, or the DB parameter group on RDS and Aurora
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_format = 'json'          # easier to paste into a plan viewer
auto_explain.sample_rate = 1              # lower it on busy systems

Leave auto_explain.log_analyze off unless you have measured its cost. The documentation warns that with it on, per-node timing runs for every statement, not just the logged ones, which "can have an extremely negative impact on performance". If you need actual row counts, turn it on with auto_explain.log_timing = off and a low sample_rate, for a limited period.

A logged plan from a lab on this site shows why this step matters. The query took 555 ms because the planner walked the created_at index backwards and filtered every row for one small tenant:

LOG:  duration: 555.793 ms  plan:
  Query Text: SELECT id, status, created_at, amount
  FROM orders
  WHERE tenant_id = $1 AND status = 'open'
  ORDER BY created_at DESC
  LIMIT 20;
  Query Parameters: $1 = '1020'
  Limit  (cost=0.43..384.74 rows=20 width=27)
    ->  Index Scan Backward using orders_created_idx on orders  (cost=0.43..128688.77 rows=6697 width=27)
          Filter: ((tenant_id = $1) AND (status = 'open'::text))

The same statement was fast for most tenants, so testing it by hand with a typical value would not have reproduced it. The generic plan regression lab walks through the cause and the fix.

3. Reproduce and measure with EXPLAIN ANALYZE

With the slow parameter values in hand, run the statement with timing and buffer counts. ANALYZE executes the statement, so wrap anything that writes in a transaction you roll back:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT)
SELECT id, status, created_at, amount
FROM orders
WHERE tenant_id = 1020 AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;
ROLLBACK;

From PostgreSQL 18, buffer counts are included automatically with ANALYZE. What to read first:

  • Estimated rows against actual rows on each node. An error of 100× or more usually explains the wrong join type or scan; fix the estimate (statistics, extended statistics, query shape) before reaching for an index.
  • Where the time accumulates. Actual time is cumulative for a node and its children, and is per loop; multiply by loops.
  • Rows removed by filter on a scan: the index found rows the query then threw away.
  • Buffers: shared read came from disk or the OS cache; temp means a spill.

Large plans are hard to read as text. Paste the JSON or text output into PlanReader to see which nodes take the time and where estimates go wrong.

Run it twice and keep the second result if you want warm-cache numbers, and say which you are comparing. On a primary under load, prefer a recent restore or a replica with the same data and settings.

4. Change one thing, then measure the same numbers

Make a single change, such as an index, an ANALYZE, extended statistics or a query rewrite, then repeat steps 3 and 1: the plan and timing for the slow parameter values, and the statement's share of total time across the workload. An index that speeds up one query also costs every write to that table, so check the write-heavy statements in pg_stat_statements too. If the share of total time did not fall, the change did not fix the problem the users felt.

Short version

  1. pg_stat_statements: which statements cost the most total time, and is the time even in the database.
  2. auto_explain: the plan used when it was slow, with the parameter values.
  3. EXPLAIN (ANALYZE, BUFFERS): reproduce, then compare estimates with actual rows and find where time and buffers go.
  4. One change, then the same measurements as before.