I am using 8.2; the db contains about 15 tables totaling 150GB.
In the db, I have a 40GB table with approximately 150M rows and 10 fields. One of the fields is a text field containing various kinds of information. I have a function which parses the field and returns an array of only those text items in the field which pass certain criteria ( e.g., length). The length of the array varies from row to row depending up the content of the field. For example, the field might contain "the quick brown fox jumped over the fence"; the returned array might be {"quick", "brown", "jumped", "over", "fence"}.
I need to be able to very quickly find all of the rows in which that field contains, for example, "fence". Another example would be the rows which contain "fence" and "wall". And of course, "fence" or "wall" is another possibility.
I believe creating a hash index on the function would solve this problem.
My questions are 1) does the fact that the arrays are of varying length affect my ability to index the function? 2) if I can do this, can I do it as a partial index? 3) is this a really stupid idea, and is there a much better/easier solution to the problem? :-)