Playbook

Slow DB Query

Identify, analyze, and optimize slow database queries.

Back to playbooks

Identifying Slow Queries

Check slow query logs (PostgreSQL pg_stat_statements, MySQL slow_log).

Monitor DB CPU and I/O wait times.

Look for high-latency spans in distributed tracing (Jaeger, Honeycomb).

Enable MySQL slow query log at runtime: 'SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;'

Check for active lock waits in Postgres: 'SELECT * FROM pg_locks JOIN pg_stat_activity USING(pid) WHERE NOT granted;'

Analysis & Profiling

Run 'EXPLAIN ANALYZE' on the targeted query.

Look for Sequential Scans on large tables.

Check for missing indexes or outdated table statistics.

Run 'VACUUM ANALYZE <table>' if statistics are stale — the planner may choose a sequential scan because row count estimates are wrong.

Optimization Tactics

Add missing indexes (B-tree, GIN, Hash).

Rewrite subqueries to JOINs where applicable.

Use connection pooling (PgBouncer, HikariCP) to reduce overhead.

Identify N+1 query patterns: look for the same query repeated hundreds of times in slow logs with small individual durations but high aggregate time.

SQL & CLI Commands

PostgreSQL: Top slow queries

SELECT query, calls, total_time, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

PostgreSQL: Active long-running queries

SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC
LIMIT 10;

MySQL: Analyze query execution

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE active = 1;

MySQL: Enable slow query log

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

Check table bloat (Postgres)

SELECT relname, n_dead_tup, last_vacuum FROM pg_stat_user_tables;

About the Slow DB Query

Slow queries are the most common root cause behind "the app is slow" — and the most systematically debuggable. The database will tell you exactly what it is doing: which queries dominate total time, which plans scan instead of seek, and which locks serialize your concurrency. The skill is knowing which view to ask.

This playbook follows the standard sequence: find the offenders (pg_stat_statements, slow query log), read their execution plans (EXPLAIN ANALYZE), and apply the fix hierarchy — index, rewrite, schema, then hardware, in that order of cheapness.

Frequently asked questions

How do I find which queries are actually slow in production?

PostgreSQL: pg_stat_statements ordered by total_exec_time — mean time alone misleads, because a 5ms query running 10,000 times/min outweighs a 2s report. MySQL: enable the slow query log with long_query_time, digest with pt-query-digest. Sort by total time consumed, not per-execution time, to find what is loading the database.

How do I read EXPLAIN ANALYZE output?

Look for: Seq Scan on large tables where you expected an index (missing index, or a function/type-cast on the column defeating it), row-estimate vs actual mismatches of 100x+ (stale statistics — run ANALYZE), nested loops over large row counts (join order gone wrong), and Sort/Hash nodes spilling to disk (work_mem too small). The node consuming the most actual time is your target.

Why isn't the database using my index?

Common reasons: a function or cast on the indexed column (WHERE lower(email) = ... needs an expression index), a leading wildcard LIKE, type mismatch between column and parameter, the planner correctly judging the table too small or the predicate too unselective for an index to help, or stale statistics. EXPLAIN with the literal values from the slow case — plans differ by parameter.

Need this managed for you, not just automated?

We're also a hands-on DevOps consultancy — Kubernetes, CI/CD, and cloud infrastructure.

Explore Our Services