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

Re: Postgres insert performance and storage requirement compared to Oracle

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 (view raw or flat)
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); ]
>
>

In response to

pgsql-performance by date

Next:From: Trenta sisDate: 2010-10-28 08:16:20
Subject: Massive update, memory usage
Previous:From: Alex HunsakerDate: 2010-10-28 04:23:44
Subject: Re: Postgres insert performance and storage requirement compared to Oracle

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