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=L3OIgS/yVeE2b1DjvLwf69u3bhuzDBGklh+b1MeDCEzUePIbROJCwBlt8JqYrZVfV7u0wVPQ5HdF9aKUcC/P+CNIJHIkwq/rRcZBfXUqUcxM48ay15fxYuHgNeBwLEs4D2VXrUotYglN1AEIz6E+BDLTAmepnt9FDK3LUbyVdO4=;
Message-ID: <604758.88084.qm@web65414.mail.ac4.yahoo.com>
References: <132275.30531.qm@web65411.mail.ac4.yahoo.com>
	<AANLkTi=0PkVscoYkMn-nETn16LAL4N-C8jzwGWffn7uV@mail.gmail.com>
Date: Wed, 27 Oct 2010 20:08:53 -0700 (PDT)
From: Divakar Singh <dpsmails@yahoo.com>
Subject: Re: Postgres insert performance and storage requirement compared to
	Oracle
To: Alex Hunsaker <badalex@gmail.com>
Cc: Steve Singer <ssinger@ca.afilias.info>, jd@commandprompt.com,
	pgsql-performance@postgresql.org
In-Reply-To: <AANLkTi=0PkVscoYkMn-nETn16LAL4N-C8jzwGWffn7uV@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-148472624-1288235333=:88084"

--0-148472624-1288235333=:88084
Content-Type: text/plain; charset=us-ascii

So another question pops up: What method in PostgreSQL does the stored proc use 
when I issue multiple insert (for loop for 100 thousand records) in the stored 
proc?
It takes half the time compared to the consecutive "insert" using libpq.
In the backend, does it use COPY or prepared statement? or something else?

 Best Regards,
Divakar


________________________________
From: Alex Hunsaker <badalex@gmail.com>
To: Divakar Singh <dpsmails@yahoo.com>
Cc: Steve Singer <ssinger@ca.afilias.info>; jd@commandprompt.com; 
pgsql-performance@postgresql.org
Sent: Thu, October 28, 2010 1:15:06 AM
Subject: Re: [PERFORM] Postgres insert performance and storage requirement 
compared to Oracle

On Wed, Oct 27, 2010 at 08:00, Divakar Singh <dpsmails@yahoo.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); ]


--0-148472624-1288235333=:88084
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial,helvetica,sans-serif;font-size:10pt">So another question pops up: What method in PostgreSQL does the stored proc use when I issue multiple insert (for loop for 100 thousand records) in the stored proc?<br>It takes half the time compared to the consecutive "insert" using libpq.<br>In the backend, does it use COPY or prepared statement? or something else?<br><div>&nbsp;</div>Best Regards,<br>Divakar<div><br></div><div style="font-family: arial,helvetica,sans-serif; font-size: 10pt;"><br><div style="font-family: verdana,helvetica,sans-serif; font-size: 10pt;"><font face="Tahoma" size="2"><hr size="1"><b><span style="font-weight: bold;">From:</span></b> Alex Hunsaker &lt;badalex@gmail.com&gt;<br><b><span style="font-weight: bold;">To:</span></b> Divakar Singh &lt;dpsmails@yahoo.com&gt;<br><b><span style="font-weight: bold;">Cc:</span></b> Steve
 Singer &lt;ssinger@ca.afilias.info&gt;; jd@commandprompt.com; pgsql-performance@postgresql.org<br><b><span style="font-weight: bold;">Sent:</span></b> Thu, October 28, 2010 1:15:06 AM<br><b><span style="font-weight: bold;">Subject:</span></b> Re: [PERFORM] Postgres insert performance and storage requirement compared to Oracle<br></font><br>On Wed, Oct 27, 2010 at 08:00, Divakar Singh &lt;<a ymailto="mailto:dpsmails@yahoo.com" href="mailto:dpsmails@yahoo.com">dpsmails@yahoo.com</a>&gt; wrote:<br>&gt; I am attaching my code below.<br>&gt; Is any optimization possible in this?<br>&gt; Do prepared statements help in cutting down the insert time to half for this<br>&gt; kind of inserts?<br><br>In half? not for me.&nbsp; Optimization possible? Sure, using the code you<br>pasted (time ./a.out 100000 &lt;method&gt;):<br>PQexec: 41s<br>PQexecPrepared: 36s<br>1 insert statement: 7s<br>COPY: 1s<br>psql: 256ms<br><br>Basically the above echoes the suggestions of
 others, use COPY if you can.<br><br>Find the source for the above attached.&nbsp; Its just a very quick<br>modified version of what you posted. [ disclaimer the additions I<br>added are almost certainly missing some required error checking... ]<br><br>[ psql is fast because the insert is really dumb: insert into aaaa (a,<br>b, c, d, e, f, g, h, j, k, l, m, n, p) select 1, 'asdf', 'asdf',<br>'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf',<br>'asdf', 'asdf', 'asdf' from generate_series(1, 100000); ]<br></div></div>
</div><br>

      </body></html>
--0-148472624-1288235333=:88084--