Clustered index when not always in the WHERE clause

From: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Clustered index when not always in the WHERE clause
Date: 2010-10-29 17:19:18
Message-ID: 4CCB0216.5000806@pukkasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: sfpug


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. 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). This would put all of
the rows with the same club_id together and all of the event_id rows
together which will benefit both queries above, correct?

Originally I thought about clustering only on club_id which would get
similar event_ids close but not perfectly in order. Any reason not to
cluster on the multiple column index?

This table has say 1mm rows but filtering by club_id or event_id will
reduce the results down to less than a couple hundred rows in most cases.

Thanks,

Brian

Responses

Browse sfpug by date

  From Date Subject
Next Message Jeff Davis 2010-10-29 19:46:30 Re: Clustered index when not always in the WHERE clause
Previous Message Joe Brenner 2010-10-23 22:11:48 Re: SFPUG/PGX Happy Hour