Matching on keyword or phrases within a field that is delimited with an "or" operator "|"

From: Jim Ostler <jowenostler(at)yahoo(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Matching on keyword or phrases within a field that is delimited with an "or" operator "|"
Date: 2012-03-12 22:57:29
Message-ID: 1331593049.69491.YahooMailNeo@web124906.mail.ne1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table that is around 20 GB, so I need to optimize as best as possible the matching with another table on keywords across multiple fields. I have around 10 fields that have keywords or phrases delimited with the "or" operator  "|". So it would be in the form of  "a | b  |  and jack  | cd" . There are around 20 keywords or phrases per field, and these keywords could be any word. 

Because of the size of the database suing a "like" match would take too long. I am not sure if tsvector would work, or if there is a way to indicate how you want it parsed? 

If I could index these fields somehow that would be best, but I don't want to do the traditional full text indexing as I only want to match whatever is between the " | " whether it is one word or more.

The original use of this was as it appears, to have the field "a |  b |  c" be read "a or b or c" etc. If there is a way to match using this type of logic with an index that would be great. 

I hope this is clear enough. Thanks for any help as I am fairly new at this so any direction would be helpful.

--Jim

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Carson Gross 2012-03-13 00:01:25 Re: Upgrade questions
Previous Message John R Pierce 2012-03-12 21:42:10 Re: A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?