Q: Multicolumn lookup, Join or Sub-query ?

From: radevenz(at)ix(dot)netcom(dot)com (Richard A(dot) DeVenezia)
To: pgsql-sql(at)postgresql(dot)org
Subject: Q: Multicolumn lookup, Join or Sub-query ?
Date: 2003-08-27 15:30:24
Message-ID: e320eda1.0308270730.4706d397@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have a table T with many columns whose values are are lookup keys
id_1, id_2, id_3, id_4, id_5, ..., id_26

The values corresponding to the keys live in table L, the lookup table:
id, id_value

T might have 100K rows and L 500K rows.

I am wondering what would be the best view (performance-wise) to see the values?

-----
Method one - join

create view V1 as
select a.id_value as v_1, ... z.id_value as v_26
from
T, L as a, L as b, ..., L as z
where
a.id = T.id_1
and b.id = T.id_2
...
and z.id = T.id_26

-----
Method two - sub-query

create view V2 as
select
(select id_value from L where id = id_1) as v_1
, (select id_value from L where id = id_2) as v_2
...
, (select id_value from L where id = id_26) as v_26

TIA,
Richard

Browse pgsql-sql by date

  From Date Subject
Next Message scott.marlowe 2003-08-27 15:31:12 Re: Canceling other backend's query
Previous Message Stephan Szabo 2003-08-27 15:11:12 Re: How to return a record set from function.