From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Postgres insert performance and storage requirement compared to Oracle |
Date: | 2010-10-26 22:14:15 |
Message-ID: | AANLkTikOew+aGf80kZX9cYYFKBQ_AuoUXEvRVc4xpyJx@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala
<mladen(dot)gogala(at)vmsinfo(dot)com> wrote:
> 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.
You are not paying attention ;-). Look upthread: "I am inserting each
row in a separate transaction, due to design of my program." (also on
commit/drop is no picnic either, but I digress...)
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2010-10-26 22:50:37 | Re: Postgres insert performance and storage requirement compared to Oracle |
Previous Message | Mladen Gogala | 2010-10-26 21:54:57 | Re: Postgres insert performance and storage requirement compared to Oracle |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2010-10-26 22:45:30 | Re: Select count(*), the sequel |
Previous Message | Mladen Gogala | 2010-10-26 21:54:57 | Re: Postgres insert performance and storage requirement compared to Oracle |