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