03. Single queryid analysis
Deep dive into one query's performance timeline. Use this after identifying a problematic query in the top-N dashboard.
When to use
- A specific query is slow and you need to understand its behavior over time
- Validating a fix — compare before and after a query or index change
- Investigating a regression — when a query suddenly got slower
Key panels
- Query latency over time — execution time per call, showing trends and spikes
- Calls over time — execution frequency, useful for detecting workload changes
- Rows over time — rows returned or affected per execution
- Wait distribution — what the query waits on (CPU, IO, Lock, Client)
- Buffer hit/read ratio — cache effectiveness for this specific query
- Planning vs execution time — breakdown of where time is spent
How to select a query
Use the queryid variable at the top of the dashboard. You can find queryids from:
- The top-N dashboard (click on a query)
- Direct query:
SELECT queryid, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
What good looks like
- Latency is stable and within your application's SLA
- Waits are mostly CPU (computation) rather than IO or Lock
- Buffer hit ratio is high (>95% for OLTP queries)
- No sudden jumps in execution time or row count
What to investigate
| Signal | Next step |
|---|---|
| High lock waits | Go to Lock contention |
| High IO waits | Check if the query needs a better index or if shared_buffers is undersized |
| Sudden latency spike | Correlate with deployment or schema change timing |
| Increasing rows per call | Table growth may require query adjustments or partitioning |
Related Checkup checks
- D004 — pg_stat_statements and pg_stat_kcache settings (CLI)
- K003 — top queries by total time (monitoring stack)
- M001 — top queries by mean execution time (monitoring stack)
- M003 — top queries by I/O time (monitoring stack)