Re: Slow SELECTS after large update cycle

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

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

>
> Best Regards, Simon Riggs

jan

--
--------------------------------------------------------------
Jan de Visser                     jdevisser(at)digitalfairway(dot)com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------