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

2004-07-02 Thread Bruno Wolff III 
On Fri, Jul 02, 2004 at 09:48:48 +0200,
  Peter Alberer <[EMAIL PROTECTED]> 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.

If submission_status is invertable you might want to create the
inverse function, mark it immutable and call it with 'closed'.
That would allow the optimizer to compare submissions.submission_id
to a constant.

Another option would be be to create an index on
submission_status(submissions.submission_id).

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

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

2004-07-02 Thread Jeff 
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