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
