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://blogvisionarios.com/es/sql-server/tecnicas-de-apoyo-la-indexacion-tradicional/ y https://blogvisionarios.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:
¿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 vistazoUna vez activada, comprobamos qué trigrams se generan para una cadena de prueba:
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:
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 ‘%’:
Volviendo a nuestra problemática inicial, vamos a crear una tabla para almacenar los textos sobre los que queremos realizar búsquedas:
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:
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:
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:
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:
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:
Si forzamos el uso del índice, vemos que podemos rascar algunas milésimas, quedando el tiempo ligeramente por debajo de 1 segundo:
A continuación vamos a crear un índice basado en trigrams en cada una de las columnas:
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:
En el caso de la búsqueda por el campo de texto, de mayor tamaño, el tiempo disminuye hasta unos 4 segundos aproximadamente:
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:
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
