Re: Inconsistent query performance

On Apr 8, 2013, at 8:46 PM, Jeff Janes wrote:

> 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.

I think you're right about this. It seems to run fast most of the time. It's those times it takes 2 minutes to respond to a customer which I'd like to minimize or eliminate.

But I think caching has a bit to do with it too. I can go back to the logs from last week and run a slow query and it will frequently be equally slow again. Not always, but enough that I think something else is wrong with my setup.

>
> You should monitor with sar, or iostat, or vmstat, or similar.

I'll look into that. Thanks Jeff :)

>
> Cheers,
>
> Jeff