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: Divakar Singh <dpsmails(at)yahoo(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: 2010-10-25 20:51:02
Message-ID: AANLkTi=cy67frLbx+YH_uUk5vqPsxt8u-Q_cpU74pYMq@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Mon, Oct 25, 2010 at 4:28 PM, Merlin Moncure <mmoncure(at)gmail(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Mark RostronDate: 2010-10-25 22:33:47
Subject: interpret statement log duration information
Previous:From: Merlin MoncureDate: 2010-10-25 20:28:57
Subject: Re: Postgres insert performance and storage requirement compared to Oracle

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-10-25 20:58:15
Subject: Re: ask for review of MERGE
Previous:From: James CloosDate: 2010-10-25 20:35:23
Subject: Re: Floating-point timestamps versus Range Types

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