Subject: Re: Slow SELECTS after large update cycle
From: Simon Riggs <simon@2ndquadrant.com>
To: Jan de Visser <jdevisser@digitalfairway.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <200603151439.13958.jdevisser@digitalfairway.com>
References: <200603151439.13958.jdevisser@digitalfairway.com>
Content-Type: text/plain
Date: Wed, 15 Mar 2006 23:21:27 +0000
Message-Id: <1142464887.3859.195.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Wed, 2006-03-15 at 14:39 -0500, Jan de Visser wrote:

> 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.
> 
> Anybody any ideas?

This is caused by updating the commit status hint bits on each row
touched by the SELECTs. This turns the first SELECT into a write
operation.

Try running a scan of the whole table to take the hit before you give it
back to the users.

Best Regards, Simon Riggs