Skip site navigation (1) Skip section navigation (2)

Re: Clustered index when not always in the WHERE clause

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
Cc: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Clustered index when not always in the WHERE clause
Date: 2010-10-29 19:46:30
Message-ID: 1288381590.352.27.camel@jdavis-ux.asterdata.local (view raw or flat)
Thread:
Lists: sfpug
On Fri, 2010-10-29 at 10:19 -0700, Brian Ghidinelli wrote:
> Question about clustered indices.  I have a table like:
> 
> id
> club_id
> event_id
> type
> ...
> 
> Clubs are 1-n to events in my system.  In this particular table there 
> are times when the event_id will be null.

If I understand correctly:
 - an event is only associated with one club (event_id determines
   club_id)
 - the combination of event and type is unique, but event is not
   unique by itself (is this true?)

This means that your table is not normalized, and I recommend that you
consider a design like:

  club_event ( club_id, event_id, ..., unique (event_id) );
  event_type ( event_id, type, ..., unique(event_id, type) );

> I'll be running two queries:
> 
> WHERE club_id = 'a' AND type = y
> WHERE event_id = 'b' AND type = z
> 
> If I understand the value of clustering correctly, it would be best to 
> have a clustered index on (club_id, event_id).

Perhaps, and you'd also want an (unclustered) index on event_id because
the second query doesn't query on the club_id.

If you choose my design you'd probably want to use a clustered index on
(event_type.event_id, event_type.type), and an unclustered index on
club_event.club_id.

Of course, there are quite a few factors. When choosing a table design
and indexing scheme, you really need to be pretty specific about the
constraints, or else it's difficult to make good choices.

Regards,
	Jeff Davis


In response to

sfpug by date

Next:From: Josh BerkusDate: 2010-10-29 20:36:45
Subject: Re: Clustered index when not always in the WHERE clause
Previous:From: Brian GhidinelliDate: 2010-10-29 17:19:18
Subject: Clustered index when not always in the WHERE clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group