Búsquedas Textuales Avanzadas en PostgreSQL: Utilizando tsvector y tsquery

Por
Diego González Rodríguez
-
0
177
MacBook Pro en un escritorio con fragmentos de código en pantalla, relacionados con PostgreSQL, TSVector, TSQuery, y operadores LIKE e ILIKE para búsqueda de texto en bases de datos
Imagen de portada que representa la optimización de búsqueda de texto en bases de datos usando PostgreSQL y técnicas de Full Text Search.

Índice

  1. Introducción
  2. Entorno
  3. Escenario de prueba
  4. Pruebas con operadores LIKE e ILIKE
  5. Full Text Search: tsvector y tsquery
  6. Conclusiones
  7. Glosario de términos
  8. Referencias bibliográficas

1. Introducción

Uno de los problemas más recurrentes en el desarrollo de software es la necesidad de realizar búsquedas textuales en múltiples campos para devolver resultados con coincidencias parciales. Un ejemplo típico es un buscador de usuarios, en el cual, a partir de una cadena de texto string, se buscan todos los usuarios cuyo nombre, apellidos o correo electrónico comienzan con dicho valor.

La solución más común es utilizar los operadores LIKE e ILIKE, combinando condiciones OR en los campos a filtrar. Aunque esta aproximación es funcional, su rendimiento puede degradarse considerablemente a medida que aumenta el tamaño de la tabla, lo cual puede afectar la experiencia del usuario.

PostgreSQL nos ofrece una solución de búsqueda de texto completo (Full Text Search) que encaja para este tipo de problemas: los tipos de datos tsvector y tsquery.

2. Entorno

  • Hardware: Portátil MacBook Pro 13″ (Apple M1 Pro, 32GB DDR4)
  • Sistema Operativo: macOS Sequoia 15.1
  • Base de Datos: PostgreSQL 15.2

3. Escenario de prueba

En nuestro escenario de pruebas, utilizaremos una base de datos PostgreSQL 15 en la que crearemos una tabla de usuarios con tres campos de tipo texto: nombre, apellido y correo electrónico. Realizaremos búsquedas parciales en estos campos utilizando diferentes enfoques.

Para evaluar las diferencias entre los distintos métodos de consulta, llenaremos la tabla con dos millones y medio de registros.

Utilizamos este fichero docker-compose.yml para iniciar un contenedor con PostgreSQL 15.2.

version: '3.9'
services:
  postgres-admin:
    image: postgres:15.2-alpine3.17
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres

Y el siguiente script para crear la tabla:

CREATE TABLE users
(
    id                  INTEGER     PRIMARY KEY,
    name                TEXT        NOT NULL,
    surname             TEXT        NOT NULL,
    email               TEXT        NOT NULL
);

Para llenar la tabla users, creamos un pequeño programa en Java:

Código Java para insertar 2.5 millones de registros en una tabla de usuarios en PostgreSQL, con campos de nombre, apellido y correo electrónico generados dinámicamente.
Código en Java para poblar una tabla de usuarios en PostgreSQL con 2.5 millones de registros de prueba.

4. Pruebas con operadores LIKE e ILIKE

Nuestra primera prueba será utilizar el operador LIKE para calcular el número de registros en los que alguno de los campos name, surname o email comienza con el texto ‘email111’.

SELECT count(*) FROM users 
WHERE name LIKE 'email111%' OR surname LIKE 'email111%' OR email LIKE 'email111%';

Esta consulta tarda aproximadamente 300 ms.

Resultado de la consulta con operador LIKE sobre los campos name, surname y email de la tabla users.
Resultado de la consulta con operador LIKE en el campo name de la tabla users.

Podemos considerar utilizar un único campo para realizar la búsqueda con LIKE, de modo que dicho campo contenga la concatenación de los campos name, surname y email. De esta forma, evitamos emplear múltiples operadores OR.

Ejecutamos el siguiente script SQL, que se encarga de crear la nueva columna y concatenar los campos existentes:

ALTER TABLE users 
ADD search_text_field TEXT;

UPDATE users 
SET search_text_field = name || ' ' || surname || ' ' || email;

Ejecutamos la consulta sobre el nuevo campo:

SELECT count(*) FROM users 
WHERE search_text_field LIKE '%email111%';

En este caso, la consulta es más lenta (438 ms), debido a que utilizamos LIKE con ‘%’ tanto al inicio como al final para garantizar coincidencias con el valor de cualquiera de los tres campos.

Resultado de la consulta con operador LIKE sobre un único campo que contiene el valor de los campos name, surname y email de la tabla users.
Resultado de la consulta con operador LIKE sobre un único campo que contiene el valor de los campos name, surname y email de la tabla users.

5. Full Text Search: tsvector y tsquery

PostgreSQL ofrece una solución de búsqueda de texto completo (Full Text Search) basada en los tipos de datos tsvector y tsquery, que puede aplicarse a nuestro caso de pruebas. Más información en la documentación oficial de PostgreSQL.

En PostgreSQL, un documento puede ser un campo de texto en una tabla, la concatenación de varios campos o una definición dinámica:

SELECT title || ' ' || author || ' ' || abstract || ' ' || body AS document
FROM messages
WHERE mid = 12;

SELECT m.title || ' ' || m.author || ' ' || m.abstract || ' ' || d.body AS document
FROM messages m, docs d
WHERE m.mid = d.did AND m.mid = 12;

Se recomienda utilizar la función COALESCE para combinar campos, ya que, si uno de los campos es nulo, todo el documento se considerará nulo y generará un nulo al ser utilizado en cualquier expresión:

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;

El tipo de datos tsvector representa un documento de forma optimizada para realizar búsquedas. Extrae las distintas palabras del documento y almacena información sobre su posición y su relevancia dentro del mismo.

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

Cada palabra se normaliza y se reduce a su lexema (por ejemplo, se almacena rat en lugar de rats en el ejemplo anterior). Además, se eliminan ciertos artículos, conjunciones y signos de puntuación según el idioma. PostgreSQL cuenta con varios diccionarios que ofrecen soporte para un amplio número de idiomas.

Añadimos un campo de tipo tsvector a nuestra tabla, que se llenará automáticamente con el contenido de las columnas name, surname y email. Además, especificamos que el idioma para el análisis de tokens sea ‘simple’, de modo que no se aplique ningún diccionario y no se excluya ninguna palabra.

ALTER TABLE users 
ADD search_tsvector_field tsvector
    GENERATED ALWAYS AS (to_tsvector('simple', coalesce(name, '') || ' ' || coalesce(surname, '') || ' ' || coalesce(email, '')))
    STORED;

CREATE INDEX searchtsvectorfld_idx ON users USING GIN (search_tsvector_field);

También creamos un índice de tipo GIN asociado al nuevo campo tsvector.

PostgreSQL ofrece dos tipos de índices para trabajar con campos tsvector: GIST y GIN. Más información en la documentación oficial.

En nuestro ejemplo, hemos optado por un índice GIN, que, aunque ocupa algo más de espacio, garantiza que no se produzcan falsos positivos y es más rápido en operaciones de lectura.

Ejecutamos nuevamente la consulta:

SELECT * FROM users 
WHERE search_tsvector_field @@ to_tsquery('simple', 'email111:*');

El resultado es espectacular: ¡7 ms! Hemos obtenido un incremento de rendimiento de más del 95 % en comparación con el uso del operador LIKE.

Captura de pantalla mostrando el resultado de una consulta de conteo en PostgreSQL utilizando el campo tsvector para búsqueda de texto completo en la tabla users.
Resultado de una consulta en PostgreSQL usando tsvector para optimizar la búsqueda de texto completo.

El tipo de dato tsquery modela una lista de palabras normalizadas sobre las que podemos aplicar expresiones booleanas, lo que nos permite realizar búsquedas más flexibles concatenando condiciones.

SELECT * FROM users 
WHERE search_tsvector_field @@ to_tsquery('simple', 'name111:* | email4:*');

Las funciones to_tsvector() y to_tsquery() convierten una cadena de texto en un tsvector y en un tsquery, respectivamente.

6. Conclusiones

Mediante soluciones específicas de bases de datos, es posible alcanzar un rendimiento significativamente superior al que ofrecen las soluciones SQL estándar. Es siempre recomendable estudiar la documentación de las herramientas que utilizamos para identificar posibles beneficios y optimizaciones.

A continuación se presentan las soluciones que ofrecen otros motores de bases de datos para búsquedas de texto completo (Full Text).

7. Glosario de términos

  • tsvector: Tipo de dato en PostgreSQL que representa un documento en una forma optimizada para búsquedas de texto completo, almacenando lexemas y sus posiciones dentro del texto.
  • tsquery: Tipo de dato en PostgreSQL que representa una consulta de texto completo, permitiendo buscar lexemas específicos utilizando operadores booleanos.
  • Full Text Search (Búsqueda de Texto Completo): Funcionalidad que permite realizar búsquedas eficientes en grandes volúmenes de texto, analizando y comparando lexemas en lugar de simples cadenas de caracteres.
  • Lexema: Forma canónica o raíz de una palabra utilizada en el procesamiento de lenguaje natural para representar todas sus variantes.
  • Índice GIN (Generalized Inverted Index): Tipo de índice en PostgreSQL diseñado para manejar eficientemente datos que contienen múltiples valores por fila, como los tipos tsvector, mejorando el rendimiento en búsquedas de texto completo.
  • Índice GiST (Generalized Search Tree): Tipo de índice flexible en PostgreSQL que puede ser utilizado para una variedad de datos y consultas, incluyendo búsquedas espaciales y de texto completo.
  • Tokenización: Proceso de dividir un texto en unidades más pequeñas llamadas tokens (palabras, frases, símbolos) para facilitar su análisis y procesamiento.
  • COALESCE: Función en SQL que devuelve el primer valor no nulo de una lista de expresiones, utilizada para manejar valores nulos en consultas y operaciones.
  • Normalización: En el contexto de búsquedas de texto, es el proceso de transformar palabras a una forma estándar, eliminando diferencias como mayúsculas, acentos o variaciones morfológicas.
  • Diccionario (en PostgreSQL): Conjunto de reglas y listas de palabras utilizado durante el análisis de texto para eliminar palabras irrelevantes (stop words) y realizar la derivación de lexemas.

8. Referencias bibliográficas

DEJA UNA RESPUESTA

Por favor ingrese su comentario!

He leído y acepto la política de privacidad

Por favor ingrese su nombre aquí

Información básica acerca de la protección de datos

  • Responsable:
  • Finalidad:
  • Legitimación:
  • Destinatarios:
  • Derechos:
  • Más información: Puedes ampliar información acerca de la protección de datos en el siguiente enlace:política de privacidad