Correct me if I'm wrong, but it may be slightly faster to use regular
expressions for this type of application. Instead of doing
(WHERE string LIKE '%MAIN%') you would use (WHERE string ~ 'MAIN'). Or
perhaps the difference in speed would be negligible?
Also, I believe there there would be a good case for including a new type of
index, for strings, which would be some kind of tree structure, based on the
first N characters of the string? Where the first branch of the tree is the
first character, second brand would be the second character, and so on.
Similar to a binary tree I guess. So that searches of type
(WHERE string LIKE 'MAIN%') would benefit greatly on large tables.
Andrew.