Skip site navigation (1) Skip section navigation (2)

Re: Slow function in queries SELECT clause.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Davor J(dot)" <DavorJ(at)live(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow function in queries SELECT clause.
Date: 2010-06-20 15:21:07
Message-ID: 25116.1277047267@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Davor J." <DavorJ(at)live(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2010-06-20 15:34:41
Subject: Re: Obtaining the exact size of the database.
Previous:From: Kevin GrittnerDate: 2010-06-20 15:02:18
Subject: Re: join vs exists

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group