Date: Sat, 22 Mar 2008 17:35:47 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: Views and functions returning sets of records
Message-ID: <20080322163547.GA12508@KanotixBox>
References: <6A737ADC-2A11-4A01-8ACE-C439E4297273@mac.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <6A737ADC-2A11-4A01-8ACE-C439E4297273@mac.com>
User-Agent: mutt-ng 1.5.9i (Linux)

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°