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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
