Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 9B41AB5DC0E for ; Mon, 27 Jun 2011 10:41:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 91665-07 for ; Mon, 27 Jun 2011 13:41:16 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id A737DB5DC00 for ; Mon, 27 Jun 2011 10:41:15 -0300 (ADT) Received: by iwn6 with SMTP id 6so4090690iwn.19 for ; Mon, 27 Jun 2011 06:41:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc:content-type; bh=QYGbC9B+7RjNpcrO2hGCUTSGmLcQRt53E8z9e4zNMdI=; b=LaQy3RmlOl9PAQbKVn1H1l4b+OsxENkTKnWdTxEHJ2/wcEdFXdcF84Vx2ELoJ4x8Hx 9pNyH73nPQ3APM73UoGv3s1woYxOBKv9fyi5upPIefqYs+JwOnpAEDDc6r0wSSWY7+AK uX3DE8TeU2ZcOwiJnuxVE14yTr/Cz5IJ7YcEo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=acdXG/wcea4lheaALsyhBcsQkWmopnGKDTC7Y6b1lISdza+h+Ut9+mh3EfUQixXmfw imm3FahkdLvEi35vx/dqnNcS4fVVa3fwXQ+cMxDwUoWAXbmnM55OqfNuHw8mYWHnvqSJ ZrDPNmNiDRB0tuCm2ZPaK1t5ngbXHsAHU4RRM= Received: by 10.42.140.66 with SMTP id j2mr6564804icu.215.1309182075225; Mon, 27 Jun 2011 06:41:15 -0700 (PDT) MIME-Version: 1.0 Received: by 10.42.4.16 with HTTP; Mon, 27 Jun 2011 06:40:35 -0700 (PDT) In-Reply-To: <733155.36648.qm@web161424.mail.bf1.yahoo.com> References: <733155.36648.qm@web161424.mail.bf1.yahoo.com> From: Pavel Stehule Date: Mon, 27 Jun 2011 15:40:35 +0200 Message-ID: Subject: Re: best performance for simple dml To: chester c young Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=90e6ba6e8ddc8a454804a6b1b463 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.031 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, FREEMAIL_REPLY=1.929, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201106/99 X-Sequence-Number: 35565 --90e6ba6e8ddc8a454804a6b1b463 Content-Type: text/plain; charset=UTF-8 Hello 2011/6/27 chester c young > forgive me for brain storming a little re copy: > > if there are a limited number of tables you're inserting, would there be > anything wrong with the app opening a copy connection? ie, a connection > initiates the copy and then stays open like a pipe for any inserts coming > through it. visually it's a very cool paradigm, but is it actually a good > idea? > depends on application. Usually you can use a connection better than just "insert connection". I am thinking, so it doesn't carry some special - it remove a connection cost, but nothing more. You can use a more connections to do paralel inserts - it has a sense. look on pgpool or other similar sw for connection pooling Pavel > > --- On *Mon, 6/27/11, Pavel Stehule * wrote: > > > From: Pavel Stehule > Subject: Re: [SQL] best performance for simple dml > To: "chester c young" > Cc: pgsql-sql@postgresql.org > Date: Monday, June 27, 2011, 1:05 AM > > 2011/6/27 chester c young > > > > > > two questions: > > I thought copy was for multiple rows - is its setup cost effective for > one row? > > I expect it will be faster for one row too - it is not sql statement > > if you want to understand to performance issues you have to understand to > > a) network communication costs > b) SQL parsing and SQL planning costs > c) commits costs > d) other costs - triggers, referential integrity costs > > > > > copy would also only be good for insert or select, not update - is this > right? > > sure, > > If you need to call a lot of simple dml statement in cycle, then > > a) try tu move it to stored function > b) if you can't to move it, then ensure, so statements will be > executed under outer transaction > > slow code > > for(i = 0; i < 1000; i++) > exec("insert into foo values($1), itoa(i)); > > 10x faster code > > exec('begin'); > for(i = 0; i < 1000; i++) > exec("insert into foo values($1), itoa(i)); > exec('commit'); > > Regards > > Pavel Stehule > > > > > --- On Mon, 6/27/11, Pavel Stehule > > wrote: > > > > From: Pavel Stehule > > > > Subject: Re: [SQL] best performance for simple dml > > To: "chester c young" > > > > Cc: pgsql-sql@postgresql.org > > Date: Monday, June 27, 2011, 12:35 AM > > > > Hello > > > > try it and you will see. Depends on network speed, hw speed. But the most > fast is using a COPY API > > > > http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html > > > > Regards > > > > Pavel Stehule > > > > > > 2011/6/27 chester c young > > > > > > what is the best performance / best practices for frequently-used simple > dml, for example, an insert > > 1. fast-interface > > 2. prepared statement calling "insert ..." with binary parameters > > 3. prepared statement calling "myfunc(..." with binary parameters; myfunc > takes its arguments and performs an insert using them > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org > ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > --90e6ba6e8ddc8a454804a6b1b463 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello

2011/6/27 chester c young <chestercyoung@yahoo= .com>
forgive me for brain storming a little re= copy:

if there are a limited number of tables you'r= e inserting, would there be anything wrong with the app opening a copy conn= ection? =C2=A0ie, a connection initiates the copy and then stays open like = a pipe for any inserts coming through it. =C2=A0visually it's a very co= ol paradigm, but is it actually a good idea?

depends on application. Usu= ally you can use a connection better than just "insert connection"= ;. I am thinking, so it doesn't carry some special - it remove a connec= tion cost, but nothing more. You can use a more connections to do paralel i= nserts - it has a sense.

look on pgpool or other similar sw for connection pooling

Pavel<= br>

=C2=A0

--- On Mon, 6/27/11, P= avel Stehule <pavel.stehule@gmail.com> wrote:

From: Pavel Stehule <pavel.stehule@gmail.com>
Subject= : Re: [SQL] best performance for simple dml
To: "chester c young" <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Date: Monday, June 27, 2011, 1:05 AM

2011/6/27 chester c young <chestercyoung@yahoo.com>
>
> two question= s:
> I thought copy was for multiple rows - is its setup cost effecti= ve for one row?

I expect it will be faster for one row too - it is not sql statement
if you want to understand to performance issues you have to understand= to

a) network communication costs
b) SQL parsing and SQL plannin= g costs
c) commits costs
d) other costs - triggers, referential integrity costs<= br>
>
> copy would also only be good for insert or select, not = update - is this right?

sure,

If you need to call a lot of si= mple dml statement in cycle, then

a) try tu move it to stored function
b) if you can't to move it,= then ensure, so statements will be
executed under outer transaction
=
slow code

for(i =3D 0; i < 1000; i++)
=C2=A0 exec("insert into foo values($1), itoa(i));

10x faster code
exec('begin');
for(i =3D 0; i < 1000; i++)
=C2=A0 exe= c("insert into foo values($1), itoa(i));
exec('commit');
Regards

Pavel Stehule

>
> --- On Mon, 6/27/11, Pavel= Stehule <pavel.stehule@gmail.com> wrote:
>
> From:= Pavel Stehule <pavel.stehule@gmail.com>
> Subject: Re: [SQL] best performance for simple dml
> To: "c= hester c young" <chestercyoung@yahoo.com>
> Cc: p= gsql-sql@postgresql.org
> Date: Monday, June 27, 2011, 12:35 AM
>
> Hello
>
> try it and you will see. De= pends on network speed, hw speed. But the most fast is using a COPY API
= >
> http://www.postgresql.org/docs/9.0/interacti= ve/libpq-copy.html
>
> Regards
>
> Pavel Stehule
>
>
> = 2011/6/27 chester c young <chestercyoung@yahoo.com>
>
= > what is the best performance / best practices for frequently-used simp= le dml, for example, an insert
> 1. fast-interface
> 2. prepared statement calling "insert .= .." with binary parameters
> 3. prepared statement calling "= ;myfunc(..." with binary parameters; myfunc takes its arguments and pe= rforms an insert using them
>

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql= .org)
To make changes to your subscription:
http://www.postgresql= .org/mailpref/pgsql-sql

--90e6ba6e8ddc8a454804a6b1b463--