From barai@cs.ucr.edu Thu Jan 19 17:38:19 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 6FB309DCA43 for ; Thu, 19 Jan 2006 13:38:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 62512-04 for ; Thu, 19 Jan 2006 13:38:18 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from server10.araisoft.com (server10.araisoft.com [72.9.228.195]) by postgresql.org (Postfix) with ESMTP id B62B59DCA06 for ; Thu, 19 Jan 2006 13:38:16 -0400 (AST) Received: from localhost (unknown [127.0.0.1]) by server10.araisoft.com (Postfix) with ESMTP id 6AD853D6200D for ; Thu, 19 Jan 2006 09:38:13 -0800 (PST) Received: from server10.araisoft.com ([127.0.0.1]) by localhost (server10.araisoft.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09282-02 for ; Thu, 19 Jan 2006 09:38:07 -0800 (PST) Received: from uni (uni.cs.ucr.edu [138.23.204.215]) by server10.araisoft.com (Postfix) with ESMTP id 172DA3D6200B for ; Thu, 19 Jan 2006 09:38:07 -0800 (PST) From: "Benjamin Arai" To: Subject: Stored Procedures Date: Thu, 19 Jan 2006 09:38:01 -0800 Message-ID: <000001c61d1f$19fed2c0$d7cc178a@uni> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C61CDC.0BDB92C0" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcYdHxfGZh71Ht+kT9CLUyLvKorDqg== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670 X-Virus-Scanned: by amavisd-new at araisoft.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.185 required=5 tests=[AWL=0.184, HTML_MESSAGE=0.001] X-Spam-Score: 0.185 X-Spam-Level: X-Archive-Number: 200601/327 X-Sequence-Number: 16805 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C61CDC.0BDB92C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi, Will simple queries such as "SELECT * FROM blah_table WHERE tag='x'; work any faster by putting them into a stored procedure? Benjamin Arai barai@cs.ucr.edu http://www.benjaminarai.com ------=_NextPart_000_0001_01C61CDC.0BDB92C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

Will = simple queries=20 such as "SELECT * FROM blah_table WHERE tag=3D'x'; work any faster by = putting them=20 into a stored procedure?

Benjamin = Arai

barai@cs.ucr.edu

http://www.benjaminarai.com

------=_NextPart_000_0001_01C61CDC.0BDB92C0-- From akretschmer@spamfence.net Thu Jan 19 20:04:38 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8A4C39DCAB2 for ; Thu, 19 Jan 2006 16:04:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90022-01 for ; Thu, 19 Jan 2006 16:04:35 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from 137086.vserver.de (vs137086.vserver.de [62.75.137.86]) by postgresql.org (Postfix) with ESMTP id 514299DCAAE for ; Thu, 19 Jan 2006 15:34:24 -0400 (AST) Received: from a-kretschmer.de (p54B39DCE.dip0.t-ipconnect.de [84.179.157.206]) (authenticated bits=0) by 137086.vserver.de (8.12.8/8.12.8) with ESMTP id k0JJYMCZ016962 for ; Thu, 19 Jan 2006 20:34:23 +0100 Received: from kretschmer by a-kretschmer.de with local (Exim 3.36 #1) id 1EzfYA-0001Px-00 for pgsql-performance@postgresql.org; Thu, 19 Jan 2006 20:34:26 +0100 Date: Thu, 19 Jan 2006 20:34:26 +0100 From: Andreas Kretschmer To: pgsql-performance@postgresql.org Subject: Re: Stored Procedures Message-ID: <20060119193426.GA5403@kaufbach.delug.de> References: <000001c61d1f$19fed2c0$d7cc178a@uni> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <000001c61d1f$19fed2c0$d7cc178a@uni> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! X-GPG-Fingerprint: EE16 3C01 7B9C 10F7 2C8B 3B86 4DB3 D9EE 7F45 84DA X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the answer is "no"! X-Lugdd: Gerd Kube X-Info: My name is root. Just root. And I am licensed to kill -9 User-Agent: mutt-ng 1.5.9i (Linux) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.113 required=5 tests=[AWL=0.113] X-Spam-Score: 0.113 X-Spam-Level: X-Archive-Number: 200601/328 X-Sequence-Number: 16806 Benjamin Arai schrieb: > Hi, > > Will simple queries such as "SELECT * FROM blah_table WHERE tag='x'; work any > faster by putting them into a stored procedure? IMHO no, why do you think so? You can use PREPARE instead, if you have many selects like this. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° From rikard.pavelic@zg.htnet.hr Fri Jan 20 18:51:24 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 72B9C9DC842 for ; Fri, 20 Jan 2006 14:51:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31580-05 for ; Fri, 20 Jan 2006 14:51:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from pop.xnet.hr (dns1.xnet.hr [82.193.192.5]) by postgresql.org (Postfix) with ESMTP id BD4D69DCB74 for ; Fri, 20 Jan 2006 14:51:18 -0400 (AST) Received: (qmail 26701 invoked from network); 20 Jan 2006 18:51:45 -0000 Received: by simscan 1.1.0 ppid: 26692, pid: 26694, t: 0.5947s scanners: attach: 1.1.0 clamav: 0.87/m:34/d:1131 spam: 3.1.0 Received: from unknown (HELO ?83.139.74.33?) (83.139.74.33) by pop.xnet.hr with SMTP; 20 Jan 2006 18:51:44 -0000 Message-ID: <43D130EF.3010205@zg.htnet.hr> Date: Fri, 20 Jan 2006 19:50:23 +0100 From: Rikard Pavelic User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: [PERFORMANCE] Stored Procedures Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.479 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.479 X-Spam-Level: X-Archive-Number: 200601/351 X-Sequence-Number: 16829 >>Hi, >> >> Will simple queries such as "SELECT * FROM blah_table WHERE tag='x'; work any >> faster by putting them into a stored procedure? > >IMHO no, why do you think so? You can use PREPARE instead, if you have many >selects like this. I tought that creating stored procedures in database means storing it's execution plan (well, actually storing it like a compiled object). Well, that's what I've learned couple a years ago in colledge ;) What are the advantages of parsing SP functions every time it's called? My position is that preparing stored procedures for execution solves more problems, that it creates. And the most important one to be optimizing access to queries from multiple connections (which is one of the most important reasons for using stored procedures in the first place). Best regards, Rikard From rikard.pavelic@zg.htnet.hr Fri Jan 20 18:56:22 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 59A3A9DC9CC for ; Fri, 20 Jan 2006 14:56:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32203-05 for ; Fri, 20 Jan 2006 14:56:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from pop.xnet.hr (dns1.xnet.hr [82.193.192.5]) by postgresql.org (Postfix) with ESMTP id A0FFE9DCB60 for ; Fri, 20 Jan 2006 14:56:14 -0400 (AST) Received: (qmail 27825 invoked from network); 20 Jan 2006 18:56:41 -0000 Received: by simscan 1.1.0 ppid: 27813, pid: 27815, t: 0.8915s scanners: attach: 1.1.0 clamav: 0.87/m:34/d:1131 spam: 3.1.0 Received: from unknown (HELO ?83.139.74.33?) (83.139.74.33) by pop.xnet.hr with SMTP; 20 Jan 2006 18:56:40 -0000 Message-ID: <43D13217.2030704@zg.htnet.hr> Date: Fri, 20 Jan 2006 19:55:19 +0100 From: Rikard Pavelic User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Stored procedures References: <6.2.1.2.0.20060120120150.08ab7b00@localhost> <20060120172026.GF20182@pervasive.com> <25978.1137783074@sss.pgh.pa.us> In-Reply-To: <25978.1137783074@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.479 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.479 X-Spam-Level: X-Archive-Number: 200601/353 X-Sequence-Number: 16831 > >> Hi, >> >> Will simple queries such as "SELECT * FROM blah_table WHERE tag='x'; >> work any >> faster by putting them into a stored procedure? > > IMHO no, why do you think so? You can use PREPARE instead, if you have > many > selects like this. I tought that creating stored procedures in database means storing it's execution plan (well, actually storing it like a compiled object). Well, that's what I've learned couple a years ago in colledge ;) What are the advantages of parsing SP functions every time it's called? My position is that preparing stored procedures for execution solves more problems, that it creates. And the most important one to be optimizing access to queries from multiple connections (which is one of the most important reasons for using stored procedures in the first place). Best regards, Rikard From jnasby@pervasive.com Fri Jan 20 19:10:32 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id CDE529DC99D for ; Fri, 20 Jan 2006 15:10:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33552-07 for ; Fri, 20 Jan 2006 15:10:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 915B69DC842 for ; Fri, 20 Jan 2006 15:10:30 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 863063983D; Fri, 20 Jan 2006 13:10:31 -0600 (CST) Date: Fri, 20 Jan 2006 13:10:31 -0600 From: "Jim C. Nasby" To: Rikard Pavelic Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORMANCE] Stored Procedures Message-ID: <20060120191031.GO20182@pervasive.com> References: <43D130EF.3010205@zg.htnet.hr> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43D130EF.3010205@zg.htnet.hr> X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.099 required=5 tests=[AWL=0.099] X-Spam-Score: 0.099 X-Spam-Level: X-Archive-Number: 200601/356 X-Sequence-Number: 16834 On Fri, Jan 20, 2006 at 07:50:23PM +0100, Rikard Pavelic wrote: > >>Hi, > >> > >>Will simple queries such as "SELECT * FROM blah_table WHERE tag='x'; work > >>any > >>faster by putting them into a stored procedure? > > > > >IMHO no, why do you think so? You can use PREPARE instead, if you have many > >selects like this. > > > I tought that creating stored procedures in database means > storing it's execution plan (well, actually storing it like a > compiled object). Well, that's what I've learned couple a years > ago in colledge ;) My college professor said it, it must be true! ;P My understanding is that in plpgsql, 'bare' queries get prepared and act like prepared statements. IE: SELECT INTO variable field FROM table WHERE condition = true ; > What are the advantages of parsing SP functions every time it's called? > > My position is that preparing stored procedures for execution solves > more problems, that it creates. > And the most important one to be optimizing access to queries from > multiple connections (which is one of the most important reasons > for using stored procedures in the first place). Ok, so post some numbers then. It might be interesting to look at the cost of preparing a statement, although AFAIK that does not store the query plan anywhere. In most databases, query planning seems to be a pretty expensive operation. My experience is that that isn't the case with PostgreSQL. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 From rikard.pavelic@zg.htnet.hr Fri Jan 20 19:39:43 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 492A89DC889 for ; Fri, 20 Jan 2006 15:39:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38326-06 for ; Fri, 20 Jan 2006 15:39:40 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from pop.xnet.hr (dns1.xnet.hr [82.193.192.5]) by postgresql.org (Postfix) with ESMTP id 66BFD9DC85B for ; Fri, 20 Jan 2006 15:39:36 -0400 (AST) Received: (qmail 5660 invoked from network); 20 Jan 2006 19:39:48 -0000 Received: by simscan 1.1.0 ppid: 5641, pid: 5642, t: 3.6098s scanners: attach: 1.1.0 clamav: 0.87/m:34/d:1131 spam: 3.1.0 Received: from unknown (HELO ?83.139.74.33?) (83.139.74.33) by pop.xnet.hr with SMTP; 20 Jan 2006 19:39:44 -0000 Message-ID: <43D13C2F.1030203@zg.htnet.hr> Date: Fri, 20 Jan 2006 20:38:23 +0100 From: Rikard Pavelic User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: [PERFORMANCE] Stored Procedures References: <43D130EF.3010205@zg.htnet.hr> <20060120191031.GO20182@pervasive.com> In-Reply-To: <20060120191031.GO20182@pervasive.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.479 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.479 X-Spam-Level: X-Archive-Number: 200601/358 X-Sequence-Number: 16836 Jim C. Nasby wrote: > My college professor said it, it must be true! ;P > > The famous joke ;) > My understanding is that in plpgsql, 'bare' queries get prepared and act > like prepared statements. IE: > > SELECT INTO variable > field > FROM table > WHERE condition = true > ; > > Unfortunately I don't know enough about PostgreSQL, but from responses I've been reading I've come to that conclusion. > Ok, so post some numbers then. It might be interesting to look at the > cost of preparing a statement, although AFAIK that does not store the > query plan anywhere. > > In most databases, query planning seems to be a pretty expensive > operation. My experience is that that isn't the case with PostgreSQL. > I didn't think about storing query plan anywhere on the disk, rather keep them in memory pool. It would be great if we had an option to use prepare statement for stored procedure so it would prepare it self the first time it's called and remained prepared until server shutdown or memory pool overflow. This would solve problems with prepare which is per session, so for prepared function to be optimal one must use same connection. From jnasby@pervasive.com Fri Jan 20 21:35:15 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id E6AE09DCA39 for ; Fri, 20 Jan 2006 17:35:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76094-10 for ; Fri, 20 Jan 2006 17:35:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id DDF379DC809 for ; Fri, 20 Jan 2006 17:35:08 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id AB0B539846; Fri, 20 Jan 2006 15:34:55 -0600 (CST) Date: Fri, 20 Jan 2006 15:34:55 -0600 From: "Jim C. Nasby" To: Rikard Pavelic Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORMANCE] Stored Procedures Message-ID: <20060120213455.GT20182@pervasive.com> References: <43D130EF.3010205@zg.htnet.hr> <20060120191031.GO20182@pervasive.com> <43D13C2F.1030203@zg.htnet.hr> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43D13C2F.1030203@zg.htnet.hr> X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.099 required=5 tests=[AWL=0.099] X-Spam-Score: 0.099 X-Spam-Level: X-Archive-Number: 200601/363 X-Sequence-Number: 16841 On Fri, Jan 20, 2006 at 08:38:23PM +0100, Rikard Pavelic wrote: > This would solve problems with prepare which is per session, so for > prepared function to be > optimal one must use same connection. If you're dealing with something that's performance critical you're not going to be constantly re-connecting anyway, so I don't see what the issue is. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 From rikard.pavelic@zg.htnet.hr Sat Jan 21 08:00:20 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 19D519DC813 for ; Sat, 21 Jan 2006 04:00:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07165-08 for ; Sat, 21 Jan 2006 04:00:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from pop.xnet.hr (dns1.xnet.hr [82.193.192.5]) by postgresql.org (Postfix) with ESMTP id DF2649DC80E for ; Sat, 21 Jan 2006 04:00:15 -0400 (AST) Received: (qmail 10147 invoked from network); 21 Jan 2006 08:00:45 -0000 Received: by simscan 1.1.0 ppid: 10138, pid: 10140, t: 0.4931s scanners: attach: 1.1.0 clamav: 0.87/m:34/d:1131 spam: 3.1.0 Received: from unknown (HELO ?83.139.74.33?) (83.139.74.33) by pop.xnet.hr with SMTP; 21 Jan 2006 08:00:44 -0000 Message-ID: <43D1E9E0.3000905@zg.htnet.hr> Date: Sat, 21 Jan 2006 08:59:28 +0100 From: Rikard Pavelic User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: "Jim C. Nasby" CC: pgsql-performance@postgresql.org Subject: Re: [PERFORMANCE] Stored Procedures References: <43D130EF.3010205@zg.htnet.hr> <20060120191031.GO20182@pervasive.com> <43D13C2F.1030203@zg.htnet.hr> <20060120213455.GT20182@pervasive.com> In-Reply-To: <20060120213455.GT20182@pervasive.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.479 required=5 tests=[AWL=0.000, DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.479 X-Spam-Level: X-Archive-Number: 200601/384 X-Sequence-Number: 16862 Jim C. Nasby wrote: > If you're dealing with something that's performance critical you're not > going to be constantly re-connecting anyway, so I don't see what the > issue is. > I really missed your point. In multi user environment where each user uses it's connection for identification purposes, this seems like a reasonable optimization. I know there is pgpool, but it's non windows, and it's not the best solution for every other problem. From rikard.pavelic@zg.htnet.hr Sat Jan 21 21:06:58 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3C33B9DC804 for ; Sat, 21 Jan 2006 17:06:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51628-03 for ; Sat, 21 Jan 2006 17:06:57 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from pop.xnet.hr (dns1.xnet.hr [82.193.192.5]) by postgresql.org (Postfix) with ESMTP id 6193E9DC85A for ; Sat, 21 Jan 2006 17:06:52 -0400 (AST) Received: (qmail 23354 invoked from network); 21 Jan 2006 21:07:24 -0000 Received: by simscan 1.1.0 ppid: 23342, pid: 23348, t: 0.5873s scanners: attach: 1.1.0 clamav: 0.87/m:34/d:1131 spam: 3.1.0 Received: from unknown (HELO ?83.139.74.33?) (83.139.74.33) by pop.xnet.hr with SMTP; 21 Jan 2006 21:07:24 -0000 Message-ID: <43D2A245.6040804@zg.htnet.hr> Date: Sat, 21 Jan 2006 22:06:13 +0100 From: Rikard Pavelic User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: [PERFORMANCE] Stored Procedures References: <43D130EF.3010205@zg.htnet.hr> <20060120191031.GO20182@pervasive.com> <43D13C2F.1030203@zg.htnet.hr> <20060120213455.GT20182@pervasive.com> In-Reply-To: <20060120213455.GT20182@pervasive.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.479 required=5 tests=[AWL=0.000, DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.479 X-Spam-Level: X-Archive-Number: 200601/401 X-Sequence-Number: 16879 Jim C. Nasby wrote: > If you're dealing with something that's performance critical you're not > going to be constantly re-connecting anyway, so I don't see what the > issue is. > I didn't include mailing list in my second reply :( so here it is again. Someone may find this interesting... http://archives.postgresql.org/pgsql-general/2004-04/msg00084.php From Tom Lane: "EXECUTE means something different in plpgsql than it does in plain SQL, and you do not need PREPARE at all in plpgsql. plpgsql's automatic caching of plans gives you the effect of PREPARE on every statement without your having to ask for it." From mjs_ops@gmx.net Mon Jan 23 10:19:29 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 247359DC9DA for ; Mon, 23 Jan 2006 06:19:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08920-07 for ; Mon, 23 Jan 2006 06:19:30 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from mail.gmx.net (mail.gmx.net [213.165.64.21]) by postgresql.org (Postfix) with SMTP id 104599DC9BE for ; Mon, 23 Jan 2006 06:19:25 -0400 (AST) Received: (qmail invoked by alias); 23 Jan 2006 10:19:26 -0000 Received: from 201-15-251-250.ctame704.dsl.brasiltelecom.net.br (EHLO servidor) [201.15.251.250] by mail.gmx.net (mp039) with SMTP; 23 Jan 2006 11:19:26 +0100 X-Authenticated: #15924888 Subject: Re: [PERFORMANCE] Stored Procedures From: Marcos To: "Jim C. Nasby" Cc: Rikard Pavelic , pgsql-performance@postgresql.org In-Reply-To: <20060120213455.GT20182@pervasive.com> References: <43D130EF.3010205@zg.htnet.hr> <20060120191031.GO20182@pervasive.com> <43D13C2F.1030203@zg.htnet.hr> <20060120213455.GT20182@pervasive.com> Content-Type: text/plain; charset=iso8859-1 Date: Mon, 23 Jan 2006 08:14:14 +0000 Message-Id: <1138004054.949.10.camel@servidor> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 FreeBSD GNOME Team Port Content-Transfer-Encoding: 8bit X-Y-GMX-Trusted: 0 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200601/409 X-Sequence-Number: 16887 Em Sex, 2006-01-20 às 15:34 -0600, Jim C. Nasby escreveu: > On Fri, Jan 20, 2006 at 08:38:23PM +0100, Rikard Pavelic wrote: > > This would solve problems with prepare which is per session, so for > > prepared function to be > > optimal one must use same connection. > > If you're dealing with something that's performance critical you're not > going to be constantly re-connecting anyway, so I don't see what the > issue is. This one was my doubt, perhaps in based desktop applications this is true, but in web applications this is not the re-connecting is constant :(. Then the preprare not have very advantage because your duration is per session. Marcos. From schabi@logix-tt.com Mon Jan 23 12:30:35 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3CD949DC847 for ; Mon, 23 Jan 2006 08:30:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32507-09 for ; Mon, 23 Jan 2006 08:30:36 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.logix-tt.com (serval.logix-tt.com [213.239.221.42]) by postgresql.org (Postfix) with ESMTP id 1D1959DC813 for ; Mon, 23 Jan 2006 08:30:30 -0400 (AST) Received: from kingfisher.intern.logix-tt.com (Mddbe.m.pppool.de [89.49.221.190]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.logix-tt.com (Postfix) with ESMTP id B433C24400F; Mon, 23 Jan 2006 13:30:33 +0100 (CET) Received: from [127.0.0.1] (localhost [127.0.0.1]) by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id A071F18189FA6; Mon, 23 Jan 2006 13:30:31 +0100 (CET) Message-ID: <43D4CC67.9050703@logix-tt.com> Date: Mon, 23 Jan 2006 13:30:31 +0100 From: Markus Schaber Organization: Logical Tracking and Tracing International AG, Switzerland User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Cc: Marcos Subject: Re: [PERFORMANCE] Stored Procedures References: <43D130EF.3010205@zg.htnet.hr> <20060120191031.GO20182@pervasive.com> <43D13C2F.1030203@zg.htnet.hr> <20060120213455.GT20182@pervasive.com> <1138004054.949.10.camel@servidor> In-Reply-To: <1138004054.949.10.camel@servidor> X-Enigmail-Version: 0.93.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.124 required=5 tests=[AWL=0.124] X-Spam-Score: 0.124 X-Spam-Level: X-Archive-Number: 200601/411 X-Sequence-Number: 16889 Hi, Marcos, Marcos wrote: > This one was my doubt, perhaps in based desktop applications this is > true, but in web applications this is not the re-connecting is > constant :(. If this is true, then you have a much bigger performance problem than query plan preparation. You really should consider using a connection pool (most web application servers provide pooling facilities) or some other means to keep the connection between several http requests. Worried, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org From mjs_ops@gmx.net Mon Jan 23 15:32:31 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A82FC9DC813 for ; Mon, 23 Jan 2006 11:32:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67850-04 for ; Mon, 23 Jan 2006 11:32:35 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from mail.gmx.net (mail.gmx.net [213.165.64.21]) by postgresql.org (Postfix) with SMTP id A19139DC809 for ; Mon, 23 Jan 2006 11:32:28 -0400 (AST) Received: (qmail invoked by alias); 23 Jan 2006 15:32:31 -0000 Received: from 201-15-251-250.ctame704.dsl.brasiltelecom.net.br (EHLO servidor) [201.15.251.250] by mail.gmx.net (mp017) with SMTP; 23 Jan 2006 16:32:31 +0100 X-Authenticated: #15924888 Subject: Re: [PERFORMANCE] Stored Procedures From: Marcos To: Markus Schaber Cc: pgsql-performance@postgresql.org In-Reply-To: <43D4CC67.9050703@logix-tt.com> References: <43D130EF.3010205@zg.htnet.hr> <20060120191031.GO20182@pervasive.com> <43D13C2F.1030203@zg.htnet.hr> <20060120213455.GT20182@pervasive.com> <1138004054.949.10.camel@servidor> <43D4CC67.9050703@logix-tt.com> Content-Type: text/plain Date: Mon, 23 Jan 2006 13:27:25 +0000 Message-Id: <1138022845.949.26.camel@servidor> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 FreeBSD GNOME Team Port Content-Transfer-Encoding: 7bit X-Y-GMX-Trusted: 0 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.144 required=5 tests=[AWL=0.144] X-Spam-Score: 0.144 X-Spam-Level: X-Archive-Number: 200601/412 X-Sequence-Number: 16890 Hi Markus > You really should consider using a connection pool (most web application > servers provide pooling facilities) or some other means to keep the > connection between several http requests. Yes. I'm finding a connection pool, I found the pgpool but yet don't understand how it's work I'm go read more about him. Thanks Marcos From dave@tridecap.com Mon Jan 23 16:23:52 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 248C49DC813 for ; Mon, 23 Jan 2006 12:23:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76551-04 for ; Mon, 23 Jan 2006 12:23:50 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from globalrelay.com (mail1.globalrelay.com [216.18.71.77]) by postgresql.org (Postfix) with ESMTP id DBA1E9DC804 for ; Mon, 23 Jan 2006 12:23:47 -0400 (AST) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [216.160.41.114] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 81841653; Mon, 23 Jan 2006 08:23:28 -0800 From: "Dave Dutcher" To: "'Marcos'" Cc: Subject: Re: [PERFORMANCE] Stored Procedures Date: Mon, 23 Jan 2006 10:23:17 -0600 Message-ID: <000001c62039$579e88b0$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 Importance: Normal In-Reply-To: <1138022845.949.26.camel@servidor> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200601/413 X-Sequence-Number: 16891 I don't think pgpool is what you need. If I understand pgpool correctly, pgpool lets you pool multiple postgres servers together. You are just looking for database connection pooling. A simple connection pool is basically just an application wide list of connections. When a client needs a connection, you just request a connection from the pool. If there is an unused connection in the pool, it is given to the client and removed from the unused pool. If there is no unused connection in the pool, then a new connection is opened. When the client is done with it, the client releases it back into the pool. You can google for 'database connection pool' and you should find a bunch of stuff. It's probably a good idea to find one already written. If you write your own you have to make sure it can deal with things like dead connections, synchronization, and maximum numbers of open connections. Dave -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Marcos Sent: Monday, January 23, 2006 7:27 AM To: Markus Schaber Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] [PERFORMANCE] Stored Procedures Hi Markus > You really should consider using a connection pool (most web application > servers provide pooling facilities) or some other means to keep the > connection between several http requests. Yes. I'm finding a connection pool, I found the pgpool but yet don't understand how it's work I'm go read more about him. Thanks Marcos ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org From frank@wiles.org Mon Jan 23 17:24:45 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id E41219DC97A for ; Mon, 23 Jan 2006 13:24:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91786-03 for ; Mon, 23 Jan 2006 13:24:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from frank.wiles.org (frank.wiles.org [24.124.39.75]) by postgresql.org (Postfix) with ESMTP id 610799DC8D9 for ; Mon, 23 Jan 2006 13:24:41 -0400 (AST) Received: from kungfu (frank.wiles.org [127.0.0.1]) by frank.wiles.org (8.13.1/8.13.1) with SMTP id k0NHOW8Y008844; Mon, 23 Jan 2006 11:24:33 -0600 Date: Mon, 23 Jan 2006 11:25:26 -0600 From: Frank Wiles To: "Dave Dutcher" Cc: mjs_ops@gmx.net, pgsql-performance@postgresql.org Subject: Re: [PERFORMANCE] Stored Procedures Message-Id: <20060123112526.1ee25dcd.frank@wiles.org> In-Reply-To: <000001c62039$579e88b0$8300a8c0@tridecap.com> References: <1138022845.949.26.camel@servidor> <000001c62039$579e88b0$8300a8c0@tridecap.com> X-Mailer: Sylpheed version 2.0.1 (GTK+ 2.6.10; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.065 required=5 tests=[AWL=0.065] X-Spam-Score: 0.065 X-Spam-Level: X-Archive-Number: 200601/414 X-Sequence-Number: 16892 On Mon, 23 Jan 2006 10:23:17 -0600 "Dave Dutcher" wrote: > I don't think pgpool is what you need. If I understand pgpool > correctly, pgpool lets you pool multiple postgres servers together. > You are just looking for database connection pooling. While pgpool can let you pool together multiple backend servers, it also functions well as just a connection pooling device with only one backend. --------------------------------- Frank Wiles http://www.wiles.org ---------------------------------