Re: Initial queries of day slow

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(dot)albe(at)wien(dot)gv(dot)at>wrote:

> 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
>