Hi everyone,
Thank you for your help. But my query is not always slow. It works fast under normal conditions, but in a way that I don't understand it works very slow at some times.
I think there may be a different reason why a query that takes 800ms, in general, takes 18 minutes at a time.
As previously asked, do you have log_lock_waits turned on? If so, do you see entries in the db server's log file?
Also, we really could use the plan capture of the slow execution, not of the same query except when it is not slow. The only way of to get that reliably while also getting the original output of the query (which I assume you need) is with auto_explain, with settings like:
auto_explain.log_min_duration = '600s'
auto_explain.log_analyze=on
auto_explain.log_buffers=on
auto_explain.log_nested_statements=on
Then go trolling through the log file. Note that this does cause every query run on your system to take detailed timings of each step, which can slow down the entire system, especially if you are on a system with slow user access to the clock (common with old hardware or old kernels). But I think the extra info you get is well worth the extra load. You could leave auto_explain.log_analyze=off globally, then turn it on only in the specific script file you are seeing the problem with.
Cheers,
Jeff