Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id D71521337B68
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 14 Mar 2011 05:46:25 -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 67042-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 14 Mar 2011 08:46:14 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.csc.lt (mail.csc.lt [193.138.191.5])
	by mail.postgresql.org (Postfix) with ESMTP id EFB871337B5D
	for <pgsql-performance@postgresql.org>;
	Mon, 14 Mar 2011 05:46:13 -0300 (ADT)
Received: (qmail 27555 invoked by uid 210); 14 Mar 2011 08:46:10 -0000
Received: from mail.nsoft.lt by mail (envelope-from <julius@nsoft.lt>,
	uid 201) with qmail-scanner-2.05st
	(clamdscan: 0.97/12830. spamassassin: 3.2.1. perlscan: 2.05st.  
	Clear:RC:1(77.240.249.53):.
	Processed in 0.021527 secs); 14 Mar 2011 08:46:10 -0000
Received: from mail.nsoft.lt (77.240.249.53)
	by mail.csc.lt with SMTP; 14 Mar 2011 08:46:10 -0000
Received: from [192.168.16.113] (unknown [77.240.249.53])
	(using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.nsoft.lt (Postfix) with ESMTPS id 5653F8E07A;
	Mon, 14 Mar 2011 10:46:08 +0200 (EET)
Message-ID: <4D7DD5CF.4080502@nsoft.lt>
Date: Mon, 14 Mar 2011 10:46:07 +0200
From: Julius Tuskenis <julius@nsoft.lt>
Organization: UAB nSoft
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; lt-LT;
	rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9
MIME-Version: 1.0
To: Merlin Moncure <mmoncure@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: unexpected stable function behavior
References: <4D78FB98.7090609@nsoft.lt>
	<AANLkTimTR_f98H4+h57iW6-S=BjZaMTSQ6VznRgXU5Ly@mail.gmail.com>
In-Reply-To: <AANLkTimTR_f98H4+h57iW6-S=BjZaMTSQ6VznRgXU5Ly@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------050303050207000208040102"
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: 201103/139
X-Sequence-Number: 42818

This is a multi-part message in MIME format.
--------------050303050207000208040102
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hello, Merlin

Thank you for your quick response.

2011.03.10 23:14, Merlin Moncure rašė:
> This is a huge problem with non trivial functions in the select list.
> Pushing the result into and a subquery does NOT guarantee that the
> inner result is materialized first.
 From the postgresql documentation about STABLE functions: "This 
category allows the optimizer to optimize multiple calls of the function 
to a single call." I thought that this means that optimizer executes the 
function only for now parameter sets and stores results in some "cache" 
and use it if the parameters are already known. I realize this is very 
naive approach and most probably everything is much more complicated. I 
would appreciate if someone would explain the mechanism (or provide with 
some useful link).

>     Try a CTE.
>
> with foo as
> (
>    select yadda;
> )
> select func(foo.a), foo.* from foo;
I'm sorry, but I'm totally new to CTE. Would you please show me how 
should I use the stable function and where the parameters should be put 
to improve the behavior of the optimizer for my problem?

Thank you in advance

-- 
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050


--------------050303050207000208040102
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    Hello, Merlin<br>
    <br>
    Thank you for your quick response.<br>
    <br>
    2011.03.10 23:14, Merlin Moncure rašė:
    <blockquote
      cite="mid:AANLkTimTR_f98H4+h57iW6-S=BjZaMTSQ6VznRgXU5Ly@mail.gmail.com"
      type="cite">
      <pre wrap="">This is a huge problem with non trivial functions in the select list.
Pushing the result into and a subquery does NOT guarantee that the
inner result is materialized first.</pre>
    </blockquote>
    From the postgresql documentation about STABLE functions: "<span
      class="Apple-style-span" style="border-collapse: separate; color:
      rgb(0, 0, 0); font-family: 'Times New Roman'; font-style: normal;
      font-variant: normal; font-weight: normal; letter-spacing: normal;
      line-height: normal; orphans: 2; text-indent: 0px; text-transform:
      none; white-space: normal; widows: 2; word-spacing: 0px;
      font-size: medium;"><span class="Apple-style-span"
        style="font-family: verdana,sans-serif; font-size: 12px;
        line-height: 18px; text-align: left;">This category allows the
        optimizer to optimize multiple calls of the function to a single
        call.</span></span>" I thought that this means that optimizer
    executes the function only for now parameter sets and stores results
    in some "cache" and use it if the parameters are already known. I
    realize this is very naive approach and most probably everything is
    much more complicated. I would appreciate if someone would explain
    the mechanism (or provide with some useful link).<br>
    <br>
    <blockquote
      cite="mid:AANLkTimTR_f98H4+h57iW6-S=BjZaMTSQ6VznRgXU5Ly@mail.gmail.com"
      type="cite">
      <pre wrap="">   Try a CTE.

with foo as
(
  select yadda;
)
select func(foo.a), foo.* from foo;
</pre>
    </blockquote>
    I'm sorry, but I'm totally new to CTE. Would you please show me how
    should I use the stable function and where the parameters should be
    put to improve the behavior of the optimizer for my problem?<br>
    <br>
    Thank you in advance<br>
    <pre class="moz-signature" cols="72">-- 
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050</pre>
  </body>
</html>

--------------050303050207000208040102--