Thanks, I'll run the EXPLAIN (ANALYZE, BUFFERS) today and tomorrow morning. I just tried it now on a query that took 109035.116 ms this morning (Which returns one row). It has returned675.496 ms. I will run on this same query at 5am tomorrow. Thank you.
If the problem is largely encapsulated by that one query, I'd just write a cron job to execute that query every morning 15 minutes before you open for business.
At present we run pg_dumps every three hours.
We orginally found autovacuum too intrusive so switched to manual. We've had no problems with performance at all, only this. We're going to turn autovacuum back on to see if it makes any impact to this particular issue.
Did you go from 'Autovacuum only' to 'nightly vacuum, no autovac' in one step? Mostly likely adding the nightly vacuum while leaving autovac on would have solved the problem, while being less likely to cause other problems. (This is a side note--having autovac off is unlikely to be causing the particular problem you are reporting here.)
