Busca textual em SQL
Em geral quando pensamos em busca textual em SQL a primeira coisa que pensamos é em usar o LIKE:
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name LIKE 'Jen%';
Porém para buscas mais complexas, se torna bem difícil e pouco performático varrer toda uma tabela em busca de uma padrão de caracteres. Para esses casos em que queremos resultados relevantes para um ou mais termos podemos usar uma recurso bem interessante disponibilizado pela maioria dos bancos SQL , o Full Text Search.
Full Text Search
Para utilizar o recurso no Postgres é relativamente simples, digamos que nosso banco tenha uma tabela products
com campos VARCHAR product_name
e product_description
.
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR (50),
product_description VARCHAR (200),
product_price NUMERIC(5,2)
);
INSERT INTO products(product_name,product_description, product_price) VALUES
('ASUS RGB Leds','Fancy RGB leds for PC decoration', 15.00),
('HP RGB Leds','Simple RGB leds for PC decoration', 10.00),
('HP Cooler Fan','A strong fan for any CPU ', 10.00),
('Intel i3 CPU','Basic processor for computers', 50.00),
('Intel i5 CPU','Intermediate processor for computers', 75.00),
('Intel i7 CPU','Advanced processor for computers', 100.00),
('AMD 586 CPU','Modern processor for computers', 100.00),
('HP Mouse','Simple wired HP mouse', 12.00),
('ASUS Mouse','Simple wired ASUS mouse', 14.99),
('Xiomi Mouse','Xiomi gamer mouse with RGB', 18.99),
('HP Kitty Mouse','Fancy Kitty decorated HP mouse', 25.50),
('Corsair RAM Memory 2GB','Basic desktop RAM Memory - 2GB', 20.00),
('Corsair RAM Memory 4GB','Intermediate desktop RAM Memory - 4GB', 40.00),
('Corsair RAM Memory 8GB','Advanced desktop RAM Memory - 8GB', 60.00);
Para realizarmos uma busca nos nomes do produtos podemos usar
SELECT product_name, product_price FROM products
WHERE to_tsvector(product_name) @@ to_tsquery('CPU');
product_name | product_price
--------------+---------------
Intel i3 CPU | 50.00
Intel i5 CPU | 75.00
Intel i7 CPU | 100.00
AMD 586 CPU | 100.00
(4 rows)
No caso acima estamos procurando o termo desejado apenas na coluna product_name
, mas para essa tabela em específico seria mais interessante que a busca incluísse também a coluna product_description
,
SELECT product_name, product_price FROM products
WHERE to_tsvector(product_name) || to_tsvector(product_description)
@@ to_tsquery('CPU');
product_name | product_price
---------------+---------------
HP Cooler Fan | 10.00
Intel i3 CPU | 50.00
Intel i5 CPU | 75.00
Intel i7 CPU | 100.00
AMD 586 CPU | 100.00
(5 rows)
Perceba que neste caso seria interessante que os resultados fossem ordenados por relevância, então que tal usarmos o ts_rank
para que apareçam primeiro os resultados em que o termo procurado esteja no nome do produto?
SELECT product_name, product_price FROM products
WHERE to_tsvector(product_name) || to_tsvector(product_description)
@@ to_tsquery('CPU')
ORDER BY
ts_rank(
setweight(to_tsvector(product_name),'A') ||
setweight(to_tsvector(product_description),'B')
, to_tsquery('CPU'))
DESC;
product_name | product_price
---------------+---------------
Intel i3 CPU | 50.00
Intel i5 CPU | 75.00
Intel i7 CPU | 100.00
AMD 586 CPU | 100.00
HP Cooler Fan | 10.00
(5 rows)
Performance
Logicamente essa abordagem de pesquisa não é tão performática já que continuamos varrendo toda a tabela pra gerar os vetores FTS e compararmos com o termo procurado, então que tal criamos um outra coluna que vai conter os vetores criados a partir das colunas relevantes da nossa tabela de produtos. Também podemos aproveitar e criar um índice para esta coluna e agilizarmos mais ainda as buscas futuras.
ALTER TABLE products
ADD COLUMN search_vectors_col tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english',product_name),'A') ||
setweight(to_tsvector('english',product_description),'B')
) STORED;
CREATE INDEX idx_fts ON products
USING gin(search_vectors_col);
Agora nossas buscas além de menos custosas vão ser até mais simples:
SELECT product_name, product_price FROM products
WHERE search_vectors_col @@ to_tsquery('CPU')
ORDER BY ts_rank(search_vectors_col, to_tsquery('CPU')) DESC;
Veja também
Abuse do to_tsquery
usando operadores lógicos de acordo com a necessidade da sua aplicação, ex: to_tsquery('CPU & Intel')
, to_tsquery('CPU | processor')
, to_tsquery('CPU & !AMD')
.
O recurso FTS do Postgres também suporta múltiplas línguas assim como o uso da extensão pg_trgm
para permitir a busca com erros ortográficos e a extensão unaccent
pra melhorar as buscas com caracteres acentuados.
Perceba também que ao criarmos o índice para nossa coluna de vetores nós usamos um índice do tipo GIN
, mas também temos a opção de usar o tipo GiST
cabendo ao desenvolvedor escolher aquele que mais se adequar à sua necessidade.
Continue pesquisando sobre o tema e happy coding, guys.