> > We're using a statement like this to dump between 500K and >5 million > > rows. > > > COPY(SELECT SomeID FROM SomeTable WHERE SomeColumn > '0') > > TO '/dev/shm/SomeFile.csv' > > > Upon first run, this operation can take several minutes. Upon second > > run, it will be complete in generally well under a minute. > > Hmmm ... define "first" versus "second". What do you do to return it > to the slow state? Interesting that you ask. I haven't found a very reliable way to reproduce this. Typically, just waiting a while to run the same query the second time will reproduce this behavior. I restarted postgresql and it was reproduced as well. However, I can't find a way to flush buffers/etc, to reproduce the problem on-demand. The first time vs second time is usually simply defined as the temp. CSV file being created slowly once, and then fast, although frankly sometimes it's always slow, and sometimes seemingly a bit quicker, depending on the query. As I mentioned in my other post, the query is always of the simple form above - single column greater than or equal to a single value (int or single byte char). We had set shared_buffers to 1028M, which at first seemed to make a significant difference. With some testing, various queries ran under 20 seconds, the "first time", and the second time at the same speed or a bit quicker. However, operations then restarted the system (and downgraded to Red Hat 5.0) and now we're back where we started. Even with the increased shared_buffers, things are slow - perhaps I'll try to increase it more. Upon first starting postgresql, and executing the above statement, the process begins to grow to 1gb and then stops. When it stops, the CSV file starts to "trickle" out to disk. Best, H
