Subject Why does a query take many times as long the first time it's run?
Author Tim Ward
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