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

Operadores de correspondência de padrões

• LIKE( ~~) é simples e rápido, mas limitado em suas capacidades.
  ILIKE( ~~*) a variante que não diferencia maiúsculas de minúsculas.

• ~(correspondência de expressão regular) é poderoso, mas mais complexo e pode ser lento para qualquer coisa além de expressões básicas.
  ~*é a variante que não diferencia maiúsculas de minúsculas.

• SIMILAR TOé simplesmente inútil . Uma mistura peculiar de LIKEexpressões regulares. Eu nunca uso. Veja abaixo.

Todos os itens acima podem usar um índice de trigrama. Veja abaixo.
Para padrões ancorados à esquerda, também um índice de árvore B usando COLLATE "C"ou a classe de operador text_pattern_ops. Veja abaixo.

Noções básicas sobre correspondência de padrões no manual .

Operadores relacionados

• ^@é o operador "começa com" (para correspondência de prefixo), equivalente à starts_with()função.
  Adicionado com o Postgres 11, pode usar um índice SP-GiST . Desde o Postgres 15 também um índice de árvore B usando um agrupamento "C". Veja abaixo.

• % é o operador de "semelhança", fornecido pelo módulo adicionalpg_trgm. Veja abaixo.

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

Sua pergunta

... é praticamente o ideal. 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;

Ou equivalente (um pouco mais caro):

... WHERE name ~ '^B' OR name LIKE '^D'

Um pouco mais curto, mas não pode usar um índice:

... WHERE name LIKE ANY ('{B%,D%}')
... WHERE name ~ ANY ('{^B,^D}')

Uma expressão regular com ramificações encurta um pouco mais a sintaxe:

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

Ou uma classe de caracteres (somente para o caso com um único caractere):

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

Para tabelas maiores, o suporte a índices melhora o desempenho em ordens de magnitude.

No Postgres 11 ou posterior, o novo ^@é mais conveniente, pois podemos usar o prefixo sem adornos diretamente - e rápido quando suportado com um índice SP-GiST:

... WHERE name ^@ 'B' OR name ^@ 'D'

Ou:

... WHERE name ^@ ANY ('{B,D}')

No Postgres 15, a primeira variante também pode usar um índice de árvore B usando COLLATE "C".

db<>fique aqui

Índice

Se estiver preocupado com o desempenho, crie um índice como este para tabelas maiores para oferecer suporte a padrões de pesquisa ancorados à esquerda (correspondendo desde o início da string):

CREATE INDEX spelers_name_special_idx ON spelers (name COLLATE "C");

Requer suporte de agrupamento por coluna adicionado ao Postgres 9.1 .

Ver:

• Existe uma diferença entre text_pattern_ops e COLLATE "C"?
• O índice B-tree não parece ser usado?

Em bancos de dados executados com a localidade "C" (não típico), um índice de árvore B simples faz o trabalho.

Em versões mais antigas (ou ainda hoje), você pode usar a classe de operador especialtext_pattern_ops para o mesmo propósito:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

SIMILAR TOou expressões regulares com expressões básicas ancoradas à esquerda também podem usar esse índice. Mas não com ramificações (B|D)ou classes de caracteres [BD]. E nenhum índice atualmente suporta LIKE ANY.

Correspondência de trigramas

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

A partir do Postgres 9.1 , você pode instalar o módulo adicionalpg_trgm para fornecer suporte de índice para qualquer padrão LIKE/ ILIKE(e padrões regexp simples com ~/ ~*) usando um índice GIN ou GiST.

Detalhes, exemplo e links:

• Como o LIKE é implementado?

pg_trgmfornece operadores adicionais como:

• %- o operador "semelhança"
• <%(comutador: %>) - o operador "word_similarity" no Postgres 9.6 ou posterior
• <<%(comutador: %>>) - o operador "strict_word_similarity" 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 idiomas naturais.

A correspondência de prefixo também é suportada:

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

Assim como a pesquisa de frase desde o Postgres 9.6:

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

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

Ferramentas adicionais para correspondência de strings difusas

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

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

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

SIMILAR TOexpressões são reescritas em expressões regulares internamente. Para cada SIMILAR TOexpressão, há pelo menos uma expressão regular mais rápida (economizando a sobrecarga de reescrever a expressão). Não há ganho de desempenho em usar SIMILAR TO ever .

Expressões simples que podem se contentar com LIKE( ~~) são mais rápidas de LIKEqualquer maneira.

SIMILAR TOé suportado apenas 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 regexp - ou assim ouvi.

EXPLAIN ANALYZEo revela. Basta tentar 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 TOfoi reescrito com uma expressão regular ( ~).

Que tal adicionar uma coluna à tabela. Dependendo de suas necessidades 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 como SQL Server , mas a nova coluna pode ser mantida via trigger. Obviamente, essa nova coluna seria indexada.

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

As consultas que correspondem à expressão em suas condições podem utilizar esse índice.

Dessa forma, o impacto no desempenho é obtido quando os dados são criados ou alterados, 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 idéia se o acima ou sua expressão original são sargáveis ​​​​no Postgres.

Se você criar o índice sugerido também estaria interessado em saber como 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

O que eu fiz no passado, diante de um problema de desempenho semelhante, foi incrementar o caractere ASCII da última letra e fazer um BETWEEN. Você então obtém o melhor desempenho para um subconjunto da funcionalidade LIKE. Claro, ele só funciona em certas situações, mas para conjuntos de dados ultragrandes em que você está pesquisando um nome, por exemplo, faz com que o desempenho passe de péssimo para aceitável.

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

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

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

Para verificar as iniciais, costumo usar casting to "char"(com aspas duplas). Não é portátil, mas muito rápido. Internamente, ele simplesmente remove o texto e retorna o primeiro caractere, e as operações de comparação "char" são muito rápidas porque o tipo é de 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 conversão para "char"é mais rápida que a ascii()slution por @Sole021, mas não é compatível com UTF8 (ou qualquer outra codificação para esse assunto), retornando simplesmente o primeiro byte, portanto, deve ser usado apenas nos casos em que a comparação é com o antigo 7 simples caracteres ASCII de -bit.

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

1. índice parcial (ou particionado - se criado para intervalo completo manualmente) - 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. particionar a própria tabela (usando o primeiro caractere como chave de particionamento) - vale a pena considerar esta técnica especialmente 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 beneficiar do uso do índice BRIN (sobre o primeiro caractere).

Provavelmente mais rápido para fazer uma comparação de caractere único:

SUBSTR(s.name,1,1)='B' OR SUBSTR(s.name,1,1)='D'