Thread: View vs Stored Proc Performance

On Fri, Sep 11, 2009 at 11:46 AM, Jason Tesser <jasontesser@gmail.com> wrote:
> Is it faster to use a Stored Proc that returns a Type or has Out Parameters
> then a View?  Views are easier to maintain I feel.  I remember testing this
> around 8.0 days and the view seemed slower with a lot of data.

for the most part, a view can be faster and would rarely be slower.
Views are like C macros for you query...they are expanded first and
then planned.  Functions (except for very simple ones) are black boxes
to the planner and can materially hurt query performance in common
cases.  The only case where a function would win is when dealing with
conner case planner issues (by forcing a nestloop for example).

merlin

On Fri, Sep 11, 2009 at 2:56 PM, Jason Tesser <jasontesser@gmail.com> wrote:
> OK so in my case I have a Person, Email, Phone and Address table.  I want to
> return the Person and an Array of the others. so my return type would be
> something like Person, Email[], Phone[], Address[]
>
> When passed a personId.
>
> Are you saying this is better in a view.  Create a view that can return that
> as oppessed to 1. defining a type for a function to return or 2. a function
> that returns 4 out parameters (Person, Address[] ,....)

if you are using 8.3+ and are wiling to make a composite type:

create table person_t(email text, phone text, address text);

select person_id, array_agg((email, phone, address)::person_t) from
person group by 1;

or, detail fields are in another table:

select person_id, (select array(select (email, phone,
address)::person_t) from detail where person_id = p.person_id) from
person_t;

merlin

On Fri, Sep 11, 2009 at 5:27 PM, Jason Tesser <jasontesser@gmail.com> wrote:
> Right what I was wondering is is this better done in a view? or a stored
> proc?   I am guessing based on your initial response the view is better
> performance.  These are the types of queries I will be doing though.
>

in performance terms the view should be faster if you are doing things
like joining the result to another table...the planner can see
'through' the view, etc.  in a function, the result is fetched first
and materialized without looking at the rest of the query.  the actual
mechanism you use to build the arrays is likely going to be more
important than anything else.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> like joining the result to another table...the planner can see
> 'through' the view, etc.  in a function, the result is fetched first
> and materialized without looking at the rest of the query.

I though the planner would "see through" SQL language functions and
inline them when possible, so they often can make for parametrized
views...

Regards,
--
dim

On Sat, Sep 12, 2009 at 7:51 AM, Dimitri Fontaine
<dfontaine@hi-media.com> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> like joining the result to another table...the planner can see
>> 'through' the view, etc.  in a function, the result is fetched first
>> and materialized without looking at the rest of the query.
>
> I though the planner would "see through" SQL language functions and
> inline them when possible, so they often can make for parametrized
> views...

It can happen for simple functions but often it will not.  For views
it always happens.

merlin

Merlin Moncure wrote:
> On Sat, Sep 12, 2009 at 7:51 AM, Dimitri Fontaine
> <dfontaine@hi-media.com> wrote:
>> Merlin Moncure <mmoncure@gmail.com> writes:
>>> like joining the result to another table...the planner can see
>>> 'through' the view, etc.  in a function, the result is fetched first
>>> and materialized without looking at the rest of the query.
>> I though the planner would "see through" SQL language functions and
>> inline them when possible, so they often can make for parametrized
>> views...
>
> It can happen for simple functions but often it will not.  For views
> it always happens.

Are functions in language 'sql' handled differently than those of
language 'plpgsql'?

I think they're not so in any case a function will behave as a black box
with regards to the planner and optimizer (and views are always
'transparent').

Ivan Voras <ivoras@freebsd.org> writes:
> Are functions in language 'sql' handled differently than those of
> language 'plpgsql'?

Yes.

> I think they're not so in any case a function will behave as a black box
> with regards to the planner and optimizer (and views are always
> 'transparent').

No.

            regards, tom lane

2009/9/15 Tom Lane <tgl@sss.pgh.pa.us>:
> Ivan Voras <ivoras@freebsd.org> writes:
>> Are functions in language 'sql' handled differently than those of
>> language 'plpgsql'?
>
> Yes.
>
>> I think they're not so in any case a function will behave as a black box
>> with regards to the planner and optimizer (and views are always
>> 'transparent').
>
> No.

Thanks! This is interesting information!

--
f+rEnSIBITAhITAhLR1nM9F4cIs5KJrhbcsVtUIt7K1MhWJy1A==