Re: Column as arrays.. more efficient than columns?

Nobody has any comments on this??

On Thu, 2007-09-06 at 12:22 +0800, Ow Mun Heng wrote:
> Table is like
>
> create table foo (
> number int,
> subset int,
> value int
> )
>
> select * from foo;
> number | subset | value
> 1 1 1
> 1 2 2
> 1 3 10
> 1 4 3
>
> current query is like
>
> select number,
> avg(case when subset = 1 then value else null end) as v1,
> avg(case when subset = 2 then value else null end) as v2,
> avg(case when subset = 3 then value else null end) as v3,
> avg(case when subset = 4 then value else null end) as v4
> from foo
> group by number
>
> results
> ------
> number | v1 | v2 | v3 | v4
> 1 1 2 10 4
>
>
> I'm thinking of denormalising it a bit and put it either as an array or
> just create a new table with the end result like the above.
>
> I just want to know which is more efficient. Users can just do a
>
> select * from new_foo where number = 'X';
>
>
> Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend