| 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: | Whole Thread | Raw Message | 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
| 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 |