Misture consultas de texto completo e sem texto

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

Efetue uma pesquisa mista de texto completo e não texto

Os índices de pesquisa suportam texto completo, correspondência exata, colunas numéricas e colunas JSON/JSONB. Pode combinar condições de texto e não texto na cláusula WHERE de forma semelhante às consultas de pesquisa de várias colunas. O otimizador de consultas tenta otimizar os predicados não textuais com um índice de pesquisa. Se isso não for possível, o Spanner avalia a condição para cada linha que corresponda ao índice de pesquisa. As colunas referenciadas não armazenadas no índice de pesquisa são obtidas da tabela base.

Considere o seguinte exemplo:

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 PostgreSQL do Spanner tem as seguintes limitações:

  • A função spanner.tokenize_number só suporta o tipo bigint.
  • O spanner.token não suporta a tokenização de matrizes.
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 nesta tabela inclui o seguinte:

  • Rating e Genres estão incluídos no índice de pesquisa. O Spanner acelera as condições através de listas de publicação do índice de pesquisa. ARRAY_INCLUDES_ANY e ARRAY_INCLUDES_ALL são funções do GoogleSQL e não são suportadas para o dialeto 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 forma, incluindo a combinação de predicados de texto completo e não texto. Esta 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 está armazenado no índice, mas o esquema não pede ao Spanner para criar um índice de tokens para os respetivos valores possíveis. Por conseguinte, o predicado de texto completo em Title e o predicado não textual em Rating são acelerados, mas o predicado em Likes não é. No Spanner, a consulta obtém todos os documentos com o termo "carro" no campo Title e uma classificação superior a 4. Em seguida, filtra os documentos que não têm, pelo menos, 1000 gostos. Esta consulta usa muitos recursos se quase todos os álbuns tiverem o termo "carro" no título e quase todos tiverem uma classificação de 5, mas poucos álbuns tiverem 1000 gostos. Nesses casos, a indexação Likes de forma semelhante a Rating poupa 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 está armazenado no índice. A seguinte consulta faz uma junção inversa entre AlbumsIndex e Albums para obter Cover para 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

O que se segue?