Bruno Wolff III wrote:
> On Tue, Jun 14, 2005 at 08:33:34 -0500,
> Peter Fein <pfein@pobox.com> 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@pobox.com 773-575-0694
Basically, if you're not a utopianist, you're a schmuck. -J. Feldman