Ricerca a testo intero utilizzando pg_textsearch

L'estensione pg_textsearch in Cloud SQL per PostgreSQL fornisce la ricerca a testo intero utilizzando l'algoritmo di punteggio BM25 (Best Matching 25) standard di settore per fornire un punteggio di rilevanza altamente accurato. L'estensione è un progetto open source disponibile su GitHub.

L'estensione pg_textsearch richiede PostgreSQL 17 o versioni successive.

Poiché pg_textsearch supporta la frequenza inversa del documento, la saturazione di frequenza del termine e la normalizzazione della lunghezza del documento, può produrre risultati più pertinenti rispetto alla funzione ts_rank integrata di PostgreSQL. Inoltre, poiché opera direttamente sulle pagine di archiviazione PostgreSQL standard, non è necessario installare un motore esterno come Elasticsearch o preoccuparsi della manutenzione di cluster doppi e della sincronizzazione dei dati. Utilizzando pg_textsearch, puoi creare esperienze di ricerca solide, scalabili e altamente pertinenti senza uscire dall'ecosistema PostgreSQL.

Esistono diverse opzioni per la ricerca testuale in Cloud SQL per PostgreSQL:

  • Corrispondenza esatta: con la ricerca SQL standard, utilizzi le istruzioni LIKE e ILIKE per cercare una sequenza esatta di caratteri. Una query di esempio potrebbe essere ILIKE '%smart fitness watches%', che troverebbe istanze come:

    • "Scopri i nostri nuovi smartwatch per il fitness in offerta."
    • "Gli smartwatch per il fitness sono ottimi regali."

    Tuttavia, non troverebbe:

    • "I runner vogliono uno smartwatch per il fitness impermeabile."
    • "Questo orologio è particolarmente smart per il fitness."
  • Ricerca a testo intero: utilizzando l'algoritmo BM25 e tsvector, la ricerca a testo intero suddivide il testo in parole radice, ignora le parole filtro e assegna un punteggio alla pertinenza. Comprende le regole della lingua, inclusi i plurali e la grammatica, e tiene conto della frequenza delle parole. Una ricerca a testo intero di smart AND fitness AND watches troverebbe le istanze che la query di corrispondenza esatta di esempio non troverebbe, oltre a istanze più complicate come queste:

    • "Uno smartwatch è perfetto per la tua routine di fitness quotidiana."
    • "Non tutti gli orologi sono così smart quando si tratta di fitness."

    Tuttavia, non troverebbe:

    • "Uno smartwatch intelligente e attento alla salute può aiutarti nel tuo programma di allenamento."
    • "Questo braccialetto intelligente per il monitoraggio dell'attività fisica è un vero affare."
  • Ricerca semantica: utilizzando modelli di AI, la ricerca semantica converte il testo in vettori e misura la distanza di somiglianza. Comprende il significato, l'intent, il contesto, i sinonimi e i concetti correlati. Una ricerca semantica di smart fitness watches troverebbe le istanze di esempio che gli altri metodi di ricerca non troverebbero. Capirebbe che un "orologio intelligente per la salute" è lo stesso di uno "smartwatch per il fitness" e che anche un "braccialetto intelligente per il monitoraggio dell'attività fisica" potrebbe essere pertinente. Potrebbe erroneamente trovare una corrispondenza con un contapassi se non ha dato sufficiente priorità alla parola "orologio".

Installa l'estensione pg_textsearch

Per installare e attivare pg_textsearch, segui questi passaggi:

  1. Imposta il flag cloudsql.enable_pg_textsearch su on come descritto in Configura i flag di database. In questo modo, ottieni pg_textsearch in più rispetto allo spazio di archiviazione di cui già disponi (shared_preload_libraries).

  2. Installa l'estensione pg_textsearch

      CREATE EXTENSION pg_textsearch;
    
  3. Verifica l'installazione:

      SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch'
    

Cerca utilizzando pg_textsearch

Supponiamo che la seguente tabella di esempio sia stata compilata con i dati:

CREATE TABLE documents (
    doc_id TEXT PRIMARY KEY,
    content TEXT,
    text_embedding vector(3072)
    GENERATED ALWAYS AS (embedding('gemini-embedding-001', content)) STORED 
  );

Prima di utilizzare la ricerca a testo intero, crea un indice BM25:

CREATE INDEX idx_docs_bm25
      ON documents
      USING bm25 (content)
      WITH (text_config = 'english');

Puoi quindi eseguire una query di ricerca a testo intero come questa:

SELECT doc_id, content, content <@> 'database system'
      AS score FROM documents
      ORDER BY content <@> 'database system'
      ASC LIMIT 5;

Esegui ricerche ibride utilizzando pg_textsearch e vector

Puoi utilizzare pg_textsearch insieme all'estensione di ricerca semantica vector per eseguire ricerche ibride. Installa l'estensione di ricerca semantica vector in questo modo:

CREATE EXTENSION IF NOT EXISTS vector CASCADE;

Esegui una query di ricerca semantica e a testo intero ibrida come questa:

WITH
  -- Semantic search results
  vector_search AS (
    SELECT doc_id,
      RANK () OVER (ORDER BY text_embedding <=>
                             google_ml.embedding('gemini-embedding-001',
                                                 'database')::VECTOR ) AS rank
      FROM documents
      ORDER BY text_embedding <=> google_ml.embedding('gemini-embedding-001',
                                                      'database')::VECTOR
    LIMIT 10
  ),
  -- Full text search results
  text_search AS (
    SELECT doc_id,
      RANK () OVER (ORDER BY content <@> 'database' ASC) AS rank
    FROM documents
    ORDER BY content <@> 'database' ASC
    LIMIT 10
  )
  -- RRF combining both semantic and full text search results
  SELECT
    COALESCE(vector_search.doc_id, text_search.doc_id) AS id,
    COALESCE(1.0 / (60 + vector_search.rank), 0.0) +
    COALESCE(1.0 / (60 + text_search.rank), 0.0) AS rrf_score
  FROM vector_search
    FULL OUTER JOIN text_search ON vector_search.doc_id = text_search.doc_id
  ORDER BY rrf_score DESC
  LIMIT 5;

Flag per la configurazione dell'estensione pg_textsearch

Utilizza i seguenti flag per configurare la ricerca a testo intero di pg_textsearch: