Re: Difference between array column type and separate table

Let's say you have a table called Threads, and each thread can have zero or
more "tags" associated with it. A tag is just a byte which maps to some
enum somewhere.
There's two ways I can think of to do this. The first would be to have:

create table Threads (
Id uuid not null,
Posted timestamp not null,
Subject varchar(255) not null,
Replies int4 not null,
PosterId uuid not null,
primary key (Id)
);

and a separate table for each tag on a thread:

create table ThreadTags (
Id uuid not null,
Tag int2 not null,
ThreadId uuid not null,
primary key (Id)
)

I can query for all threads with a certain tag using a join:

select * from Threads T
inner join ThreadTags tag ON tag.ThreadId = T.Id AND tag.Tag = 5;

This should work fine. However, recently I was digging through Postgres
manuals and found that you can store arrays of stuff in a column. 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?

One thing about the array approach is I'm using NHibernate which doesn't
really seem to want to support Postgres arrays without a whole bunch of
custom driver code and IUserTypes and junk, so I'd like to make sure this
architecture is best before I commit to it. Thanks!!

Mike

Let's say you have a table called Threads, and each thread can have zero or
more "tags" associated with it. A tag is just a byte which maps to some
enum somewhere.
There's two ways I can think of to do this. The first would be to have:

create table Threads (
Id uuid not null,
Posted timestamp not null,
Subject varchar(255) not null,
Replies int4 not null,
PosterId uuid not null,
primary key (Id)
);

and a separate table for each tag on a thread:

create table ThreadTags (
Id uuid not null,
Tag int2 not null,
ThreadId uuid not null,
primary key (Id)
)

I can query for all threads with a certain tag using a join:

select * from Threads T
inner join ThreadTags tag ON tag.ThreadId = T.Id AND tag.Tag = 5;

This should work fine. However, recently I was digging through Postgres
manuals and found that you can store arrays of stuff in a column. 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?

One thing about the array approach is I'm using NHibernate which doesn't
really seem to want to support Postgres arrays without a whole bunch of
custom driver code and IUserTypes and junk, so I'd like to make sure this
architecture is best before I commit to it. Thanks!!

Mike

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:737,49fc1d20129743379199738!

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 <
dalroi(at)solfertje(dot)student(dot)utwente(dot)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!
>
>
>

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.