Re: Difference between array column type and separate table

Alban Hertroys wrote:
> On May 2, 2009, at 9:33 AM, Mike Christensen wrote:
>
>> ...
>>  create table Threads ( ...  Tags int2[], ...);
>>
>> 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 don't think array values are indexable either. ...

Of course they are indexable if you construct the right index
http://www.postgresql.org/docs/current/static/intarray.html

For certain queries, the array will probably be *much* faster
than the extra tables.  For example a query like this:

  SELECT * FROM Threads WHERE Threads.tags @@ '1&(2|3)'::query_int;

that does a single indexscan will be far far faster using an
extra table, where the query would look something like:

   select * from Threads T where
     id in (select threadid from threadtags where id = 1)
     and
     id in (select threadid from threadtags where id = 2 or id = 3);

requiring 3 indexscans on a table with many more rows, and
some potentially rather expensive joins.