I’m curious about the ability to mark a field under indexes as “Text Pattern“ true on a table and wanted to understand what benefit it provides and if it is more performant?
Text Pattern Indexes support left-bound, case-insensitive matching of text patterns. It is intended to directly support “starts with” text searches/filtering. Full Wildcard search - find a string anywhere within the field - does not benefit from the Text Pattern index currently, nor “ends with” text searches. The Text Pattern index can also support equalities.
Performance somewhat depends on data distribution and search term, but generally does improve appreciably for “starts with” type filtering on widely varied data.
3 Likes