Ricerca a testo intero utilizzando pg_textsearch

L'estensione pg_textsearch in Cloud SQL per PostgreSQL fornisce la ricerca full-text utilizzando l'algoritmo di valutazione BM25 (Best Matching 25) standard del settore per fornire un punteggio di pertinenza estremamente 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 del documento inversa, la saturazione della frequenza dei termini 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 devi installare un motore esterno come Elasticsearch o preoccuparti della manutenzione del doppio cluster 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 di testo 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:

    • "Esplora i nostri nuovi smartwatch per il fitness in offerta."
    • "Gli smartwatch per il fitness sono un regalo perfetto."

    Tuttavia, non riuscirebbe a trovare:

    • "I runner vogliono uno smartwatch impermeabile per il fitness."
    • "Questo orologio è particolarmente smart per quanto riguarda 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 valuta la pertinenza. Comprende le regole linguistiche, inclusi plurali e grammatica, e tiene conto della frequenza delle parole. Una ricerca full-text di smart AND fitness AND watches troverebbe le istanze che la query di corrispondenza esatta dell'esempio non troverebbe, nonché istanze più complicate come queste:

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

    Tuttavia, non riuscirebbe a trovare:

    • "Uno smartwatch intelligente che monitora la salute può aiutarti nel tuo programma di allenamento."
    • "Questa smartband per il monitoraggio dell'attività fisica è un 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'intento, 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 uno "smartwatch intelligente per la salute" è lo stesso di uno "smartwatch per il fitness" e che anche un "braccialetto intelligente per il monitoraggio dell'esercizio fisico" potrebbe essere pertinente. Potrebbe corrispondere erroneamente a un braccialetto pedometro se non ha dato la priorità sufficiente 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 flag di database. In questo modo, pg_textsearch viene aggiunto a 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');

A questo punto, puoi creare 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 nel seguente modo:

CREATE EXTENSION IF NOT EXISTS vector CASCADE;

Crea 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 full-text di pg_textsearch: