Finding the Queries That Matter

Scenario

Example app: a B2B invoicing SaaS on Postgres. Over three months, API p99 latency crept from 180 ms to 900 ms and database CPU sits at 85% at peak. The team's first idea is a bigger instance or read replicas.

What you see in production

The slow query log is full of a monthly revenue report that takes 14 seconds — everyone blames it. But it runs a few times a day. Upgrading the instance buys two weeks before CPU is back at 85%.

The naive code

Nobody looks at aggregate load — only at individual slow queries:

-- what the team looked at: the slowest single statements
SELECT query, max_exec_time FROM pg_stat_statements ORDER BY max_exec_time DESC LIMIT 5;

# Query_time: 14.2  Lock_time: 0.0  Rows_sent: 12  Rows_examined: 48000000
SELECT customer_id, sum(total) FROM invoices WHERE issued_at >= '2026-09-01' GROUP BY customer_id;

Reproduce it

A few rows of pg_stat_statements. Sorting by total time (calls × mean) instead of the slowest single call tells a different story:

stats = [   # query, calls per day, mean ms
    ("monthly revenue report", 4, 14_000),
    ("GET /invoices list: SELECT … WHERE account_id = $1 ORDER BY issued_at DESC LIMIT 50", 2_400_000, 38),
    ("SELECT * FROM users WHERE id = $1", 9_000_000, 0.4),
    ("SELECT count(*) FROM invoices WHERE account_id = $1", 2_400_000, 21),
    ("UPDATE sessions SET last_seen = now() WHERE id = $1", 6_000_000, 1.1),
]
total_ms = sum(calls * mean for _, calls, mean in stats)
print("by slowest single call:", max(stats, key=lambda s: s[2])[0])
print("by total time per day:")
for name, calls, mean in sorted(stats, key=lambda s: s[1] * s[2], reverse=True):
    share = calls * mean / total_ms * 100
    print(f"  {share:5.1f}%  {calls * mean / 3_600_000:7.1f} h/day  {name[:70]}")

by slowest single call: monthly revenue report
by total time per day:
   60.1%     25.3 h/day  GET /invoices list: SELECT … WHERE account_id = $1 ORDER BY issued_at 
   33.2%     14.0 h/day  SELECT count(*) FROM invoices WHERE account_id = $1
    4.3%      1.8 h/day  UPDATE sessions SET last_seen = now() WHERE id = $1
    2.4%      1.0 h/day  SELECT * FROM users WHERE id = $1
    0.0%      0.0 h/day  monthly revenue report

Root cause

Database load is calls × time per call. A fast query (38 ms) called 2.4 million times a day costs ~25 hours of database time; the "terrible" report costs 15 minutes. Scaling hardware without knowing this just postpones the problem — and read replicas wouldn't even help a query that must read fresh data.

The fix

Make total time the first view of database performance:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;   -- also add it to shared_preload_libraries
SELECT round(total_exec_time / 1000 / 3600, 1) AS hours,
       calls,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       round(100 * total_exec_time / sum(total_exec_time) OVER (), 1) AS pct,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- performance_schema digests aggregate normalized statements
SELECT DIGEST_TEXT, COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT / 1e12, 1) AS total_s,
       ROUND(AVG_TIMER_WAIT / 1e9, 2) AS mean_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

@event.listens_for(engine, "before_cursor_execute")
def start(conn, cursor, statement, params, context, executemany):
    context._t0 = time.perf_counter()

@event.listens_for(engine, "after_cursor_execute")
def stop(conn, cursor, statement, params, context, executemany):
    metrics.histogram("db.query.ms", (time.perf_counter() - context._t0) * 1000,
                      tags={"statement": fingerprint(statement)})

Then fix the top entries: here, the invoice list needs an index on (account_id, issued_at) (next lessons), and the per-request count(*) can be cached or dropped from the page.

Trade-offs

• pg_stat_statements adds a little overhead and normalises parameters away — combine it with auto_explain (log plans of slow statements) to see why a query is slow.
• Statistics accumulate since the last reset; reset after big changes (pg_stat_statements_reset()) or compare snapshots over time.
• Application-side timing adds network and pool wait — useful, but different from server execution time.

Detect it in production

• A weekly "top 10 by total time" review; alert when a new fingerprint enters the top 5 after a deploy.
• Track calls per request per endpoint: a jump usually means an N+1 slipped in.
• MySQL: enable the slow log with long_query_time low enough (e.g. 0.2 s) and use pt-query-digest to aggregate it.

Key points

• Optimise by total time (calls × mean), not by the slowest single query.
• Enable pg_stat_statements / performance_schema digests everywhere, including staging.
• Measure before buying hardware or adding replicas.