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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse sfpug by date

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