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 <[EMAIL PROTECTED]>
http://www.jefftrout.com/
http://www.stuarthamm.net/


---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Reply via email to