From chestercyoung@yahoo.com Mon Jun 27 06:30:03 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 5F079B5DF46 for ; Mon, 27 Jun 2011 03:30:03 -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 19429-01-8 for ; Mon, 27 Jun 2011 06:29:57 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm17.bullet.mail.bf1.yahoo.com (nm17.bullet.mail.bf1.yahoo.com [98.139.212.176]) by mail.postgresql.org (Postfix) with SMTP id 1EF73B5DFF3 for ; Mon, 27 Jun 2011 03:09:10 -0300 (ADT) Received: from [98.139.212.148] by nm17.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 06:09:10 -0000 Received: from [98.139.212.201] by tm5.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 06:09:10 -0000 Received: from [127.0.0.1] by omp1010.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 06:09:10 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 387080.42926.bm@omp1010.mail.bf1.yahoo.com Received: (qmail 37919 invoked by uid 60001); 27 Jun 2011 06:09:10 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1309154950; bh=gDOOt0vFQfpV7hmxok6zhhveCjjqj3mFO1fMeePRIt4=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=UC36LUsB4AVO4bPE3wsAOdxPtFDudOHWNpQGvZ/n3AaA1sTf7q5bKapj8dTmzDGhPH/CRAsGM7hdxT4YWna4/dLcq2lVRym7rrh5lVXiDFYQ9IEMA6XxdQJhMJlPMvxYIrysB2lB7O345PSTBir/860AWXt7xX7wW4acTVOBTJw= 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:MIME-Version:Content-Type; b=tOZ4xKs7eFCzCXLVxhMrcUSFN53zNSkQOoZZwwUdlYcFI6apHrOehtbeAaDh5WwK8s96Kot7w0tEr/CEM7cpnChUhwcD9zMN8s6wiBsgWR1tErp2dDES0U2aIiFSeAWaP3gYuKFkpVolsc0vibthYLgbVEHA8rRWhiZ7C0ez7uI=; Message-ID: <333801.29762.qm@web161424.mail.bf1.yahoo.com> X-YMail-OSG: AlTG2_MVM1l3C7wtBd.laH4fzDYRSDcB4WXh20Xj6ssOTW7 76INi4rrQ8bCHAgoWbvCuBIu6oVxr6xzha.LwTj9FcoTGUEOJDlHVvssDn1. g_u4lR1IbSDTsZn.gGMC3siDSDogjzP8aPHFPDJBAg5Oc7ytUbW7WaNuvL6h HPvYopth9AHjYKm0FQ_dzaAQPPHbJzbIasfAmMVMuQxktziShrFnhCUQ1_Xv WlDP9pMR9s1joAUSy6Y6fuQD4K7xN.9Q8DHodLIWRul8e6ANFaFN64EP8WvA 6O5G_IadM32fkIZdDNzl5QL.MDPhH7831LG1C09TXwxNcQ_02pjmEpRocPVV VHbkQUdoBhBFbUeKzZ33q5jBy4ReYWvVS8otmcdMWyPNGMYN.Ifm5_g-- Received: from [124.120.220.3] by web161424.mail.bf1.yahoo.com via HTTP; Sun, 26 Jun 2011 23:09:10 PDT X-Mailer: YahooMailClassic/14.0.3 YahooMailWebService/0.8.111.304355 Date: Sun, 26 Jun 2011 23:09:10 -0700 (PDT) From: chester c young Subject: best performance for simple dml To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-1181573717-1006212829-1309154950=:29762" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.802 tagged_above=-5 required=5 tests=BAYES_50=0.8, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201106/92 X-Sequence-Number: 35558 ---1181573717-1006212829-1309154950=:29762 Content-Type: text/plain; charset=us-ascii 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 ---1181573717-1006212829-1309154950=:29762 Content-Type: text/html; charset=us-ascii
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

---1181573717-1006212829-1309154950=:29762-- From pavel.stehule@gmail.com Mon Jun 27 06:36:05 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C10E2B5DBD5 for ; Mon, 27 Jun 2011 03:36:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 98348-03 for ; Mon, 27 Jun 2011 06:35:59 +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 7ECC8B5D80A for ; Mon, 27 Jun 2011 03:35:59 -0300 (ADT) Received: by iwn6 with SMTP id 6so3856955iwn.19 for ; Sun, 26 Jun 2011 23:35:59 -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=glyCgFIYhcQIFFtlxO9Vw00mYuxwLM13JZb76acBjfs=; b=uuVfNUcqvUU1+66NFT8i8u7vwvJ5EQ/tnkC8GMS+X/XeUSRDUKOrO91jZ82IyO3vJG 5Wnj5ero/RykQTa3ZC9jpJXpXaZWOzFzq4IDGcB8J2pIDRo+2Rq88RSgDqR5VqioQ35h 6CEiPTbGQILK9oiuMYvoPzSZIlEQ0Lf7kJ5j4= 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=wvjbWMS3KtJiN877RUqZumr06DyOdXyTmb6k4KirQJkx7Xl1NE1Ix6txlgTqFcJgCG WXFfdtnBHK98kNoAT5k+i6Dd+tMuOa7h6qrngaDT1A+rM69GUpF+6bhslKSB0KFjIWy8 cb5XlfETHTPx7Qd5xsZ/T2+jWrWPF78G6Zg/s= Received: by 10.42.132.70 with SMTP id c6mr7208728ict.349.1309156559102; Sun, 26 Jun 2011 23:35:59 -0700 (PDT) MIME-Version: 1.0 Received: by 10.42.4.16 with HTTP; Sun, 26 Jun 2011 23:35:19 -0700 (PDT) In-Reply-To: <333801.29762.qm@web161424.mail.bf1.yahoo.com> References: <333801.29762.qm@web161424.mail.bf1.yahoo.com> From: Pavel Stehule Date: Mon, 27 Jun 2011 08:35:19 +0200 Message-ID: Subject: Re: best performance for simple dml To: chester c young Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=90e6ba5bbc77a9221c04a6abc3d7 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201106/93 X-Sequence-Number: 35559 --90e6ba5bbc77a9221c04a6abc3d7 Content-Type: text/plain; charset=UTF-8 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 > > --90e6ba5bbc77a9221c04a6abc3d7 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello

try it and you will see. Depends on network speed, hw speed. B= ut 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 pract= ices for frequently-used simple dml, for example, an insert

<= div>1. fast-interface

2. prepared statement calling "insert ..." wi= th binary parameters

3. prepared statement calling= "myfunc(..." with binary parameters; myfunc takes its arguments = and performs an insert using them


--90e6ba5bbc77a9221c04a6abc3d7-- From chestercyoung@yahoo.com Mon Jun 27 06:48:36 2011 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-- From pavel.stehule@gmail.com Mon Jun 27 07:06:19 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 27343B5DBC4 for ; Mon, 27 Jun 2011 04:06:19 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 40934-01 for ; Mon, 27 Jun 2011 07:06:12 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iy0-f174.google.com (mail-iy0-f174.google.com [209.85.210.174]) by mail.postgresql.org (Postfix) with ESMTP id BFB9DB5DBCB for ; Mon, 27 Jun 2011 04:06:12 -0300 (ADT) Received: by iyb12 with SMTP id 12so3847249iyb.19 for ; Mon, 27 Jun 2011 00:06:12 -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=pnzie45IKV1DPtuNtnmacB9F9WySyETHyZeuj/+KzS0=; b=kSEKvBRXlXgPG7OAqwtPZo/cUokyflY+BAukRmdlQmBtbdhc6UjZM84uHnzSaT8dVf 4F1u5E2yr7Dmnqs28TYoQqLXuSenQ86Cn4zbAhm3NQNOM5mTaxXK96CZ27YkRZ0q86x1 zz6QT1Q48vK5rTfU2An4NuljuwgdS4hd2smms= 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=bdLa4lDGntZh0Pt3lhalV8/zkacNIYEN4b/YCtrJwjmyysUFTEdZFTM+kT7J9gnVUB 7dJRHJjscI+4WSLBE0SXCBE7k4+P+VGh13FqHEJ8AvNSAhdKP8cpR8ti8/cD/iEHCW92 ieXQnFhL1mYNg8qOTxN4h/zwexZiY+acRTTnk= Received: by 10.42.18.194 with SMTP id y2mr5273995ica.483.1309158372112; Mon, 27 Jun 2011 00:06:12 -0700 (PDT) MIME-Version: 1.0 Received: by 10.42.4.16 with HTTP; Mon, 27 Jun 2011 00:05:32 -0700 (PDT) In-Reply-To: <576786.8402.qm@web161422.mail.bf1.yahoo.com> References: <576786.8402.qm@web161422.mail.bf1.yahoo.com> From: Pavel Stehule Date: Mon, 27 Jun 2011 09:05:32 +0200 Message-ID: Subject: Re: best performance for simple dml To: chester c young Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201106/95 X-Sequence-Number: 35561 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 > From chestercyoung@yahoo.com Mon Jun 27 07:49:57 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id EA28FB5DBCB for ; Mon, 27 Jun 2011 04:49:57 -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 80363-03 for ; Mon, 27 Jun 2011 07:49:51 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm29-vm0.bullet.mail.bf1.yahoo.com (nm29-vm0.bullet.mail.bf1.yahoo.com [98.139.213.166]) by mail.postgresql.org (Postfix) with SMTP id 08823B5D803 for ; Mon, 27 Jun 2011 04:49:50 -0300 (ADT) Received: from [98.139.212.151] by nm29.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 07:49:50 -0000 Received: from [98.139.212.193] by tm8.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 07:49:50 -0000 Received: from [127.0.0.1] by omp1002.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 07:49:50 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 282946.86444.bm@omp1002.mail.bf1.yahoo.com Received: (qmail 77343 invoked by uid 60001); 27 Jun 2011 07:49:50 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1309160990; bh=i1hqRmA2KLhDaejJciIp73Z6qPueiVSfG4+axKKcw0w=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=A9k+GcZK72RIC8p/ih/BPxBL6vD+IUOMSqHp0BW4bCH4oUBwLq6J7hH3wNBU+huyjCBbMXEcl/Gvy1TO70dc1kfiZK9dR8Vg3scmaSHBVx1TGVKg42i94WBL7mgu7Ff1if82G8nNdI2Pxv1w5d2dwaxb4xCpgIUPAjSlSgtSstY= 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=rBDJWYvC7DDmlq6KUMxw2RRaDjX8HX7TNhkQZr3RKJc06w+PAmAnRG0iAboimE3jh8hgFzLup8Po6kbvi5DfcQZhxHMeeaExsPvQurgL3FDwgzGGw+qVudzAsKS5YMz/Mnhx7W3dzGa3UVNw1aq7vhNvytfCu5TRJozTFzhcqRw=; Message-ID: <2547.71122.qm@web161421.mail.bf1.yahoo.com> X-YMail-OSG: HhAj9PUVM1nICToW3mJ0CJ8YOciB5bwWF.4Kx_K5ta4B0WZ u1avqNUUApXUJh69xPnQrVdDEDyeL_it.RAqPfj57KLeLj3mxasWH0sfMBme wpTCmPnlgijKKuDRXprg9j4ytT1lyqPwywpGvVOPk9W1bRMsbj0oUkjEppLe 719D1Rb_9r6Wul6q2E3xhEG3RUFY59H2slaV28vdo.Pm1X4NMkFAc566jkPz CsxjUMfQ6aBrkM_2FKrF1jIo49wYUYWV3RUQ58RF6heWIWyQXQ1F0hRiuby0 8xtS0IA60SCPfZoo3hkxTEUgK25wUu.Ed8oWhqYwhv07k3n5kepTLHG.rLlZ AXtAAg16jTJL37BU3nxmBQJSgvEmJzmal9bsFSBz2.a7iYexjbaM574BkOBY 9PEnJQ6tQuFRjLAyu Received: from [124.120.220.3] by web161421.mail.bf1.yahoo.com via HTTP; Mon, 27 Jun 2011 00:49:49 PDT X-Mailer: YahooMailClassic/14.0.3 YahooMailWebService/0.8.111.304355 Date: Mon, 27 Jun 2011 00:49:49 -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="-1876451226-1294435204-1309160989=:71122" 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/96 X-Sequence-Number: 35562 ---1876451226-1294435204-1309160989=:71122 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable very nice pointers. =A0thank you very much! --- 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 on= e 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 r= ight? 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 =3D 0; i < 1000; i++) =A0 exec("insert into foo values($1), itoa(i)); 10x faster code exec('begin'); for(i =3D 0; i < 1000; i++) =A0 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 > ---1876451226-1294435204-1309160989=:71122 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
very nice pointers.  thank you very much= !

--- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.c= om> wrote:

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

2011/6/27 chester c young <che= stercyoung@yahoo.com>
>
> two questions:
> I thoug= ht 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<= 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 functi= on
b) if you can't to move it, then ensure, so statements will be
exe= cuted under outer transaction

slow code

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

10x = faster code

exec('begin');
for(i =3D 0; i < 1000; i++)
&nbs= p; exec("insert into foo values($1), itoa(i));
exec('commit');

Re= gards

Pavel Stehule

>
> --- On Mon, 6/27/11, Pavel S= tehule <pavel.stehule@gmail.com<= /a>> wrote:
>
> From: Pavel Stehule <pavel.stehule@gmail.com>
> Subject: Re: [SQL] best performanc= e for simple dml
> To: "chester c young" <che= stercyoung@yahoo.com>
> Cc: pgsql-sql@p= ostgresql.org
> Date: Monday, June 27, 2011, 12:35 AM
>
= > Hello
>
> try it and you will see. Depends on network spee= d, hw speed. But the most fast is using a COPY API
>
> http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html<= /a>
>
> Regards
>
> Pavel Stehule
>
>
> 2011/6/27 ch= ester c young <chestercyoung@yahoo.com>
&= gt;
> what is the best performance / best practices for frequently-us= ed simple dml, for example, an insert
> 1. fast-interface
> 2. = prepared statement calling "insert ..." with binary parameters
> 3. p= repared statement calling "myfunc(..." with binary parameters; myfunc takes= its arguments and performs an insert using them
>
---1876451226-1294435204-1309160989=:71122-- From craig@postnewspapers.com.au Mon Jun 27 08:02:49 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id C91ECB5DC22 for ; Mon, 27 Jun 2011 05:02:49 -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 99430-04 for ; Mon, 27 Jun 2011 08:02:43 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from www.postnewspapers.com.au (www.postnewspapers.com.au [173.230.139.50]) by mail.postgresql.org (Postfix) with ESMTP id 9B1F5B5D803 for ; Mon, 27 Jun 2011 05:02:43 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by www.postnewspapers.com.au (Postfix) with ESMTP id 7FC3E2D; Mon, 27 Jun 2011 04:02:42 -0400 (EDT) X-Virus-Scanned: amavisd-new at postnewspapers.com.au.com Received: from www.postnewspapers.com.au ([127.0.0.1]) by localhost (www.postnewspapers.com.au [127.0.0.1]) (amavisd-new, port 10024) with LMTP id PnbycjbNWlL3; Mon, 27 Jun 2011 04:02:42 -0400 (EDT) Received: from mail.postnewspapers.com.au (ppp171-128.static.internode.on.net [150.101.171.128]) by www.postnewspapers.com.au (Postfix) with ESMTP id A6A1127; Mon, 27 Jun 2011 04:02:41 -0400 (EDT) Received: from [10.0.0.53] (wallace.localnet [10.0.0.53]) by mail.postnewspapers.com.au (Postfix) with ESMTP id 408B8AE22A; Mon, 27 Jun 2011 16:02:40 +0800 (WST) Message-ID: <4E083920.1070302@postnewspapers.com.au> Date: Mon, 27 Jun 2011 16:02:40 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.15) Gecko/20110307 Thunderbird/3.1.9 MIME-Version: 1.0 To: Pavel Stehule CC: chester c young , pgsql-sql@postgresql.org Subject: Re: best performance for simple dml References: <576786.8402.qm@web161422.mail.bf1.yahoo.com> In-Reply-To: X-Enigmail-Version: 1.1.2 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201106/97 X-Sequence-Number: 35563 On 27/06/11 15:05, Pavel Stehule wrote: > exec('begin'); > for(i = 0; i < 1000; i++) > exec("insert into foo values($1), itoa(i)); > exec('commit'); You can probably also benefit from multi-valued INSERTs, though I haven't verified this. INSERT INTO foo VALUES (1,'joe','dean'), (4,'fred','bob'), (11,'anne','smith'); There'll be a threshhold above which the COPY protocol becomes faster, though. -- Craig Ringer From chestercyoung@yahoo.com Mon Jun 27 13:02:51 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id D978EB5DBC4 for ; Mon, 27 Jun 2011 10:02:51 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 76759-08 for ; Mon, 27 Jun 2011 13:02:45 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm30.bullet.mail.bf1.yahoo.com (nm30.bullet.mail.bf1.yahoo.com [98.139.212.189]) by mail.postgresql.org (Postfix) with SMTP id 060A7B5D803 for ; Mon, 27 Jun 2011 10:02:44 -0300 (ADT) Received: from [98.139.212.146] by nm30.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 13:02:44 -0000 Received: from [98.139.212.204] by tm3.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 13:02:44 -0000 Received: from [127.0.0.1] by omp1013.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 13:02:44 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 848572.75667.bm@omp1013.mail.bf1.yahoo.com Received: (qmail 36754 invoked by uid 60001); 27 Jun 2011 13:02:44 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1309179764; bh=cwuMn5a8bsw6sjn18hynJfjiaMwyUfvEfgzxh1GoHa4=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=HlarkWF6ut7IDGszW2i1zqInr7ASrbVL6ucTWOgAvsrRpVJmW7yj4UvmPAhLhKTV5C5z79crS0RC6EDVoZOXkbRzbvUTUEBDhbiN1S8Rg2zUqZdHkh9XlTEgqhSLNRyGPY6m1dAnnXovYYnEtIkfAS/jHstOuv2I8t2LoZbVCo0= 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=yz6iieZ+3rlgpxQY6It/2huaKKGfnwkNuyAFQVbawb/IzZGE2Zmkiou8nXdtrTmV+7CoApx5KNLS2BoQ6T2bFeRw4XVpDbYE695rpOcDuQ2nUQLHkd41geuBPVxVv0eG68W4HSoY19BCo82PAFIAU99C3tSUb81dvkOHvozuquc=; Message-ID: <733155.36648.qm@web161424.mail.bf1.yahoo.com> X-YMail-OSG: IxB8ZWwVM1msoNOqv6BmUlAiVVOdv44rY5C7pOl0eo_UHUm re9bNAWO_kA9LtDBgEAtpTPJ4ib.jX9yoLjTuhVX65fsFpPTxkuds9c0K3bh ouP4ioSFQlnGJU3HATpFuM4AKdE2UD4tZEXS2Fjhc57_UH1PpIBj73VR4CEf ygXYwPnzQ9IVYmxse6NpK_PtBqKcWYX3_7fHEkvbfOfdoPtCNf5vIvbzNPix 5zQI_1AJSUEB5nl4T32Tg_wgyq3PRZk6g6aT9Dj.CMK_dlrFDw_8o9nbqCRC Z.mn7O6GDU2DxXmKT8mp7kFThdjHdHZFPHuf6xBBjqhM8j6azO0nC5XkIoa. SWpmEUS1VSq4.LnRSh.A7iXfgQFRdeoj8KLH79Heb7_3tsQbcZvdyd3_gyXf XBtu5slFP.ao64.df Received: from [124.120.220.3] by web161424.mail.bf1.yahoo.com via HTTP; Mon, 27 Jun 2011 06:02:44 PDT X-Mailer: YahooMailClassic/14.0.3 YahooMailWebService/0.8.111.304355 Date: Mon, 27 Jun 2011 06:02:44 -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="-1181573717-449051971-1309179764=:36648" 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/98 X-Sequence-Number: 35564 ---1181573717-449051971-1309179764=:36648 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable forgive me for brain storming a little re copy: if there are a limited number of tables you're inserting, would there be an= ything wrong with the app opening a copy connection? =A0ie, a connection in= itiates the copy and then stays open like a pipe for any inserts coming thr= ough it. =A0visually it's a very cool paradigm, but is it actually a good i= dea? --- 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 on= e 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 r= ight? 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 =3D 0; i < 1000; i++) =A0 exec("insert into foo values($1), itoa(i)); 10x faster code exec('begin'); for(i =3D 0; i < 1000; i++) =A0 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 > --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ---1181573717-449051971-1309179764=:36648 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
forgive me for brain storming a little re cop= y:

if there are a limited number of tables you're insert= ing, 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 f= or any inserts coming through it.  visually it's a very cool paradigm,= but is it actually a good idea?

--- 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 youn= g" <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Date:= Monday, June 27, 2011, 1:05 AM

2011/6/27 c= hester c young <chestercyoung@yahoo.com>
>> two questions:
> I thought copy was for multiple rows - is its= setup cost effective for one row?

I expect it will be faster for on= e row too - it is not sql statement

if you want to understand to per= formance issues you have to understand to

a) network communication c= osts
b) SQL parsing and SQL planning costs
c) commits costs
d) oth= er costs - triggers, referential integrity costs

>
> copy w= ould 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 cycl= e, then

a) try tu move it to stored function
b) if you can't to m= ove it, then ensure, so statements will be
executed under outer transact= ion

slow code

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

10x faster code

= exec('begin');
for(i =3D 0; i < 1000; i++)
  exec("insert int= o foo values($1), itoa(i));
exec('commit');

Regards

Pavel = Stehule

>
> --- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> From: P= avel Stehule <pavel.stehule@gmail.com>
&g= t; Subject: Re: [SQL] best performance for simple dml
> To: "chester = c young" <chestercyoung@yahoo.com>
> C= c: pgsql-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 Stehul= e
>
>
> 2011/6/27 chester c young <chestercyoung@yahoo.com>
>
> what is the best performa= nce / 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 yo= ur subscription:
http://www.postgresql.org/mailpref/pgsql-sql
---1181573717-449051971-1309179764=:36648-- From pavel.stehule@gmail.com Mon Jun 27 13:41:22 2011 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--