Re: Postgres insert performance and storage requirement compared to Oracle

Hello

2010/10/28 Divakar Singh <dpsmails@yahoo.com>:
> 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?

nothing special - but it run as inprocess inside server backend. The
are no data casting, there are no overhead from communication, there
are no overhead from content switch.

Regards

Pavel Stehule

> 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); ]
>
>