Como usar consulta fuzzy no RDS - ApsaraDB RDS: Central de Documentação do Alibaba Cloud

Quando usar o pg_bigm

Tanto o pg_bigm quanto o pg_trgm são extensões de busca de texto completo para o RDS for PostgreSQL. Consulte a tabela abaixo para escolher a opção mais adequada.

Recurso	pg_trgm	pg_bigm
Método de correspondência de frases	3-gramas	2-gramas
Tipos de índice suportados	GIN e GiST	GIN
Operadores de busca de texto completo suportados	`LIKE`, `ILIKE`, `~` e `~*`	`LIKE`
Busca de texto completo para idiomas não alfabéticos	Não suportado	Suportado
Busca de texto completo para palavras-chave com 1–2 caracteres	Lenta	Rápida
Busca por similaridade	Suportada	Suportada
Tamanho máximo de uma coluna indexável	238.609.291 bytes (~228 MB)	107.374.180 bytes (~102 MB)

Use o pg_bigm quando seus dados incluírem conteúdo não alfabético ou quando você buscar frequentemente palavras-chave muito curtas. Prefira o pg_trgm para buscas sem distinção entre maiúsculas e minúsculas (ILIKE) ou com operadores de expressão regular (~, ~*).

Pré-requisitos

Antes de começar, verifique se os seguintes requisitos foram atendidos:

Uma instância do RDS for PostgreSQL que atenda aos seguintes requisitos de versão:

Importante

Não é possível criar o pg_bigm em instâncias com versão secundária do mecanismo anterior a 20230830. Se sua instância executar uma versão mais antiga e já utilizar essa extensão, a funcionalidade não será afetada. No entanto, para instalar ou reinstalar a extensão, atualize a versão secundária do mecanismo para a versão mais recente. Para mais detalhes, consulte Restrições na criação de extensões.

Versão principal	Versão secundária do mecanismo
PostgreSQL 17	20250830 ou posterior
PostgreSQL 16	Sem restrições
PostgreSQL 10–15	20230830 ou posterior

Adicione pg_bigm ao Value do parâmetro shared_preload_libraries. Exemplo: 'pg_stat_statements,auto_explain,pg_bigm'. Para instruções detalhadas, consulte Definir parâmetros da instância.

Limitações

O tamanho da coluna do índice GIN não pode exceder 107.374.180 bytes (~102 MB).
Se o banco de dados contiver conteúdo não ASCII, defina a codificação de caracteres como UTF8. Para verificar a codificação atual, execute:
```
SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database();
```

Operações básicas

Criar a extensão

postgres=> CREATE EXTENSION pg_bigm;
CREATE EXTENSION

Criar um índice GIN

Crie uma tabela, insira dados e construa um índice GIN usando gin_bigm_ops:

postgres=> CREATE TABLE pg_tools (tool text, description text);
CREATE TABLE
postgres=> INSERT INTO pg_tools VALUES ('pg_hint_plan', 'Tool that allows a user to specify an optimizer HINT to PostgreSQL');
INSERT 0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_dbms_stats', 'Tool that allows a user to stabilize planner statistics in PostgreSQL');
INSERT 0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_bigm', 'Tool that provides 2-gram full text search capability in PostgreSQL');
INSERT 0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_trgm', 'Tool that provides 3-gram full text search capability in PostgreSQL');
INSERT 0 1

-- Single-column GIN index
postgres=> CREATE INDEX pg_tools_idx ON pg_tools USING gin (description gin_bigm_ops);
CREATE INDEX

-- Multi-column GIN index with FASTUPDATE disabled
postgres=> CREATE INDEX pg_tools_multi_idx ON pg_tools USING gin (tool gin_bigm_ops, description gin_bigm_ops) WITH (FASTUPDATE = off);
CREATE INDEX

Buscar texto completo

Use o operador LIKE com o padrão %keyword%:

postgres=> SELECT * FROM pg_tools WHERE description LIKE '%search%';
  tool   |                             description
---------+---------------------------------------------------------------------
 pg_bigm | Tool that provides 2-gram full text search capability in PostgreSQL
 pg_trgm | Tool that provides 3-gram full text search capability in PostgreSQL
(2 rows)

Buscar por similaridade

Use o operador =% com pg_bigm.similarity_limit para controlar o limiar de correspondência:

postgres=> SET pg_bigm.similarity_limit TO 0.2;
SET
postgres=> SELECT tool FROM pg_tools WHERE tool =% 'bigm';
  tool
---------
 pg_bigm
 pg_trgm
(2 rows)

Desinstalar a extensão

postgres=> DROP EXTENSION pg_bigm;
DROP EXTENSION

Funções da extensão

likequery

No pg_bigm, a busca de texto completo usa correspondência de padrões LIKE. Isso exige que a palavra-chave esteja entre % e que quaisquer caracteres literais % sejam escapados. Use likequery para automatizar essa conversão em vez de implementar a lógica de escape na aplicação.

Parâmetro: Uma única string.
Valor retornado: Uma string de busca compatível com LIKE, com % adicionado antes e depois da palavra-chave, e caracteres literais % escapados com \.

postgres=> SELECT likequery('pg_bigm has improved the full text search performance by 200%');
                             likequery
-------------------------------------------------------------------
 %pg\_bigm has improved the full text search performance by 200\%%
(1 row)

postgres=> SELECT * FROM pg_tools WHERE description LIKE likequery('search');
  tool   |                             description
---------+---------------------------------------------------------------------
 pg_bigm | Tool that provides 2-gram full text search capability in PostgreSQL
 pg_trgm | Tool that provides 3-gram full text search capability in PostgreSQL
(2 rows)

show_bigm

Retorna um array com todos os elementos de 2-gramas de uma string. A função adiciona um espaço antes e depois da string antes de calcular as substrings de 2-gramas.

Parâmetro: Uma única string.
Valor retornado: Um array com todas as substrings de 2-gramas.

postgres=> SELECT show_bigm('full text search');
                            show_bigm
------------------------------------------------------------------
 {" f"," s"," t",ar,ch,ea,ex,fu,"h ","l ",ll,rc,se,"t ",te,ul,xt}
(1 row)

bigm_similarity

Calcula a similaridade entre duas strings com base nos elementos de 2-gramas comuns.

Parâmetros: Duas strings.
Valor retornado: Um número de ponto flutuante entre 0 e 1, onde 0 indica strings completamente diferentes e 1 indica strings idênticas.

Nota

Como espaços são adicionados antes e depois das strings durante o cálculo de 2-gramas, a similaridade entre ABC e B é 0, enquanto a similaridade entre ABC e A é 0,25. A função diferencia maiúsculas de minúsculas: portanto, a similaridade entre ABC e abc é 0.

postgres=> SELECT bigm_similarity('full text search', 'text similarity search');
 bigm_similarity
-----------------
       0.5714286
(1 row)

postgres=> SELECT bigm_similarity('ABC', 'A');
 bigm_similarity
-----------------
            0.25
(1 row)

postgres=> SELECT bigm_similarity('ABC', 'B');
 bigm_similarity
-----------------
               0
(1 row)

postgres=> SELECT bigm_similarity('ABC', 'abc');
 bigm_similarity
-----------------
               0
(1 row)

pg_gin_pending_stats

Retorna o número de páginas e tuplas na lista pendente de um índice GIN.

Parâmetro: O nome ou OID do índice GIN.
Valores retornados: O número de páginas e o número de tuplas na lista pendente.

Nota

Se o índice GIN foi criado com FASTUPDATE = false, não há lista pendente e a função retorna 0.

postgres=> SELECT * FROM pg_gin_pending_stats('pg_tools_idx');
 pages | tuples
-------+--------
     0 |      0
(1 row)

Parâmetros

Parâmetro	Descrição	Padrão
`pg_bigm.last_update`	Data da última atualização da extensão. Somente leitura.	—
`pg_bigm.enable_recheck`	Controla se ocorre reverificação após uma varredura de índice GIN. Mantenha como `on` para garantir resultados precisos.	`on`
`pg_bigm.gin_key_limit`	Número máximo de elementos de 2-gramas usados na busca de texto completo. Defina como `0` para usar todos os elementos. Reduza este valor se o uso de todos os elementos degradar o desempenho.	`0`
`pg_bigm.similarity_limit`	Limiar de similaridade para buscas por similaridade. Apenas tuplas com pontuação acima desse limiar são retornadas.	—

pg_bigm.last_update

SHOW pg_bigm.last_update;

pg_bigm.enable_recheck

Quando enable_recheck está definido como on (padrão), os resultados da varredura do índice GIN passam por reverificação conforme a condição real da consulta, filtrando falsos positivos.

O exemplo a seguir demonstra essa diferença. Com enable_recheck = off, a palavra "trivial" é retornada incorretamente ao buscar por "trial", pois ambas compartilham os mesmos elementos de 2-gramas:

postgres=> CREATE TABLE tbl (doc text);
CREATE TABLE
postgres=> INSERT INTO tbl VALUES('He is awaiting trial');
INSERT 0 1
postgres=> INSERT INTO tbl VALUES('It was a trivial mistake');
INSERT 0 1
postgres=> CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops);
CREATE INDEX
postgres=> SET enable_seqscan TO off;
SET

postgres=> EXPLAIN ANALYZE SELECT * FROM tbl WHERE doc LIKE likequery('trial');
                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl  (cost=20.00..24.01 rows=1 width=32) (actual time=0.020..0.021 rows=1 loops=1)
   Recheck Cond: (doc ~~ '%trial%'::text)
   Rows Removed by Index Recheck: 1
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on tbl_idx  (cost=0.00..20.00 rows=1 width=0) (actual time=0.013..0.013 rows=2 loops=1)
         Index Cond: (doc ~~ '%trial%'::text)
 Planning Time: 0.117 ms
 Execution Time: 0.043 ms
(8 rows)

-- With enable_recheck = on (default): correct result
postgres=> SELECT * FROM tbl WHERE doc LIKE likequery('trial');
         doc
----------------------
 He is awaiting trial
(1 row)

-- With enable_recheck = off: false positive included
postgres=> SET pg_bigm.enable_recheck = off;
SET
postgres=> SELECT * FROM tbl WHERE doc LIKE likequery('trial');
           doc
--------------------------
 He is awaiting trial
 It was a trivial mistake
(2 rows)

pg_bigm.gin_key_limit

pg_bigm.similarity_limit

-- Return matches with similarity above 0.2
SET pg_bigm.similarity_limit TO 0.2;