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>
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.
