X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 62C25D1B755 for ; Mon, 26 Jul 2004 01:57:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64011-06 for ; Mon, 26 Jul 2004 04:57:37 +0000 (GMT) Received: from fungible2.mail.rice.edu (fungible2-dmfe1.mail.rice.edu [128.42.59.172]) by svr1.postgresql.org (Postfix) with ESMTP id 261E3D1B1D0 for ; Mon, 26 Jul 2004 01:57:29 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by fungible2.mail.rice.edu (Postfix) with SMTP id 75929199C3 for ; Sun, 25 Jul 2004 23:57:35 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by fungible2.mail.rice.edu (Postfix) with ESMTP id 46DAD1995F for ; Sun, 25 Jul 2004 23:57:35 -0500 (CDT) Received: from fungible2.mail.rice.edu ([127.0.0.1]) by localhost (fungible2.mail.rice.edu [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 28429-06 for ; Sun, 25 Jul 2004 23:57:33 -0500 (CDT) Received: from wallace.cnx.rice.edu (wallace.cnx.rice.edu [128.42.246.68]) by fungible2.mail.rice.edu (Postfix) with ESMTP id 6D38F1994E for ; Sun, 25 Jul 2004 23:57:33 -0500 (CDT) Received: from reedstrm by wallace.cnx.rice.edu with local (Exim 3.36 #1 (Debian)) id 1BoxXy-00045d-00 for ; Sun, 25 Jul 2004 23:57:10 -0500 Date: Sun, 25 Jul 2004 23:57:10 -0500 From: "Ross J. Reedstrom" To: pgsql-performance@postgresql.org Subject: arrays and indexes Message-ID: <20040726045710.GA14892@cnx.rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , pgsql-performance@postgresql.org Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.5.6+20040523i X-Virus-Scanned: by amavis-20030616-p6 at rice.edu X-DCC--Metrics: fungible2.mail.rice.edu 1067; Body=1 Fuz1=1 Fuz2=1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/165 X-Sequence-Number: 7556 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