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.