immutable functions vs. join for lookups ? - Mailing list pgsql-performance

From Enrico Weigelt
Subject immutable functions vs. join for lookups ?
Date
Msg-id 20050415205511.GB20345@nibiru.borg.metux.de
Whole thread Raw
Responses Re: immutable functions vs. join for lookups ?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko <qnex42@gmail.com>)
List pgsql-performance
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:     contact@metux.de
  cellphone: +49 174 7066481
---------------------------------------------------------------------
 -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------

pgsql-performance by date:

Previous
From: "Matthew Nuzum"
Date:
Subject: Re: Spend 7K *WHERE*? WAS Intel SRCS16 SATA raid? and How
Next
From: Thomas F.O'Connell
Date:
Subject: pgbench Comparison of 7.4.7 to 8.0.2