How to Debug a Slow PostgreSQL Query
- Author
- Vishal Maurya
- Published on
- Reading time
- 2 min read
Overview
Adding an index is not a universal fix for a slow query. PostgreSQL may be scanning many rows, choosing a poor join strategy, sorting a large result, or waiting on locks. Start with a representative query and evidence from the plan.
Capture the exact SQL, bind-value shape, table sizes, latency, and frequency. Use EXPLAIN to inspect the planned operations. Use EXPLAIN ANALYZE carefully because it executes the statement and can be expensive or have side effects for data-changing queries.
Read the plan
- Compare estimated rows with actual rows; large differences can indicate stale statistics or skewed data.
- Look at buffer reads and repeated work in nested loops.
- Check whether sorting happens before a restrictive limit.
- Test a composite index only when it matches the filter and ordering pattern.
- Verify improvement using the same data shape and concurrent workload.
- Check lock waits and connection saturation if query execution time alone does not explain request latency.
Example: a concrete starting point
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, created_at, status
FROM orders
WHERE account_id = 42 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;
The example illustrates the boundary and validation pattern; adapt names, persistence, error handling, and provider-specific details to your application. It is not a complete production system by itself.
Failure cases to test
Indexes speed some reads but cost disk space and write time. A sequential scan can be optimal when a large share of a table is needed. Avoid sharing production query plans with customer identifiers or literals that disclose sensitive data.
Before release, test the happy path, invalid input, missing permissions, dependency failure, and a retry or duplicate request where relevant. Record expected behavior so the implementation can be reviewed against the real business requirement.
When to bring in a developer
I can profile slow database-backed endpoints, review query plans and indexes, and measure the impact of changes against realistic workloads.
Conclusion
The reliable version of this feature is not just the happy-path demo. It makes ownership, validation, authorization, failure handling, and operational behavior explicit. Start with the smallest useful workflow, measure it against real data, and expand only when the next requirement is clear.