From: | Aleksander Kmetec <aleksander(dot)kmetec(at)intera(dot)si> |
---|---|
To: | Richard Troy <rtroy(at)ScienceTools(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: index type for indexing long texts |
Date: | 2007-01-15 14:40:46 |
Message-ID: | 45AB926E.8090302@intera.si |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)kmetec(at)intera(dot)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
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Shane Ambler | 2007-01-15 15:10:06 | Re: Performance with very large tables |
Previous Message | dparent | 2007-01-15 14:11:02 | Runtime error when calling function from .NET ( Function returns record) |