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

Re: Postgres insert performance and storage requirement compared to Oracle

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: 2010-10-26 23:16:53
Message-ID: AANLkTi=-fK6ODDNJPntq3OeUJQzKtHbsmkdDM851waOG@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Tue, Oct 26, 2010 at 6:50 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala
> <mladen(dot)gogala(at)vmsinfo(dot)com> wrote:
>> 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.
>
> This is just not true.  ON COMMIT DELETE ROWS simply arranges for a
> TRUNCATE to happen immediately before each commit.  See
> PreCommit_on_commit_actions() in tablecmds.c.

quite so.  If you are doing anything performance sensitive with 'on
commit drop', you are better off organizing a cache around
txid_current() (now(), pid for older pg versions).  Skips the writes
to the system catalogs and truncate.

merlin

In response to

Responses

pgsql-performance by date

Next:From: Ozer, PamDate: 2010-10-26 23:27:28
Subject: Slow Query- Bad Row Estimate
Previous:From: Tom LaneDate: 2010-10-26 22:51:31
Subject: Re: Select count(*), the sequel

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-10-26 23:36:08
Subject: Re: psql: Don't close stdin, don't leak file descriptor with ON_ERROR_STOP
Previous:From: Robert HaasDate: 2010-10-26 22:50:37
Subject: Re: Postgres insert performance and storage requirement compared to Oracle

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