From: Jan de Visser <jdevisser@digitalfairway.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow SELECTS after large update cycle
Date: Wed, 15 Mar 2006 19:42:00 -0500
User-Agent: KMail/1.9.1
References: <200603151439.13958.jdevisser@digitalfairway.com>
	<1142464887.3859.195.camel@localhost.localdomain>
In-Reply-To: <1142464887.3859.195.camel@localhost.localdomain>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200603151942.01123.jdevisser@digitalfairway.com>

On Wednesday 15 March 2006 18:21, Simon Riggs wrote:
> 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 que=
ry
> > 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.

Thanks Simon. I didn't know about the cause, but I expected the answer to b=
e=20
'deal with it', as it is. At least I can explain it now...

>
> Best Regards, Simon Riggs

jan

=2D-=20
=2D-------------------------------------------------------------
Jan de Visser =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 jdevisser@digitalfairway.com

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Baruk Khazad! Khaza=
d ai-menu!
=2D-------------------------------------------------------------