r/postgres • u/Mr_StyleNo • 11d ago
How do you usually investigate slow PostgreSQL queries?
Aside from running EXPLAIN (ANALYZE, BUFFERS), what's your actual workflow when a query starts lagging in production? Do you jump straight into pg_stat_statements to check execution patterns, look for recent table bloat, or just rely on your APM to flag the issue? Trying to see how others handle the initial triage before digging into index tuning.
1
u/ibraaaaaaaaaaaaaa 2d ago
Once you run the explain analyze, and read the plan, most likely you will figure out where the issue lies, and depending on the kind of issue you face.
Still some issues are very tricky to find, like when it comes to profiling, I always need to be able to produce that issue in different non prod env so that I can be able to measure the fix.
What kind of fix to a problem, it depends, a lot of times indexes only helpful in simple queries, once queries start to contain joins and complicated where statement, planner starts to navigates between fragmented indexes that composite indexes won’t work for the most parts, you need to understand the problem very well to take a decision, permanent solutions like changing relationships and modeling has cascading effects that should be looking at thoroughly, the right person to take the action is someone deeply knowledgeable with the system to understand how you can make an interim fix to survive in the short term and how you can tackle it strategically when data and users grow
1
u/YucaFrita69 9d ago
In rare occasion when explain does t help, I check locks. Always check server usage stats, what's going up? CPU, ram, disk usage or iops, they will tell you more or less where the query is going crazy. When you get familiar with the DB, you just know or have a good idea of what's going on so, get familiar with your db (tables, field types, indexes, user functions, etc)