Re: COPY vs INSERT

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> So what's the difference between a COPY and a batch of INSERT
>> statements.  Also, surely, fsyncs only occur at the end of a
>> transaction, no need to fsync before a commit has been issued, right?

> With COPY, the data being inserted itself does not have to pass through
> the postgresql parser.

Also, there is a whole lot of one-time-per-statement overhead that can
be amortized across many rows instead of only one.  Stuff like opening
the target table, looking up the per-column I/O conversion functions,
identifying trigger functions if any, yadda yadda.  It's not *that*
expensive, but compared to an operation as small as inserting a single
row, it's significant.

            regards, tom lane