Combiner des requêtes en texte intégral et non textuelles

Cette page explique comment effectuer une recherche combinant des données textuelles et non textuelles.

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

Les index de recherche sont compatibles avec le texte intégral, la correspondance exacte, les colonnes numériques et les colonnes JSON/JSONB. Vous pouvez combiner des conditions textuelles et non textuelles dans la clause WHERE, comme dans les requêtes de recherche multicolonne. L'optimiseur de requêtes tente d'optimiser les prédicats non textuels avec un indice 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 prise en charge de PostgreSQL par Spanner présente les limites suivantes:

  • La fonction spanner.tokenize_number n'est compatible qu'avec le type bigint.
  • spanner.token ne permet pas de tokenizer des 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 de listes d'affichage d'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 quelque manière que ce soit, y compris en mélangeant des prédicats de texte complet 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 complet sur Title et le prédicat non textuel sur Rating sont accélérés, mais le prédicat sur Likes ne l'est pas. Dans Spanner, la requête extrait tous les documents contenant le terme "voiture" dans Title et ayant 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 presque tous 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

Étape suivante