Re: Initial queries of day slow

On Mon, Apr 7, 2014 at 3:58 AM, Rebecca Clarke <r(dot)clarke83(at)gmail(dot)com> wrote:

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

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

Cheers,

Jeff