pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ross J. Reedstrom <reedstrm@rice.edu>
To: pgsql-performance@postgresql.org
Subject: arrays and indexes
Date: Sun, 25 Jul 2004 23:57:10 -0500
Message-ID: <20040726045710.GA14892@cnx.rice.edu> (raw)

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





view thread (8+ messages)  latest in thread

Message-ID: <20040726045710.GA14892@cnx.rice.edu>
Permalink:  ../20040726045710.GA14892@cnx.rice.edu/
Also on:    postgresql.org/message-id/20040726045710.GA14892@cnx.rice.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: reedstrm@rice.edu
  Subject: Re: arrays and indexes
  In-Reply-To: <20040726045710.GA14892@cnx.rice.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox