Crea ed esegui query su un indice RUM

Gli indici RUM in AlloyDB per PostgreSQL ti consentono di accelerare le query di ricerca a testo intero archiviando informazioni su posizione e attributi insieme ai lessemi nell'indice. Puoi attivare l'estensione rum per creare indici RUM e ottimizzare il ranking di pertinenza, le ricerche di frasi e l'ordinamento basato su timestamp.

Prima di iniziare

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

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

Per saperne di più sulla concessione dei ruoli, consulta Aggiungi un utente o un account di servizio IAM a un cluster.

Crea l'estensione RUM

Devi creare l'estensione RUM una volta per database.

1. Connettiti al tuo database AlloyDB utilizzando psql o un altro client. Per saperne di più, consulta Connettiti a un'istanza del 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 a testo intero, 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 riassume 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 a testo intero.	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 di colonne di 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 su hash ordinata in base a un'altra colonna.	Non supporta la corrispondenza dei prefissi.
rum_tsquery_ops	Indicizzazione dei valori tsquery archiviati 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 ranking rapido 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:

   sql 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 campionati:

   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 le prestazioni 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 per 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:*');
   

Indice per la ricerca hash ottimizzata

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 per prefisso. L'esempio seguente presuppone che tu abbia una tabella denominata documents con una colonna search_vector.

1. Crea l'indice RUM utilizzando la classe di operatori 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 per testo e ordinano per un altro campo, ad esempio un timestamp. Questo pattern memorizza il valore del campo aggiuntivo direttamente nell'indice, evitando un'operazione di ordinamento lenta dopo la ricerca. L'esempio seguente presuppone che tu abbia 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 sia l'ordinamento:

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

Indice per la ricerca di array

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 su array che si sovrappongono (&&), contengono (@>) o sono contenuti da (<@) 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 contengono 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 con rappresentazione in virgola mobile. Queste classi di operatori ti consentono di utilizzare l'operatore <=> per calcolare in modo efficiente la distanza tra i valori. L'esempio seguente presuppone che tu abbia 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 di hash ottimizzata ordinata 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 la corrispondenza dei prefissi. L'esempio seguente presuppone che tu abbia una tabella denominata documents con una colonna search_vector e una colonna timestamp published_at.

1. Crea un indice RUM che utilizzi la classe di operatori 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 in base alla 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 una "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:

   sql 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 di 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 nella 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

• Scopri di più sulla ricerca a testo intero.
• Scopri come eseguire una ricerca di somiglianza vettoriale ibrida.