Re: index type for indexing long texts

Thank you both for your suggestions.

I think I'll try the GiST approach first since using an existing contrib extension as a starting point seems like a
simpler task for someone like me. :)

Regards,
Aleksander

Richard Troy wrote:
>
>> Aleksander Kmetec <aleksander.kmetec@intera.si> writes:
>>> I'm looking for a solution for indexing long TEXT columns. We're currently using a HASH index, which can handle
most
>>> situations, but every now and then we need support for even longer texts.
>>> One solution would be to create a functional index which would only use the first N chars of mycol, but then we'd
have
>>> to change several hundred occurences of "mycol = someval" with "(mycol = someval AND firstN(mycol) =
firstN(someval))",
>>> as well as update some SQL generators...
>>> That's why I'd be interested to know if there are any index types available which store only the first N chars or
use
>>> some highly compressed form for storing index data, and then recheck any potential hits against the main table. And
if
>>> something like that does not exist yet, how difficult would it be to construct such a solution out of many "spare
parts"
>>> that come with PG?
>
> Try moving where the hash takes place - ie, use your own hash function to
> create the key.
>
> RT
>
>
>