Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

sfpug by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group