🐢Slow queries
Symptoms, not a single knob
“Slow” often means sequential scans, bad join order, lock waits, cold caches, or chatty round trips, not only “missing index.” Start from measured latency, wait events, and a representative plan with bind parameters and session settings, not vibes.
SQL
PostgreSQL: top statements by mean time (pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 3;✓Illustrative result set
| query | calls | mean_exec_time |
|---|---|---|
| SELECT * FROM orders WHERE lower(email)=lower($1) | 120000 | 48.2 |
| WITH … big analytics … | 400 | 9200 |
🔎Triage patterns
Correlate shape with calendar.
| Symptom | Likely cause |
|---|---|
| Spikes at top of hour | Cron ETL, vacuum, or migrations colliding with OLTP, check locks and IO wait. |
| Linear slowdown as data grows | Seq scans, stale stats, or join fan-out, compare estimated vs actual rows first. |
Laptop forensics
- Indexing because the plan looked scary without checking row estimates.
- Profiling only on tiny dev datasets with uniform data.
- Ignoring queueing when concurrency rises, CPU idle but p99 high.
Production-like evidence
- Capture plan hash, parameters, and session GUCs alongside wall time.
- Put top offenders in an SLO dashboard with owners and review cadence.
- Automate weekly digests from pg_stat_statements / Query Store equivalents.
Total time
Rank by calls × mean latency as well as worst single query, micro-optimizing rare statements wastes weeks while hot paths fund most user pain.
TL;DR
Slow queries are a symptom: gather who (user/app), what (SQL + parameters), when (concurrency), and plan (estimates vs actuals). Common causes: missing index, stale statistics, lock waits, cold cache after deploy, or sudden cardinality skew. Fix the highest total time consumers first, not only the single slowest statement.