Estive precisando otimizar a consulta em uma mega estrutura de dados, a principio estava usando LIKE, mas logo de inicio foi falado que precisariamos de uma consulta usando Full Text Search.
Eu sabia muito pouco do conceito do FTS, pois em nenhum momento precisei usar no nível que a equipe precisava.
Então eu comecei a pesquisar, encontrei muitos conceitos e pouco prática...
Então resolvi ir direto na fonte, rs...acessei o conteudo de referencia no Postgres e comecei a estudar o FTS.
Estudei e comparei com tudo que eu já havia achado na internet...então percebi que as pessoas enchiam muito os textos pra dizer que sabiam, que tinham o total conhecimento etc...etc...etc.
Comecei os meus testes e percebi que pra voce ter um resultado satisfatório nao precisava de muita enganação, bastava ir direto ao assunto.
Mas voce precisa pelo menos entender alguns conceitos do FTS, senão por menor que seja o meu código voce vai ficar perdido.
O FTS (Full Text Search) é uma técnica de pesquisa e recuperação de dados no formato texto armazenada em banco de dados, usando PL/SQL.
Na busca Textual com FTS voce podera fazer uma indexação completa de texto e pré-processar documentos salvando na própria entidade(tuplas). Voce podera fazer uso de Dicionários.
No FTS existe: GiST e GIN
GIN ele efetua pesquisas aproximadamente três vezes mais rápido do que GiST;
GIN demoram aproximadamente três vezes mais para serem construídos do que GiST;
GIN são mais lentos para atualização de índices;
GiST são mais rápidos para atualização de índices;
Usar GiST ou GIN no FTS?
Regra básica e usar índices GIN para dados estáticos, porque as pesquisas são mais rápidas.
E índices GiST para dados que são dinâmicos, porque são mais rápidos para atualização.
Vamos na prática.
1 - Eu criei uma tabela noticias, que tem os seguintes campos: id_noticia, titulo, texto_intro, texto
2 - Altero a tabela adicionando um novo campo do tipo tsvector
ALTER TABLE noticias ADD busca tsvector;
3 - Criei o indice
CREATE INDEX noticias_gidx ON noticias USING gin(busca);
4 - Criei uma funcção pra poder criticar todos os caracteres desconhecidos.
CREATE FUNCTION to_ascii(bytea, name) RETURNS text AS
'to_ascii_encname'
LANGUAGE internal STRICT;
5 - Criei uma função que trata os valores com caracteres desconhecidos e retorna os valores tratados.
CREATE FUNCTION simples(texto varchar) RETURNS varchar AS
'select lower(to_ascii(convert_to($1, ''latin1''), ''latin1''))'
LANGUAGE sql IMMUTABLE STRICT;
6 - Atualizo o campo que eu criei 'busca' com o valores acrescentando tambem um peso pra cada item indexado no meu tratamento.
UPDATE noticias SET busca = to_tsvector('portuguese',simples(titulo));
UPDATE noticias SET busca = setweight(to_tsvector('portuguese',coalesce(titulo,'')), 'A') ||
setweight(to_tsvector('portuguese',coalesce(texto,'')), 'B') ||
setweight(to_tsvector('portuguese',coalesce(texto_intro,'')), 'C') ;
Pronto....
Agora e so testar...
EXPLAIN SELECT id_noticia, titulo FROM noticias
WHERE busca @@ plainto_tsquery(simples('brasil equipe'));
SELECT
titulo,
ts_rank_cd('{0.8, 0.6, 0.4, 0.0}', busca, to_tsquery('brasil')) AS rank
FROM noticias
WHERE busca @@@ to_tsquery(simples('brasil'))
ORDER BY rank DESC;
Obrigado a todos...
