Slow-Query Rate %, PostgreSQL

​

At a glance

The share of query executions that are “slow”, defined as statements whose mean execution time exceeds 100ms, expressed as a percentage of total calls. Where the percentile cards (p95, p99) tell you how slow the tail is, this card tells you how much of your workload is slow at all. A 2% slow-query rate on a million-call window means roughly 20,000 executions crossed the 100ms line; a 12% rate means more than one query in eight is dragging. It is the breadth metric: it catches the difference between “one report is slow” (narrow, low rate, possibly high p99) and “a large slice of normal traffic has degraded” (broad, high rate). For a DBA it is the early-warning gauge that an index regression, a stale plan, or a cache miss is spreading across the workload.

​

Calculation

delta_calls            = calls(now)          - calls(window_start)
delta_total_exec_time  = total_exec_time(now) - total_exec_time(window_start)
windowed_mean_ms       = delta_total_exec_time / delta_calls

A statement is "slow" for the window if windowed_mean_ms > 100.

slow_calls  = sum of delta_calls over statements where windowed_mean_ms > 100
total_calls = sum of delta_calls over all statements
slow_query_rate_pct = 100 * slow_calls / total_calls

1. Threshold is on the windowed mean, not per execution. Because pg_stat_statements stores only a per-statement mean (not a histogram), a statement is classified slow-or-not as a whole for the window. A statement that averages 90ms is fully excluded even if some of its individual runs exceeded 100ms; a statement that averages 110ms contributes all its calls as slow. This is a deliberate, conservative approximation: it tends to slightly under-count slow individual executions and is stable, which is what you want in an alerting gauge. Where a provider exposes a true per-execution histogram (Aurora Performance Insights, Cloud SQL Query Insights), the card prefers that finer source.
2. Call-weighting makes the rate represent real traffic. A nightly maintenance statement that runs twice and takes 3 seconds each barely moves the rate, because it is two calls out of (say) two million. A web-tier lookup that regressed to 120ms and runs 400,000 times in the window dominates the rate. The gauge is therefore a faithful “what fraction of the work my database actually did was slow” measure, not a count of slow statement shapes.

​

Worked example

Diagnosis:
  - rate broad (9.4%) but driven by ONE statement at high call volume
  - that statement was fast yesterday, slow today, deploy in between
  - EXPLAIN (ANALYZE, BUFFERS) on the tracking_no lookup:
        was an Index Scan on shipments_tracking_no_idx (0.3ms)
        is now a Seq Scan on shipments (118ms)
  - cause: the deploy's migration renamed the column the index was built on,
           leaving the index unused; the planner fell back to a full scan.

1. Rate and percentile answer different questions. p99 told them “the tail is a bit worse”; the slow-query rate told them “a large, specific slice of normal traffic regressed”. A high rate concentrated in one statement is the classic signature of a single broken index or stale plan, which is faster to fix than a diffuse slowdown.
2. Always decompose the rate by statement. A 9% rate spread evenly across hundreds of statements (cache cold after a restart, undersized instance) needs a capacity or configuration fix. A 9% rate concentrated in one statement needs a query or index fix. The gauge is the alarm; the per-statement breakdown is the diagnosis.
3. The 100ms line is a convention, not physics. For this OLTP API, 100ms is a sensible “slow” cut-off. A reporting database where 100ms queries are normal would want the threshold higher, otherwise the gauge reads permanently red. Tune it to your workload in the Sensitivity tab.

​

Sibling cards to read alongside

​

Reconciling against the source

pg_stat_statements is the source. To see which shapes are over the 100ms line right now:

SELECT queryid, calls, mean_exec_time, total_exec_time, query
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY calls DESC;

Note this orders by lifetime cumulative means, not the windowed delta the card uses, so the raw list will differ from the gauge’s window. log_min_duration_statement is the other native angle: set it (for example to 100ms) and PostgreSQL logs every statement that crosses the line to the server log, giving you per-execution truth that pg_stat_statements cannot. auto_explain with auto_explain.log_min_duration captures the plan of slow statements automatically, which is the fastest way to confirm a plan regression.

Amazon RDS / Aurora: Performance Insights surfaces top SQL by load and per-statement latency, and the slow-query log can be enabled via the parameter group. Aurora’s per-execution data is finer than pg_stat_statements’ means. Google Cloud SQL: Query Insights ranks queries by latency and shows plan samples. Azure Database for PostgreSQL: Query Store plus the log_min_duration_statement server parameter.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre