SIMILAR TO expressions are rewritten into regular expressions internally. Why are regular expressions ( ~) always faster than SIMILAR TO? In particular, various implementations of the levenshtein() function may be instrumental. The additional module fuzzystrmatch offers some more options, but performance is generally inferior to all of the above. Additional tools for fuzzy string matching How to search hyphenated words in PostgreSQL full text search?Ĭonsider the introduction in the manual and the overview of operators and functions.Get partial match from GIN indexed TSVECTOR columnĪs well as phrase search since Postgres 9.6:.It uses dictionaries and stemming and is a great tool to find words in documents, especially for natural languages. Is a special type of pattern matching with separate infrastructure and index types. >) - the 'strict_word_similarity' operator in Postgres 11 or later.) - the 'word_similarity' operator in Postgres 9.6 or later.Pg_trgm provides additional operators like: Trigram matches or text search use special GIN or GiST indexes.īeginning with Postgres 9.1 you can install the additional module pg_trgm to provide index support for any LIKE / ILIKE pattern (and simple regexp patterns with ~ / ~*) using a GIN or GiST index. And no index currently supports LIKE ANY. But not with branches (B|D) or character classes. SIMILAR TO or regular expressions with basic left-anchored expressions can use this index, too. In older versions (or still today), you can use the special operator class text_pattern_ops for the same purpose: CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops) In DBs running with the 'C' locale (not typical), a plain B-tree index does the job. Is there a difference between text_pattern_ops and COLLATE 'C'?.Requires per-column collation support added with Postgres 9.1. If concerned with performance, create an index like this for bigger tables to support left-anchored search patterns (matching from the start of the string): CREATE INDEX spelers_name_special_idx ON spelers (name COLLATE 'C') In Postgres 15, the first variant can also use a B-tree index using COLLATE 'C'. In Postgres 11 or later the new is more convenient as we can use the unadorned prefix directly - and fast when supported with an SP-GiST index. WHERE name ~ '^.*'įor bigger tables, index support improves performance by orders of magnitude. Or a character class (only for the case with a single character).

WHERE name LIKE ANY ('')Ī regular expression with branches shortens the syntax some more. WHERE name ~ '^B' OR name LIKE '^D'Ī bit shorter, but can't use an index. Syntax won't get much shorter, query won't get much faster: SELECT name FROM spelers % is the 'similarity' operator, provided by the additional module pg_trgm. Since Postgres 15 also a B-tree index using a 'C' collation. Related is 'starts with' operator (for prefix matching), equivalent to the starts_with() function.Īdded with Postgres 11, can use an SP-GiST index. See below.īasics about pattern matching in the manual.

See below.įor left-anchored patterns also a B-tree index using COLLATE 'C' or the operator class text_pattern_ops. See below.Īll of the above can use a trigram index. A peculiar blend of LIKE and regular expressions. ~ (regular expression match) is powerful but more complex and may be slow for anything more than basic expressions.

ILIKE ( ~~*) the case insensitive variant. LIKE ( ~~) is simple and fast but limited in its capabilities.

Difference between like and ilike