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.
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.
Rebecca Clarke wrote: > I'm a bit stumped. At present I'm finding that queries to my database, that normally execute promptly, > are taking a long time when they are executed first thing in the morning (after the database has been > inactive for several hours). After the first execution, everything is back to normal. > > A while back I turned autovacuum off and now instead I run a daily cron at 3am that executes a script > which does a VACUUM ANALYZE on each table.
It could be that during the day the necessary pages are cached in the buffer pool or the file system cache, but have dropped out of the cache during the night.
Try EXPLAIN (ANALYZE, BUFFERS) SELECT ... first thing in the morning and during the day and compare the "shared read" and "shared hit" values.
It may well be the nightly VACUUM ANALYZE that does that - is autovacuum not doing ist job for you? Is there anything else going on on the machine during the night, like backups or batch jobs?
