Bruno Wolff III wrote: > On Tue, Jun 14, 2005 at 08:33:34 -0500, > Peter Fein <pfein@xxxxxxxxx> wrote: > >>Knowing the specifics of the data I'm putting in sometext, a halfway >>decent hash function would make collisions so rare as to make the chance >>insignificant (and collisions wouldn't break anything anyway). Is this >>approach reasonable, or should I use a hash index on (group_id, >>sometext) - does this suffer from the same size limitation as btrees? I >>thought hash indexes were slow... > > > The hash value should be saved as a separate column. Then it sounds > like you want a partial btree index of (group_id, hash) where the > flag is set. I'm unclear why I'd need to store the hash in a column. I suppose I could have the hash column populated by a trigger on inserts, but this seems to get me the same functionality & is less obvious. For the archives, I did: CREATE UNIQUE INDEX idx_md5_sometext ON mytable USING btree (group_id, md5(sometext)) WHERE group_representative = true; I then basically replicate this in a SELECT on the client side (including calculating the MD5 by the client) to figure out the correct value for group_representative before inserting a new row. This is the only way I use the MD5, so I don't much care about retrieving it in other contexts. -- Peter Fein pfein@xxxxxxxxx 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
