immutable functions vs. join for lookups ?

From: Enrico Weigelt
Subject: immutable functions vs. join for lookups ?
Date: ,
Msg-id: 20050415205511.GB20345@nibiru.borg.metux.de
(view: Whole thread, Raw)
Responses: Re: immutable functions vs. join for lookups ?  (Tom Lane)
Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko)
List: pgsql-performance

Tree view

immutable functions vs. join for lookups ?  (Enrico Weigelt, )
 Re: immutable functions vs. join for lookups ?  (Tom Lane, )
  Re: immutable functions vs. join for lookups ?  (Enrico Weigelt, )
   Re: immutable functions vs. join for lookups ?  (Jaime Casanova, )
    Re: immutable functions vs. join for lookups ?  (Enrico Weigelt, )
     Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko, )
 Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko, )
 Re: immutable functions vs. join for lookups ?  ("Merlin Moncure", )
  Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko, )
   Re: immutable functions vs. join for lookups ?  ("Jim C. Nasby", )
    Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko, )
     Re: immutable functions vs. join for lookups ?  (Christopher Kings-Lynne, )
     Re: immutable functions vs. join for lookups ?  (Christopher Kings-Lynne, )
  Re: immutable functions vs. join for lookups ?  (Tom Lane, )
   Re: immutable functions vs. join for lookups ?  (Greg Stark, )
    Re: immutable functions vs. join for lookups ?  (Tom Lane, )
     Re: immutable functions vs. join for lookups ?  (Enrico Weigelt, )
      Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko, )

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:     
  cellphone: +49 174 7066481
---------------------------------------------------------------------
 -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: speed of querry?
From: Ron Mayer
Date:
Subject: Re: Spend 7K *WHERE*? WAS Intel SRCS16 SATA raid? and How