From: Divakar Singh <dpsmails@yahoo.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: Tue, 26 Oct 2010 04:44:28 -0700 (PDT)
Message-ID: <208728.42197.qm@web65409.mail.ac4.yahoo.com> (raw)
In-Reply-To: <AANLkTi=cy67frLbx+YH_uUk5vqPsxt8u-Q_cpU74pYMq@mail.gmail.com>
References: <402913.53404.qm@web65405.mail.ac4.yahoo.com>
<AANLkTi=0iZDF7EHebukEJ+jxFN8giCy3iXxYuSe_mLUY@mail.gmail.com>
<AANLkTi=cy67frLbx+YH_uUk5vqPsxt8u-Q_cpU74pYMq@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dpsmails@yahoo.com, mmoncure@gmail.com
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
In-Reply-To: <208728.42197.qm@web65409.mail.ac4.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox