I was experimenting with a few different methods of taking a line of text, parsing it, into a set of fields, and then getting that info into a table. 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). 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? NOTE: the code that does the parsing is actually the same, line-for-line, the only difference is whether the routine is called by a postgresql function or by a C program via main, so obviously the overhead is elsewhere. NOTE #2: We are talking about approximately 2.6 million lines. I was testing: \copy some_table from 'some_file.csv' with csv vs. insert into some_table select (some_func(line)).* from some_temp_table; where some_func had been defined with (one) IN TEXT and (8) OUT params of varying types. PostgreSQL 9.1.1 on Linux, x86_64 -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
