Hash index on function which returns varying length arrays

From: "David Monarchi" <david(dot)e(dot)monarchi(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Hash index on function which returns varying length arrays
Date: 2007-03-02 19:49:00
Message-ID: eea51fdb0703021149y42a61101g9bdd6014c8520d1f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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? :-)

TIA,
David

Browse pgsql-novice by date

  From Date Subject
Next Message Jan Danielsson 2007-03-03 02:04:02 Average over time
Previous Message Abu Mushayeed 2007-03-01 20:06:17 Please need help