I like the "add an analyze" idea; the two most likely causes of the phenomenon (to my mind) are either: a) Something's getting locked and Tom Lane's idea of checking pg_locks when you notice it's stopped can help track down the problem. Further to that, the thing I'd be expecting to see if the problem is locking is that the connection that's blocked up will be waiting on a lock held by another connection. It's near certain that you'll find that the connection doing the work will have LOTS of locks outstanding; that's not a problem at all; that's perfectly normal. You need to look keenly for locks that have not yet been granted. b) I find it common in my environments to need to do manual ANALYZE requests all the time because I'll set up temporary tables (that the autovacuum daemon can't do anything about) which, as the stats are lacking, will lead to awful query plans that make queries run badly. If you use temporary tables, that's like a heavy "thumb on the scale" that can lead to awful performance, unless those tables get an ANALYZE after getting populated. Unpredictable slowness can certainly result from tables having changed size leading to pathological query plans. ANALYZE will help.
