Thread: Views and functions returning sets of records
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
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°
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
Tom Lane <tgl@sss.pgh.pa.us> schrieb: > Giorgio Valoti <giorgio_v@mac.com> writes: > > maybe its 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°
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
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
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
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