Re: Postgres insert performance and storage requirement compared to Oracle

[Divakar Singh <dpsmails@xxxxxxxxx>]

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@xxxxxxxxx>
To: Divakar Singh <dpsmails@xxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
Sent: Tue, October 26, 2010 2:21:02 AM
Subject: Re: Postgres insert performance and storage requirement compared to Oracle

On Mon, Oct 25, 2010 at 4:28 PM, Merlin Moncure <mmoncure@xxxxxxxxx> 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 = "">
  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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance