Thread: 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 meb@speakeasy.net -- Let's organize this thing and take all the fun out of it. --
> 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
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 (rbt@rbt.ca) 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 meb@speakeasy.net -- Let's organize this thing and take all the fun out of it. --
Mark Bronnimann wrote: > 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... > It sounds like you're using a sql function, not a plpgsql function (although I don't think you said either way). If you write the function in plpgsql it will get parsed and cached on the first call in a particular backend session, which *might* give you improved performance on subsequent calls, if there are any; are you using persistent connections? Alternatively, it might work to use a prepared query. Joe
Mark, I'm crossing this over to the performance list; it's really appropriate on both lists. So I'm quoting you in full as well. > 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? Actually, this is exactly what I'd expect in your situation. The SRF returns the records in a very inefficient fashion: by materializing the result set and looping through it to return it to the calling cursor, whereas the View does set-based operations to grab blocks of data. Also PL/pgSQL as a language is not nearly as optimized as Oracle's PL/SQL. It's also possible that PostgreSQL handles criteria-filtered views better than Oracle does. I wouldn't be surprised. The only times I can imagine an SRF being faster than a view with a where clause are: 1) When you're only returning a small part of a complex result set, e.g. 10 rows out of 32,718. 2) When the view is too complex (e.g. UNION with subselects) for the Postgres planner to "push down" the WHERE criteria into the view execution. I've been planning on testing the performance of SRFs vs. views myself for paginated result sets in a web application, but haven't gotten around to it since I can't get my www clients to upgrade to 7.3 ... -- -Josh Berkus Aglio Database Solutions San Francisco