Menggabungkan kueri teks lengkap dan non-teks

Halaman ini menjelaskan cara melakukan penelusuran yang menggabungkan data teks lengkap dan non-teks.

Melakukan penelusuran teks lengkap dan non-teks campuran

Indeks penelusuran mendukung kolom teks lengkap, pencocokan persis, kolom numerik, dan kolom JSON/JSONB. Anda dapat menggabungkan kondisi teks dan non-teks dalam klausa WHERE, mirip dengan kueri penelusuran multi-kolom. Pengoptimal kueri mencoba mengoptimalkan predikat non-teks dengan indeks penelusuran. Jika tidak memungkinkan, Spanner akan mengevaluasi kondisi untuk setiap baris yang cocok dengan indeks penelusuran. Kolom yang dirujuk dan tidak disimpan dalam indeks penelusuran diambil dari tabel dasar.

Perhatikan contoh berikut:

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

Dukungan PostgreSQL Spanner memiliki batasan berikut:

  • Fungsi spanner.tokenize_number hanya mendukung jenis bigint.
  • spanner.token tidak mendukung tokenisasi array.
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);

Perilaku kueri pada tabel ini mencakup hal berikut:

  • Rating dan Genres disertakan dalam indeks penelusuran. Spanner mempercepat kondisi menggunakan daftar posting indeks penelusuran. ARRAY_INCLUDES_ANY, ARRAY_INCLUDES_ALL adalah fungsi GoogleSQL dan tidak didukung untuk dialek PostgreSQL.

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

  • Kueri dapat menggabungkan konjungsi, disjungsi, dan negasi dengan cara apa pun, termasuk menggabungkan predikat teks lengkap dan non-teks. Kueri ini sepenuhnya dipercepat oleh indeks penelusuran.

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

  • Likes disimpan dalam indeks, tetapi skema tidak meminta Spanner untuk membuat indeks token untuk kemungkinan nilainya. Oleh karena itu, predikat teks lengkap di Title dan predikat non-teks di Rating dipercepat, tetapi predikat di Likes tidak. Di Spanner, kueri mengambil semua dokumen dengan istilah "mobil" di Title dan rating lebih dari 4, lalu memfilter dokumen yang tidak memiliki setidaknya 1.000 suka. Kueri ini menggunakan banyak resource jika hampir semua album memiliki istilah "mobil" dalam judulnya dan hampir semuanya memiliki rating 5, tetapi hanya sedikit album yang memiliki 1.000 suka. Dalam kasus tersebut, pengindeksan Likes mirip dengan Rating akan menghemat resource.

    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 tidak disimpan dalam indeks. Kueri berikut melakukan gabungan balik antara AlbumsIndex dan Albums untuk mengambil Cover untuk semua album yang cocok.

    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

Langkah berikutnya