From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: David Roussel <pgsql-performance@diroussel.xsmail.com>
Cc: Mischa Sandberg <mischa.sandberg@telus.net>
Cc: pgsql-perform <pgsql-performance@postgresql.org>
Subject: Re: COPY vs INSERT
Date: Wed, 04 May 2005 22:22:56 -0400
Message-ID: <16747.1115259776@sss.pgh.pa.us> (raw)
In-Reply-To: <42797C1A.3090303@familyhealth.com.au>
References: <OF93C70832.2AC188CE-ON85256FF6.0056B587-85256FF6.005774C2@us.ibm.com>
<21154.1115136741@sss.pgh.pa.us>
<1115146397.4277c89dc37b4@webmail.telus.net>
<1115246490.1709.233393619@webmail.messagingengine.com>
<42797C1A.3090303@familyhealth.com.au>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, chriskl@familyhealth.com.au, pgsql-performance@diroussel.xsmail.com, mischa.sandberg@telus.net
Subject: Re: COPY vs INSERT
In-Reply-To: <16747.1115259776@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox