pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedoptimal insert
7+ messages / 5 participants
[nested] [flat]
* optimal insert
@ 2006-10-08 21:04 Dirk Jagdmann <jagdmann@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Dirk Jagdmann @ 2006-10-08 21:04 UTC (permalink / raw)
To: pgsql-sql
Hello experts,
I have a database that contains three tables:
create table a (
id serial primary key,
... -- 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,
b int not null references b
);
Tables a and b have already been filled with lots of rows. Now my
application needs to insert the relationship of a to b into table a_b
and is currently doing it with inserts like the following:
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);
So for a batch of inserts the value of a stays the same, while for by
arbitrary values are inserted. Now I have wondered if PostreSQL offers
a smarter way to insert those values? A solution can contains usage of
some plpgsql code.
--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: optimal insert
@ 2006-10-09 04:09 Bruno Wolff III <bruno@wolff.to>
parent: Dirk Jagdmann <jagdmann@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2006-10-09 04:09 UTC (permalink / raw)
To: Dirk Jagdmann <jagdmann@gmail.com>; +Cc: pgsql-sql
On Sun, Oct 08, 2006 at 23:04:02 +0200,
Dirk Jagdmann <jagdmann@gmail.com> wrote:
>
> 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);
>
> So for a batch of inserts the value of a stays the same, while for by
> arbitrary values are inserted. Now I have wondered if PostreSQL offers
> a smarter way to insert those values? A solution can contains usage of
> some plpgsql code.
I don't think you are going to be able to save anything by 'a' being fixed
unless there is a way to compute 'b', so that you can use a select statement.
Just do the normal stuff you would when speeding up inserts. The biggest
probably being wrapping them all up in one transaction.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: optimal insert
@ 2006-10-10 20:45 Aaron Bono <postgresql@aranya.com>
parent: Dirk Jagdmann <jagdmann@gmail.com>
2 siblings, 2 replies; 7+ messages in thread
From: Aaron Bono @ 2006-10-10 20:45 UTC (permalink / raw)
To: Dirk Jagdmann <jagdmann@gmail.com>; +Cc: pgsql-sql
On 10/8/06, Dirk Jagdmann <jagdmann@gmail.com> wrote:
>
> Hello experts,
>
> I have a database that contains three tables:
>
> create table a (
> id serial primary key,
> ... -- 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,
> b int not null references b
> );
>
> Tables a and b have already been filled with lots of rows. Now my
> application needs to insert the relationship of a to b into table a_b
> and is currently doing it with inserts like the following:
>
> 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);
>
> So for a batch of inserts the value of a stays the same, while for by
> arbitrary values are inserted. Now I have wondered if PostreSQL offers
> a smarter way to insert those values? A solution can contains usage of
> some plpgsql code.
It depends on your logic. If you can write a query that selects out the a
and b records, there is a smart way:
insert into a_b(a, b)
select 1, b.id from b where b.id in (100, 200, 54, 4577);
This is not really smart because you already have the id values - the select
may diminish your performance rather than help. But if the select is
"smarter" then you don't even need to know what the b.id values are:
insert into a_b(a, b)
select 1, b.id from b where b.somecolumn = 'somevalue';
Hope this helps.
> ==================================================================
> Aaron Bono
> Aranya Software Technologies, Inc.
> http://www.aranya.com
> http://codeelixir.com
> ==================================================================
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: optimal insert
@ 2006-10-10 21:04 George Pavlov <gpavlov@mynewplace.com>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 1 reply; 7+ messages in thread
From: George Pavlov @ 2006-10-10 21:04 UTC (permalink / raw)
To: Dirk Jagdmann <jagdmann@gmail.com>; +Cc: pgsql-sql
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
> [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
>
> On 10/8/06, Dirk Jagdmann <jagdmann@gmail.com> wrote:
>
> Hello experts,
>
> I have a database that contains three tables:
>
> create table a (
> id serial primary key,
> ... -- 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,
> b int not null references b
> );
>
> Tables a and b have already been filled with lots of
> rows. Now my
> application needs to insert the relationship of a to b
> into table a_b
> and is currently doing it with inserts like the following:
>
> 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);
>
> So for a batch of inserts the value of a stays the
> same, while for by
> arbitrary values are inserted. Now I have wondered if
> PostreSQL offers
> a smarter way to insert those values? A solution can
> contains usage of
> some plpgsql code.
>
>
> It depends on your logic. If you can write a query that
> selects out the a and b records, there is a smart way:
>
> insert into a_b(a, b)
> select 1, b.id from b where b.id in (100, 200, 54, 4577);
>
> This is not really smart because you already have the id
> values - the select may diminish your performance rather than
> help. But if the select is "smarter" then you don't even
> need to know what the b.id <http://b.id; values are:
>
> insert into a_b(a, b)
> select 1, b.id from b where b.somecolumn = 'somevalue';
>
> Hope this helps.
>
>
>
> ==================================================================
> Aaron Bono
> Aranya Software Technologies, Inc.
> http://www.aranya.com
> http://codeelixir.com
>
> ==================================================================
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: optimal insert
@ 2006-10-10 21:12 Dirk Jagdmann <jagdmann@gmail.com>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 0 replies; 7+ messages in thread
From: Dirk Jagdmann @ 2006-10-10 21:12 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-sql
Hello Aaron,
thank you for your suggestion. I will have to think if something
similar would be of any benefit for my data.
--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: optimal insert
@ 2006-10-10 21:15 Dirk Jagdmann <jagdmann@gmail.com>
parent: George Pavlov <gpavlov@mynewplace.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dirk Jagdmann @ 2006-10-10 21:15 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; +Cc: pgsql-sql
Hello George,
> And don't forget that \COPY and especially COPY are usually much faster
> (and, IMHO, easier to compose/maintain) than gobs of INSERTs.
I did not forget, but my application uses embedded SQL (with the epcg
preprocessor) and I don't think it can handle COPYs :(
--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: optimal insert
@ 2006-10-11 12:31 Markus Schaber <schabi@logix-tt.com>
parent: Dirk Jagdmann <jagdmann@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Markus Schaber @ 2006-10-11 12:31 UTC (permalink / raw)
To: Dirk Jagdmann <jagdmann@gmail.com>; +Cc: pgsql-sql
Hi, Dirk,
Dirk Jagdmann wrote:
> So for a batch of inserts the value of a stays the same, while for by
> arbitrary values are inserted. Now I have wondered if PostreSQL offers
> a smarter way to insert those values? A solution can contains usage of
> some plpgsql code.
[local]:postgres=# insert into a_b (a,b) SELECT 1,
(ARRAY[100,200,54,4577])[i] FROM generate_series(1,4) AS i;
INSERT 0 4
[local]:postgres=# select * from a_b;
a | b
---+------
1 | 100
1 | 200
1 | 54
1 | 4577
But I tend to agree with others that packing a whole bunch of INSERTs
into a single transaction, and using COPY instead will give you better
benefits.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-10-11 12:31 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-08 21:04 optimal insert Dirk Jagdmann <jagdmann@gmail.com>
2006-10-09 04:09 ` Bruno Wolff III <bruno@wolff.to>
2006-10-10 20:45 ` Aaron Bono <postgresql@aranya.com>
2006-10-10 21:04 ` George Pavlov <gpavlov@mynewplace.com>
2006-10-10 21:15 ` Dirk Jagdmann <jagdmann@gmail.com>
2006-10-10 21:12 ` Dirk Jagdmann <jagdmann@gmail.com>
2006-10-11 12:31 ` Markus Schaber <schabi@logix-tt.com>
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