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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
