Re: Inconsistent query performance

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