pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: COPY vs INSERT
17+ messages / 12 participants
[nested] [flat]
* Re: COPY vs INSERT
@ 2005-05-03 18:53 Mischa Sandberg <mischa.sandberg@telus.net>
0 siblings, 1 reply; 17+ messages in thread
From: Mischa Sandberg @ 2005-05-03 18:53 UTC (permalink / raw)
To: pgsql-performance
> Steven Rosenstein <srosenst@us.ibm.com> writes:
> > My question is, are there any advantages, drawbacks, or outright
> > restrictions to using multiple simultaneous COPY commands to load
> data into
> > the same table?
Do you mean, multiple COPY commands (connections) being putline'd from
the same thread (process)? I have indirect evidence that this may hurt.
Two copy commands from different threads/processes are fine, and can
help, if they alternate contention on some other resource (disk/CPU).
I'm basing this on being at the third generation of a COPY
implementation. The app loads about 1M objects/hour from 6 servers.
Each object is split across four tables.
The batch load opens four connections and firehoses records down each.
A batch is 10K objects.
COPY invokes all the same logic as INSERT on the server side
(rowexclusive locking, transaction log, updating indexes, rules).
The difference is that all the rows are inserted as a single
transaction. This reduces the number of fsync's on the xlog,
which may be a limiting factor for you. You'll want to crank
WAL_BUFFERS and CHECKPOINT_SEGMENTS to match, though.
One of my streams has 6K records; I run with WB=1000, CS=128.
The downside I found with multiple clients inserting large blocks of
rows was, that they serialized. I THINK that's because at some point
they all needed to lock the same portions of the same indexes. I'm still
working on how to avoid that, tuning the batch size and inserting into a
"queue" table with fewer indexes.
COPY (via putline) didn't do measurably better than INSERT until I
batched 40 newline-separate rows into one putline call, which improved
it 2-3:1. The suspect problem was stalling on the TCP stream; the driver
was flushing small packets. This may or may not be relevant to you;
depends on how much processing (waiting) your app does between posting
of rows.
In such a case, writing alternately to two TCP streams from the same
process increases the likelihood of a stall. I've never tested that
set-up; it would have been heading AWAY from the solution in my case.
Hope that helps.
--
Engineers think equations approximate reality.
Physicists think reality approximates the equations.
Mathematicians never make the connection.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-04 22:41 David Roussel <pgsql-performance@diroussel.xsmail.com>
parent: Mischa Sandberg <mischa.sandberg@telus.net>
0 siblings, 3 replies; 17+ messages in thread
From: David Roussel @ 2005-05-04 22:41 UTC (permalink / raw)
To: Mischa Sandberg <mischa.sandberg@telus.net>; pgsql-performance
> COPY invokes all the same logic as INSERT on the server side
> (rowexclusive locking, transaction log, updating indexes, rules).
> The difference is that all the rows are inserted as a single
> transaction. This reduces the number of fsync's on the xlog,
> which may be a limiting factor for you. You'll want to crank
> WAL_BUFFERS and CHECKPOINT_SEGMENTS to match, though.
> One of my streams has 6K records; I run with WB=1000, CS=128.
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?
David
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-04 23:11 Mischa Sandberg <mischa.sandberg@telus.net>
parent: David Roussel <pgsql-performance@diroussel.xsmail.com>
2 siblings, 2 replies; 17+ messages in thread
From: Mischa Sandberg @ 2005-05-04 23:11 UTC (permalink / raw)
To: pgsql-performance
Quoting David Roussel <pgsql-performance@diroussel.xsmail.com>:
> > COPY invokes all the same logic as INSERT on the server side
> > (rowexclusive locking, transaction log, updating indexes, rules).
> > The difference is that all the rows are inserted as a single
> > transaction. This reduces the number of fsync's on the xlog,
> > which may be a limiting factor for you. You'll want to crank
> > WAL_BUFFERS and CHECKPOINT_SEGMENTS to match, though.
> > One of my streams has 6K records; I run with WB=1000, CS=128.
>
> 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?
Sorry, I was comparing granularities the other way araound. As far as
xlog is concerned, a COPY is ALWAYS one big txn, no matter how many
putline commands you use to feed the copy. With inserts, you can choose
whether to commit every row, every nth row, etc.
Copy makes better use of the TCP connection for transmission. COPY uses
the TCP connection like a one-way pipe. INSERT is like an RPC: the
sender has to wait until the insert's return status roundtrips.
--
Engineers think equations approximate reality.
Physicists think reality approximates the equations.
Mathematicians never make the connection.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-04 23:23 John A Meinel <john@arbash-meinel.com>
parent: David Roussel <pgsql-performance@diroussel.xsmail.com>
2 siblings, 0 replies; 17+ messages in thread
From: John A Meinel @ 2005-05-04 23:23 UTC (permalink / raw)
To: David Roussel <pgsql-performance@diroussel.xsmail.com>; +Cc: Mischa Sandberg <mischa.sandberg@telus.net>; pgsql-performance
David Roussel wrote:
>>COPY invokes all the same logic as INSERT on the server side
>>(rowexclusive locking, transaction log, updating indexes, rules).
>>The difference is that all the rows are inserted as a single
>>transaction. This reduces the number of fsync's on the xlog,
>>which may be a limiting factor for you. You'll want to crank
>>WAL_BUFFERS and CHECKPOINT_SEGMENTS to match, though.
>>One of my streams has 6K records; I run with WB=1000, CS=128.
>
>
> 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?
I think COPY also has the advantage that for index updates it only grabs
the lock once, rather than grabbing and releasing for each row. But I
believe you are right that fsync only happens on COMMIT.
>
> David
John
=:->
Attachments:
signature.asc (application/pgp-signature, 250B) download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-04 23:29 Christopher Petrilli <petrilli@gmail.com>
parent: Mischa Sandberg <mischa.sandberg@telus.net>
1 sibling, 1 reply; 17+ messages in thread
From: Christopher Petrilli @ 2005-05-04 23:29 UTC (permalink / raw)
To: Mischa Sandberg <mischa.sandberg@telus.net>; +Cc: pgsql-performance
On 5/4/05, Mischa Sandberg <mischa.sandberg@telus.net> wrote:
> Quoting David Roussel <pgsql-performance@diroussel.xsmail.com>:
>
> > > COPY invokes all the same logic as INSERT on the server side
> > > (rowexclusive locking, transaction log, updating indexes, rules).
> > > The difference is that all the rows are inserted as a single
> > > transaction. This reduces the number of fsync's on the xlog,
> > > which may be a limiting factor for you. You'll want to crank
> > > WAL_BUFFERS and CHECKPOINT_SEGMENTS to match, though.
> > > One of my streams has 6K records; I run with WB=1000, CS=128.
> >
> > 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?
>
> Sorry, I was comparing granularities the other way araound. As far as
> xlog is concerned, a COPY is ALWAYS one big txn, no matter how many
> putline commands you use to feed the copy. With inserts, you can choose
> whether to commit every row, every nth row, etc.
>
> Copy makes better use of the TCP connection for transmission. COPY uses
> the TCP connection like a one-way pipe. INSERT is like an RPC: the
> sender has to wait until the insert's return status roundtrips.
I have found even greater performance increases by using COPY FROM
<filename> not COPY FROM STDIN. This allows the backend process to
directly read the file, rather than shoving it over a pipe (thereby
potentially hitting the CPU multiple times). My experience is that
this is anywhere from 5-10x faster than INSERT statements on the
whole, and sometimes 200x.
Chris
--
| Christopher Petrilli
| petrilli@gmail.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-04 23:33 Kris Jurka <books@ejurka.com>
parent: Mischa Sandberg <mischa.sandberg@telus.net>
1 sibling, 1 reply; 17+ messages in thread
From: Kris Jurka @ 2005-05-04 23:33 UTC (permalink / raw)
To: Mischa Sandberg <mischa.sandberg@telus.net>; +Cc: pgsql-performance
On Wed, 4 May 2005, Mischa Sandberg wrote:
> Copy makes better use of the TCP connection for transmission. COPY uses
> the TCP connection like a one-way pipe. INSERT is like an RPC: the
> sender has to wait until the insert's return status roundtrips.
Not true. A client may send any number of Bind/Execute messages on a
prepared statement before a Sync message. So multiple inserts may be sent
in one network roundtrip. This is exactly how the JDBC driver
implements batch statements. There is some limit to the number of queries
in flight at any given moment because there is the potential to deadlock
if both sides of network buffers are filled up and each side is blocked
waiting on a write. The JDBC driver has conservatively selected 256 as
the maximum number of queries to send at once.
Kris Jurka
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-05 01:51 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: David Roussel <pgsql-performance@diroussel.xsmail.com>
2 siblings, 1 reply; 17+ messages in thread
From: Christopher Kings-Lynne @ 2005-05-05 01:51 UTC (permalink / raw)
To: David Roussel <pgsql-performance@diroussel.xsmail.com>; +Cc: Mischa Sandberg <mischa.sandberg@telus.net>; pgsql-performance
> 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.
Chris
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-05 02:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2005-05-05 02:22 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: David Roussel <pgsql-performance@diroussel.xsmail.com>; Mischa Sandberg <mischa.sandberg@telus.net>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-05 04:41 Mischa Sandberg <mischa.sandberg@telus.net>
parent: Kris Jurka <books@ejurka.com>
0 siblings, 1 reply; 17+ messages in thread
From: Mischa Sandberg @ 2005-05-05 04:41 UTC (permalink / raw)
To: pgsql-performance
Quoting Kris Jurka <books@ejurka.com>:
> On Wed, 4 May 2005, Mischa Sandberg wrote:
>
> > Copy makes better use of the TCP connection for transmission. COPY
> uses
> > the TCP connection like a one-way pipe. INSERT is like an RPC: the
> > sender has to wait until the insert's return status roundtrips.
>
> Not true. A client may send any number of Bind/Execute messages on
a
> prepared statement before a Sync message. So multiple inserts may
be
> sent
> in one network roundtrip. This is exactly how the JDBC driver
> implements batch statements. There is some limit to the number of
> queries
> in flight at any given moment because there is the potential to
> deadlock
> if both sides of network buffers are filled up and each side is
> blocked
> waiting on a write. The JDBC driver has conservatively selected 256
> as
> the maximum number of queries to send at once.
Hunh. Interesting optimization in the JDBC driver. I gather it is
sending a string of (;)-separated inserts. Sounds like
efficient-but-risky stuff we did for ODBC drivers at Simba ... gets
interesting when one of the insert statements in the middle fails.
Good to know. Hope that the batch size is parametric, given that you
can have inserts with rather large strings bound to 'text' columns in
PG --- harder to identify BLOBs when talking to PG, than when talking
to MSSQL/Oracle/Sybase.
--
Engineers think equations approximate reality.
Physicists think reality approximates the equations.
Mathematicians never make the connection.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-05 09:15 Kris Jurka <books@ejurka.com>
parent: Mischa Sandberg <mischa.sandberg@telus.net>
0 siblings, 0 replies; 17+ messages in thread
From: Kris Jurka @ 2005-05-05 09:15 UTC (permalink / raw)
To: Mischa Sandberg <mischa.sandberg@telus.net>; +Cc: pgsql-performance
On Wed, 4 May 2005, Mischa Sandberg wrote:
> Quoting Kris Jurka <books@ejurka.com>:
>
> > Not true. A client may send any number of Bind/Execute messages on
> > a prepared statement before a Sync message.
> Hunh. Interesting optimization in the JDBC driver. I gather it is
> sending a string of (;)-separated inserts.
No, it uses the V3 protocol and a prepared statement and uses
Bind/Execute, as I mentioned.
> Sounds like efficient-but-risky stuff we did for ODBC drivers at Simba
> ... gets interesting when one of the insert statements in the middle
> fails.
When running inside a transaction (as you really want to do anyway when
bulk loading) it is well defined, it is a little odd for auto commit mode
though. In autocommit mode the transaction boundary is at the Sync
message, not the individual Execute messages, so you will get some
rollback on error. The JDBC spec is poorly defined in this area, so we
can get away with this.
> Good to know. Hope that the batch size is parametric, given that
> you can have inserts with rather large strings bound to 'text' columns
> in PG --- harder to identify BLOBs when talking to PG, than when talking
> to MSSQL/Oracle/Sybase.
The batch size is not a parameter and I don't think it needs to be. The
issue of filling both sides of network buffers and deadlocking only needs
to be avoided on one side. The response to an insert request is small and
not dependent on the size of the data sent, so we can send as much as we
want as long as the server doesn't send much back to us.
Kris Jurka
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-06 00:53 Keith Worthington <KeithW@NarrowPathInc.com>
parent: Christopher Petrilli <petrilli@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Keith Worthington @ 2005-05-06 00:53 UTC (permalink / raw)
To: Christopher Petrilli <petrilli@gmail.com>; +Cc: pgsql-performance
Christopher Petrilli wrote:
> On 5/4/05, Mischa Sandberg <mischa.sandberg@telus.net> wrote:
>
>>Quoting David Roussel <pgsql-performance@diroussel.xsmail.com>:
>>
>>
>>>>COPY invokes all the same logic as INSERT on the server side
>>>>(rowexclusive locking, transaction log, updating indexes, rules).
>>>>The difference is that all the rows are inserted as a single
>>>>transaction. This reduces the number of fsync's on the xlog,
>>>>which may be a limiting factor for you. You'll want to crank
>>>>WAL_BUFFERS and CHECKPOINT_SEGMENTS to match, though.
>>>>One of my streams has 6K records; I run with WB=1000, CS=128.
>>>
>>>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?
>>
>>Sorry, I was comparing granularities the other way araound. As far as
>>xlog is concerned, a COPY is ALWAYS one big txn, no matter how many
>>putline commands you use to feed the copy. With inserts, you can choose
>>whether to commit every row, every nth row, etc.
>>
>>Copy makes better use of the TCP connection for transmission. COPY uses
>>the TCP connection like a one-way pipe. INSERT is like an RPC: the
>>sender has to wait until the insert's return status roundtrips.
>
>
> I have found even greater performance increases by using COPY FROM
> <filename> not COPY FROM STDIN. This allows the backend process to
> directly read the file, rather than shoving it over a pipe (thereby
> potentially hitting the CPU multiple times). My experience is that
> this is anywhere from 5-10x faster than INSERT statements on the
> whole, and sometimes 200x.
>
> Chris
>
Unfortunately, COPY FROM '<file>' can only be done by a superuser. If
you that option then that is great. If not...
--
Kind Regards,
Keith
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-06 06:51 Jim C. Nasby <decibel@decibel.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 17+ messages in thread
From: Jim C. Nasby @ 2005-05-06 06:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; David Roussel <pgsql-performance@diroussel.xsmail.com>; Mischa Sandberg <mischa.sandberg@telus.net>; pgsql-performance
On Wed, May 04, 2005 at 10:22:56PM -0400, Tom Lane wrote:
> 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.
Has thought been given to supporting inserting multiple rows in a single
insert? DB2 supported:
INSERT INTO table VALUES(
(1,2,3),
(4,5,6),
(7,8,9)
);
I'm not sure how standard that is or if other databases support it.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-06 07:30 Dennis Bjorklund <db@zigo.dhs.org>
parent: Jim C. Nasby <decibel@decibel.org>
1 sibling, 2 replies; 17+ messages in thread
From: Dennis Bjorklund @ 2005-05-06 07:30 UTC (permalink / raw)
To: Jim C. Nasby <decibel@decibel.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; David Roussel <pgsql-performance@diroussel.xsmail.com>; Mischa Sandberg <mischa.sandberg@telus.net>; pgsql-performance
On Fri, 6 May 2005, Jim C. Nasby wrote:
> Has thought been given to supporting inserting multiple rows in a single
> insert? DB2 supported:
>
> INSERT INTO table VALUES(
> (1,2,3),
> (4,5,6),
> (7,8,9)
> );
>
> I'm not sure how standard that is or if other databases support it.
The sql standard include this, except that you can not have the outer ().
So it should be
INSERT INTO table VALUES
(1,2,3),
(4,5,6),
(7,8,9);
Do DB2 demand these extra ()?
--
/Dennis Björklund
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-06 09:27 Harald Fuchs <use_reply_to@protecting.net>
parent: Dennis Bjorklund <db@zigo.dhs.org>
1 sibling, 0 replies; 17+ messages in thread
From: Harald Fuchs @ 2005-05-06 09:27 UTC (permalink / raw)
To: pgsql-performance
In article <Pine.LNX.4.44.0505060927520.7072-100000@zigo.dhs.org>,
Dennis Bjorklund <db@zigo.dhs.org> writes:
> On Fri, 6 May 2005, Jim C. Nasby wrote:
>> Has thought been given to supporting inserting multiple rows in a single
>> insert? DB2 supported:
>>
>> INSERT INTO table VALUES(
>> (1,2,3),
>> (4,5,6),
>> (7,8,9)
>> );
>>
>> I'm not sure how standard that is or if other databases support it.
> The sql standard include this, except that you can not have the outer ().
> So it should be
> INSERT INTO table VALUES
> (1,2,3),
> (4,5,6),
> (7,8,9);
Since MySQL has benn supporting this idiom for ages, it can't be
standard ;-)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-06 12:38 Bruno Wolff III <bruno@wolff.to>
parent: Jim C. Nasby <decibel@decibel.org>
1 sibling, 1 reply; 17+ messages in thread
From: Bruno Wolff III @ 2005-05-06 12:38 UTC (permalink / raw)
To: Jim C. Nasby <decibel@decibel.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; David Roussel <pgsql-performance@diroussel.xsmail.com>; Mischa Sandberg <mischa.sandberg@telus.net>; pgsql-performance
On Fri, May 06, 2005 at 01:51:29 -0500,
"Jim C. Nasby" <decibel@decibel.org> wrote:
> On Wed, May 04, 2005 at 10:22:56PM -0400, Tom Lane wrote:
> > 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.
>
> Has thought been given to supporting inserting multiple rows in a single
> insert? DB2 supported:
>
> INSERT INTO table VALUES(
> (1,2,3),
> (4,5,6),
> (7,8,9)
> );
>
> I'm not sure how standard that is or if other databases support it.
It's on the TODO list. I don't remember anyone bringing this up for about
a year now, so I doubt anyone is actively working on it.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-06 13:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2005-05-06 13:51 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Jim C. Nasby <decibel@decibel.org>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; David Roussel <pgsql-performance@diroussel.xsmail.com>; Mischa Sandberg <mischa.sandberg@telus.net>; pgsql-performance
Bruno Wolff III <bruno@wolff.to> writes:
> "Jim C. Nasby" <decibel@decibel.org> wrote:
>> Has thought been given to supporting inserting multiple rows in a single
>> insert?
> It's on the TODO list. I don't remember anyone bringing this up for about
> a year now, so I doubt anyone is actively working on it.
It is on TODO but I think it is only there for standards compliance.
It won't produce near as much of a speedup as using COPY does ---
in particular, trying to put thousands of rows through at once with
such a command would probably be a horrible idea. You'd still have
to pay the price of lexing/parsing, and there would also be considerable
flailing about with deducing the data type of the VALUES() construct.
(Per spec that can be used in SELECT FROM, not only in INSERT, and so
it's not clear to what extent we can use knowledge of the insert target
columns to avoid running the generic union-type-resolution algorithm for
each column of the VALUES() :-(.) Add on the price of shoving an
enormous expression tree through the planner and executor, and it starts
to sound pretty grim.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: COPY vs INSERT
@ 2005-05-08 17:12 Jim C. Nasby <decibel@decibel.org>
parent: Dennis Bjorklund <db@zigo.dhs.org>
1 sibling, 0 replies; 17+ messages in thread
From: Jim C. Nasby @ 2005-05-08 17:12 UTC (permalink / raw)
To: Dennis Bjorklund <db@zigo.dhs.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; David Roussel <pgsql-performance@diroussel.xsmail.com>; Mischa Sandberg <mischa.sandberg@telus.net>; pgsql-performance
On Fri, May 06, 2005 at 09:30:46AM +0200, Dennis Bjorklund wrote:
> The sql standard include this, except that you can not have the outer ().
> So it should be
>
> INSERT INTO table VALUES
> (1,2,3),
> (4,5,6),
> (7,8,9);
>
> Do DB2 demand these extra ()?
My recollection is that it does, but it's been a few years...
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2005-05-08 17:12 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-03 18:53 Re: COPY vs INSERT Mischa Sandberg <mischa.sandberg@telus.net>
2005-05-04 22:41 ` David Roussel <pgsql-performance@diroussel.xsmail.com>
2005-05-04 23:11 ` Mischa Sandberg <mischa.sandberg@telus.net>
2005-05-04 23:29 ` Christopher Petrilli <petrilli@gmail.com>
2005-05-06 00:53 ` Keith Worthington <KeithW@NarrowPathInc.com>
2005-05-04 23:33 ` Kris Jurka <books@ejurka.com>
2005-05-05 04:41 ` Mischa Sandberg <mischa.sandberg@telus.net>
2005-05-05 09:15 ` Kris Jurka <books@ejurka.com>
2005-05-04 23:23 ` John A Meinel <john@arbash-meinel.com>
2005-05-05 01:51 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-05-05 02:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-06 06:51 ` Jim C. Nasby <decibel@decibel.org>
2005-05-06 07:30 ` Dennis Bjorklund <db@zigo.dhs.org>
2005-05-06 09:27 ` Harald Fuchs <use_reply_to@protecting.net>
2005-05-08 17:12 ` Jim C. Nasby <decibel@decibel.org>
2005-05-06 12:38 ` Bruno Wolff III <bruno@wolff.to>
2005-05-06 13:51 ` Tom Lane <tgl@sss.pgh.pa.us>
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