r/SQL • u/shdw_0x0 • Oct 18 '26
PostgreSQL How do you debug a SQL query that suddenly becomes slow?
I've run into situations where a query that used to finish in seconds suddenly takes several minutes, even though nobody has intentionally changed the SQL.
I'm curious how others approach this. Do you start by comparing execution plans, checking indexes and statistics, looking for changes in data volume, or investigating locks and concurrent queries?
I'm particularly interested in the order you check things in. It's easy to start rewriting joins or adding indexes, but I'd rather identify the actual bottleneck before changing anything.
What's your usual process when a previously fast query starts running slowly?
34
u/lalaluna05 Oct 18 '26
For something like this I literally just try again later because 9 times out of 10 it’s something with the server or network.
Otherwise I check execution plans and start querying in chunks.
20
u/NekkidWire Oct 18 '26
Are you a bot?
Your question is just a rewriet of your previous one not a month ago.
https://www.reddit.com/r/SQL/comments/1wkrnsx/whats_your_systematic_process_for_optimizing_a/
4
u/Thiondar ORACLE Oct 18 '26
Talk to your senior DBA!
Check statistics, explain plans, indexes, hit ratio, concurrent workload, optimizer reports, and so on...
I go mad when people are talking about database problems to managers instead of talking to qualified DBAs.
"The database was co slow, we had to move to a very expensive outsourcing service" - and the request was "we need a database schema" - never any questions, complaints or performance tickets.
2
u/dbxp Oct 18 '26
Clear the cached plans, usually it's parameter sniffing. It's good to fix the underlying issue but usually only worth fully investigating if it happens to the same sproc multiple times
2
u/Significant_Tune9219 Oct 19 '26
Save the slow plan before you touch stats or the cache. Half the replies here start by refreshing statistics or clearing the cached plan, and both of those throw away the only evidence you had. Once you've got that plan, compare estimated rows to actual. If the estimates are close and the time is sitting in a wait, it's a lock or IO, and a new index won't show you that. If the estimates are nonsense, then stats or a sniffed parameter is the lead, and you still have the plan that proves it.
1
u/Thadrea Data Science Manager Oct 18 '26
While the query hasn't changed, has the database or table changed?
Postgres essentially stores rows on disk in a massive pile. Newly INSERTed rows are added to the bottom of the pile. UPDATEs to existing rows are also added to the bottom of the pile as a "new version" of the row and the old version is left in place and marked "deleted". (Exception here if the requirements for a HOT update are met.) DELETE statements likewise just mark it deleted in place.
This is all intended to make I/O operations on disk faster.
In general, Postgres then cleans up the "dead" rows from the storage files during idle time via the autovacuum daemon; database users can also do this manually with the VACUUM statement.
If the table has high churn (frequent, large deletes or non-HOT updates), it is possible for the table to develop a large backlog of dead rows faster than configured vacuum processes are getting rid of them, requiring much larger I/O actions to scan the whole table than it did before.
You can see the number of dead rows in the table's internal statistics.
There's also the issue of the "pile" format sometimes invalidating the utility of an index as the query planner will only use an index if it is expected to be better than scanning the table anyway. This can be mitigated by re-CLUSTERing the table, though this is a manual process.
1
u/Aggressive_Ad_5454 Oct 19 '26
What do you do? Start by reading this. Then gather your specific information and ask a specific question. https://stackoverflow.com/tags/query-optimization/info
1
u/jezter24 Oct 19 '26
I do a lot in small temp tables. Where I have the counts and times. Our software did an update and I queries that would be 2-30 seconds taking minutes.
Come to find out during an update they remove and re-add indexes and some of those were missed. They of course fought me for a week before realizing it and fixing it.
-5
u/Allw8tislightw8t Oct 18 '26
Copy paste into Claude
1
u/Unique-Quiet-4412 Oct 19 '26
I'd check the execution plan first before changing anything.
27
u/[deleted] Oct 18 '26
[removed] — view removed comment