Hi all -
I've got a schema I'm working on modifying, nad I need some help getting
the best performance out. The orginal schema has a many to many linkage
between a couple tables, using a two column linkage table. This is used
to represent groups of people and their relationship to an object
(authors, copyrightholders, maintainers) This worked fine, and, with the
right indixes, is quite zippy. Approximate schems:
table content (
contentid serial,
name text,
<...>
authorgroupid int,
cpholdergroupid int,
maintgroupid int)
table groups (
personid text,
groupid int)
Note that neither grouid nor personid are unique.
Now the users want not just groups, but ordered lists. Well, that's just
fine: we could do it with another column in the groups linkage table,
and some additional logic in the middleware for detecting identical
groups, but it occured to me that PG's array types are just the ticket
for ordered lists like this.
So, by dropping arrays of personids (authors, copyrightholders,
maintainers, ...) into the content table, I can do everything I need.
Only one problem. Retreiving all the content for a particular
person/role is fairly common. Queries of the form:
SELECT * from content c join groups g on c.authorgroupid = g.personid
where personid = 'ross';
work fine and use the index on groups.personid.
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?
I'm using 7.4.3, BTW.
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Research Scientist phone: 713-348-6166
The Connexions Project http://cnx.rice.edu fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
