From: Julius Tuskenis <julius@nsoft.lt>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: unexpected stable function behavior
Date: Mon, 14 Mar 2011 10:46:07 +0200
Message-ID: <4D7DD5CF.4080502@nsoft.lt> (raw)
In-Reply-To: <AANLkTimTR_f98H4+h57iW6-S=BjZaMTSQ6VznRgXU5Ly@mail.gmail.com>
References: <4D78FB98.7090609@nsoft.lt>
<AANLkTimTR_f98H4+h57iW6-S=BjZaMTSQ6VznRgXU5Ly@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: julius@nsoft.lt, mmoncure@gmail.com
Subject: Re: unexpected stable function behavior
In-Reply-To: <4D7DD5CF.4080502@nsoft.lt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox