From: | Naz Gassiep <naz(at)mira(dot)net> |
---|---|
To: | Naz Gassiep <naz(at)mira(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Hash Indexes |
Date: | 2008-01-07 14:49:53 |
Message-ID: | 47823C11.9020706@mira.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> Why are hash indexes "obviously" best? In an ideal world with a good
> implementation maybe, but postgresql b-trees are really quite good.
>
Because doing normal queries on a table where there are large text
blocks is unlikely to be a good idea. E.g.,:
SELECT * FROM table WHERE textcol = 'a 4kb block of text';
> You could always do something like:
>
> CREATE INDEX foo ON table((md5(textcol)));
>
> Then it will get used in queries like:
> SELECT * FROM table WHERE md5(textcol) = md5('text');
>
That's exactly what I was considering doing, however there is always the
change of a hash collision. Yes, this is a very remote chance, however
the ramifications of a collision under those circumstances is
potentially catastrophic. Think a user being delivered text that
contains confidential and sensitive material as opposed to the latest
memo about the cleaning of toilets.
I would assume that hash indexes have inbuilt mechanisms for collision
checking before returning the row as a match. Am I correct in this
assumption?
Best regards,
- Naz.
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Hartmann | 2008-01-07 14:56:49 | Re: Looking for freelancer in Switzerland |
Previous Message | Gregory Stark | 2008-01-07 14:46:19 | Re: Index trouble with 8.3b4 |