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 returned 675.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.
On Mon, Apr 7, 2014 at 10:50 AM, Albe Laurenz <laurenz.albe@xxxxxxxxxx> wrote:
Rebecca Clarke wrote:It could be that during the day the necessary pages are cached in
> 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.
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?
Yours,
Laurenz Albe
