Re: Mysterious performance of query because of plsql function in where condition

On Jul 2, 2004, at 3:48 AM, Peter Alberer wrote:
>
> Postgres seems to execute the function "submission_status" for every
> row
> of
> the submissions table (~1500 rows). The query therefore takes quite a
> lot
> time, although in fact no row is returned from the assignments table
> when
> the condition package_id=949589 is used.
>

Well, you need to think of it this way - PG has no idea what the
function does so it treats it as a "black box" - thus it has to run it
for each row to see what evaluates too - especially since it is in a
where clause.

If you really want a function there you can use a SQL function instead
of plpgsql - PG has smart enough to push that function up into your
query and let the optimizer look at the whole thing.

You can also take a look at the various flags you can use while
creating functions such as immutable, strict, etc. they can help

--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/