From: radevenz@ix.netcom.com
To: pgsql-sql@postgresql.org
Subject: Q: Multicolumn lookup, Join or Sub-query ?
Date: 27 Aug 2003 08:30:24 -0700
Message-ID: <e320eda1.0308270730.4706d397@posting.google.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: radevenz@ix.netcom.com
Subject: Re: Q: Multicolumn lookup, Join or Sub-query ?
In-Reply-To: <e320eda1.0308270730.4706d397@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox