PolarDB:pg_bigm

Tamanho da coluna indexada

A coluna na qual você cria um índice GIN não pode exceder 107.374.180 bytes (~102 MB). Exemplo de comando:

CREATE TABLE t1 (description text);
CREATE INDEX t1_idx ON t1 USING gin (description gin_bigm_ops);

INSERT INTO t1 SELECT repeat('A', 107374181);

CREATE EXTENSION pg_bigm;

DROP EXTENSION pg_bigm;

-- Create a table and insert sample data
CREATE TABLE pg_tools (tool text, description text);

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

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

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

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)

SELECT tool FROM pg_tools WHERE tool =% 'bigm';

   tool
---------
 pg_bigm
(1 row)

likequery

No pg_bigm, a busca de texto completo usa correspondência de padrões LIKE. Portanto, a palavra-chave de busca deve estar delimitada por %, e qualquer caractere % literal na palavra-chave precisa ser escapado. Normalmente, as aplicações clientes lidam com esse escape automaticamente. A função likequery simplifica esse processo ao converter automaticamente uma palavra-chave em uma string de padrão compatível com LIKE.

Sintaxe: likequery(keyword text) → text

Essa função adiciona % antes e depois da palavra-chave, além de escapar quaisquer caracteres % literais com uma barra invertida.

Exemplos:

SELECT likequery('pg_bigm has improved the full text search performance by 200%');

Resultado:

                             likequery
-------------------------------------------------------------------
 %pg\_bigm has improved the full text search performance by 200\%%
(1 row)

SELECT * FROM pg_tools WHERE description LIKE likequery('search');

Resultado:

   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 todos os elementos de 2-gramas de uma string como um array. Antes de extrair os elementos, a função preenche a entrada com um espaço à esquerda e outro à direita.

Sintaxe: show_bigm(string text) → text[]

Exemplo:

SELECT show_bigm('full text search');

Resultado:

                            show_bigm
------------------------------------------------------------------
 {" f"," s"," t",ar,ch,ea,ex,fu,"h ","l ",ll,rc,se,"t ",te,ul,xt}
(1 row)

bigm_similarity

Retorna uma pontuação de similaridade em ponto flutuante entre duas strings, variando de 0 (completamente diferentes) a 1 (idênticas). O cálculo baseia-se nos elementos de 2-gramas compartilhados entre as duas strings. Antes da comparação, a função adiciona um espaço no início e no fim de cada string.

Sintaxe: bigm_similarity(string1 text, string2 text) → float4

Exemplos:

SELECT bigm_similarity('full text search', 'text similarity search');
-- Result: 0.571429

SELECT bigm_similarity('ABC', 'A');
-- Result: 0.25

SELECT bigm_similarity('ABC', 'B');
-- Result: 0

SELECT bigm_similarity('ABC', 'abc');
-- Result: 0 (case-sensitive)

pg_gin_pending_stats

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

Sintaxe: pg_gin_pending_stats(index regclass) → (pages int, tuples int)

Exemplo:

SELECT * FROM pg_gin_pending_stats('pg_tools_idx');

Resultado:

 pages | tuples
-------+--------
     0 |      0
(1 row)

pg_bigm.enable_recheck

Controla se o pg_bigm executa uma etapa de reverificação após recuperar candidatos do índice GIN.

Como funciona a reverificação: A busca de texto completo com pg_bigm ocorre em duas fases:

1. Recuperação de candidatos — o índice GIN retorna todas as linhas cujos elementos de 2-gramas indexados coincidem com os 2-gramas da palavra-chave da consulta.

2. Reverificação — cada candidato é reavaliado em relação ao padrão LIKE original para filtrar falsos positivos.

Falsos positivos ocorrem porque strings não relacionadas podem compartilhar os mesmos 2-gramas. Por exemplo, buscar por trial gera os 2-gramas tr, ri, ia e al. A string "It was a trivial mistake" contém todos esses quatro 2-gramas e é retornada como candidata, mesmo sem corresponder a %trial%. A reverificação elimina esse resultado incorreto.

Mantenha pg_bigm.enable_recheck definido como on para obter resultados precisos.

Exemplo — reverificação ativada (padrão):

CREATE TABLE tbl (doc text);

INSERT INTO tbl VALUES('He is awaiting trial');
INSERT INTO tbl VALUES('It was a trivial mistake');

CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops);

SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT * FROM tbl WHERE doc LIKE likequery('trial');

Resultado:

                                                          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)

O índice retorna 2 candidatos; a reverificação elimina o falso positivo e devolve apenas a linha correta:

SELECT * FROM tbl WHERE doc LIKE likequery('trial');

Resultado:

         doc
----------------------
 He is awaiting trial
(1 row)

Exemplo — reverificação desativada:

SET pg_bigm.enable_recheck = off;
SELECT * FROM tbl WHERE doc LIKE likequery('trial');

Resultado:

           doc
--------------------------
 He is awaiting trial
 It was a trivial mistake
(2 rows)

Com a reverificação desativada, o falso positivo "It was a trivial mistake" aparece nos resultados.

pg_bigm.gin_key_limit

Defina o número máximo de elementos de 2-gramas usados durante a busca no índice GIN. O valor padrão é 0, o que indica o uso de todos os elementos de 2-gramas da palavra-chave da consulta.

Se palavras-chave longas degradarem o desempenho da consulta, reduza este valor para limitar a quantidade de 2-gramas avaliados durante a consulta ao índice.

pg_bigm.similarity_limit

Defina o limiar mínimo de pontuação de similaridade para buscas com o operador =%. Apenas linhas com pontuação bigm_similarity acima desse limiar serão retornadas.