From: | Josh Berkus <josh(at)agliodbs(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 20:36:45 |
Message-ID: | 4CCB305D.1080405@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Ghidinelli | 2010-10-29 21:05:36 | Re: Clustered index when not always in the WHERE clause |
Previous Message | Jeff Davis | 2010-10-29 19:46:30 | Re: Clustered index when not always in the WHERE clause |