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