Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: optimal insert
Date: Tue, 10 Oct 2006 14:04:10 -0700
Message-ID: 
 <8C5B026B51B6854CBE88121DBF097A864DF146@ehost010-33.exch010.intermedia.net>
In-Reply-To: <bf05e51c0610101345k293ce702mc2e3e6985ef18ad7@mail.gmail.com>
Thread-Topic: [SQL] optimal insert
Thread-Index: AcbsrPBH+KS+FYynQZOFhTAeNFz0pQAAeuJQ
From: "George Pavlov" <gpavlov@mynewplace.com>
To: "Dirk Jagdmann" <jagdmann@gmail.com>
Cc: <pgsql-sql@postgresql.org>

And don't forget that \COPY and especially COPY are usually much faster
(and, IMHO, easier to compose/maintain) than gobs of INSERTs.


> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org=20
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Aaron Bono
> Sent: Tuesday, October 10, 2006 1:46 PM
> To: Dirk Jagdmann
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] optimal insert
>=20
> On 10/8/06, Dirk Jagdmann <jagdmann@gmail.com> wrote:=20
>=20
> 	Hello experts,
> =09
> 	I have a database that contains three tables:
> =09
> 	create table a (
> 	id serial primary key,=20
> 	... -- some more fields not relevant for my question
> 	);
> 	create table b (
> 	id serial primary key,
> 	... -- some more fields not relevant for my question
> 	);
> 	create table a_b (
> 	a int not null references a,=20
> 	b int not null references b
> 	);
> =09
> 	Tables a and b have already been filled with lots of=20
> rows. Now my
> 	application needs to insert the relationship of a to b=20
> into table a_b
> 	and is currently doing it with inserts like the following:=20
> =09
> 	insert into a_b(a,b) values(1,100);
> 	insert into a_b(a,b) values(1,200);
> 	insert into a_b(a,b) values(1,54);
> 	insert into a_b(a,b) values(1,4577);
> =09
> 	So for a batch of inserts the value of a stays the=20
> same, while for by=20
> 	arbitrary values are inserted. Now I have wondered if=20
> PostreSQL offers
> 	a smarter way to insert those values? A solution can=20
> contains usage of
> 	some plpgsql code.
>=20
> =20
> It depends on your logic.  If you can write a query that=20
> selects out the a and b records, there is a smart way:
> =20
> insert into a_b(a, b)
> select 1, b.id from b where b.id in (100, 200, 54, 4577);
> =20
> This is not really smart because you already have the id=20
> values - the select may diminish your performance rather than=20
> help.  But if the select is "smarter" then you don't even=20
> need to know what the b.id <http://b.id>  values are:
> =20
> insert into a_b(a, b)
> select 1, b.id from b where b.somecolumn =3D 'somevalue';
> =20
> Hope this helps.
> =20
>=20
> =09
> =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> 	   Aaron Bono
> 	   Aranya Software Technologies, Inc.=20
> 	   http://www.aranya.com
> 	   http://codeelixir.com
> =09
> =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
>=20
>=20