Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 76E411337B5B for ; Wed, 27 Oct 2010 00:11:11 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 00792-05 for ; Wed, 27 Oct 2010 03:10:58 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm28-vm0.bullet.mail.ac4.yahoo.com (nm28-vm0.bullet.mail.ac4.yahoo.com [98.139.52.246]) by mail.postgresql.org (Postfix) with SMTP id 7D5281336DB6 for ; Wed, 27 Oct 2010 00:10:58 -0300 (ADT) Received: from [98.139.52.195] by nm28.bullet.mail.ac4.yahoo.com with NNFMP; 27 Oct 2010 03:10:56 -0000 Received: from [98.139.52.128] by tm8.bullet.mail.ac4.yahoo.com with NNFMP; 27 Oct 2010 03:10:56 -0000 Received: from [127.0.0.1] by omp1011.mail.ac4.yahoo.com with NNFMP; 27 Oct 2010 03:10:56 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 559597.94591.bm@omp1011.mail.ac4.yahoo.com Received: (qmail 44576 invoked by uid 60001); 27 Oct 2010 03:10:56 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1288149056; bh=glWfxjWOJmLf846is7Yl5039BhfDmOPcf0xpMTvysds=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=yOS9kzSy/fwaZDaKaMMDp7pQ8GRzfnzTn3Vb9rQuCd8zLHJCCjS/0iBkQO2xTvEp0ZMW9lnL57kKho9zQtV12nF4fI7V0R+/pBo7I3DiqMRN0s5B/aKKsY9b3OUxAcGgadLl1kUdXVce0u8Ss1VMD7kr1FU37c8AFuFI2y8WZWo= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=hxuh2e5NcM9HQfRAQz9ZU3bShPZbgd8syELNHD2NwC5N+dKt39VqFY8QYkuw98/Woa4FwFB0a0bXbLqa3ef/tPtkbFiuMKXglWX96ksppIKoYYlQHzPnF93j2preCEYtGQwhvoDDgWmjHCCs2y4HJktD5LDmnhZridh2UZz89JU=; Message-ID: <252538.42200.qm@web65405.mail.ac4.yahoo.com> X-YMail-OSG: bJkjUBwVM1kOo7IkGY56alVyYEBgzpoyEoqnDQ.V.zN9hcq kk_mKDF.FQn4g2xDgmEzVyxIB_FScu1EBenxJMcOQb7cybEF53IQe7REhORX YAgtrW.RpM5tMXg_bxTNloHeFG9vZxjL52W1KoUZDM.YQ_a7hvCJDG82Q083 tP33lgx0_7K_xNRI6P9Jtovk1HsfeB_PwThZfTxwGXfw_yUonzOJ04rbxglv Vupd_BeYghdJ9PUSvzAgwFS562gRi2qIB97mkKnCyUlymPs_ImGDSZ5JnZ6Y snVvUINaI40FmwmAi2GLvDVIsG.Zo2zXWLL43HQaSNdlEGmXoKfSquIrpkAV 3T3B7Be.FIXla60MCq_sPV8_xh4u77YkeMHh4.jCQWA-- Received: from [194.237.142.20] by web65405.mail.ac4.yahoo.com via HTTP; Tue, 26 Oct 2010 20:10:56 PDT X-Mailer: YahooMailRC/504.5 YahooMailWebService/0.8.107.284920 References: <402913.53404.qm@web65405.mail.ac4.yahoo.com> <208728.42197.qm@web65409.mail.ac4.yahoo.com> <770715.39613.qm@web29003.mail.ird.yahoo.com> <4CC741FF.8050709@vmsinfo.com> <4CC74E31.6060307@vmsinfo.com> Date: Tue, 26 Oct 2010 20:10:56 -0700 (PDT) From: Divakar Singh Subject: Re: Postgres insert performance and storage requirement compared to Oracle To: Merlin Moncure , Robert Haas Cc: Mladen Gogala , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-722794063-1288149056=:42200" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.594 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, SARE_MILLIONSOF=0.315, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/578 X-Sequence-Number: 40996 --0-722794063-1288149056=:42200 Content-Type: text/plain; charset=us-ascii Dear All, Thanks for your inputs on the insert performance part. Any suggestion on storage requirement? VACUUM is certainly not an option, because this is something related to maintenance AFTER insertion. I am talking about the plain storage requirement w.r. to Oracle, which I observed is twice of Oracle in case millions of rows are inserted. Anybody who tried to analyze the average storage requirement of PG w.r. to Oracle? Best Regards, Divakar ________________________________ From: Merlin Moncure To: Robert Haas Cc: Mladen Gogala ; pgsql-performance@postgresql.org Sent: Wed, October 27, 2010 4:46:53 AM Subject: Re: [PERFORM] Postgres insert performance and storage requirement compared to Oracle On Tue, Oct 26, 2010 at 6:50 PM, Robert Haas wrote: > On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala > 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance --0-722794063-1288149056=:42200 Content-Type: text/html; charset=us-ascii
Dear All,
Thanks for your inputs on the insert performance part.
Any suggestion on storage requirement?
VACUUM is certainly not an option, because this is something related to maintenance AFTER insertion.
I am talking about the plain storage requirement w.r. to Oracle, which I observed is twice of Oracle in case millions of rows are inserted.
Anybody who tried to analyze the average storage requirement of PG w.r. to Oracle?
 
Best Regards,
Divakar


From: Merlin Moncure <mmoncure@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>; pgsql-performance@postgresql.org
Sent: Wed, October 27, 2010 4:46:53 AM
Subject: Re: [PERFORM] Postgres insert performance and storage requirement compared to Oracle

On Tue, Oct 26, 2010 at 6:50 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala
> <mladen.gogala@vmsinfo.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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--0-722794063-1288149056=:42200--