Date: Tue, 29 Jul 2003 22:08:59 -0400
From: Mark Bronnimann <meb@speakeasy.net>
To: pgsql-sql@postgresql.org
Subject: function returning setof performance question
Message-ID: <20030730020859.GA1795@spiff.astoria.bogus>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.4.1i


  I have a question regarding the performance of a function returning a 
set of a view as opposed to just selecting the view with the same 
where clause. Please, if this should go to the performance list instead, 
let me know. I'm just wondering about this from the sql end of things. 

  Here's the environment:

  I'm working from PHP, calling on the query. 

  I have a view that joins 12 tables and orders the results. 

  From PHP, I do a select on that view with a where clause. 

  I created a function that queries the view with the where clause 
included in the function. The function is returning a setof that 
view taking one variable for the where clause (there are several 
other static wheres in there).

  I have found that querying the view with the where clause is 
giving me quicker results than if I call the function. 

  The performance hit is tiny, we're talking less than 1/2 a second, 
but when I've done this sort of thing in Oracle I've seen a performance 
increase, not a decrease. 

  Any ideas? 

  Thanks folks... I'm new to the list. 


-- 

Mark Bronnimann
meb@speakeasy.net
      
-- Let's organize this thing and take all the fun out of it. --