Overview of pattern matching operators
LIKE
(~~
) is simple and fast but limited in its capabilities.
ILIKE
(~~*
) the case insensitive variant.
pg_trgm extends index support for both.
~
(regular expression match) is powerful but more complex and may be slow for anything more than basic expressions.
~*
is the case insensitive variant.
SIMILAR TO
is just pointless. A peculiar halfbreed of LIKE
and regular expressions. I never use it. See below.
% is the "similarity" operator, provided by the additional module pg_trgm
. See below.
@@
is the text search operator. See below.
Basics about pattern matching in the manual.
Your query
... is pretty much the optimum. Syntax won't get much shorter, query won't get much faster:
SELECT name
FROM spelers
WHERE name LIKE 'B%' OR name LIKE 'D%'
ORDER BY 1;
If you really want to shorten the syntax, use a regular expression with branches:
...
WHERE name ~ '^(B|D).*'
Or slightly faster, with a character class:
...
WHERE name ~ '^[BD].*'
A quick test without index yields faster results than for SIMILAR TO
in either case for me.
With an appropriate B-Tree index in place, LIKE
wins this race by orders of magnitude.
Index for superior performance
If you are 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");
Requires per-column collation support added with Postgres 9.1.
See:
In DBs running with the "C" locale (not typical), a plain B-tree index does the job.
In older versions (or still today if you insist), 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);
SIMILAR TO
or regular expressions with basic left-anchored expressions can use this index, too. But not with branches (B|D)
or character classes [BD]
(at least in my tests on PostgreSQL 9.0).
Trigram matching
Trigram matches or text search use special GIN or GiST indexes.
Beginning 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.
Details, example and links:
pg_trgm
also provides these operators:
%
- the "similarity" operator
<%
(commutator: %>
) - the "word_similarity" operator in Postgres 9.6 or later
<<%
(commutator: %>>
) - the "strict_word_similarity" operator in Postgres 11 or later
Text search
Is a special type of pattern matching with separate infrastructure and index types. It uses dictionaries and stemming and is a great tool to find words in documents, especially for natural languages.
Prefix matching is also supported:
As well as phrase search since Postgres 9.6:
Consider the introduction in the manual and the overview of operators and functions.
Additional tools for fuzzy string matching
The additional module fuzzystrmatch offers some more options, but performance is generally inferior to all of the above.
In particular, various implementations of the levenshtein()
function may be instrumental.
Why are regular expressions (~
) always faster than SIMILAR TO
?
The answer is simple. SIMILAR TO
expressions are rewritten into regular expressions internally. So, for every SIMILAR TO
expression, there is at least one faster regular expression (that saves the overhead of rewriting the expression). There is no performance gain in using SIMILAR TO
ever.
And simple expressions that can be done with LIKE
(~~
) are faster with LIKE
anyway.
SIMILAR TO
is only supported in PostgreSQL because it ended up in early drafts of the SQL standard. They still haven't gotten rid of it. But there are plans to remove it and include regexp matches instead - or so I heard.
EXPLAIN ANALYZE
reveals it. Just try with any table yourself!
EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO 'B%';
Reveals:
...
Seq Scan on spelers (cost= ...
Filter: (name ~ '^(?:B.*)$'::text)
SIMILAR TO
has been rewritten with a regular expression (~
).
Ultimate performance for this particular case
But EXPLAIN ANALYZE
reveals more. Try, with the afore-mentioned index in place:
EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ '^B.*;
Reveals:
...
-> Bitmap Heap Scan on spelers (cost= ...
Filter: (name ~ '^B.*'::text)
-> Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ...
Index Cond: ((prod ~>=~ 'B'::text) AND (prod ~<~ 'C'::text))
Internally, with an index that is not locale-aware (text_pattern_ops
or using locale C
) simple left-anchored expressions are rewritten with these text pattern operators: ~>=~
, ~<=~
, ~>~
, ~<~
. This is the case for ~
, ~~
or SIMILAR TO
alike.
The same is true for indexes on varchar
types with varchar_pattern_ops
or char
with bpchar_pattern_ops
.
So, applied to the original question, this is the fastest possible way:
SELECT name
FROM spelers
WHERE name ~>=~ 'B' AND name ~<~ 'C'
OR name ~>=~ 'D' AND name ~<~ 'E'
ORDER BY 1;
Of course, if you should happen to search for adjacent initials, you can simplify further:
WHERE name ~>=~ 'B' AND name ~<~ 'D' -- strings starting with B or C
The gain over plain use of ~
or ~~
is tiny. If performance isn't your paramount requirement, you should just stick with the standard operators - arriving at what you already have in the question.
s.name
indexed?name
could be useful here if you care about performance.