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?
Yours,
Laurenz Albe