Con bastante frecuencia nos encontramos que nuestros clientes sufren problemas de rendimiento debido a búsquedas sobre campos de texto. Permitir filtrados “libres” sobre textos, descripciones, documentos, etc. hace que las operaciones de búsqueda se vuelvan pesadas y la experiencia del usuario se degrade.

En el diseño de nuestros modelos de datos casi siempre acaban apareciendo ciertos campos donde se almacena información textual sobre la que posteriormente se realizarán labores de búsqueda. Para acelerar este tipo de operaciones en muchas ocasiones no disponemos de estructuras de indexación adecuadas y nos encontramos que ni los árboles B ni las tablas hash nos puedan ayudar a mejorar el rendimiento.

A lo largo de los años nos hemos topado muchas veces con problemáticas donde era necesario crear “indexación especializada”. En estos dos posts podéis ver algunas técnicas que son útiles para crear indexación especializada en SQL Server: https://blogs.solidq.com/es/sql-server/tecnicas-de-apoyo-la-indexacion-tradicional/ y https://blogs.solidq.com/es/sql-server/combinando-ngrams-y-fulltext-search/

Una de las características más llamativas de PostgreSQL es su carácter abierto y la gran cantidad de extensiones disponibles. Para esta problemática concreta existe una extensión llamada pg_trgm que está lista para ser usada y que nos permite crear índices basados en trigrams.

Un trigram es una secuencia de tres caracteres consecutivos que podemos encontrar en una cadena de texto. Por ejemplo los trigrams básicos de “Verne” serían “Ver”, “ern” y “rne”. Gracias a esta extensión podremos realizar esta descomposición y su indexación de forma transparente y automática, acelerando de forma enorme el rendimiento de las búsquedas.

Vamos a comenzar activando esta extensión en nuestra base de datos:

query editor create extension postgresql

¿Necesitas sacar más partido a tus datos para impulsar tu proyecto?

Acelera tus procesos de Business Analytics. Toma mejores decisiones, optimiza tu Datawarehouse y el proceso de generación de informes en tus proyectos de analítica con nuestra consultoría en Business Intelligence.

Voy a echar un vistazo

Una vez activada, comprobamos qué trigrams se generan para una cadena de prueba:

select query create extension postgresql

Podemos ver como realmente PostgreSQL no está generando únicamente los trigrams básicos sino también está considerando los inicios y finales de las palabras que pueden tener menos de tres caracteres. De esta forma también podemos mejorar el rendimiento cuando la búsqueda incluya menos de tres caracteres.

También esta librería nos permite cálculos “fuzzy” de similitud entre textos usando la función similarity:

Indexación con Trigrams en PostgreSQL

Por defecto un valor de 0.3 o superior considera que las cadenas son suficientemente similares, como ocurre con los dos primeros casos del ejemplo.

Podemos validar esta similitud con el operador de similaridad ‘%’:

Indexación con Trigrams en PostgreSQL

Volviendo a nuestra problemática inicial, vamos a crear una tabla para almacenar los textos sobre los que queremos realizar búsquedas:

query editor postgresql

El siguiente paso será rellenar dicha tabla con un volumen de texto importante para poder así comparar el rendimiento. Para ello crearemos una función que genere textos aleatorios de forma eficiente:

CREATE OR REPLACE FUNCTION random_text(longitud INTEGER)
RETURNS TEXT
LANGUAGE plpgsql AS $$
DECLARE
tope int;
BEGIN
tope := CEIL(longitud / 32.)::integer;
return lower(
substring(
(SELECT string_agg(md5(random()::TEXT), ) FROM generate_series(1, tope)
), 1, longitud) );
END
$$;

Esta función utiliza algunos trucos para mejorar el rendimiento como generar los textos a partir del MD5 de un flotante aleatorio y concatenarlos tantas veces como sean necesarios teniendo en cuenta la longitud del MD5 (32 caracteres).

A continuación vamos a generar 1000000 registros con cadenas de 500 caracteres y de 30000 caracteres aleatorios apoyándonos en dicha función:

insert into textos (
descripcion, texto
)
select
random_text(500),
random_text(30000)
from generate_series(1, 100000)
 

Si hemos insertado 1000000 filas con unos 30 KB por fila tendremos aproximadamente 30 GB de texto en total. Si realizamos una búsqueda para localizar algunos registros podemos ver que es una operación costosa, tanto sobre el campo descripción como, especialmente, sobre el campo texto:

postgresql select count
Indexación con Trigrams en PostgreSQL

El siguiente paso lógico que normalmente nos plantearíamos es intentar indexar estos campos con un índice tradicional. Vamos a crear un par de índices uno por cada columna y analizaremos si tenemos alguna mejora.

Al intentarlo nos encontramos con que un índice no puede tener más de 8191 bytes en su clave, por lo que no es posible crearlo sobre nuestra columna de tipo texto:

Indexación con Trigrams en PostgreSQL

Si comprobamos el rendimiento sobre la columna dirección, que es inferior a 8191 bytes, vemos que el añadir el índice tradicional el optimizador decide no usarlo. Si utiliza sin embargo paralelismo para la operación de lectura de la tabla:

postgresql node type

Si forzamos el uso del índice, vemos que podemos rascar algunas milésimas, quedando el tiempo ligeramente por debajo de 1 segundo:

postgresql node type description
postgresql select count data output success

A continuación vamos a crear un índice basado en trigrams en cada una de las columnas:

postgresql select count data output success query returned

Una vez tenemos el índice creado si lanzamos la misma query obtenemos un resultado ya considerablemente mejor, bajando menos de 45 milisegundos la búsqueda por descripción:

postgresql select count data output success total query
postgresql select count data output description

En el caso de la búsqueda por el campo de texto, de mayor tamaño, el tiempo disminuye hasta unos 4 segundos aproximadamente:

postgresql select count total query
Indexación con Trigrams en PostgreSQL

Queda probado por tanto que la mejora en la eficiencia al utilizar este tipo de índices especializados al realizar filtrados de tipo LIKE ‘%patron%’ es enorme:

postgresql duration

En cualquier motor de base de datos es crítico entender las posibilidades que nos ofrece a nivel de indexación para maximizar su rendimiento. En el caso de PostgreSQL tenemos una extensión que nos permite crear índices trigram que acelerarán de forma considerable nuestras búsquedas de texto libre.

En los casos en los que no exista ningún tipo de índice que de forma directa pueda mejorar el rendimiento nos plantearemos pensar en soluciones alternativas. Para ello podremos necesitar conocer y apoyarnos en las opciones de extensibilidad que cada motor relacional específicamente nos ofrezca.

¡Has llegado al final! Parece que te ha gustado nuestro post sobre PostgreSQL

Recuerda que, acelerar tus procesos de Business Analytics es imprescindible para tomar mejores decisiones para tu negocio. Optimiza tu Datawarehouse y el proceso de generación de informes en tus proyectos de analítica con nuestra consultoría en Business Intelligence.

Voy a Echar un vistazo
1 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like