From: Davor J. <DavorJ@live.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow function in queries SELECT clause.
Date: Mon, 21 Jun 2010 08:57:41 +0200
Message-ID: <hvn2ij$25ql$1@news.hub.org> (raw)
References: <hvj6cg$2dna$1@news.hub.org>
<25116.1277047267@sss.pgh.pa.us>
Thanks Tom,
Your concepts of "inlining" and "black box" really cleared things up for me.
With fnc_unit_convert() written in SQL and declared as STABLE I indeed have
fast performance now.
I appreciate the note on the IMMUTABLE part. The table contents should not
change in a way to affect the functions. So, as far as I understand the
Postgres workings, this shouldn't pose a problem.
Regards,
Davor
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:25116.1277047267@sss.pgh.pa.us...
> "Davor J." <DavorJ@live.com> writes:
>> Suppose 2 functions: factor(int,int) and offset(int, int).
>> Suppose a third function: convert(float,int,int) which simply returns
>> $1*factor($2,$3)+offset($2,$3)
>> All three functions are IMMUTABLE.
>
> You should write the third function as a SQL function, which'd allow it
> to be inlined.
>
>> VERY FAST (half a second):
>> ----------------
>> SELECT data*factor(1,2)+offset(1,2) FROM tbl_data;
>
> In this case both factor() calls are folded to constants, hence executed
> only once.
>
>> VERY SLOW (a minute):
>> ----------------
>> SELECT convert(data, 1, 2) FROM tbl_data;
>
> Without inlining, there's no hope of any constant-folding here.
> The optimizer just sees the plpgsql function as a black box and
> can't do anything with it.
>
> BTW, your later mail shows that the factor() functions are not really
> IMMUTABLE, since they select from tables that presumably are subject to
> change. The "correct" declaration would be STABLE. If you're relying
> on constant-folding to get reasonable application performance, you're
> going to have to continue to mislabel them as IMMUTABLE; but be aware
> that you're likely to have issues any time you do change the table
> contents. The changes won't get reflected into existing query plans.
>
> regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
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: DavorJ@live.com
Subject: Re: Slow function in queries SELECT clause.
In-Reply-To: <hvn2ij$25ql$1@news.hub.org>
* 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