Giorgio Valoti <giorgio_v@xxxxxxx> 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°
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
