On Sun, May 4, 2008 at 5:11 PM, Hans Zaunere <lists@xxxxxxxxxxx> wrote: > Hello, > > 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. > Almost certainly a buffering issue. First time it's reading the file into memory WHILE also doing other things, file system wise. Second time it's in memory (kernel cache) and zips right by. What can you do? First you need to see what's really happening, which means learning how to drive vmstat, iostat, top, etc to see what's happening on your machine. You'll likely want to look into doing something that will reduce contention on the database partition set for starters. Table spaces, big RAID arrays (big meaning a lot of spindles), battery backed RAID controller.
