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

Sua consulta é praticamente a melhor. A sintaxe não ficará muito mais curta, a consulta não ficará muito mais rápida:

SELECT name FROM spelers WHERE name LIKE "B%" OR name LIKE "D%" ORDER BY 1; 

Se y você realmente deseja encurtar a sintaxe , use uma expressão regular com branches :

... WHERE name ~ "^(B|D).*" 

Ou ligeiramente mais rápido, com uma classe de caracteres :

... WHERE name ~ "^[BD].*" 

Um teste rápido sem índice produz resultados mais rápidos do que para SIMILAR TO em ambos os casos para mim.
Com um índice B-Tree apropriado no lugar, LIKE vence esta corrida por ordens de magnitude.

Leia o básico sobre correspondência de padrões no manual .

Índice para desempenho superior

Se você estiver preocupado com desempenho, crie um índice como este para tabelas maiores:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Torna esse tipo de consulta mais rápido em ordens de magnitude. Considerações especiais se aplicam à ordem de classificação específica da localidade. Leia mais sobre classes de operadores no manual . Se você estiver usando a localidade ” C ” padrão (a maioria das pessoas não), um índice simples (com classe de operador padrão) irá fazer.

Esse índice só é bom para padrões ancorados à esquerda (correspondência desde o início da string).

SIMILAR TO ou expressões regulares com expressões ancoradas à esquerda básicas também podem usar este índice. Mas não com ramificações (B|D) ou classes de caracteres [BD] (pelo menos em meus testes no PostgreSQL 9.0).

As correspondências de trigramas ou pesquisa de texto usam índices especiais GIN ou GiST.

Visão geral dos operadores de correspondência de padrões

• LIKE ( ~~ ) é simples e rápido, mas limitado em seus recursos.
  ILIKE ( ~~* ) a variante não diferencia maiúsculas de minúsculas.
  pg_trgm estende o suporte de índice para ambos. p>

• ~ (correspondência de expressão regular) é poderoso, mas mais complexo e pode ser lento para qualquer coisa além de expressões básicas.

• SIMILAR TO é apenas inútil . Um mestiço peculiar de LIKE e expressões regulares. Eu nunca uso isso. Veja abaixo.

• % é o operador de ” similaridade “, fornecido pelo módulo adicional pg_trgm. Veja abaixo.

• @@ é o operador de pesquisa de texto. Veja abaixo.

pg_trgm – correspondência de trigrama

Começando com PostgreSQL 9.1 você pode facilitar a extensão pg_trgm para fornecer suporte de índice para qualquer LIKE / ILIKE padrão (e padrões regexp simples com ~) usando um Índice GIN ou GiST.

Detalhes, exemplo e links:

• Como o LIKE é implementado?

pg_trgm também fornece estes operadores :

• % – o ” similaridade ” operador
• <% (comutador: %>) – a ” word_similarity ” no Postgres 9.6 ou posterior
• <<% (comutador: %>>) – o ” strict_word_similarity ” operador no Postgres 11 ou posterior

Pesquisa de texto

É um tipo especial de correspondência de padrões com infraestrutura e tipos de índice separados. Ele usa dicionários e lematização e é uma ótima ferramenta para encontrar palavras em documentos, especialmente para línguas naturais.

Correspondência de prefixo também é compatível:

• Obtenha correspondência parcial da coluna TSVECTOR indexada por GIN

Bem como pesquisa de frase desde Postgres 9.6:

• Como pesquisar palavras hifenizadas na pesquisa de texto completo PostgreSQL?

Considere o introdução no manual e a visão geral dos operadores e funções .

Ferramentas adicionais para correspondência de string difusa

O módulo adicional fuzzystrmatch oferece mais algumas opções, mas o desempenho é geralmente inferior a todos os anteriores.

Em particular, vários implementações da função levenshtein() podem ser instrumentais.

Por que as expressões regulares (~) são sempre mais rápidas do que SIMILAR TO?

A resposta é simples. SIMILAR TO expressões são reescritas em expressões regulares internamente. Portanto, para cada SIMILAR TO expressão, há pelo menos uma expressão regular mais rápida (que economiza a sobrecarga de reescrever a expressão). Não há ganho de desempenho ao usar SIMILAR TO nunca .

E expressões simples que podem ser feitas com LIKE (~~) são mais rápidas com LIKE de qualquer maneira.

SIMILAR TO só é suportado no PostgreSQL porque acabou nos primeiros rascunhos do padrão SQL. Eles ainda não se livraram dele. Mas há planos para removê-lo e incluir correspondências de regexp – ou foi o que ouvi.

EXPLAIN ANALYZE o revela. Experimente com qualquer mesa você mesmo!

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO "B%"; 

Revela:

... Seq Scan on spelers (cost= ... Filter: (name ~ "^(?:B.*)$"::text) 

SIMILAR TO foi reescrito com uma expressão regular (~).

Desempenho final para este caso específico

Mas EXPLAIN ANALYZE revela mais. Tente, com o índice mencionado anteriormente em vigor:

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ "^B.*; 

Revela:

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

Internamente, com um índice que não reconhece a localidade (text_pattern_ops ou usando a localidade C) expressões simples ancoradas à esquerda são reescritas com estes operadores de padrão de texto: ~>=~, ~<=~, ~>~, ~<~. Este é o caso de ~, ~~ ou SIMILAR TO semelhantes.

O mesmo é verdadeiro para índices nos tipos varchar com varchar_pattern_ops ou char com bpchar_pattern_ops.

Então, aplicado para a pergunta original, esta é a maneira mais rápida possível :

SELECT name FROM spelers WHERE name ~>=~ "B" AND name ~<~ "C" OR name ~>=~ "D" AND name ~<~ "E" ORDER BY 1; 

Claro, se por acaso você pesquisar iniciais adjacentes , poderá simplificar ainda mais:

WHERE name ~>=~ "B" AND name ~<~ "D" -- strings starting with B or C 

O ganho sobre o uso simples de ~ ou ~~ é mínimo. Se o desempenho não for seu requisito primordial, você deve apenas se limitar aos operadores padrão – chegando ao que você já tem em questão.

Comentários

• O OP não ‘ não tem um índice no nome, mas você sabe, se tivesse, a consulta original envolveria 2 buscas de intervalo e similar uma varredura?
• @MartinSmith: Um teste rápido com EXPLAIN ANALYZE mostra 2 varreduras de índice de bitmap.Múltiplas varreduras de índice de bitmap podem ser combinadas rapidamente.
• Obrigado. Portanto, haveria qualquer milha em substituir o OR por UNION ALL ou substituir name LIKE 'B%' por name >= 'B' AND name <'C' no Postgres?
• @MartinSmith: UNION ganhou ‘ t mas, sim, combinar os intervalos em uma cláusula WHERE irá acelerar a consulta. Eu adicionei mais à minha resposta. Claro, você deve levar em consideração sua localidade. A pesquisa com reconhecimento de local é sempre mais lenta.
• @a_horse_with_no_name: Espero que não. Os novos recursos de pg_tgrm com índices GIN são um deleite para pesquisa de texto genérico. Uma pesquisa ancorada no início já é mais rápida do que isso.

Que tal adicionar uma coluna ao tabela. Dependendo de seus requisitos reais:

person_name_start_with_B_or_D (Boolean) person_name_start_with_char CHAR(1) person_name_start_with VARCHAR(30) 

PostgreSQL não suporta colunas computadas em tabelas base a la SQL Server , mas a nova coluna pode ser mantida via gatilho. Obviamente, esta nova coluna seria indexada.

Como alternativa, um índice em uma expressão lhe daria o mesmo, mais barato. Por exemplo:

CREATE INDEX spelers_name_initial_idx ON spelers (left(name, 1)); 

Consultas que correspondem à expressão em suas condições podem utilizar este índice.

Dessa forma, o impacto do desempenho é obtido quando os dados são criados ou corrigidos, portanto, pode ser apropriado apenas para um ambiente de baixa atividade (ou seja, muito menos gravações do que leituras).

Você poderia tentar

SELECT s.name FROM spelers s WHERE s.name SIMILAR TO "(B|D)%" ORDER BY s.name 

No entanto, não tenho ideia se a expressão acima ou sua original são sargáveis no Postgres.

Se você criar o índice sugerido, também gostaria de saber isso se compara com as outras opções.

SELECT name FROM spelers WHERE name >= "B" AND name < "C" UNION ALL SELECT name FROM spelers WHERE name >= "D" AND name < "E" ORDER BY name 

Comentários

• Funcionou e tenho um custo de 1,19 onde eu tinha 1,25. Obrigado!

O que fiz no passado, diante de um problema de desempenho semelhante, foi incremente o caractere ASCII da última letra e faça um BETWEEN. Você obtém o melhor desempenho para um subconjunto da funcionalidade LIKE. Claro, isso só funciona em certas situações, mas para conjuntos de dados muito grandes em que você está pesquisando um nome, por exemplo, faz o desempenho ir de péssimo para aceitável.

Pergunta muito antiga, mas encontrei outra solução rápida para este problema:

SELECT s.name FROM spelers s WHERE ascii(s.name) in (ascii("B"),ascii("D")) ORDER BY 1 

Visto que a função ascii ( ) olha apenas para o primeiro caractere da string.

Comentários

• Isso usa um índice em (name)?

Para verificar as iniciais, costumo usar fundição para "char" (com aspas duplas). Não é portátil, mas muito rápido. Internamente, ele simplesmente desmonta o texto e retorna o primeiro caractere, e as operações de comparação “char” são muito rápidas porque o tipo tem comprimento fixo de 1 byte:

SELECT s.name FROM spelers s WHERE s.name::"char" =ANY( ARRAY[ "char" "B", "D" ] ) ORDER BY 1 

Observe que a transmissão para "char" é mais rápida do que a ascii() slution de @ Sole021, mas não é compatível com UTF8 (ou qualquer outra codificação para esse assunto), retornando simplesmente o primeiro byte, então só deve ser usado em casos onde a comparação é contra caracteres ASCII de 7 bits antigos simples.

Existem dois métodos não mencionados ainda para lidar com esses casos:

1. índice parcial (ou particionado – se criado manualmente para todo o alcance) – mais útil quando apenas um subconjunto de dados é necessário (por exemplo, durante alguma manutenção ou temporário para alguns relatórios):

   CREATE INDEX ON spelers WHERE name LIKE "B%" 

2. particionando a própria tabela (usando o primeiro caractere como chave de particionamento) – esta técnica é especialmente wort h considerando no PostgreSQL 10+ (particionamento menos doloroso) e 11+ (remoção de partição durante a execução da consulta).

Além disso, se os dados em uma tabela são classificados, pode-se beneficie de usar o índice BRIN (sobre o primeiro caractere).