I notice there are several modules to create specialized indices in
PostgreSQL for searching VARCHAR data. For example, fuzzy, trigram, full
text, etc.
I've been googling around but I can't find the optimal method (reasonable
speed and size, simplicity) for my use case.
My text searches will always be like the following. User specifies a word
(e.g. "John") and I have a field called "FullName" that could return
records with "John Doe", "Robert Johnson", "Joe Johnson Smith", etc. I may
also extend the search criteria to other fields. So for example the query
would always look like this:
SELECT * FROM MyTable WHERE upper(FullName) LIKE upper('%John%');
So you see it is also case insensitive. Pretty simple/standard stuff.
That said, which would be the best extension module to use? A "gist" index
on the uppercased column? Or something else? Thanks!
