From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Divakar Singh <dpsmails(at)yahoo(dot)com> |
Cc: | Alex Hunsaker <badalex(at)gmail(dot)com>, 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-28 04:26:08 |
Message-ID: | AANLkTim5ZP0gKBjL9C-ap3sVOf-pm+Qe2qA8JxYgoH88@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello
2010/10/28 Divakar Singh <dpsmails(at)yahoo(dot)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(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
> 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(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); ]
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Trenta sis | 2010-10-28 08:16:20 | Massive update, memory usage |
Previous Message | Alex Hunsaker | 2010-10-28 04:23:44 | Re: Postgres insert performance and storage requirement compared to Oracle |