El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

La cerca de text complet (Full-Text Search, FTS) en PostgreSQL és una funcionalitat poderosa que permet realitzar cerques eficients i avançades en textos. Aquesta funcionalitat és especialment útil per a aplicacions que necessiten cercar paraules o frases dins de grans volums de text, com ara motors de cerca, aplicacions de gestió de continguts, i sistemes de gestió documental.

Conceptes clau

Abans de començar amb la implementació de la cerca de text complet, és important entendre alguns conceptes clau:

Document: Un text que es vol indexar i cercar.
Tokenització: El procés de dividir un document en paraules o tokens.
Diccionari: Un conjunt de regles que defineixen com es processen els tokens.
TSVector: Una representació del document que permet la cerca eficient.
TSQuery: Una representació de la consulta de cerca.

Creació d'un índex de text complet

Per utilitzar la cerca de text complet, primer hem de crear un índex de text complet en la taula que conté els documents. A continuació, es mostra com fer-ho:

Exemple pràctic

Suposem que tenim una taula articles amb les següents columnes:

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT
);

Pas 1: Afegir una columna TSVector

Afegim una columna tsvector per emmagatzemar la representació de text complet del document:

ALTER TABLE articles ADD COLUMN tsv_body TSVECTOR;

Pas 2: Actualitzar la columna TSVector

Actualitzem la columna tsvector amb el contingut de les columnes title i body:

UPDATE articles SET tsv_body = to_tsvector('english', title || ' ' || body);

Pas 3: Crear un índex GIN

Creem un índex GIN (Generalized Inverted Index) en la columna tsvector per millorar el rendiment de les cerques:

CREATE INDEX idx_tsv_body ON articles USING GIN(tsv_body);

Pas 4: Crear un trigger per mantenir l'índex actualitzat

Creem un trigger per assegurar-nos que la columna tsvector s'actualitzi automàticament quan es modifiquin les columnes title o body:

CREATE FUNCTION update_tsv_body() RETURNS TRIGGER AS $$
BEGIN
    NEW.tsv_body := to_tsvector('english', NEW.title || ' ' || NEW.body);
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsv_body_trigger
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION update_tsv_body();

Realitzar cerques de text complet

Un cop tenim l'índex creat, podem realitzar cerques de text complet utilitzant l'operador @@ per comparar un tsvector amb una tsquery.

Exemple de consulta

Suposem que volem cercar articles que continguin les paraules "PostgreSQL" i "performance":

SELECT id, title, body
FROM articles
WHERE tsv_body @@ to_tsquery('english', 'PostgreSQL & performance');

Funcions i operadors útils

to_tsvector(language, text): Converteix un text en un tsvector.
to_tsquery(language, query): Converteix una consulta de text en un tsquery.
plainto_tsquery(language, text): Converteix un text pla en un tsquery.
ts_rank(tsvector, tsquery): Calcula la rellevància d'un document respecte a una consulta.

Exemple de funcions

SELECT id, title, ts_rank(tsv_body, to_tsquery('english', 'PostgreSQL & performance')) AS rank
FROM articles
WHERE tsv_body @@ to_tsquery('english', 'PostgreSQL & performance')
ORDER BY rank DESC;

Exercicis pràctics

Exercici 1

Crea una taula documents amb les columnes id, title, i content. Afegeix una columna tsvector per emmagatzemar la representació de text complet del document, crea un índex GIN, i implementa un trigger per mantenir l'índex actualitzat.

Solució

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT
);

ALTER TABLE documents ADD COLUMN tsv_content TSVECTOR;

UPDATE documents SET tsv_content = to_tsvector('english', title || ' ' || content);

CREATE INDEX idx_tsv_content ON documents USING GIN(tsv_content);

CREATE FUNCTION update_tsv_content() RETURNS TRIGGER AS $$
BEGIN
    NEW.tsv_content := to_tsvector('english', NEW.title || ' ' || NEW.content);
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsv_content_trigger
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW EXECUTE FUNCTION update_tsv_content();

Exercici 2

Realitza una cerca de text complet en la taula documents per trobar documents que continguin les paraules "database" i "optimization".

Solució

SELECT id, title, content
FROM documents
WHERE tsv_content @@ to_tsquery('english', 'database & optimization');

Conclusió

La cerca de text complet en PostgreSQL és una eina poderosa per realitzar cerques eficients en grans volums de text. Amb l'ús de tsvector, tsquery, i índexs GIN, podem millorar significativament el rendiment de les cerques de text. A més, les funcions i operadors proporcionats per PostgreSQL permeten una gran flexibilitat en la creació de cerques avançades.

Cerca de text complet

Conceptes clau

Creació d'un índex de text complet

Exemple pràctic

Pas 1: Afegir una columna TSVector

Pas 2: Actualitzar la columna TSVector

Pas 3: Crear un índex GIN

Pas 4: Crear un trigger per mantenir l'índex actualitzat

Realitzar cerques de text complet

Exemple de consulta

Funcions i operadors útils

Exemple de funcions

Exercicis pràctics

Exercici 1

Solució

Exercici 2

Solució

Conclusió

Curs de PostgreSQL

Mòdul 1: Introducció a PostgreSQL

Mòdul 2: Operacions bàsiques de SQL

Mòdul 3: Consultes SQL avançades

Mòdul 4: Disseny de bases de dades i normalització

Mòdul 5: Funcionalitats avançades de PostgreSQL

Mòdul 6: Optimització i millora del rendiment

Mòdul 7: Seguretat i gestió d'usuaris

Mòdul 8: Treballant amb JSON i funcionalitats NoSQL

Mòdul 9: Extensions i eines avançades

Mòdul 10: Estudis de cas i aplicacions del món real