From: Tom Lane <tgl@sss.pgh.pa.us>
To: Davor J. <DavorJ@live.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow function in queries SELECT clause.
Date: Sun, 20 Jun 2010 11:21:07 -0400
Message-ID: <25116.1277047267@sss.pgh.pa.us> (raw)
In-Reply-To: <hvj6cg$2dna$1@news.hub.org>
References: <hvj6cg$2dna$1@news.hub.org>
"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
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: tgl@sss.pgh.pa.us, DavorJ@live.com
Subject: Re: Slow function in queries SELECT clause.
In-Reply-To: <25116.1277047267@sss.pgh.pa.us>
* 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