From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: Views and functions returning sets of records
Date: Sat, 22 Mar 2008 17:35:47 +0100
Message-ID: <20080322163547.GA12508@KanotixBox> (raw)
In-Reply-To: <6A737ADC-2A11-4A01-8ACE-C439E4297273@mac.com>
References: <6A737ADC-2A11-4A01-8ACE-C439E4297273@mac.com>
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°
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: akretschmer@spamfence.net
Subject: Re: Views and functions returning sets of records
In-Reply-To: <20080322163547.GA12508@KanotixBox>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox