On Sat, Dec 10, 2011 at 8:32 PM, Craig Ringer <ringerc@xxxxxxxxxxxxx> wrote: > On 12/11/2011 09:27 AM, Jon Nelson wrote: >> >> The first method involved writing a C program to parse a file, parse >> the lines and output newly-formatted lines in a format that >> postgresql's COPY function can use. >> End-to-end, this takes 15 seconds for about 250MB (read 250MB, parse, >> output new data to new file -- 4 seconds, COPY new file -- 10 >> seconds). > > Why not `COPY tablename FROM /path/to/myfifo' ? If I were to do this in any sort of production environment, that's exactly what I would do. I was much more concerned about the /huge/ difference -- 10 seconds for COPY and 120 seconds for (INSERT INTO / CREATE TABLE AS / whatever). >> The next approach I took was to write a C function in postgresql to >> parse a single TEXT datum into an array of C strings, and then use >> BuildTupleFromCStrings. There are 8 columns involved. >> Eliding the time it takes to COPY the (raw) file into a temporary >> table, this method took 120 seconds, give or take. >> >> The difference was /quite/ a surprise to me. What is the probability >> that I am doing something very, very wrong? > > Have a look at how COPY does it within the Pg sources, see if that's any > help. I don't know enough about Pg's innards to answer this one beyond that > suggestion, sorry. Ack. Regarding a subsequent email, I was using full transactions. -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
