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.

GoogleSQL 

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);

PostgreSQL

O suporte do Spanner para PostgreSQL tem as seguintes limitações:

  • A função spanner.tokenize_number só aceita o tipo bigint.
  • spanner.token não oferece suporte a matrizes de tokenização.
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