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
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 |