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@xxxxxxxxxxxxxxxxxx Baruk Khazad! Khazad ai-menu! --------------------------------------------------------------
