Re: Clustered index when not always in the WHERE clause

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

Is the same event_id value shared by more than on club_id? If so, then
clustering won't benefit you at all for event_id queries.

However, note that PostgreSQL does not automatically maintain
clustering, so you'll need to recluster periodically to maintain the
same benefit.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com