How to Spot the Real Bottleneck

~14 min · operations, diagnosis

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Performance debugging starts with the data, not the guess

The first instinct on a slow system is to add an index, increase the cache, or rewrite the query. The professional instinct is to measure first: which query is slow, when, how often, and by how much. Without numbers, every change is a guess and most guesses make things worse.

The two diagnosis dimensions

Query-level — which query is the offender? pg_stat_statements ranks every query by total time, mean time, call count.
Wait-level — what is the database waiting on? pg_stat_activity shows live queries and what they're waiting for (locks, IO, CPU).

The mental ranking

Order of suspicion: (1) missing index, (2) lock contention, (3) bloat / dead tuples, (4) cache pressure / disk IO, (5) network / pooling. Almost every "the database is slow" incident lands somewhere in this list.

Code

Top queries by total time·sql

-- Requires CREATE EXTENSION pg_stat_statements + restart
SELECT
    LEFT(query, 80)        AS query,
    calls,
    total_exec_time / 1000 AS total_seconds,
    mean_exec_time         AS mean_ms,
    rows
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  20;

Live activity — what's running right now·sql

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS runtime,
       LEFT(query, 80) AS query
FROM   pg_stat_activity
WHERE  state <> 'idle'
ORDER  BY runtime DESC NULLS LAST;

Lock waits — who is blocked by whom·sql

SELECT blocked.pid    AS blocked_pid,
       blocked.query  AS blocked_query,
       blocking.pid   AS blocking_pid,
       blocking.query AS blocking_query
FROM   pg_stat_activity blocked
JOIN   pg_locks blocked_locks   ON blocked_locks.pid = blocked.pid AND NOT blocked_locks.granted
JOIN   pg_locks blocking_locks  ON blocking_locks.locktype = blocked_locks.locktype
                                AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
                                AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
                                AND blocking_locks.granted
                                AND blocking_locks.pid <> blocked_locks.pid
JOIN   pg_stat_activity blocking ON blocking.pid = blocking_locks.pid;

External links

pg_stat_statements

Exercise

On any production-like database, install pg_stat_statements and let it accumulate for an hour. Identify the top 5 queries by total_exec_time. For each, check whether it has an obvious bottleneck (missing index, sequential scan).

Progress

Progress is local-only — sign in to sync across devices.

← 🚀 Application IntegrationQuiz · 5 questions Next →Query Shape First, Indexes Second

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.