pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Ross J. Reedstrom <reedstrm@rice.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: arrays and indexes
Date: 26 Jul 2004 02:27:20 -0400
Message-ID: <87acxnwaev.fsf@stark.xeocode.com> (raw)
In-Reply-To: <20040726045710.GA14892@cnx.rice.edu>
References: <20040726045710.GA14892@cnx.rice.edu>


"Ross J. Reedstrom" <reedstrm@rice.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




view thread (8+ messages)  latest in thread

Message-ID: <87acxnwaev.fsf@stark.xeocode.com>
Permalink:  ../87acxnwaev.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87acxnwaev.fsf@stark.xeocode.com

 · 

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: gsstark@mit.edu, reedstrm@rice.edu
  Subject: Re: arrays and indexes
  In-Reply-To: <87acxnwaev.fsf@stark.xeocode.com>

* 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