immutable functions vs. join for lookups ?

От: Enrico Weigelt
Тема: immutable functions vs. join for lookups ?
Дата: ,
Msg-id: 20050415205511.GB20345@nibiru.borg.metux.de
(см: обсуждение, исходный текст)
Ответы: Re: immutable functions vs. join for lookups ?  (Tom Lane)
Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko)
Список: pgsql-performance

Скрыть дерево обсуждения

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 по дате сообщения:

От: Kevin Brown
Дата:
Сообщение: Re: How to improve db performance with $7K?
От: Hannes Dorbath
Дата:
Сообщение: Re: Query Optimizer Failure / Possible Bug