Re: Views and functions returning sets of records

Giorgio Valoti <giorgio_v(at)mac(dot)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°