Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id ED654B5D80A for ; Mon, 27 Jun 2011 03:48:36 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 26044-09 for ; Mon, 27 Jun 2011 06:48:30 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm11.bullet.mail.bf1.yahoo.com (nm11.bullet.mail.bf1.yahoo.com [98.139.212.170]) by mail.postgresql.org (Postfix) with SMTP id 4C041B5D803 for ; Mon, 27 Jun 2011 03:48:30 -0300 (ADT) Received: from [98.139.212.152] by nm11.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 06:48:29 -0000 Received: from [98.139.212.240] by tm9.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 06:48:29 -0000 Received: from [127.0.0.1] by omp1049.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 06:48:29 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 629831.37122.bm@omp1049.mail.bf1.yahoo.com Received: (qmail 9904 invoked by uid 60001); 27 Jun 2011 06:48:29 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1309157309; bh=nQLszZUwDQ2J+pN3SOQEZ7l0cqAr0u2yoSmnYpZbTd0=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=YIj8opuCLiT6L/FVVeoCKhAIDIZCR4kyLsfH+ozao9TkFRkbDUJylDVz2TEh3JP/WcOYCAjBHvWeQp1jU4P0wRwsglPQ1aQrBKsPd7AegfbhJKaFlX/JIU6rj+2RK09eaEJbZt9WG/mKVMj2FaveOTqbSOGtd88hSKaAQ+t6LCs= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=QSVyuiuXAl7aV8Gj7wnEB1GJvKSa9SAvNA0no4dW6viYHhojeGCop5Yxpos1aujL3Qb0IMVh3EcdPqiAR2ch/AhKJZI1DBlhiicQU32RTmIW+KfidbBuwWZUfY1UEB7Ve3tD9yoq/WtxTBYGWRo/bycD2nEt4rh+PuQHxXAE0sY=; Message-ID: <576786.8402.qm@web161422.mail.bf1.yahoo.com> X-YMail-OSG: UeKmUyQVM1mR9FHK_Rdh8SE.mHdvlvRJOR4QGBTMyKTtJ.q WlwHhu1Y6XYGPYw6twR52fIiQcvYCLm.rGjfaSA3m718eCuvgsNT7450qGle 3oZmOsI75LN6cABCpg79IU4.lY4OP0hc..E9zdDeJMjGjbRtRvXNXFVq8tkp SZ0K4oSCjUlHSweBC8epCuiB.mbZJqkp5_hdQBK7Xa4QeDQ6R6ZKqgUEQ.8C mDOUdjdARAoU3XiFm7i6zwcFEURGmVjtqI_DlIpxS9l2beDfo7Ed2tPg7wVa tNdpoBtFywP8qVH54lj8oTMxTckr8A2LxW1wopZ4kK8GdZy6eTyLr8HnPOQ_ wOl7KYJr1AuIZjbBJ6A5jpvcvAoTS9phs3ztQJSYKXJGm.aoXAoiXv4CG7Xp 4RKwZGq3uzdbGaPlv Received: from [124.120.220.3] by web161422.mail.bf1.yahoo.com via HTTP; Sun, 26 Jun 2011 23:48:29 PDT X-Mailer: YahooMailClassic/14.0.3 YahooMailWebService/0.8.111.304355 Date: Sun, 26 Jun 2011 23:48:29 -0700 (PDT) From: chester c young Subject: Re: best performance for simple dml To: Pavel Stehule Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="755586553-331223825-1309157309=:8402" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.908 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201106/94 X-Sequence-Number: 35560 --755586553-331223825-1309157309=:8402 Content-Type: text/plain; charset=us-ascii two questions: I thought copy was for multiple rows - is its setup cost effective for one row? copy would also only be good for insert or select, not update - is this right? --- 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 --755586553-331223825-1309157309=:8402 Content-Type: text/html; charset=us-ascii
two questions:

I thought copy was for multiple rows - is its setup cost effective for one row?

copy would also only be good for insert or select, not update - is this right?

--- 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: "chester c young" <chestercyoung@yahoo.com>
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 <chestercyoung@yahoo.com>
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


--755586553-331223825-1309157309=:8402--