Re: [SQL] function returning setof performance question

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