Combiner des requêtes textuelles et non textuelles

Cette page explique comment effectuer une recherche qui mélange des données en texte intégral et non textuelles.

Effectuer une recherche mixte en texte intégral et non textuelle

Les index de recherche sont compatibles avec les colonnes de texte intégral, de correspondance exacte, numériques et JSON/JSONB. Vous pouvez combiner des conditions textuelles et non textuelles dans la clause WHERE de la même manière que pour les requêtes de recherche multicolonnes. L'optimiseur de requêtes tente d'optimiser les prédicats non textuels avec un index de recherche. Si ce n'est pas possible, Spanner évalue la condition pour chaque ligne correspondant à l'index de recherche. Les colonnes référencées qui ne sont pas stockées dans l'index de recherche sont extraites de la table de base.

Prenons l'exemple suivant :

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

La compatibilité de Spanner avec PostgreSQL présente les limites suivantes :

  • La fonction spanner.tokenize_number n'accepte que le type bigint.
  • spanner.token ne permet pas de tokeniser les tableaux.
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);

Le comportement des requêtes sur cette table est le suivant :

  • Rating et Genres sont inclus dans l'index de recherche. Spanner accélère les conditions à l'aide des listes de publication de l'index de recherche. ARRAY_INCLUDES_ANY et ARRAY_INCLUDES_ALL sont des fonctions GoogleSQL et ne sont pas compatibles avec le dialecte PostgreSQL.

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

  • La requête peut combiner des conjonctions, des disjonctions et des négations de n'importe quelle manière, y compris en mélangeant des prédicats de texte intégral et non textuels. Cette requête est entièrement accélérée par l'index de recherche.

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

  • Likes est stocké dans l'index, mais le schéma ne demande pas à Spanner de créer un index de jetons pour ses valeurs possibles. Par conséquent, le prédicat de texte intégral sur Title et le prédicat non textuel sur Rating sont accélérés, mais pas le prédicat sur Likes. Dans Spanner, la requête récupère tous les documents contenant le terme "voiture" dans Title et une note supérieure à 4, puis filtre les documents qui n'ont pas au moins 1 000 "J'aime". Cette requête utilise beaucoup de ressources si presque tous les albums contiennent le terme "voiture" dans leur titre et ont une note de 5, mais que peu d'albums ont 1 000 "J'aime". Dans ce cas, l'indexation de Likes de la même manière que Rating permet d'économiser des ressources.

    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'est pas stocké dans l'index. La requête suivante effectue une jointure arrière entre AlbumsIndex et Albums pour extraire Cover pour tous les albums correspondants.

    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

Étapes suivantes