Misture consultas de texto completo e não de texto

Esta página descreve como realizar uma pesquisa que mistura dados de texto completo e não texto.

Realizar uma pesquisa mista de texto completo e não textual

Os índices de pesquisa oferecem suporte a texto completo, correspondência exata, colunas numéricas e colunas JSON/JSONB. É possível combinar condições de texto e não texto na cláusula WHERE de forma semelhante a consultas de pesquisa de várias colunas. O otimizador de consulta tenta otimizar predicados não de texto com um índice de pesquisa. Se isso não for possível, o Spanner vai avaliar a condição para cada linha que corresponder ao índice de pesquisa. As colunas referenciadas que não são armazenadas no índice de pesquisa são extraídas da tabela base.

Veja o exemplo a seguir.

CREATE TABLE Albums (
  AlbumId STRING(MAX) NOT NULL,
  Title STRING(MAX),
  Rating FLOAT64,
  Genres ARRAY<STRING(MAX)>,
  Likes INT64,
  Cover BYTES(MAX),
  Title_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(Title)) HIDDEN,
  Rating_Tokens TOKENLIST AS (TOKENIZE_NUMBER(Rating)) HIDDEN,
  Genres_Tokens TOKENLIST AS (TOKEN(Genres)) HIDDEN
) PRIMARY KEY(AlbumId);

CREATE SEARCH INDEX AlbumsIndex
ON Albums(Title_Tokens, Rating_Tokens, Genres_Tokens)
STORING (Likes);

CREATE TABLE albums (
  albumid character varying NOT NULL,
  title character varying,
  rating bigint,
  genres character varying NOT NULL,
  likes bigint,
  cover bytea,
  title_tokens spanner.tokenlist AS (spanner.tokenize_fulltext(title)) VIRTUAL HIDDEN,
  rating_tokens spanner.tokenlist AS (spanner.tokenize_number(rating)) VIRTUAL HIDDEN,
  genres_tokens spanner.tokenlist AS (spanner.token(genres)) VIRTUAL HIDDEN,
PRIMARY KEY(albumid));

CREATE SEARCH INDEX albumsindex
ON albums(title_tokens, rating_tokens, genres_tokens)
INCLUDE (likes);

O comportamento das consultas nessa tabela inclui o seguinte:

• Rating e Genres são incluídos no índice de pesquisa. O Spanner acelera as condições usando listas de publicação de índices de pesquisa. ARRAY_INCLUDES_ANY e ARRAY_INCLUDES_ALL são funções do GoogleSQL e não são compatíveis com o dialeto do PostgreSQL.

  SELECT Album
  FROM Albums
  WHERE Rating > 4
    AND ARRAY_INCLUDES_ANY(Genres, ['jazz'])
  

• A consulta pode combinar conjunções, disjunções e negações de qualquer maneira, inclusive misturando predicados de texto completo e não de texto. Essa consulta é totalmente acelerada pelo índice de pesquisa.

  SELECT Album
  FROM Albums
  WHERE (SEARCH(Title_Tokens, 'car')
        OR Rating > 4)
    AND NOT ARRAY_INCLUDES_ANY(Genres, ['jazz'])
  

• Likes é armazenado no índice, mas o esquema não solicita que o Spanner crie um índice de token para os valores possíveis. Portanto, o predicado de texto completo em Title e o predicado não de texto em Rating são acelerados, mas o predicado em Likes não é. No Spanner, a consulta busca todos os documentos com o termo "car" no Title e uma classificação maior que 4. Em seguida, ela filtra os documentos que não têm pelo menos 1.000 curtidas. Essa consulta usa muitos recursos se quase todos os álbuns tiverem o termo "car" no título e quase todos tiverem uma classificação de 5, mas poucos álbuns tiverem 1.000 curtidas. Nesses casos, a indexação de Likes de forma semelhante a Rating economiza recursos.

  GoogleSQL

  SELECT Album
  FROM Albums
  WHERE SEARCH(Title_Tokens, 'car')
    AND Rating > 4
    AND Likes >= 1000
  

  PostgreSQL

  SELECT album
  FROM albums
  WHERE spanner.search(title_tokens, 'car')
    AND rating > 4
    AND likes >= 1000
  

• Cover não é armazenado no índice. A consulta a seguir faz uma junção reversa entre AlbumsIndex e Albums para buscar Cover em todos os álbuns correspondentes.

  GoogleSQL

  SELECT AlbumId, Cover
  FROM Albums
  WHERE SEARCH(Title_Tokens, 'car')
    AND Rating > 4
  

  PostgreSQL

  SELECT albumid, cover
  FROM albums
  WHERE spanner.search(title_tokens, 'car')
    AND rating > 4
  

A seguir

• Saiba mais sobre consultas de pesquisa de texto completo.
• Saiba como classificar os resultados da pesquisa.
• Saiba como realizar uma pesquisa de substring.
• Saiba como paginar os resultados da pesquisa.
• Saiba como pesquisar várias colunas.