A practical method for finding slow PostgreSQL queries, reading query plans and choosing indexes that improve production behaviour without creating write overhead blindly.
Measure a real query shape, inspect its plan and index the access pattern—not a column in isolation.
Start with a slow operation
Do not begin with a list of columns that sound important. Start with an observable user or worker operation, capture the actual SQL, bound parameters and row counts, then reproduce it against production-like data.
A query that is fast on a small local database may change completely once a table has millions of rows or a selective filter becomes unselective. Latency distribution and database load reveal whether the path deserves an index or a different shape.
Read the plan before changing schema
EXPLAIN ANALYZE shows how PostgreSQL actually reached rows: scans, joins, estimates, loops and time. Compare estimated rows with actual rows; large gaps point to stale statistics, skewed data or a predicate the planner cannot estimate well.
An index is useful when it narrows work, supports ordering or avoids an expensive join strategy. Composite index order matters: lead with the equality predicates that make the set small, then consider range and sort requirements.
Keep the repair operational
Create large production indexes with the appropriate online migration strategy and observe the change after release. An index that improves one endpoint can increase write cost, vacuum work and storage pressure for every mutation.
Keep the query, migration and performance evidence together in review. Remove duplicate or unused indexes only after observing real workload, not merely because their names look similar.
Evidence to take into review
- The slow operation and real query shape are identified.
- EXPLAIN ANALYZE is reviewed against representative data.
- Index order matches predicates, joins and ordering.
- Migration and rollback behaviour are planned.
- Latency and write impact are verified after release.
Explore all engineering notes.
Use PRODUCTION-7 to connect this concern with the other dimensions of a trustworthy backend.
View all articles Get the checklist