[SQL] function returning setof performance question
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 [EMAIL PROTECTED] -- Let's organize this thing and take all the fun out of it. -- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] function returning setof performance question
Thanks for the reply. I was hoping to eliminate the parse call on the view because I was doing the where clause on the view instead of putting the where in the view. In all, I was hoping to keep a single view called from multiple functions with different where clauses. Yep... I shoulda known better... Thanks again! And Rod Taylor ([EMAIL PROTECTED]) said...: > > 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. > > Thats just plain strange (never tried on Oracle). Why in the world > would adding the overhead of a function call (with no other changes) > increase performance? > > The function has additional overhead in the form of the plpgsql > interpreter. You may find a c function will give close to identical > performance as with the standard view so long as the query is the same. > > > One thing to keep in mind is that the view can be rearranged to give a > better query overall. The exact work completed for the view may be > different when called from within a different SQL statement. Most > functions -- some SQL language based functions are strange this way -- > cannot do this > -- Mark Bronnimann [EMAIL PROTECTED] -- Let's organize this thing and take all the fun out of it. -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
