Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 1E627B5DBF9
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 17 Aug 2011 21:06:06 -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 91924-01
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 18 Aug 2011 00:05:57 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-vx0-f174.google.com (mail-vx0-f174.google.com
	[209.85.220.174])
	by mail.postgresql.org (Postfix) with ESMTP id 08FDCB5DBF6
	for <pgsql-performance@postgresql.org>;
	Wed, 17 Aug 2011 21:05:56 -0300 (ADT)
Received: by vxi9 with SMTP id 9so1323454vxi.19
	for <pgsql-performance@postgresql.org>;
	Wed, 17 Aug 2011 17:05:56 -0700 (PDT)
Received: by 10.52.28.204 with SMTP id d12mr53031vdh.359.1313625956565;
	Wed, 17 Aug 2011 17:05:56 -0700 (PDT)
Received: from [10.1.1.8] (124-169-181-228.dyn.iinet.net.au [124.169.181.228])
	by mx.google.com with ESMTPS id n5sm1030197vcw.8.2011.08.17.17.05.54
	(version=SSLv3 cipher=OTHER); Wed, 17 Aug 2011 17:05:56 -0700 (PDT)
Message-ID: <4E4C575D.6080008@ringerc.id.au>
Date: Thu, 18 Aug 2011 08:05:49 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: Anish Kejariwal <anishkej@gmail.com>
CC: Pavel Stehule <pavel.stehule@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Calculating statistic via function rather than with
	query is slowing my query
References: 
 <CAOpcnr9q2-q-n3TkuQFFgwc2UANFikZoFqhPwfmBJFu6b_MVrw@mail.gmail.com>
	<CAFj8pRDVTgF6RQwOEs1wJLHWy-FVO-mFGFRkZF70rmrxY8GTCQ@mail.gmail.com>
	<CAOpcnr-1=YGsp=OAV58cdEfNw4EEuuzEqo53+9C7kURZLmX4Ew@mail.gmail.com>
In-Reply-To: 
 <CAOpcnr-1=YGsp=OAV58cdEfNw4EEuuzEqo53+9C7kURZLmX4Ew@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------070909030906000707070804"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201108/212
X-Sequence-Number: 44659

This is a multi-part message in MIME format.
--------------070909030906000707070804
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

On 18/08/2011 3:00 AM, Anish Kejariwal wrote:
> Thanks Pavel! that definitely solved it.
>
> Unfortunately, the function I gave you was a simple/short version of 
> what the actual function is going to be.  The actual function is going 
> to get parameters passed to it, and based on the parameters will go 
> through some if...else conditions, and maybe even call another 
> function.  Based on that, I was definitely hoping to use plpgsql, and 
> the overhead is unfortunate.
>
> Is there any way to get around this overhead?  Will I still have the 
> same overhead if I use plperl, plpython, pljava, or write the function 
> in C?

You can probably still write it as an SQL function if you use CASE WHEN 
appropriately.

--
Craig Ringer

--------------070909030906000707070804
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=windows-1252"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    On 18/08/2011 3:00 AM, Anish Kejariwal wrote:
    <blockquote
cite="mid:CAOpcnr-1=YGsp=OAV58cdEfNw4EEuuzEqo53+9C7kURZLmX4Ew@mail.gmail.com"
      type="cite">Thanks Pavel! that definitely solved it.  
      <div><br>
      </div>
      <div>Unfortunately, the function I gave you was a simple/short
        version of what the actual function is going to be.  The actual
        function is going to get parameters passed to it, and based on
        the parameters will go through some if...else conditions, and
        maybe even call another function.  Based on that, I was
        definitely hoping to use plpgsql, and the overhead is<span
          class="Apple-style-span" style="font-family: arial,
          sans-serif; font-size: 13px; background-color: rgb(255, 255,
          255); "> unfortunate.  </span>
        <div>
          <font class="Apple-style-span" face="arial, sans-serif"><br>
          </font></div>
        <div><font class="Apple-style-span" face="arial, sans-serif">Is
            there any way to get around this overhead?  Will I still
            have the same overhead if I use plperl, plpython, pljava, or
            write the function in C?</font><br>
        </div>
      </div>
    </blockquote>
    <br>
    You can probably still write it as an SQL function if you use CASE
    WHEN appropriately.<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------070909030906000707070804--