From: Mladen Gogala <mladen.gogala@vmsinfo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: Tue, 26 Oct 2010 17:54:57 -0400
Message-ID: <4CC74E31.6060307@vmsinfo.com> (raw)
In-Reply-To: <AANLkTinntqfT1b9xaRfGGqVwwsGhyA7=JhLsuQ10Xzyy@mail.gmail.com>
References: <402913.53404.qm@web65405.mail.ac4.yahoo.com>
<AANLkTi=0iZDF7EHebukEJ+jxFN8giCy3iXxYuSe_mLUY@mail.gmail.com>
<AANLkTi=cy67frLbx+YH_uUk5vqPsxt8u-Q_cpU74pYMq@mail.gmail.com>
<208728.42197.qm@web65409.mail.ac4.yahoo.com>
<AANLkTikeYj52q1v1RcwCYR=9Kv5mxJmh+Q+KvXcmibYK@mail.gmail.com>
<770715.39613.qm@web29003.mail.ird.yahoo.com>
<AANLkTinaW3ZtGJbzgAARG0WfAOJxSPAbdVAZa6X_2dh+@mail.gmail.com>
<4CC741FF.8050709@vmsinfo.com>
<AANLkTinntqfT1b9xaRfGGqVwwsGhyA7=JhLsuQ10Xzyy@mail.gmail.com>
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
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: mladen.gogala@vmsinfo.com
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
In-Reply-To: <4CC74E31.6060307@vmsinfo.com>
* 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