Hi folks,
I like to use (immutable) functions for looking up serveral
(almost constant) things, i.e fetching a username by id.
This makes my queries more clear.
But is this really performant ?
Lets imagine:
We've got an table with user accounts (uid,name,...). Then we've
got another one which contains some items assigned to users, and
so are linked to them by an uid field.
Now want to view the items with usernames instead of just uid:
a) SELECT items.a, items.b, ..., users.username FROM items, users
WHERE items.uid = users.uid;
c) CREATE FUNCTION id2username(oid) RETURNS text
LANGUAGE 'SQL' IMMUTABLE AS '
SELECT username AS RESULT FROM users WHERE uid = $1';
SELECT items.a, items.b, ..., id2username(users.uid);
Which one is faster with
a) only a few users (<50)
b) many users ( >1k )
while we have several 10k of items ?
thx
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: [EMAIL PROTECTED]
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
