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. [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly