From: Chris <dmagick@gmail.com>
To: Jan de Visser <jdevisser@digitalfairway.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow SELECTS after large update cycle
Date: Thu, 16 Mar 2006 10:11:55 +1100
Message-ID: <44189F3B.1020209@gmail.com> (raw)
In-Reply-To: <200603151439.13958.jdevisser@digitalfairway.com>
References: <200603151439.13958.jdevisser@digitalfairway.com>
Jan de Visser wrote:
> Hello,
>
> After fixing the hanging problems I reported here earlier (by uninstalling
> W2K3 SP1), I'm running into another weird one.
>
> After doing a +/- 8hr cycle of updates and inserts (what we call a 'batch'),
> the first 'reporting' type query on tables involved in that write cycle is
> very slow. As an example, I have a query which according to EXPLAIN ANALYZE
> takes about 1.1s taking 46s. After this one hit, everything is back to
> normal, and subsequent executions of the same query are in fact subsecond.
> Restarting the appserver and pgsql does not make the slowness re-appear, only
> running another batch will.
>
> During the 'write'/batch cycle, a large number of rows in various tables are
> inserted and subsequently (repeatedly) updated. The reporting type queries
> after that are basically searches on those tables.
After a large batch you need to run 'analyze' over the tables involved
to get postgresql to update it's statistics so it can work out which
indexes etc it should use.
--
Postgresql & php tutorials
http://www.designmagick.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dmagick@gmail.com, jdevisser@digitalfairway.com
Subject: Re: Slow SELECTS after large update cycle
In-Reply-To: <44189F3B.1020209@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox