The first time I run a certain SELECT query after not running it for a while (hours or days) I might get stats like:
Current memory = 3265728 Delta memory = 290716 Max memory = 3267368 Elapsed time= 166.92 sec Buffers = 150 Reads = 92650 Writes 65659 Fetches = 16182424 If I immediately run the same query again I get something like Current memory = 3186284 Delta memory = -79444 Max memory = 3326372 Elapsed time= 1.98 sec Buffers = 150 Reads = 14491 Writes 80 Fetches = 1176021 (note 2 seconds instead of 167 seconds) . Subsequent runs then take consistently 2 seconds. What's going on? Does the large number of "Writes" indicate that garbage collection has kicked in? If so, what can I do to prevent this? - a user who is expecting to wait 2 seconds for their data is not going to be happy seeing a timeout because the browser code is not expecting this operation to take several minutes. (We've seen this several times with different queries, so don't think it's anything specific to the query being run.) -- Tim Ward
