Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id C36221337B55 for ; Tue, 26 Oct 2010 08:44:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 43925-03 for ; Tue, 26 Oct 2010 11:44:31 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm4-vm0.bullet.mail.ac4.yahoo.com (nm4-vm0.bullet.mail.ac4.yahoo.com [98.139.53.206]) by mail.postgresql.org (Postfix) with SMTP id E82981337B4E for ; Tue, 26 Oct 2010 08:44:29 -0300 (ADT) Received: from [98.139.52.195] by nm4.bullet.mail.ac4.yahoo.com with NNFMP; 26 Oct 2010 11:44:29 -0000 Received: from [98.139.52.178] by tm8.bullet.mail.ac4.yahoo.com with NNFMP; 26 Oct 2010 11:44:29 -0000 Received: from [127.0.0.1] by omp1061.mail.ac4.yahoo.com with NNFMP; 26 Oct 2010 11:44:29 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 488225.97726.bm@omp1061.mail.ac4.yahoo.com Received: (qmail 45205 invoked by uid 60001); 26 Oct 2010 11:44:29 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1288093469; bh=eI9uxoBTP99mfmMp1dnLHcsWNFxT9sqOsVPPRAYLcmw=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=adHLL7K9oyXx8gVksl5elcHl+30aeGPHWueRIUYO+Fk4d6GQoOY/mMPkGpCuskGHCncZn8Db5ixH0ACDpMMVvwUFowpCy3+4T1aUiqHCb/cuUVPYX81IDUbQcFPHmYk4Uw1ONvyfsjXbiJC8xEuqfIbWo+f0RoHMxrLf6T59AEM= 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=oeOX7aE4I4T3O9ip21nBY46SHNEAfGZUUyHM5eYkvPylFR56EKObTVcb4ANwIc4nVCYC8ugqbYzcnPezW1BYrWZTSj9bDw8GaFjXKDzkcjs6kAD6sejVsMfU1HU0lwh5ZHU207BYDLXfALAYul1oRbqEcwGj3Gh5d2DP6qsjwQc=; Message-ID: <208728.42197.qm@web65409.mail.ac4.yahoo.com> X-YMail-OSG: sYuAa4gVM1kuMRj70sNfHQSZeDt8.8n2S.xh_Jm0bMvuBRA uzSq0wxSqfAvXGVshQC4rragfHGv6j7z6aB5KLTdmUejtWYKEjgzwPaT2Xcl oAwhMKIKEImhBLsoLCbK6rjN3DkYT5y8jL6RTOLtispeuHbjbUPLJriR1qtx Mvnfau4_fnOiqjZu8jpH7JT8sBMPqMsQ.zJnsIe2d5E4jOFFnCj9Kb9TA.hb bqpetrX795Khqtyqvqx1VOChFMytiTtPoKviVA2d7iM.vwi6LcyaqNUg7giZ dotBpC7JuXPQj_g.LwDZVDWFXnUs01Q1_lrh1pt6KJ1MgA0G0sPVit047Woy LrLzRsCo- Received: from [194.237.142.6] by web65409.mail.ac4.yahoo.com via HTTP; Tue, 26 Oct 2010 04:44:28 PDT X-Mailer: YahooMailRC/504.5 YahooMailWebService/0.8.107.284920 References: <402913.53404.qm@web65405.mail.ac4.yahoo.com> Date: Tue, 26 Oct 2010 04:44:28 -0700 (PDT) From: Divakar Singh Subject: Re: Postgres insert performance and storage requirement compared to Oracle To: Merlin Moncure Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1333829222-1288093468=:42197" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/420 X-Sequence-Number: 40838 --0-1333829222-1288093468=:42197 Content-Type: text/plain; charset=us-ascii Hi Merlin, Thanks for your quick input. Well 1 difference worth mentioning: I am inserting each row in a separate transaction, due to design of my program. -Divakar ________________________________ From: Merlin Moncure To: Divakar Singh Cc: pgsql-performance@postgresql.org Sent: Tue, October 26, 2010 2:21:02 AM Subject: Re: [PERFORM] Postgres insert performance and storage requirement compared to Oracle On Mon, Oct 25, 2010 at 4:28 PM, Merlin Moncure wrote: > I ran the following tests w/libpqtypes. While you probably wont end > up using libpqtypes, it's illustrative to mention it because it's > generally the easiest way to get data into postgres and by far the > fastest (excluding 'COPY'). source code follows after the sig (I > banged it out quite quickly, it's messy!) :-). I am not seeing your > results. I had a really horrible bug in there -- leaking a param inside the array push loop. cleaning it up dropped another 5 seconds or so from the 4th million inserted to the remote server!. Using local prepare (PQspecPrepare) prob another second or two could be shaved off. PGparam *t = PQparamCreate(conn); for(i=0; i
Hi Merlin,
Thanks for your quick input.
Well 1 difference worth mentioning:
I am inserting each row in a separate transaction, due to design of my program.
 
-Divakar


From: Merlin Moncure <mmoncure@gmail.com>
To: Divakar Singh <dpsmails@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Tue, October 26, 2010 2:21:02 AM
Subject: Re: [PERFORM] Postgres insert performance and storage requirement compared to Oracle

On Mon, Oct 25, 2010 at 4:28 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> I ran the following tests w/libpqtypes.  While you probably wont end
> up using libpqtypes, it's illustrative to mention it because it's
> generally the easiest way to get data into postgres and by far the
> fastest (excluding 'COPY').  source code follows after the sig (I
> banged it out quite quickly, it's messy!) :-).  I am not seeing your
> results.

I had a really horrible bug in there -- leaking a param inside the
array push loop.  cleaning it up dropped another 5 seconds or so from
the 4th million inserted to the remote server!.  Using local prepare
(PQspecPrepare) prob another second or two could be shaved off.

PGparam *t = PQparamCreate(conn);

for(i=0; i<INS_COUNT; i++)
{
  PGint4 a=i;
  PGtext b = "some_text";
  PGtimestamp c;
  PGbytea d;

  d.len = 8;
  d.data = b;

  c.date.isbc  = 0;
  c.date.year  = 2000;
  c.date.mon    = 0;
  c.date.mday  = 19;
  c.time.hour  = 10;
  c.time.min    = 41;
  c.time.sec    = 6;
  c.time.usec  = 0;
  c.time.gmtoff = -18000;

  PQputf(t, "%int4 %text %timestamptz %bytea", a, b, &c, &d);
  PQputf(arr.param, "%ins_test", t);
  PQparamReset(t);
}

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-1333829222-1288093468=:42197--