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 < dal...@solfertje.student.utwente.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! > > >
