Skip site navigation (1) Skip section navigation (2)

Re: Postgres insert performance and storage requirement compared to Oracle

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: 2010-10-26 21:54:57
Message-ID: 4CC74E31.6060307@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On 10/26/2010 5:27 PM, Jon Nelson wrote:
> start loop:
>    populate rows in temporary table
>    insert from temporary table into permanent table
>    truncate temporary table
>    loop
>
> I do something similar, where I COPY data to a temporary table, do
> lots of manipulations, and then perform a series of INSERTS from the
> temporary table into a permanent table.
>

1) It's definitely not faster because you have to insert into the 
temporary table, in addition to inserting into the permanent table.
2) This is what I had in mind:

mgogala=# create table a(c1 int);
CREATE TABLE
mgogala=# create temporary table t1(c1 int) on commit delete rows;
CREATE TABLE
mgogala=# begin;
BEGIN
mgogala=# insert into t1 select generate_series(1,1000);
INSERT 0 1000
mgogala=# insert into a select * from t1;
INSERT 0 1000
mgogala=# commit;
COMMIT
mgogala=# select count(*) from a;
  count
-------
   1000
(1 row)

mgogala=# select count(*) from t1;
  count
-------
      0
(1 row)

The table is created with "on commit obliterate rows" option which means 
that there is no need to do "truncate". The "truncate" command is a 
heavy artillery. Truncating a temporary table is like shooting ducks in 
a duck pond, with a howitzer.

-- 

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions




In response to

Responses

pgsql-performance by date

Next:From: Merlin MoncureDate: 2010-10-26 22:14:15
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Previous:From: Jon NelsonDate: 2010-10-26 21:27:23
Subject: Re: Postgres insert performance and storage requirement compared to Oracle

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2010-10-26 22:14:15
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Previous:From: Robert HaasDate: 2010-10-26 21:32:58
Subject: Re: Simplifying replication

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group