Re: Difference between array column type and separate table

Yeah I talked with some other SQL guru friends of mine and they all agree
the separate table is the way to go for a number of reasons, so that's what
I'll stick with. It was just one of those things where you see a new
feature and try to find an excuse to try it out <g>
Thanks!
Mike

On Sat, May 2, 2009 at 3:14 AM, Alban Hertroys <
dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:

> On May 2, 2009, at 9:33 AM, Mike Christensen wrote:
>
> Using this, I could completely get rid of ThreadTags and have a table like
>> this:
>>
>> create table Threads (
>> Id uuid not null,
>> Posted timestamp not null,
>> Subject varchar(255) not null,
>> Replies int4 not null,
>> PosterId uuid not null,
>> Tags int2[],
>> primary key (Id)
>> );
>>
>> and then find threads using the ANY function:
>>
>> select * from Threads where 5 = ANY (Tags);
>>
>> To me this seems cleaner, but I'm wondering about performance. If I had
>> millions of threads, is a JOIN going to be faster? I guess what I'm asking
>> about is the underlying implementation of ANY. Is it doing a sequential
>> search? Can I index Tags and will ANY() then use that index? Any other
>> opinions on what option is better?
>>
>
> If you modify the array the entire array needs to be rewritten. I don't
> think you'd want that with millions of threads in it. I don't think array
> values are indexable either. So while they're probably faster to query for
> small amounts of threads, the join is likely faster to query for large
> amounts (provided they're indexed properly, of course).
>
> If you want to be sure, play around with explain analyse with both
> implementations.
>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:880,49fc1d1e129741592332518!
>
>
>