全文クエリと非テキストクエリを組み合わせる

このページでは、全文データと非テキストデータを組み合わせて検索する方法について説明します。

全文検索と非テキスト検索を組み合わせて実行する

検索インデックスは、全文検索、完全一致、数値列、JSON/JSONB 列をサポートしています。複数列の検索クエリと同様に、WHERE 句でテキスト条件と非テキスト条件を組み合わせることができます。クエリ オプティマイザは、検索インデックスを使用してテキスト以外の述語を最適化しようとします。検索インデックスに一致する行が 1 つしかない場合は、Spanner は条件を評価しません。一致する行が複数ある場合は、Spanner は検索インデックスに一致するすべての行の条件を評価します。検索インデックスに格納されていない参照列は、ベーステーブルからフェッチされます。

次に例を示します。

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

Spanner PostgreSQL のサポートには次の制限事項があります。

  • spanner.tokenize_number 関数は bigint 型のみをサポートします。
  • spanner.token は配列のトークン化をサポートしていません。
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);

このテーブルに対するクエリの動作は次のとおりです。

  • Rating と Genres は検索インデックスに含まれます。Spanner は、検索インデックスの投稿リストを使用して条件を高速化します。ARRAY_INCLUDES_ANY と ARRAY_INCLUDES_ALL は GoogleSQL 関数であり、PostgreSQL 言語ではサポートされていません。

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

  • クエリでは、全文述語と非テキスト述語を組み合わせるなど、任意の方法で結合、除外、否定を組み合わせることが可能です。このクエリは、検索インデックスによって完全に高速化されます。

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

  • Likes はインデックスに格納されますが、スキーマは Spanner に対して、有効な値のトークン インデックスの作成をリクエストしません。したがって、Title の全文述語と Rating の非テキスト述語は高速化されますが、Likes の述語は高速化されません。Spanner では、このクエリによって、Title に「car」というキーワードが含まれ、評価が 4 より大きいすべてのドキュメントが取得され、高評価が 1,000 件未満のドキュメントが除外されます。ほとんどのアルバムのタイトルに「car」という単語が含まれ、ほとんどのアルバムの評価が 5 で、高評価が 1,000 件のアルバムが少ない場合は、このクエリで大量のリソースが使用されます。このような場合は、Rating と同様に Likes をインデックスに登録するとリソースを節約できます。

    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 はインデックスに保存されません。次のクエリは、AlbumsIndex と Albums の間でバック結合を行い、一致するすべてのアルバムの Cover を取得します。

    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

次のステップ