Thread: Views and functions returning sets of records

Views and functions returning sets of records

From
Giorgio Valoti
Date:
Hi all,
maybe it’s a naive question but I was wondering if there is any
difference, from a performance point of view, between a view and a
function performing the same task, something like:

CREATE VIEW foo AS …;
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;


Thank you
--
Giorgio Valoti





Re: Views and functions returning sets of records

From
Andreas Kretschmer
Date:
Giorgio Valoti <giorgio_v@mac.com> schrieb:

> Hi all,
> maybe it?s a naive question but I was wondering if there is any
> difference, from a performance point of view, between a view and a
> function performing the same task, something like:
>
> CREATE VIEW foo AS ?;
> CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
>     SELECT * FROM foo WHERE fooid = $1;
> $$ LANGUAGE SQL;

Yes. The planner can't sometimes optimze the query, a simple example:

I have ha table called 'words', it contains a few thousand simple words.

test=# \d words
   Table "public.words"
 Column | Type | Modifiers
--------+------+-----------
 w      | text |
Indexes:
    "idx_words" btree (lower(w) varchar_pattern_ops)


Now i'm searching and the index is in use:

test=# explain analyse select * from words where lower(w) like lower('foo');
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_words on words  (cost=0.00..6.01 rows=1 width=12) (actual time=0.065..0.065 rows=0 loops=1)
   Index Cond: (lower(w) ~=~ 'foo'::character varying)
   Filter: (lower(w) ~~ 'foo'::text)
 Total runtime: 0.187 ms
(4 rows)





Now i'm writung a function for that:

test=*# create or replace function get_words(text) returns setof record as $$select * from words where lower(w) like
lower($1);$$ language sql; 
CREATE FUNCTION
Time: 4.413 ms

The query inside the function body is the same as above, let's test:

test=*# explain analyse select * from get_words('foo') as (w text);
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Function Scan on get_words  (cost=0.00..12.50 rows=1000 width=32) (actual time=213.947..213.947 rows=0 loops=1)
 Total runtime: 214.031 ms
(2 rows)


As you can see, a slow seq. scan are used now. Because the planner don't
know the argument and don't know if he can use the index or not. In my
case the planner created a bad plan.


But a VIEW is not a function, it's only a RULE for SELECT on a virtual table:

test=*# create view view_words as select * from words;
CREATE VIEW
Time: 277.411 ms
test=*# explain analyse select * from view_words where lower(w) like lower('foo');
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_words on words  (cost=0.00..6.01 rows=1 width=12) (actual time=0.044..0.044 rows=0 loops=1)
   Index Cond: (lower(w) ~=~ 'foo'::character varying)
   Filter: (lower(w) ~~ 'foo'::text)
 Total runtime: 0.259 ms
(4 rows)


It's the same plan as above for the source table.




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Views and functions returning sets of records

From
Tom Lane
Date:
Giorgio Valoti <giorgio_v@mac.com> writes:
> maybe it�s a naive question but I was wondering if there is any
> difference, from a performance point of view, between a view and a
> function performing the same task,

Yes.  Usually the view will win.

            regards, tom lane

Re: Views and functions returning sets of records

From
Andreas Kretschmer
Date:
Tom Lane <tgl@sss.pgh.pa.us> schrieb:

> Giorgio Valoti <giorgio_v@mac.com> writes:
> > maybe it’s a naive question but I was wondering if there is any
> > difference, from a performance point of view, between a view and a
> > function performing the same task,
>
> Yes.  Usually the view will win.

*smile*


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Views and functions returning sets of records

From
Giorgio Valoti
Date:
On 22/mar/08, at 17:55, Andreas Kretschmer wrote:

> Tom Lane <tgl@sss.pgh.pa.us> schrieb:
>
>> Giorgio Valoti <giorgio_v@mac.com> writes:
>>> maybe it’s a naive question but I was wondering if there is any
>>> difference, from a performance point of view, between a view and a
>>> function performing the same task,
>>
>> Yes.  Usually the view will win.
>
> *smile*

:-(
I was thinking about using using functions as the main way to
interact with the database from an external application. The
(supposed) rationale was to simplify the application code: you only
have to worry about in and out function parameters.
Are there any way to pass some hints to the planner? For example,
could the IMMUTABLE/STABLE/VOLATILE modifiers be of some help?


Thank you
--
Giorgio Valoti





Re: Views and functions returning sets of records

From
Tom Lane
Date:
Giorgio Valoti <giorgio_v@mac.com> writes:
> Are there any way to pass some hints to the planner? For example,
> could the IMMUTABLE/STABLE/VOLATILE modifiers be of some help?

Those don't really do anything for set-returning functions at the
moment.

As of 8.3 there is a ROWS attribute for SRFs that can help with one
of the worst problems, namely that the planner has no idea how many
rows a SRF might return.  It's simplistic (just an integer constant
estimate) but better than no control at all.

As of CVS HEAD (8.4 to be) there's a capability in the planner to
"inline" SRFs that are single SELECTs in SQL language, which should
pretty much eliminate the performance differential against a comparable
view.  Unfortunately 8.4 release is at least a year away, but just
so you know.  (I suppose if you were desperate enough to run a privately
modified copy, that patch should drop into 8.3 easily enough.)  IIRC
the restrictions for this to happen are
    * single SELECT
    * function declared to return set
    * function NOT declared strict or volatile
    * function NOT declared SECURITY DEFINER or given any
      local parameter settings
The latter restrictions are needed so that inlining doesn't change
the semantics.

            regards, tom lane

Re: Views and functions returning sets of records

From
Rajashri Tupe
Date:
Can we write retrieving only 10 records from 4000 records
plz tell me asap

On Mar 23, 8:28 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Giorgio Valoti <giorgi...@mac.com> writes:
> > Are there any way to pass some hints to the planner? For example,
> > could the IMMUTABLE/STABLE/VOLATILE modifiers be of some help?
>
> Those don't really do anything for set-returning functions at the
> moment.
>
> As of 8.3 there is a ROWS attribute for SRFs that can help with one
> of the worst problems, namely that the planner has no idea how many
> rows a SRF might return.  It's simplistic (just an integer constant
> estimate) but better than no control at all.
>
> As of CVS HEAD (8.4 to be) there's a capability in the planner to
> "inline" SRFs that are single SELECTs in SQL language, which should
> pretty much eliminate the performance differential against a comparable
> view.  Unfortunately 8.4 release is at least a year away, but just
> so you know.  (I suppose if you were desperate enough to run a privately
> modified copy, that patch should drop into 8.3 easily enough.)  IIRC
> the restrictions for this to happen are
>         * single SELECT
>         * function declared to return set
>         * function NOT declared strict or volatile
>         * function NOT declared SECURITY DEFINER or given any
>           local parameter settings
> The latter restrictions are needed so that inlining doesn't change
> the semantics.
>
>                         regards, tom lane
>
> -
> Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance


Re: Views and functions returning sets of records

From
Craig Ringer
Date:
Rajashri Tupe wrote:
> Can we write retrieving only 10 records from 4000 records
> plz tell me asap
>
Is this question connected to the previous discussion?

Can you be more specific with your question?

Without having any idea what you're talking about, I'll direct you to
the "LIMIT" clause of the SELECT statement in case that's what you mean.

http://www.postgresql.org/docs/current/static/queries-limit.html

--
Craig Ringer