I 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?
BTW, a glaring flaw in that theory is that if this query is touching only
about four pages worth of data, and you are running it ten times a second,
how in heck would that data ever fall out of shared buffer cache at all?
Your working set across your whole DB would have to be enormously more
than your 8GB shared_buffers setting for that to possibly happen.
So what seems more likely after more thought is that the pages are staying
in our shared buffer arena just fine, but the kernel is randomly choosing
to swap out parts of the arena, and the delays correspond to swap-in
waits. (There would still have to be a mighty crummy disk subsystem
underlying things for swap-in to take so long, but this is a more
plausible theory for exactly what's invoking the disk read.)
Postgres can't directly see when this is happening, but you could try
watching "iostat 1" and noticing whether swap-in events seem to be
correlated with the slow queries.
If this is the problem, then the answer is to reduce the pressure on
system memory so that swap-outs are less likely. You might find that
a smaller shared_buffer arena is a good thing (so that all of it stays
"hot" and unswappable from the kernel's perspective). Or reduce the
number of active backend processes.
regards, tom lane
