Top queries by execution time
pg_top_queriesIdentify and rank the slowest SQL queries by total or mean execution time, with detailed metrics, so you can pinpoint and optimize performance bottlenecks.
Instructions
Top N queries by total or mean execution time. Requires the pg_stat_statements extension to be installed and enabled (most managed Postgres providers have it on by default). Returns {rows, stats_reset, stats_reset_age_seconds, dealloc}: each row has normalized query text (constants replaced with ?), call count, total/mean/min/max time in ms, rows returned, and cache hit ratio. Use this to find slow queries worth optimizing.
calls and total_time_ms are cumulative since the last pg_stat_statements_reset(), so this ranking only describes the window that started at the top-level stats_reset (with stats_reset_age_seconds beside it). This is pg_stat_statements' OWN reset clock, read from pg_stat_statements_info -- it is independent of the stats_reset reported by pg_seq_scan_tables / pg_unused_indexes, which comes from pg_stat_database, so do not compare the two timestamps or assume one implies the other. stats_reset: null means the start of the window is unknown, not that it covers all time.
READ dealloc BEFORE TRUSTING THE RANKING: it counts how many times entries for the LEAST-EXECUTED statements were evicted because more distinct statements were seen than pg_stat_statements.max allows. A non-zero dealloc means this ranking is drawn from an INCOMPLETE population -- queries may be missing from these results entirely, and an evicted query's counters restart from zero if it runs again, understating it. The larger dealloc is, the more churn, so 'not in the top N' stops being evidence that a query is cheap. Raise pg_stat_statements.max to get a complete picture.
On pg_stat_statements < 1.9 (before Postgres 14) pg_stat_statements_info does not exist, so stats_reset, stats_reset_age_seconds and dealloc are omitted entirely rather than returned as nulls, and a _warnings entry says so.
On pg_stat_statements >= 1.10 (Postgres 15+), also returns io_read_time_ms and io_write_time_ms to separate IO-bound from CPU-bound queries (null when track_io_timing = off or the query did no measurable IO -- enable track_io_timing in postgresql.conf to get non-null values). Scoped to the database in DATABASE_URL: pg_stat_statements is cluster-wide, so results are filtered by dbid to match every other tool here rather than leaking query text from unrelated databases sharing the cluster.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | Number of rows to return (default 20). | |
| orderBy | No | Ranking: total_time (cumulative impact), mean_time (worst per-call), or calls (hottest). | total_time |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| rows | Yes | ||
| dealloc | No | Times least-executed entries were evicted for exceeding pg_stat_statements.max. Non-zero means this ranking is drawn from an INCOMPLETE population. Absent below extension 1.9. | |
| _warnings | No | ||
| stats_reset | No | pg_stat_statements' OWN reset clock, independent of the pg_stat_database one the table/index tools report -- never compare the two. Absent below extension 1.9. | |
| stats_reset_age_seconds | No |