Разница между LIKE и ~ в Postgres

Мне было дано указание "не беспокоиться с LIKE" и вместо этого используйте ~. Что не так с LIKE и чем отличается ~?

Имеет ли ~ имя в этом контексте или люди говорят "использовать оператор тильды"?

Ответ 1

~ является оператором регулярного выражения и имеет возможности, связанные с этим. Вы можете указать полный диапазон подстановочных знаков и квантификаторов регулярного выражения; см. Раздел 9.7.3 документации. Это, безусловно, более мощное, чем LIKE, и должно использоваться, когда эта мощность необходима, но они служат различным целям.

Ответ 2

Нет ничего плохого в LIKE и, IMO, нет причин одобрять ~ над ним. Скорее наоборот. LIKE является стандартом SQL. Так что SIMILAR TO, но он не поддерживается широко. PostgreSQL ~ operator (или оператор сопоставления регулярных выражений posix) не является стандартом SQL.

По этой причине я предпочитаю использовать LIKE, где он достаточно экспрессивный, и я использую только ~, когда мне нужна сила полных регулярных выражений. Если мне когда-нибудь понадобится порт баз данных, это будет меньше всего, что будет больно. Я обычно использовал SIMILAR TO, когда LIKE недостаточно мощный, но после комментариев Erwin я думаю, что перестану это делать и использую ~, когда LIKE не выполняет эту работу.

Кроме того, PostgreSQL может использовать индекс b-дерева для поиска префикса (например, LIKE 'TEST%') с помощью LIKE или SIMILAR TO, если база данных находится в локали C или индекс имеет text_pattern_ops. Вопреки тому, что я написал ранее, Pg также может использовать такой индекс для левого привязанного rexx posix, ему просто нужен явный '^ TEST. *', Поэтому регулярное выражение может только совпадать с самого начала. В моем сообщении ранее неверно указано, что ~ не может использовать индекс для поиска префикса. С той разницей, что это действительно исключено, нужно ли придерживаться стандартных совместимых функций там, где это возможно или нет.

Смотрите эту демонстрацию SQLFiddle; обратите внимание на различные планы выполнения. Обратите внимание на разницу между ~ '1234.*' и ~ '^1234.*'.

Данные образца:

create table test (
   blah text
);
insert into test (blah)  select x::text from generate_series(1,10000) x;
create index test_blah_txtpat_idx ON test(blah text_pattern_ops);

обратите внимание, что ~ использует seqscan, даже если он значительно дороже (это из-за enable_seqscan), потому что у него нет альтернативы, а LIKE использует индекс. Однако исправленный ~ с левым якорем также использует индекс:

regress=# SET enable_seqscan = 'f';
SET
regress=# explain select 1 from test where blah ~ '12.*';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Seq Scan on test  (cost=10000000000.00..10000000118.69 rows=2122 width=0)
   Filter: (blah ~ '12.*'::text)
(2 rows)
regress=# explain select 1 from test where blah like '12%';
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=4.55..46.76 rows=29 width=0)
   Filter: (blah ~~ '12%'::text)
   ->  Bitmap Index Scan on test_blah_txtpat_idx  (cost=0.00..4.54 rows=29 width=0)
         Index Cond: ((blah ~>=~ '12'::text) AND (blah ~<~ '13'::text))
(4 rows)
regress=# explain select 1 from test where blah ~ '^12.*';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=5.28..51.53 rows=101 width=0)
   Filter: (blah ~ '^12.*'::text)
   ->  Bitmap Index Scan on test_blah_txtpat_idx  (cost=0.00..5.25 rows=100 width=0)
         Index Cond: ((blah ~>=~ '12'::text) AND (blah ~<~ '13'::text))
(4 rows)

Ответ 3

В PostgreSQL существует ряд операторов сопоставления шаблонов. LIKE, SIMILAR TO и ~ описаны на этой странице руководства.

Если вы можете, используйте LIKE (~~), это самый быстрый.
Если вы не можете, используйте регулярное выражение (~), оно более мощное.
Никогда пользователь ~~SIMILAR TO~~. Это совершенно бессмысленно. Еще ниже.

Установка дополнительного модуля pg_trgm также делает оператор подобия %.

Чтобы сделать изображение полным, есть также текстовый поиск с его собственной инфраструктурой.

Поддержка индексов доступна для каждого из этих операторов - в разной степени. Он регулярно превосходит производительность других опций. Но в деталях есть много возможностей, даже с индексами.

Без pg_trgm существует поддержка индексов для левых привязанных шаблонов поиска. Если ваш кластер баз данных работает с языковой версией "C", вам нужен индекс со специальным классом оператора, например text_pattern_ops или varchar_pattern_ops. И да, поддерживаются основные левые привязанные регулярные выражения. Пример:

CREATE TABLE tbl(string text);

INSERT INTO  tbl(string)
SELECT x::text FROM generate_series(1, 10000) x;

CREATE INDEX tbl_string_text_pattern_idx ON tbl(string text_pattern_ops);

SELECT * FROM tbl WHERE string ~ '^1234';  -- left anchored pattern

SQL Fiddle.

Установив pg_trgm, вы также можете использовать индексы GIN или GiST с классами операторов gist_trgm_ops или gin_trgm_ops. Эти индексы могут поддерживать любое выражение LIKE, а не только привязанные слева. Однако пока нет поддержки регулярных выражений (кроме базовых лево-привязанных). Александр Коротков и другие работают над этим:

Обновление. Поддержка произвольных совпадений регулярных выражений была добавлена в Postgres 9.3 и улучшена несколько раз с тех пор.

SIMILAR TO является частью стандарта SQL, но это очень странный синтаксис, и единственная причина, по которой PostgreSQL поддерживает его, заключается в том, чтобы оставаться стандартным. Внутренне каждое выражение SIMILAR TO переписывается с регулярным выражением. Поэтому для любого заданного выражения SIMILAR TO существует по крайней мере одно регулярное выражение, выполняющее ту же работу быстрее. Я никогда не использую SIMILAR TO. Подробнее:

Сопоставление шаблонов с LIKE, ПОДОБНЫМИ или регулярными выражениями в PostgreSQL

Ответ 4

Оператор ~~ эквивалентен LIKE. ~, с другой стороны, будет соответствовать используя регулярное выражение POSIX.

Ответ 5

Я просто сделал быстрый и простой тест, чтобы посмотреть на разницу в производительности между двумя операторами, когда не задействованы никакие индексы:

postgres=# \timing
Timing is on.
postgres=# SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text LIKE '%5%') AS x;
  count
─────────
 5217031
(1 row)

Time: 5631.662 ms
postgres=# SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text ~ '5') AS x;
  count
─────────
 5217031
(1 row)

Time: 10612.406 ms

В этом примере оператор LIKE почти в два раза быстрее, чем оператор ~. Поэтому, если скорость имеет смысл, я бы наклонился к LIKE, хотя будьте осторожны, чтобы не доработать преждевременно. ~ дает вам большую гибкость.

Для тех из вас, кто заинтересован, вот EXPLAIN планы для вышеуказанных запросов:

postgres=# EXPLAIN ANALYZE SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text LIKE '%5%') AS x;
                                                              QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Aggregate  (cost=20.00..20.01 rows=1 width=0) (actual time=9967.748..9967.749 rows=1 loops=1)
   ->  Function Scan on generate_series x  (cost=0.00..17.50 rows=1000 width=0) (actual time=1732.084..7404.755 rows=5217031 loops=1)
         Filter: ((val)::text ~~ '%5%'::text)
         Rows Removed by Filter: 4782969
 Total runtime: 9997.587 ms
(5 rows)

postgres=# EXPLAIN ANALYZE SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text ~ '5') AS x;
                                                              QUERY PLAN
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Aggregate  (cost=20.00..20.01 rows=1 width=0) (actual time=15118.061..15118.061 rows=1 loops=1)
   ->  Function Scan on generate_series x  (cost=0.00..17.50 rows=1000 width=0) (actual time=1724.591..12516.996 rows=5217031 loops=1)
         Filter: ((val)::text ~ '5'::text)
         Rows Removed by Filter: 4782969
 Total runtime: 15147.950 ms
(5 rows)

Ответ 6

Да, это означает регулярное выражение POSIX. Другой альтернативой является использование стандартного подхода SQL к регулярным выражениям с оператором "SIMILAR TO", хотя он предоставляет более ограниченный набор функций, может быть проще понять. Я думаю, что это хорошая ссылка из обмена dba: https://dba.stackexchange.com/info/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql