Re: View vs function

Hi All,

I have been reading about set returning functions. What I would like to
know is is there a performance advantage in using SRFs versus querying a
view. Assuming the underlying SQL is the same for the view vs the
function except for the WHERE clause which of these would you expect to
be faster? Or does the planner realize all this...

SELECT * FROM view_big_query WHERE column1 = 1234;

SELECT * FROM func_bug_query(1234);

--
Kind Regards,
Keith

On Sun, Mar 20, 2005 at 22:39:57 -0500,
Keith Worthington <KeithW(at)NarrowPathInc(dot)com> wrote:
> Hi All,
>
> I have been reading about set returning functions. What I would like to
> know is is there a performance advantage in using SRFs versus querying a
> view. Assuming the underlying SQL is the same for the view vs the
> function except for the WHERE clause which of these would you expect to
> be faster? Or does the planner realize all this...

In general you are going to be better off with a view, since the planner
knows what the view is doing and there may be some optimizations it
can make. Functions are just black boxes to the planner.

>
> SELECT * FROM view_big_query WHERE column1 = 1234;
>
> SELECT * FROM func_bug_query(1234);
>
> --
> Kind Regards,
> Keith
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

Bruno Wolff III wrote:
> Functions are just black boxes to the planner.

... unless the function is a SQL function that is trivial enough for the
planner to inline it into the plan of the invoking query. Currently, we
won't inline set-returning SQL functions that are used in the query's
rangetable, though. This would be worth doing, I think -- I'm not sure
how much work it would be, though.

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> Bruno Wolff III wrote:
>> Functions are just black boxes to the planner.

> ... unless the function is a SQL function that is trivial enough for the
> planner to inline it into the plan of the invoking query. Currently, we
> won't inline set-returning SQL functions that are used in the query's
> rangetable, though. This would be worth doing, I think -- I'm not sure
> how much work it would be, though.

Yeah, I've been thinking the same. It seems like it shouldn't be unduly
difficult --- not harder than inlining scalar-valued SQL functions, just
different validity conditions.

regards, tom lane