Re: arrays and indexes

"Ross J. Reedstrom" <reedstrm(at)rice(dot)edu> writes:

> In the new schema, the same thing is:
>
> SELECT * from content where 42 = ANY (authors);
>
> Works fine, but for the life of me I can't find nor figure out how to
> build an index that will be used to speed this along. Any ideas?

Well that's basically the problem with denormalized data like this.

Have you resolved what you're going to do if two sessions try to add a user to
the same group at the same time? Or how you'll go about removing a user from
all his groups in one shot?

Basically, if you denormalize in this fashion it becomes hard to use the
groups as anything but single monolithic objects. Whereas normalized data can
be queried and updated from other points of view like in the case you name
above.

Postgres does have a way to do what you ask, though. It involves GiST indexes
and the operators from the contrib/intarray directory from the Postgres
source.

However I warn you in advance that this is fairly esoteric stuff and will take
some time to get used to. And at least in my case I found the indexes didn't
actually help much for my data sets, probably because they just weren't big
enough to benefit.

--
greg