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 22D1311F6294
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Apr 2006 10:57:11 -0300 (ADT)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Score: 0.99
X-Spam-Level: 
X-Spam-Status: No, score=0.99 required=5 tests=[AWL=0.177, INFO_TLD=0.813]
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id tojmLGbKdEHM
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Apr 2006 10:56:55 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
	by postgresql.org (Postfix) with ESMTP id C612411F65C2
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Apr 2006 10:56:58 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id C9EDC30F62; Tue, 11 Apr 2006 15:56:58 +0200 (MET DST)
From: Christopher Browne <cbbrowne@acm.org>
X-Newsgroups: pgsql.performance
Subject: Re: Stored Procedure Performance
Date: Tue, 11 Apr 2006 09:56:27 -0400
Organization: cbbrowne Computing Inc
Lines: 43
Message-ID: <87acasjjw4.fsf@wolfe.cbbrowne.com>
References: 
 <416F947BF5BA9E40B5F9AA649422212E074DE930@ex-mail2.internal.rmplc.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@news.hub.org
X-message-flag: Outlook is rather hackable, isn't it?
X-Home-Page: http://www.cbbrowne.com/info/
X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux)
Cancel-Lock: sha1:aD9HstEdscoyztktrJTH/TeUTfQ=
To: pgsql-performance@postgresql.org
X-Archive-Number: 200604/236
X-Sequence-Number: 18320

sdale@rm.com ("Simon Dale") wrote:
> <p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
> font-family:Arial'>Event with the planning removed, the function still performs
> significantly slower than the raw SQL. Is that normal or am I doing something wrong
> with the creation or calling of the
> function?<o:p></o:p></span></font></p>

I'd expect this, yes.

You're doing something via "stored procedure logic" that would be done
more directly via straight SQL; of course it won't be faster.

In effect, pl/pgsql involves (planning once) then running each line of
logic.  In effect, you replaced one query (select * from some table)
into 90 queries.  Yup, there's extra cost there.

There's not some "magic" by which stored procedures provide results
faster as a natural "matter of course;" the performance benefits
generally fall out of two improvements:

 1.  You eliminate client-to-server round trips.

    A stored proc that runs 8 queries saves you 8 round trips over
    submitting the 8 queries directly.  Saving you latency time.

 2.  You can eliminate the marshalling and transmission of unnecessary
     data.

   A stored proc that runs 8 queries, and only returns summarized
   results that all come from the last table queried will eliminate
   the need to marshall and transmit (possibly over a slow link) the
   data for the 7 preceding queries.

The case that you tried can benefit from neither of those effects;
your stored procedure eliminates NO round trips, and NO
marshalling/transmission.
-- 
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/rdbms.html
Rules of  the Evil Overlord  #228.  "If the  hero claims he  wishes to
confess  in public  or to  me  personally, I  will remind  him that  a
notarized deposition will serve just as well."
<http://www.eviloverlord.com/>