Índice
- Introducción
- Entorno
- Escenario de prueba
- Pruebas con operadores
LIKEeILIKE - Full Text Search:
tsvectorytsquery - Conclusiones
- Glosario de términos
- 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.
|
YAML
|
|
|---|---|
|
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:
|
SQL
|
|
|---|---|
|
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:
|
Java
|
|
|---|---|
|
import java.sql.Connection;
import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class SearchMain { public static void main(String[] args) throws SQLException { final Connection connection = connect(); for (int i = 0; i < 2500000; i++) { final var name = "name" + i; final var surname = "surname" + i; final var email = "email" + i; String str = String.format( "insert into users (id, name ,surname, email) values (%s, '%s', '%s', '%s');", i, name, surname, email ); createItem(connection, str); } } public static Connection connect() throws SQLException { final String url = "jdbc:postgresql://localhost:5432/postgres"; final String user = "postgres"; final String password = "postgres"; return DriverManager.getConnection(url, user, password); } public static void createItem(Connection connection, String query) throws SQLException { Statement stmt = connection.createStatement(); stmt.executeUpdate(query); } } |
|
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’.
|
SQL
|
|
|---|---|
|
SELECT COUNT(*) FROM users
WHERE name LIKE 'email111%' OR surname LIKE 'email111%' OR email LIKE 'email111%'; |
|
Esta consulta tarda aproximadamente 300 ms.
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:
|
SQL
|
|
|---|---|
|
ALTER TABLE users
ADD search_text_field TEXT; UPDATE users SET search_text_field = name || ' ' || surname || ' ' || email; |
|
Ejecutamos la consulta sobre el nuevo campo:
|
SQL
|
|
|---|---|
|
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.
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:
|
SQL
|
|
|---|---|
|
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:
|
SQL
|
|
|---|---|
|
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.
|
SQL
|
|
|---|---|
|
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.
|
SQL
|
|
|---|---|
|
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:
|
SQL
|
|
|---|---|
|
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.
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.
|
SQL
|
|
|---|---|
|
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).
- Oracle: Documentación de Oracle Database
- SQL Server: Búsqueda de Texto Completo en SQL Server
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
- PostgreSQL Global Development Group. (2023). Índices de Búsqueda de Texto Completo. En Documentación de PostgreSQL 15. Disponible en: https://www.postgresql.org/docs/15/textsearch-indexes.html
- Hernández, J. (2021). Mastering PostgreSQL 13: Expert techniques to build scalable, reliable, and fault-tolerant database applications. Packt Publishing.
- Douglas, K. (2019). Beginning PostgreSQL on the Cloud: Simplifying Database as a Service on Cloud Platforms. Apress.
- Oracle Corporation. (2023). Oracle Text Application Developer’s Guide. Disponible en: https://docs.oracle.com/en/database/oracle/oracle-database/19/ccapp/index.html
- Microsoft Corporation. (2023). Búsqueda de Texto Completo (Full-Text Search). En Documentación de SQL Server. Disponible en: https://learn.microsoft.com/es-es/sql/relational-databases/search/full-text-search
