Re: Difference between array column type and separate table

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!