1. Count the queries in one request
Load the slow request on a copy with fake data and count how many statements it issues for a small and a large list. If the count rises with the list and one statement repeats with a different id, you have a query-per-row pattern and no plan will fix it. If the count is small, go to step 2.
- Repeating statement, count grows with rows: the N+1 guide and the one-endpoint job.
- Small count: continue.
2. Find the single slow statement and read its plan
Use the slow query log or statement statistics to find the statement with the most total time, then read its plan on a restored copy. Compare estimated and actual rows. A plan from a table of ten rows says nothing about ten million, so use realistic volume. If the plan shows a large scan returning few rows, go to step 3. If the statement is fast on the copy but slow in production, go to step 4.
- Read the plan guide before adding any index.
- One statement with a clear target: the one-slow-query job.
3. Ask why an existing index is ignored, and whether others are dead weight
Check column order, expression mismatches, type mismatches and stale statistics before creating anything new. When many statements are slow, or the table carries indexes nobody can account for, an index review with evidence periods is the right unit of work, not a single guess. Never drop an index on a short counter history.
- The index guides explain use and non-use.
- The index review job applies up to five approved changes, measured on staging.
4. Check for waiting: locks and long transactions
If the statement is quick on its own but requests stall, look for sessions blocked on locks and sessions idle inside a transaction. A deadlock record, or a list of blockers, names the code paths. Fix lock order first and add timeouts as a safety net. Retries come last.
- The deadlock guide shows how to capture the record.
- One workload, reproduced on staging: the deadlock job.
5. Check for waiting on connections
If errors say the pool timed out or the server has too many connections, count connections by state and multiply pool size by processes before touching any limit. Slow queries and idle transactions also hold connections, so steps 2 and 4 often come first.
- The pool guide separates sizing, leaks, process multiplication and pooler modes.
- One service, load-tested before and after: the connection-pool job.
6. Check the cache and the shape of the data
If users see old values, delete one cached key on a copy and see whether the new value appears. If a schema change is planned to fix any of the above, rehearse it with a rollback first. If rows are duplicated or sequences are behind, fix the data before blaming the query. Where several of these apply to one service, the data-layer project lists them as one agreed set with a closing measurement. No change should reach production without a restore point you have tested.
- Cache: the Redis guide and the stale-cache job.
- Schema and data: the add-column and duplicate guides.
- Several layers at once: the data-layer stabilisation project.
Sources and limits
- PostgreSQL 18: Using EXPLAIN Checked 2026-10-11.
- Plans show estimated and actual rows per step and results from a very different data size may not transfer.
- MySQL 8.4: The slow query log Checked 2026-10-11.
- The slow query log records statements over a time threshold and is disabled by default.
- PostgreSQL 18: Explicit locking, deadlocks Checked 2026-10-11.
- Transactions waiting for locks wait indefinitely unless a deadlock is detected, so holding transactions open for long periods is a bad idea.