pg_stat_statements
A Postgres extension that collects per-query statistics: how many times it ran, how long it took, how many buffers it read. ide99 renders it as a top-queries table with filters and quick jumps to EXPLAIN.
Install
The extension ships with Postgres but isn't on by default.
- Add to
postgresql.conf:shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
- Restart Postgres.
- In the target DB:
CREATE EXTENSION pg_stat_statements;
The extension needs superuser to install. On managed Postgres (Supabase, Neon, RDS) it's usually already on.
After install, a new view appears: pg_stat_statements. ide99 detects it automatically and activates the tab.
Open
In the tab header: + New tab → Top queries (name may vary). Or from the Health screen — the View slow queries button.
What's shown
| Field |
Meaning |
| Query |
Query text (normalized — literals replaced with $1, $2, ...) |
| Calls |
Total invocations |
| Total time |
Sum of all run times (ms) |
| Mean time |
Average (ms) |
| Stddev |
Spread (ms) |
| Rows |
Total rows returned |
| Hit % |
% of reads from shared buffers |
| Time / row |
Mean time / Rows — estimated cost per row |
Sorting
At the top — an Order by switch:
- Total time — who ate the most database time (for overall load tuning)
- Mean time — slowest individually (for user experience)
- Calls — most frequent (even fast can compound)
- Rows — biggest row crunchers
Filters
- User — filter by Postgres role
- Database — if stats span multiple DBs
- Min calls — hide queries with few calls (rare background jobs)
- Search — text search in the query (e.g.,
users finds anything mentioning that word)
Per-query actions
Double-click / right-click:
- Show full query — modal with full text
- EXPLAIN — opens the visualizer with this query (literals stay as
$1, ide99 doesn't substitute them)
- Copy — to clipboard
- Reset stats — reset stats for just this query
Reset
The Reset all stats button at the top runs SELECT pg_stat_statements_reset(). Useful before a load test or after a deploy — to see a "fresh" picture.
Don't reset on prod without need — you lose history that you or colleagues might use.
Auto-refresh
An Auto-refresh toggle: off / 30s / 1m / 5m. For routine monitoring, 1m is enough — stats accumulate, no need to refresh every second.
Tip: investigating "the database is slow"
- Open Top queries, sort by Total time.
- Top 3–5 queries = 80% of load.
- For each — EXPLAIN → find the hot node → add an index or rewrite.
- After fixing —
Reset all stats, monitor for a week.
Next