Creare e gestire l'indice RUM

Questo documento mostra come creare l'estensione RUM e gli indici per ottimizzare la ricerca full-text in AlloyDB per PostgreSQL. Fornisce esempi per casi d'uso comuni, tra cui classificazione, ricerca di frasi e ordinamento per timestamp.

Prima di iniziare

Per creare l'estensione RUM, devi disporre del ruolo database alloydbsuperuser.

Il ruolo IAM di amministratore AlloyDB (roles/alloydb.admin) concede il controllo completo delle risorse AlloyDB, ma non concede il ruolo database alloydbsuperuser. Per creare l'estensione, un amministratore deve concederti esplicitamente il ruolo di database alloydbsuperuser.

Per ulteriori informazioni sulla concessione dei ruoli, consulta Aggiungere un utente IAM o un account di servizio a un cluster.

Crea l'estensione RUM

Devi creare l'estensione RUM una volta per database.

  1. Connettiti al database AlloyDB utilizzando psql o un altro client. Per saperne di più, vedi Connettersi a un'istanza cluster.

  2. Esegui il seguente comando SQL per creare l'estensione:

    CREATE EXTENSION IF NOT EXISTS rum;

Crea un indice RUM

Per ottimizzare le query di ricerca full-text, crea un indice RUM sui tuoi dati. RUM offre diverse classi di operatori per diversi casi d'uso.

Tipi di classi di operatori RUM

La seguente tabella riepiloga le diverse classi di operatori RUM e i relativi casi d'uso principali.

Classe operatore Caso d'uso principale Limitazioni
rum_tsvector_ops Ricerca a testo intero standard con ranking e ricerca di frasi. N/D
rum_tsvector_hash_ops Indice più piccolo e aggiornamenti più rapidi per la ricerca full-text. Non supporta la ricerca per prefisso.
rum_tsvector_addon_ops Ricerca a testo intero ordinata in base a un'altra colonna. N/D
rum_anyarray_ops Ricerca all'interno delle colonne dell'array. N/D
rum_<TYPE>_ops Indicizzazione dei tipi scalari per le query basate sulla distanza. N/D
rum_tsvector_hash_addon_ops Ricerca a testo intero basata sull'hash ordinata in base a un'altra colonna. Non supporta la corrispondenza dei prefissi.
rum_tsquery_ops Indicizzazione dei valori tsquery memorizzati per la ricerca inversa. N/D
rum_anyarray_addon_ops Ricerca di array ordinata in base a un'altra colonna. N/D

Indice per la ricerca a testo intero di base

Utilizza la classe di operatori rum_tsvector_ops per la ricerca testuale standard che richiede funzionalità di classificazione rapida e ricerca di frasi. Questa classe di operatori memorizza la posizione di ogni lessema nell'indice. L'esempio seguente crea una tabella denominata documents con una colonna content.

  1. Crea una tabella denominata documents:

    CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  published_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

  2. Compila la tabella documents con dati di esempio:

    INSERT INTO documents (title, content) VALUES
  ('Title', 'This search engine is working as intended');

  3. Aggiungi una colonna tsvector generata alla tabella. Questa colonna memorizza automaticamente il testo elaborato e migliora il rendimento delle query:

    ALTER TABLE documents
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;

  4. Crea l'indice RUM nella nuova colonna search_vector:

    CREATE INDEX idx_docs_rum
ON documents
USING rum (search_vector rum_tsvector_ops);

  5. Esegui una query sulla tabella utilizzando l'indice. L'operatore <=> calcola il punteggio di pertinenza o la distanza tra il documento e la query direttamente dall'indice, consentendo un ordinamento rapido:

    SELECT title, content
FROM documents
WHERE search_vector @@ to_tsquery('english', 'search <-> engine')
ORDER BY search_vector <=> to_tsquery('english', 'search <-> engine');

  6. Compila la tabella documents con altri dati:

    INSERT INTO documents (title, content) VALUES ('Title1', 'English is my primary language.');
INSERT INTO documents (title, content) VALUES ('Title2', 'Google has a great engineering culture');

  7. Esegui una query di ricerca con prefisso. In questo modo vengono trovati i documenti contenenti parole che iniziano con eng, ad esempio engineer o english:

    SELECT title, content
FROM documents
WHERE search_vector @@ to_tsquery('english', 'eng:*');

Utilizza la classe di operatori rum_tsvector_hash_ops per ridurre le dimensioni dell'indice e migliorare le velocità di aggiornamento. Questa classe memorizza un hash di ogni lessema anziché il lessema completo. Questo approccio genera un indice più piccolo, ma non supporta la ricerca dei prefissi. L'esempio seguente presuppone che tu disponga di una tabella denominata documents con una colonna search_vector.

  1. Crea l'indice RUM utilizzando la classe dell'operatore hash:

    CREATE INDEX idx_docs_rum_hash
ON documents
USING rum (search_vector rum_tsvector_hash_ops);

  2. Compila la tabella documents con altri dati:

    INSERT INTO documents (title, content) VALUES ('Title3', 'That person was driving incredibly fast, however the routing was not very efficient');

  3. Esegui una query di corrispondenza standard:

    SELECT * FROM documents WHERE search_vector @@ to_tsquery('english', 'fast & efficient');

Indice per la ricerca ordinato per timestamp

Utilizza la classe di operatori rum_tsvector_addon_ops per ottimizzare le query che filtrano in base al testo e ordinano in base a un altro campo, ad esempio un timestamp. Questo pattern memorizza il valore del campo aggiuntivo direttamente nell'indice, il che evita un'operazione di ordinamento lenta dopo la ricerca. L'esempio seguente presuppone che tu disponga di una tabella denominata documents con una colonna search_vector e una colonna published_at.

  1. Crea un indice che includa il timestamp published_at:

    CREATE INDEX idx_docs_rum_timestamp
ON documents
USING rum (search_vector rum_tsvector_addon_ops, published_at)
WITH (attach = 'published_at', to = 'search_vector');

  2. Esegui una query che trova i documenti contenenti la parola engine e li ordina per data di pubblicazione. L'indice gestisce in modo efficiente sia la ricerca che l'ordinamento:

    SELECT title, published_at
FROM documents
WHERE search_vector @@ to_tsquery('english', 'engine')
ORDER BY published_at DESC;

Utilizza la classe di operatori rum_anyarray_ops per indicizzare le colonne di array, ad esempio un elenco di tag. In questo modo puoi eseguire query in modo efficiente per gli array che si sovrappongono (&&), contengono (@>) o sono contenuti in (<@) altri array. L'esempio seguente aggiunge una colonna tags alla tabella documents.

  1. Aggiungi una colonna tags e compilala con i dati:

    ALTER TABLE documents 
ADD COLUMN tags TEXT[];

INSERT INTO documents (title, content, tags) VALUES ( 'Title4', 'Sample Text', ARRAY['ai', 'ml'] );

  2. Crea l'indice RUM su una colonna TEXT[] denominata tags:

    CREATE INDEX idx_tags_rum
ON documents
USING rum (tags rum_anyarray_ops);

  3. Esegui una query per trovare i documenti che hanno ai o ml nei tag:

    SELECT * FROM documents WHERE tags && '{"ai", "ml"}';

Indice per i tipi scalari

Utilizza le classi di operatori rum_<TYPE>_ops per indicizzare le colonne che contengono valori continui, come numeri interi, timestamp o numeri in rappresentazione in virgola mobile. Queste classi di operatori consentono di utilizzare l'operatore <=> per calcolare in modo efficiente la distanza tra i valori. Il seguente esempio presuppone che tu disponga di una tabella denominata documents.

  1. Aggiungi una colonna di numeri interi generici, ad esempio rating, alla tabella documents:

    ALTER TABLE documents
ADD COLUMN rating INT;

UPDATE documents 
SET rating = floor(random() * 5 + 1);

  2. Crea un indice RUM nella colonna rating:

    CREATE INDEX idx_rating_rum
ON documents
USING rum (rating rum_int4_ops);

  3. Esegui una query per trovare i documenti con un rating più vicino al valore 5:

    SELECT title, rating
FROM documents
ORDER BY rating <=> 5;

Indice per la ricerca ottimizzata degli hash ordinati per timestamp

Utilizza la classe di operatori rum_tsvector_hash_addon_ops per combinare i vantaggi di un indice hash con le funzionalità di ordinamento di un indice aggiuntivo. Questa classe memorizza un hash di ogni lessema insieme al valore di una colonna aggiuntiva. Questa configurazione supporta l'ordinamento efficiente in base alla colonna aggiuntiva, ma non supporta la corrispondenza dei prefissi. L'esempio seguente presuppone che tu disponga di una tabella denominata documents con una colonna search_vector e una colonna timestamp published_at.

  1. Crea un indice RUM che utilizzi la classe operatore hash e includa il timestamp published_at:

    CREATE INDEX idx_docs_rum_hash_timestamp
ON documents
USING rum (search_vector rum_tsvector_hash_addon_ops, published_at)
WITH (attach = 'published_at', to = 'search_vector');

  2. Esegui una query che trova i documenti contenenti engine e li ordina per data di pubblicazione:

    SELECT title, published_at
FROM documents
WHERE search_vector @@ to_tsquery('english', 'engine')
ORDER BY published_at DESC;

Indice per le query archiviate

Utilizza la classe di operatori rum_tsquery_ops per indicizzare i valori tsquery. In questo modo, puoi eseguire la "ricerca inversa", identificando quali query archiviate corrispondono a un determinato documento di input. L'esempio seguente crea una tabella denominata queries.

  1. Crea una tabella per archiviare le query:

    CREATE TABLE queries (
query_text tsquery
);
INSERT INTO queries (query_text) VALUES (plainto_tsquery('AlloyDB is fast!'));

  2. Crea un indice RUM nella colonna query_text:

    CREATE INDEX idx_queries_rum
ON queries
USING rum (query_text rum_tsquery_ops);

  3. Esegui una query per trovare le query archiviate che corrispondono a un documento:

    SELECT *
FROM queries
WHERE to_tsvector('english', 'AlloyDB is fast') @@ query_text;

Indice per la ricerca di array ordinato per timestamp

Utilizza la classe di operatori rum_anyarray_addon_ops per indicizzare le colonne dell'array insieme a una colonna aggiuntiva per l'ordinamento. L'esempio seguente presuppone che tu abbia una tabella denominata documents con una colonna tags e una colonna timestamp published_at.

  1. Crea un indice RUM sulla colonna tags che include il timestamp published_at:

    CREATE INDEX idx_tags_rum_timestamp
ON documents
USING rum (tags rum_anyarray_addon_ops, published_at)
WITH (attach = 'published_at', to = 'tags');

  2. Esegui una query per trovare i documenti con il tag ai, ordinati per data di pubblicazione:

    SELECT title, published_at
FROM documents
WHERE tags @> '{"ai"}'
ORDER BY published_at DESC;

Passaggi successivi