Nota del 29 de octubre de 2020
Han pasado 8 años desde que escribí este post, pero sigue teniendo plena vigencia, y parece que sigue siendo uno de los más visitados del blog. Así que he actualizado la versión de la documentación, y un par de pequeños cambios para ponerlo al día. Que lo disfrutéis.

PostgreSQL tiene unas funciones bastante potentes para búsqueda de texto (Full Text Search), es decir, búsqueda de palabras claves estilo Google. Su uso es relativamente complejo, pero hay una buena documentación en la propia web: Chapter 12. Full Text Search.

Sin embargo, he escrito un resumen para gente con prisa, con las opciones más frecuentes. Helo aquí:

¿Qué es un documento?

Es la unidad mínima de búsqueda. En Postgres, un documento es un campo en una fila de una tabla, o quizá una concatenación de varios campos, de una misma tabla o de más de una con un join:

1
2
3
4
SELECT m.title || ' ' || m.author || ' ' ||
       m.abstract || ' ' || d.body AS document
 FROM messages m, docs d
 WHERE m.id = d.id AND m.id = 12;

Al combinar campos suele ser conveniente usar la función coalesce para convertir los valores NULL en ", de otro modo si un campo es nulo, el documento entero será nulo, por la propiedad de que tiene el valor NULL de que genera NULL al participar en cualquier expresión.

1
2
3
4
5
6
SELECT coalesce(m.title,'') || ' ' ||
       coalesce(m.author,'') || ' ' ||
       coalesce(m.abstract,'') || ' ' ||
       coalesce(d.body,'') AS document
 FROM messages m, docs d
 WHERE m.id = d.id AND m.id = 12;

¿Qué es un tsvector?

Es un tipo de datos de Postgres, que consiste en una lista de palabras extraidas de un documento. Las palabras están normalizadas, es decir, se eliminan las palabras “stopwords” (artículos, conjunciones, etc.) y los signos de puntuación, y el resto de palabras se reducen a su lexema básico. Finalmente se añade a cada palabra en qué posición o posiciones del documento aparece.

Existe la función to_tsvector que convierte un string a un tsvector:

1
2
3
4
5
6
SELECT to_tsvector('english',
       'a Fat  Cat sat on a mat - it ate a fat rats');

                     to_tsvector
 -----------------------------------------------------
 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4

-> se han eliminado a, on, it, –

-> se ha convertido rats a rat

-> se ha convertido todo a minúscula y sin acentos (en este caso sin efecto porque el original no los tenía)

El parámetro ‘english’ indica qué ‘text configuration’ se va a usar para procesar las palabras. Postgres incorpora un montón de plugins con diccionarios, parseadores y otros trucos que procesan texto. Una configuración es una lista de plugins a aplicar, se puede definir con el comando CREATE TEXT SEARCH CONFIGURATION. Un ejemplo en 12.7. Configuration Example.

Más abajo hacemos un resumen de las opciones de configuración que hay.

Si no se indica la configuración, se toma la que tenga nuestra base de datos por defecto, o si no, la de nuestra instalación de Postgres. En una b.d. puede haber tantas configuraciones como queramos.

Una opción útil en muchos casos es asignar diferentes pesos a las palabras según la parte del documento en que aparecen. Para ello se usa la función setweight, que asigna un peso de A (el mayor) a D (el menor) a las palabras dentro de un tsvector. El peso, junto con la posición y otros valores, se puede usar para ordenar una búsqueda por relevancia (ver más abajo).

Aquí hay un ejemplo completo de calcular un tsvector, creando varios, asignando un peso diferente a cada uno, y concatenándolos todos juntos.

1
2
3
4
5
6
SELECT setweight(to_tsvector(coalesce(m.title,'')), 'A') ||
       setweight(to_tsvector(coalesce(m.author,'')), 'B') ||
       setweight(to_tsvector(coalesce(m.abstract,'')), 'C') ||
       setweight(to_tsvector(coalesce(d.body,'')), 'D') as vector
  FROM messages m, docs d
 WHERE m.id = d.id AND m.id = 12;

¿Qué es un tsquery?

Es otro tipo de datos, que también contiene una lista de palabras normalizadas, esta vez organizadas en forma de expresión booleana, con operadores & (and), | (or) y ! (not), y paréntesis.

También existe una función to_tsquery para convertir un string en un tsquery:

1
2
3
4
5
SELECT to_tsquery('english', 'Fat & (Rat | ! Cat)');

         to_tsquery
 ---------------------------
 'fat' & ( 'rat' | !'cat' )

Esta función necesita que el string original tenga un formato estricto. Hay otra función para convertir desde un string arbitrario (por ejemplo, lo que un usuario ha introducido en una caja de búsqueda). Es un modo más básico porque simplemente añade el operador & a todas las palabras.

1
2
3
4
5
SELECT plainto_tsquery('english', 'The Fat Rats');

  plainto_tsquery
 -----------------
 'fat' & 'rat'

¿Cómo se usan?

El uso más habitual es como filtro en una cláusula where, mediante el operador @@. Este operador aplica un tsquery contra un tsvector (en cualquier orden) y dice si coincide o no.

1
2
3
SELECT title
 FROM pgweb
 WHERE to_tsvector(body) @@ to_tsquery('friend');

En este ejemplo usamos la configuración por defecto en ambos casos. Otro ejemplo más elaborado:

1
2
3
4
5
6
7
SELECT title
 FROM pgweb
 WHERE to_tsquery('create & table') @@
       to_tsvector(coalesce(title,'') || ' ' ||
                   coalesce(body,''))
 ORDER BY last_mod_date DESC
 LIMIT 10;

Esta query devuelve las 10 páginas más recientes que contienen las palabras “create” y “table” en el título o el body. Tal como está, Postgres debe leer todas las filas y convertir los datos a tsvector cada vez. Para acelerar la cosa, lo normal es usar un índice de texto.

¿Cómo se crea un índice de texto?

Hay dos tipos de índice de texto, GIN y GIST. Explicaremos las diferencias más abajo. Ambos se crean igual:

1
2
3
4
5
6
7
CREATE INDEX pgweb_idx
          ON pgweb
       USING gin(to_tsvector('english', body));

CREATE INDEX pgweb_idx
          ON pgweb
       USING gist(to_tsvector('english', body));

Importante: en el índice siempre hay que indicar la configuración (en este caso ‘english’). Sólo se usará el índice si en la expresión to_tsvector del where hemos indicado explícitamente la configuración, y ésta coincide con la del índice.

En el caso de que nuestra búsqueda de texto use varias columnas, hay dos opciones:

a) Crear un índice compuesto:

1
2
3
4
CREATE INDEX pgweb_idx
          ON pgweb
       USING gin(to_tsvector('english', coalesce(title,'') ||
                             ' ' || coalesce(body,'')));

Así la segunda query del ejemplo de arriba usaría este índice, siempre y cuando le añadamos la configuración ‘english’ a la función to_tsvector.

b) Crear un campo agregado de tipo tsvector y copiar ahí los datos ya convertidos:

1
2
3
4
5
6
ALTER TABLE pgweb
 ADD COLUMN textsearchable_index_col tsvector;

UPDATE pgweb
   SET textsearchable_index_col = to_tsvector('english',
            coalesce(title,'') || ' ' || coalesce(body,''));

Este campo hay que mantenerlo actualizado, bien mediante el código de nuestra aplicación, o usando las facilidades que da PostgreSQL. En versiones anteriores había que escribir un trigger (12.4.3. Triggers for Automatic Updates). Pero ahora podemos crear “generated columns” con la cláusula GENERATED ALWAYS, como se explica en 12.2.2. Creating indexes.

Luego ya se puede crear el índice y buscar por ese campo:

1
2
3
4
5
6
7
8
9
CREATE INDEX textsearch_idx
          ON pgweb
       USING gin(textsearchable_index_col);

SELECT title
 FROM pgweb
 WHERE to_tsquery('create & table') @@ textsearchable_index_col
 ORDER BY last_mod_date DESC
 LIMIT 10;

Las diferencias entre ambos enfoques son:

– Usando el campo agregado no hace falta indicar la configuración, se pueden hacer queries con la config por defecto.

– El campo agregado ocupa más espacio, ya que hay que duplicar los datos. Además es una solución más complicada por la sincronización entre unos campos y otros.

– La velocidad en principio es la misma, pero si usamos un índice GIST, Postgres tiene que recalcular la fórmula en todas las filas encontradas, porque no es un índice determinista (genera falsos positivos), con lo que el índice compuesto es más lento. Con el índice GIN también hay que recalcular si se usan pesos, porque éstos no se guardan en el índice. Pero si no se usan, entonces es más o menos igual de rápida la solución a) que la b).

¿Qué índice debo usar, GIST o GIN?

Los índices GIST usan un hash de longitud fija, que es bastante eficiente en espacio. Pero puede ocurrir que varios documentos generen el mismo hash, por lo que en una búsqueda aparecerán ambos cuando quizá sólo se esté buscando uno de ellos. Por tanto, Postgres recorre todas las filas devueltas por el índice y calcula de nuevo el filtro en memoria para eliminar los sobrantes. Esta operación es lenta, y además, la lectura de las filas de la tabla que no son necesarias es más lenta aún. La probabilidad de conflicto aumenta cuantas más palabras distintas haya en la tabla, por lo que estos índices son buenos cuando los documentos no tienen muchas palabras (por debajo de 10.000). Es útil además definir una buena configuración que elimine todas las palabras posibles y normalice mucho.

Los GIN en cambio, no tienen estas limitaciones, pero ocupan bastante más espacio, y son más lentos de actualizar, aunque son más rápidos de leer. La regla general suele ser usar GIN si los datos cambian poco o si hay muchas palabras distintas, y GIST para datos muy dinámicos pero sin demasiadas palabras, o si el espacio es muy importante.

Hay un análisis más completo en 2.9. GiST and GIN Index Types.

¿Cómo ordenar los resultados por relevancia?

Hay dos funciones predefinidas (ts_rank y ts_rank_cd) que calculan la relevancia de un documento respecto de un tsquery, en función del nº de veces que se encuentra cada término de búsqueda, la posición dentro del documento, los pesos asignados a cada elemento del tsvector, etc. Cada una usa un algoritmo diferente, aunque también se pueden definir funciones propias si queremos usar nuestro propio algoritmo. Se usan así:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT title, ts_rank_cd(textsearch, query) AS rank
 FROM apod, to_tsquery('neutrino|(dark & matter)') query
 WHERE query @@ textsearch
 ORDER BY rank DESC
 LIMIT 10;
                 title                         |   rank
 ----------------------------------------------+----------
 Neutrinos in the Sun                          |      3.1
 The Sudbury Neutrino Detector                 |      2.4
 A MACHO View of Galactic Dark Matter          |  2.01317
 Hot Gas and Dark Matter                       |  1.91171
 The Virgo Cluster: Hot Plasma and Dark Matter |  1.90953
 Rafting for Solar Neutrinos                   |      1.9
 NGC 4650A: Strange Galaxy and Dark Matter     |  1.85774
 Hot Gas and Dark Matter                       |   1.6123
 Ice Fishing for Cosmic Neutrinos              |      1.6
 Weak Lensing Distorts the Universe            | 0.818218

Aquí buscamos los 10 documentos con más puntuación al buscar “neutrino” o “materia oscura” en la columna agregada “textsearch” de la tabla apod.

Para ver los distintos algoritmos y las opciones de personalización con pesos y varias formas de normalización, ver 12.3.3. Ranking Search Results.

¿Cómo se usan las configuraciones de búsqueda?

Una configuración es lo que convierte un string general en un tsvector. Está compuesta de un “parser” (que divide el string en tokens) y una lista de “diccionarios”, que procesan los tokens y los convierten en lexemas básicos.

Una instalación de PostgreSQL incluye varias configuraciones por defecto, y se pueden crear configuraciones nuevas en una base de datos. La variable de configuración default_text_search_config (definida en postgresql.conf o con un comando SET) indica la configuración a usar si no se indica una en una llamada a to_tsvector o to_tsquery. Para saber qué configuraciones vienen precargadas, se puede consultar el catálogo pg_catalog de la base de datos, por ejemplo.

Crear una configuración nueva

Lo normal será crearla copiando otra existente y luego modificándola, por ejemplo:

1
2
3
CREATE TEXT SEARCH CONFIGURATION public.pg (
    COPY = pg_catalog.english
);

Pero se puede crear desde cero con

1
2
3
CREATE TEXT SEARCH CONFIGURATION public.pg (
    PARSER = "default"
);

Usar un parser

PostgreSQL trae un parser por defecto que suele ser suficiente. Analiza un string y lo divide en una lista de tokens de distintos tipos, que se pueden ver en 12.5. Parsers.

Usar diccionarios

Un diccionario recibe un token y lo convierte en un lexema final, o en otro token, aplicando una transformación, o bien lo descarta (si por ejemplo es una stop word). Para añadirlo a una configuración hay que decirle a qué tipos de tokens se va a aplicar:

1
2
3
4
ALTER TEXT SEARCH CONFIGURATION pg
  ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
                    word, hword, hword_part
  WITH pg_dict, english_ispell, english_stem;

Este comando hace que a todos los tokens de tipo asciiword, asciihword, etc. se les apliquen los diccionarios pg_dict, english_ispell y english_stem, en ese orden. Los diccionarios se van aplicando hasta que alguno reconoce el token y devuelve un lexema o lo descarta, en cuyos casos se detiene la búsqueda.

Para eliminar diccionarios:

1
2
ALTER TEXT SEARCH CONFIGURATION pg
    DROP MAPPING FOR email, url, url_path, sfloat, float;

Esto hace que a los tokens de tipo email, url, etc. no se les aplica ningún diccionario, y por tanto son eliminados en el tsvector.

Crear un diccionario nuevo

Para crear un diccionario se usa el comando CREATE TEXT SEARCH DICTIONARY. Hay que indicarle una template y uno o más ficheros de configuración. Las templates están en el directorio contrib de Postgres, y los ficheros están en $SHAREDIR (ejecutar pg_config --sharedir para saber dónde está). Cada template tiene un formato distinto. Se pueden ver todos los templates incluidos en PostgreSQL en 12.6 Dictionaries.

  • Template “simple”. Mira si el token es una stopword; si lo es, lo descarta y si no, lo convierte a minúsculas.
  • Template “synonym”. Busca el token en una lista de sinónimos, y si lo encuentra, devuelve el sinónimo.
  • Template “thesaurus”. Similar a synonym pero más potente (busca frases, y reemplaza en función de si una palabra está junto a otras concretas o no).
  • Template “ispell”. Usa un fichero de diccionario “morfológico” para normalizar formas lingüisticas (plurales, género, tiempos de los verbos, etc.).
  • Template “snowball”. El mismo efecto que ispell, pero en vez de basarse en un diccionario usa algoritmos basados en reglas y expresiones regulares.
  • Template “unaccent”. Usa un fichero de mapeo para convertir todas las letras con acentos en una sin acentos.

Esta última template no está en el PostgreSQL por defecto, se añade instalando la extensión “unaccent” (en Ubuntu está incluida en apt-get install postgresql-contrib). Para activarla en nuestra instalación de PostgreSQL hay que escribir:

1
psql -c "create extension unaccent;" template1

Al hacerlo, se añade también una función unaccent() que se puede usar en sentencias SQL. Tanto la template como el diccionario no se crean en pg_catalog sino en el schema public. Más información en F.43. unaccent.

Un ejemplo. Para crear un diccionario de sinónimos basado en el fichero $SHAREDIR/tsearch_data/pg_dict.syn (que convierte los strings “postgresql”, “postgres” y “pgsql” en “pg”), ejecutar:

1
2
3
4
CREATE TEXT SEARCH DICTIONARY pg_dict (
    TEMPLATE = synonym,
    SYNONYMS = pg_dict
);

Con esto ya se puede incluir en la configuración nueva, tal como se veía arriba en “crear una configuración nueva”.