PostgreSQL: Motor de Busca Textual do PostgreSQL

SELECT * FROM ts_debug('english', 'The red shoes are beautiful');

 alias | descriptor | token  |  dictionaries  | dictionary | lexemes 
-------+------------+--------+----------------+------------+---------
 ascii | word       | The    | {english_stem}  | english_stem| {}
 ascii | word       | red    | {english_stem}  | english_stem| {red}
 ascii | word       | shoes  | {english_stem}  | english_stem| {shoe}
 ascii | word       | are    | {english_stem}  | english_stem| {}
 ascii | word       | beautiful | {english_stem}| english_stem| {beauti}

SELECT to_tsvector('english', 'The red shoes are beautiful shoes');

'beauti':5 'red':2 'shoe':3,6

SELECT to_tsquery('english', 'red & shoe');
-- 'red' & 'shoe'

SELECT to_tsquery('english', 'red | blue');
-- 'red' | 'blue'

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT plainto_tsquery('english', 'red shoes');
-- 'red' & 'shoe'  (encontra docs com AMBOS os termos)

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT phraseto_tsquery('english', 'red shoes');
-- 'red' <-> 'shoe'  (encontra docs onde 'red' está imediatamente antes de 'shoe')

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT websearch_to_tsquery('english', '"red shoes" -broken OR new');
-- 'red' <-> 'shoe' & !'broken' | 'new'

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE TABLE catalog (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  search_vector tsvector
);

INSERT INTO catalog (name, description, search_vector) VALUES
  ('Red Running Shoes', 'Lightweight red shoes for running', 
   to_tsvector('english', 'Lightweight red shoes for running')),
  ('Blue Casual Shoes', 'Comfortable blue shoes for daily wear',
   to_tsvector('english', 'Comfortable blue shoes for daily wear')),
  ('Red Dress', 'Elegant red dress for special occasions',
   to_tsvector('english', 'Elegant red dress for special occasions'));

-- Buscar por "red shoes"
SELECT name, description
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

INSERT 0 1

SELECT name,
       ts_rank(search_vector, plainto_tsquery('english', 'red shoes')) AS rank
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes')
ORDER BY rank DESC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

ALTER TABLE catalog ADD COLUMN title_vector tsvector;

UPDATE catalog SET title_vector = to_tsvector('english', name);

-- Definir pesos: title=D(padrão), body=A(mais alto)
SELECT name,
       ts_rank(
         setweight(title_vector, 'A') || setweight(search_vector, 'B'),
         plainto_tsquery('english', 'red shoes')
       ) AS rank
FROM catalog
WHERE setweight(title_vector, 'A') || setweight(search_vector, 'B')
      @@ plainto_tsquery('english', 'red shoes')
ORDER BY rank DESC;

UPDATE 3

SELECT name,
       ts_headline('english', description, plainto_tsquery('english', 'red shoes')) AS highlighted
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

 name              | highlighted
-------------------+-------------------------------------------------
 Red Running Shoes | Lightweight <b>red</b> <b>shoes</b> for running

SELECT ts_headline(
  'english',
  description,
  plainto_tsquery('english', 'red shoes'),
  'StartSel=<em>,StopSel=</em>,MaxWords=10,MinWords=5'
) AS highlighted
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE INDEX idx_catalog_desc_fts
ON catalog USING gin (to_tsvector('english', description));

-- A consulta deve usar a mesma config
SELECT name FROM catalog
WHERE to_tsvector('english', description) @@ plainto_tsquery('english', 'red shoes');

CREATE TABLE

CREATE INDEX idx_catalog_search_fts ON catalog USING gin (search_vector);

-- A consulta usa coluna armazenada
SELECT name FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

CREATE TABLE

CREATE FUNCTION catalog_search_vector_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_catalog_search
BEFORE INSERT OR UPDATE OF name, description ON catalog
FOR EACH ROW EXECUTE FUNCTION catalog_search_vector_update();

INSERT 0 1

SELECT to_tsvector('simple', 'The red shoes');
-- 'red':2 'shoes':3 'the':1  (mantém 'the', sem stemming)

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Pré-segmentar texto chinês com espaços na camada de aplicação
INSERT INTO catalog (name, description, search_vector)
VALUES (
  'Tênis de Corrida Vermelho',
  'tênis de corrida vermelho leve para corrida',
  to_tsvector('simple', 'tênis de corrida vermelho leve para corrida')
);

-- Buscar com consulta segmentada
SELECT name FROM catalog
WHERE search_vector @@ to_tsquery('simple', 'tênis & corrida');

INSERT 0 1

CREATE TABLE articles (
  article_id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT,
  fts_en tsvector,
  fts_zh tsvector
);

-- Config english para conteúdo em inglês, simple para chinês
UPDATE articles SET
  fts_en = to_tsvector('english', COALESCE(title, '') || ' ' || COALESCE(content, '')),
  fts_zh = to_tsvector('simple', COALESCE(title, '') || ' ' || COALESCE(content, ''));

-- Buscar em inglês
SELECT title FROM articles
WHERE fts_en @@ plainto_tsquery('english', 'database optimization');

-- Buscar em chinês (segmentado pela aplicação)
SELECT title FROM articles
WHERE fts_zh @@ to_tsquery('simple', 'data & optimization');

UPDATE 3

SELECT word, ndoc, nentry
FROM ts_stat(
  'SELECT search_vector FROM catalog'
)
ORDER BY nentry DESC
LIMIT 10;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT cfgname FROM pg_ts_config;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT dictname, dictnamespace FROM pg_ts_dict;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Criar dados de teste
INSERT INTO catalog (name, description, search_vector)
SELECT
  'Product ' || i,
  'High quality product number ' || i || ' with great features and amazing design',
  to_tsvector('english', 'High quality product number ' || i || ' with great features and amazing design')
FROM generate_series(1, 100000) AS i;

-- Consulta LIKE (lenta, sem suporte de índice para curinga inicial)
EXPLAIN ANALYZE
SELECT name FROM catalog WHERE description LIKE '%great features%';

-- Busca textual com índice GIN (rápida)
CREATE INDEX idx_catalog_fts ON catalog USING gin (search_vector);
EXPLAIN ANALYZE
SELECT name FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'great features');

INSERT 0 1

-- Etapa 1: Criar tabela de produtos com tsvector pré-computado
CREATE TABLE shop_products (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  description TEXT,
  price NUMERIC(10,2) NOT NULL,
  search_doc tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', COALESCE(name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(category, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(description, '')), 'C')
  ) STORED
);

-- Etapa 2: Inserir dados de exemplo
INSERT INTO shop_products (name, category, description, price) VALUES
  ('Red Running Shoes', 'Shoes', 'Lightweight red running shoes with cushioned sole', 89.99),
  ('Blue Casual Shoes', 'Shoes', 'Comfortable blue shoes for everyday casual wear', 59.99),
  ('Red Leather Dress Shoes', 'Shoes', 'Premium red leather dress shoes for formal events', 149.99),
  ('Red Silk Dress', 'Dresses', 'Elegant red silk dress for special occasions', 199.99),
  ('Running Watch', 'Accessories', 'GPS running watch with heart rate monitor', 249.99),
  ('Running Shorts', 'Clothing', 'Breathable running shorts with pockets', 39.99);

-- Etapa 3: Criar índice GIN na coluna gerada
CREATE INDEX idx_shop_products_search ON shop_products USING gin (search_doc);

-- Etapa 4: Função de busca com ranqueamento, destaque e paginação
CREATE FUNCTION search_products(
  p_query TEXT,
  p_limit INT DEFAULT 20,
  p_offset INT DEFAULT 0
) RETURNS TABLE (
  product_id INT,
  name TEXT,
  category TEXT,
  price NUMERIC,
  rank REAL,
  headline TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    s.product_id,
    s.name,
    s.category,
    s.price,
    ts_rank(s.search_doc, websearch_to_tsquery('english', p_query)) AS rank,
    ts_headline(
      'english',
      COALESCE(s.name, '') || '. ' || COALESCE(s.description, ''),
      websearch_to_tsquery('english', p_query),
      'StartSel=<mark>,StopSel=</mark>,MaxWords=20,MinWords=5'
    ) AS headline
  FROM shop_products s
  WHERE s.search_doc @@ websearch_to_tsquery('english', p_query)
  ORDER BY rank DESC
  LIMIT p_limit
  OFFSET p_offset;
END;
$$ LANGUAGE plpgsql;

-- Etapa 5: Testar busca
SELECT * FROM search_products('red shoes');
SELECT * FROM search_products('"running shoes"');
SELECT * FROM search_products('running -shoes');

-- Etapa 6: Buscar com filtro de categoria
SELECT name, price,
       ts_rank(search_doc, plainto_tsquery('english', 'red')) AS rank
FROM shop_products
WHERE search_doc @@ plainto_tsquery('english', 'red')
  AND category = 'Shoes'
ORDER BY rank DESC;