Re: Queries intermittently slow

Scott Rankin <srankin(at)motus(dot)com> writes:
> On 1/6/16, 10:38 AM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> A possible theory is that the slow cases represent times when the desired
>> page is not in cache, but you'd have to have a seriously overloaded disk
>> subsystem for a disk fetch to take hundreds of ms. Unless maybe this is
>> running on some cloud service with totally unspecified I/O bandwidth?

> This intrigues me. We are running on a, shall we say, less than name-brand cloud provider at the moment (transitioning to AWS later this month). Is there a reasonably straightforward way of confirming this hypothesis? We have had many performance issues with this vendor in the past, so I wouldnt be surprised.

Hm, well, given that you are able to capture instances of the behavior
in EXPLAIN ANALYZE, I'd suggest trying EXPLAIN (ANALYZE,BUFFERS).
That will tell you the number of pages it found in shared buffers vs.
having to read them. Now, a "read" just means we had to ask the kernel,
not necessarily that the page came all the way from disk; if it's in
the kernel's disk cache that won't be very much slower than a shared-
buffers hit. Still, if the slowdowns are reliably seen only when a read
occurred, I'd say that's good evidence.

regards, tom lane