On Monday, April 8, 2013, Ramsey Gurley wrote:
Hi all,
I'm having issues with slow queries using postgres, and I'm finding some of the issues difficult to reproduce. My application logs slow queries for me, but often, when I go back to run explain analyze on the query it is very fast. I assume this is due to some sort of caching. Here is an example:
sqlLogging _valuesForSQL ran 116509ms with sql string:
I think that your IO subsystem is getting congested on occasion, and that you have a "selection bias". You are seeing in your log the occasions on which it is running slow, but not seeing the occasions it is running fast. When you run it manually, you see a result similar to the (invisible, but frequent) times that it did not run slow.
You should monitor with sar, or iostat, or vmstat, or similar.
Cheers,
Jeff