Skip site navigation (1) Skip section navigation (2)

Re: Postgres insert performance and storage requirement compared to Oracle

From: Alex Hunsaker <badalex(at)gmail(dot)com>
To: Divakar Singh <dpsmails(at)yahoo(dot)com>
Cc: Steve Singer <ssinger(at)ca(dot)afilias(dot)info>, jd(at)commandprompt(dot)com, pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: 2010-10-27 19:45:06
Message-ID: AANLkTi=0PkVscoYkMn-nETn16LAL4N-C8jzwGWffn7uV@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Oct 27, 2010 at 08:00, Divakar Singh <dpsmails(at)yahoo(dot)com> wrote:
> I am attaching my code below.
> Is any optimization possible in this?
> Do prepared statements help in cutting down the insert time to half for this
> kind of inserts?

In half? not for me.  Optimization possible? Sure, using the code you
pasted (time ./a.out 100000 <method>):
PQexec: 41s
PQexecPrepared: 36s
1 insert statement: 7s
COPY: 1s
psql: 256ms

Basically the above echoes the suggestions of others, use COPY if you can.

Find the source for the above attached.  Its just a very quick
modified version of what you posted. [ disclaimer the additions I
added are almost certainly missing some required error checking... ]

[ psql is fast because the insert is really dumb: insert into aaaa (a,
b, c, d, e, f, g, h, j, k, l, m, n, p) select 1, 'asdf', 'asdf',
'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf',
'asdf', 'asdf', 'asdf' from generate_series(1, 100000); ]

Attachment: pq_ex.c
Description: text/x-csrc (5.7 KB)

In response to

Responses

pgsql-performance by date

Next:From: Jesper KroghDate: 2010-10-27 19:47:23
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Previous:From: Justin PittsDate: 2010-10-27 19:44:52
Subject: Re: temporary tables, indexes, and query plans

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