Diferença entre LIKE e ~ no Postgres

Fui instruído “a não se incomodar com o LIKE ” e use ~ . O que há de errado com o LIKE e como é diferente?

~ Tem um nome neste contexto ou as pessoas dizem “use o operador til”?

~ é o operador de expressão regular e tem as capacidades implícitas por isso. Você pode especificar um intervalo completo de caracteres curinga e quantificadores de expressões regulares; veja a documentação para detalhes. É certamente mais poderoso que o LIKE e deve ser usado quando esse poder é necessário, mas eles servem a propósitos diferentes.

Não há nada de errado com LIKE e, IMO, nenhuma razão para favorecer isso. Pelo contrário, o oposto. LIKE é padrão do SQL. Então é SIMILAR TO , mas não é amplamente suportado. O ~ operator do PostgreSQL (ou operador de correspondência de expressão regular posix ) não é o padrão SQL.

Por essa razão, eu prefiro usar o LIKE onde ele é expressivo o suficiente e eu só uso ~ quando eu preciso do poder de expressões regulares completas. Se eu precisar portar bancos de dados, será uma coisa a menos que vai doer. Eu costumava usar o SIMILAR TO quando o LIKE não é poderoso o suficiente, mas depois dos comentários do Erwin eu acho que vou parar de fazer isso e usar ~ quando o LIKE não faz o trabalho.

Além disso, o PostgreSQL pode usar um índice b-tree para pesquisas de prefixo (por exemplo, LIKE 'TEST%' ) com LIKE ou SIMILAR TO se o database estiver no text_pattern_ops idioma C ou o índice tiver text_pattern_ops . Ao contrário do que escrevi anteriormente, o Pg também pode usar um índice como esse para um regex posix ancorado à esquerda, ele só precisa de um ‘TEST. *’ Explícito para que o regex possa corresponder apenas desde o início. Minha postagem anterior afirmou incorretamente que ~ não poderia usar um índice para uma pesquisa de prefixo. Com essa diferença eliminada, é preciso decidir se você deseja manter os resources compatíveis com o padrão, sempre que possível ou não.

Veja esta demonstração do SQLFiddle ; observe os diferentes planos de execução. Observe a diferença entre ~ '1234.*' e ~ '^1234.*' .

Dado dados de amostra:

 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); 

note que ~ usa um seqscan mesmo quando é substancialmente mais caro (artificialmente devido a enable_seqscan ) porque não tem alternativa, enquanto o LIKE usa o índice. No entanto, um ~ corrigido com uma âncora à esquerda também usa o índice:

 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) 

Existem vários operadores de correspondência de padrões no PostgreSQL . LIKE , SIMILAR TO e ~ são abordados nesta página de manual.

Se você puder, use LIKE ( ~~ ), é mais rápido.
Se você não puder, use uma expressão regular ( ~ ), é mais poderoso.
Nunca usuário SIMILAR TO . É totalmente inútil. Mais abaixo.

A instalação do módulo adicional pg_trgm também torna o operador de similaridade % disponível.

Para completar a imagem, há também uma pesquisa de texto com sua própria infraestrutura.

O suporte ao índice está disponível para cada um desses operadores – em graus variados. Ele regularmente supera o desempenho de outras opções. Mas há muita margem de manobra nos detalhes, mesmo com índices.

Sem pg_trgm, há suporte de índice para padrões de pesquisa ancorados à esquerda . Se o seu cluster de database for executado com um text_pattern_ops idioma diferente de “C”, você precisará de um índice com uma class de operador especial , como text_pattern_ops ou varchar_pattern_ops . E sim, expressões regulares básicas ancoradas à esquerda também são suportadas . Exemplo:

 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.

Com o pg_trgm instalado, você também tem a opção de usar os índices GIN ou GiST com as classs de operadores gist_trgm_ops ou gin_trgm_ops . Esses índices podem suportar qualquer expressão LIKE , não apenas ancorada à esquerda. No entanto, ainda não há suporte para expressões regulares (além das básicas de ancoragem à esquerda). Alexander Korotkov e outros estão trabalhando nisso:

Atualização : Suporte para correspondências arbitrárias de regex foi adicionado no Postgres 9.3 e melhorado várias vezes desde então.

SIMILAR TO faz parte do padrão SQL, mas é uma syntax muito estranha, e a única razão pela qual o PostgreSQL o suporta, é permanecer compatível com o padrão. Internamente, todas as expressões de SIMILAR TO são reescritas com uma expressão regular. Portanto, para qualquer expressão do tipo SIMILAR TO , há pelo menos um regexp fazendo o mesmo trabalho mais rápido . Eu nunca uso o SIMILAR TO . Mais:

  • Correspondência de padrões com LIKE, SIMILAR TO ou expressões regulares no PostgreSQL

O ~~ operador é equivalente a LIKE . ~ , por outro lado, corresponderá usando uma expressão regular POSIX .

Acabei de fazer um benchmark rápido e simples para observar a diferença de desempenho entre os dois operadores quando nenhum índice está envolvido :

 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 

Neste exemplo, o operador LIKE é quase duas vezes mais rápido que o operador ~ . Então, se a velocidade é a essência, eu me inclinaria para o LIKE , embora tenha cuidado para não otimizar prematuramente. ~ dá-lhe muito mais flexibilidade.

Para aqueles de vocês que estão interessados, aqui estão os planos EXPLAIN para as consultas acima:

 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) 

Sim, significa POSIX regex. Outra alternativa é usar a abordagem padrão SQL para expressões regulares com o operador “SIMILAR TO”, embora forneça um conjunto mais limitado de resources, seja mais fácil de entender. Eu acho que esta é uma boa referência da troca de dba: https://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-para-ou-regular-expressions-in-postgresql

Como é apenas uma parte correspondente da seqüência de caracteres no início ou no final ou no meio e inclinação (~) está combinando com regex

Para explicar isso ainda mais vamos criar uma tabela e inserir alguns valores

 # create table users(id serial primary key, name character varying); 

Agora vamos inserir alguns valores na tabela

 # insert into users (name) VALUES ('Alex'), ('Jon Snow'), ('Christopher'), ('Arya'),('Sandip Debnath'), ('Lakshmi'),('alex@gmail.com'),('@sandip5004'), ('lakshmi@gmail.com'); 

Agora sua mesa deve ficar assim

  id | name ----+------------------- 1 | Alex 2 | Jon Snow 3 | Christopher 4 | Arya 5 | Sandip Debnath 6 | Lakshmi 7 | alex@gmail.com 8 | lakshmi@gmail.com 9 | @sandip5004 

Caso GOSTO

 # select * from users where name like 'A%'; id | name ----+------ 1 | Alex 4 | Arya (2 rows) 

Como você pode ver, 'A%' só nos obterá os valores cujo nome começa com o capital A.

 # select * from users where name like '%a%'; id | name ----+------------------- 4 | Arya 5 | Sandip Debnath 6 | Lakshmi 7 | alex@gmail.com 8 | lakshmi@gmail.com 

Como você pode ver, '%a%' só nos dará os valores cujo nome tenha a entre o nome.

 # select * from users where name like '%a'; id | name ----+------ 4 | Arya 

Como você pode ver, '%a' levará apenas os valores cujo nome termina com a .

Caso ~ (inclinação)

 # select * from users where name ~* 't'; id | name ----+---------------- 3 | Christopher 5 | Sandip Debnath 

Como você pode ver, o name ~* 't' somente nos dará os valores cujo nome possui t . ~ significa sensível a maiúsculas e ~ * significa insensível a maiúsculas e minúsculas

 # select * from users where name ~ 'T'; id | name ----+------ (0 rows) 

a consulta acima nos deu 0 linhas, pois T não estava combinando com nenhuma input

Agora vamos considerar um caso em que só precisamos buscar os ids de email e não sabemos o que os ids de email possuem, mas sabemos o padrão do email, ou seja, haverá alguma letra ou número ou _ ou. ou – e, em seguida, @ e mais um pouco de letra ou número ou – então. então com ou in ou org etc e podemos criar o padrão usando a expressão regular.

Agora vamos tentar buscar resultados usando expressões regulares

 # select * from users where name ~* '[a-z0-9\.\-\_]+@[a-z0-9\-]+\.[az]{2,5}'; id | name ----+------------------- 7 | alex@gmail.com 8 | lakshmi@gmail.com 

Da mesma forma podemos buscar alguns nomes que tem um espaço entre

 #select * from users where name ~* '[az]+\s[az]+'; id | name ----+---------------- 2 | Jon Snow 5 | Sandip Debnath 

[az] + significa que pode haver qualquer letra de a para z e + significa que pode ocorrer 1 ou mais vezes e \ s significa que depois haverá um espaço entre e depois novamente um conjunto de letras que podem ocorrer 1 ou mais vezes.

Espero que esta análise detalhada ajude.