Indexando ando: Índices GIN

PostgreSQL tiene varios tipos de índices: B-tree, Hash, GiST, Gin y SP-GiST. Obviamente cada uno de ellos cubre una necesidad específica. Por ejemplo, la documentación de PostgreSQL dice sobre los índices GIN:

Los índices GIN son índices invertidos que pueden manejar valores que contienen más de una clave, arreglos, por ejemplo. Como GiST y SP-GiST, GIN puede soportar diferentes estrategias de indexación definidos por el usuario y los operadores particulares con la que un índice GIN se puede utilizar varía en función de la estrategia de indexación. A modo de ejemplo, la distribución estándar de PostgreSQL incluye clases de operador GIN para arreglos unidimensionales, que admiten consultas indexadas utilizando estos operadores:

<@
@>
=
&&

(Consulte la Sección 9.18 para el significado de estos operadores.) Muchas otras clases de operadores GIN están disponibles en la colección contrib o como proyectos separados. Para obtener más información, consulte el Capítulo 57.

Así que los índices GIN pueden usarse para indexar los elementos de un arreglo, de un hstore y así.

Pero en esta ocasión hablaremos de uno de esos módulos contrib que proveen mas clases de operadores que pueden usarse con los índices GIN: pg_trgm.

Este módulo crea trigramas de cadenas de texto de modo que puede usarse para encontrar similitudes. Eso permite que los índices tipo GIN que usen la clase de operador gin_trgm_ops puedan ser usados en busquedas con LIKE aun cuando el comodín ‘%’ se encuentre al inicio del patrón de busqueda (por ejemplo: nombre LIKE ‘%jaime%’).

Para crear un índice que pueda usarse así, el índice debe crearse de este modo:

CREATE INDEX idx_gin ON table USING GIN (campo_texto gin_trgm_ops);

Con un índice como este he visto consultas bajar de más de 10s a unos pocos milisegundos; sin embargo, antes que corran a crear estos índices consideremos los problemas que tiene.

Considere la siguiente consulta “select show_trgm(‘Jaime Casanova’);” esta nos muestra los trigramas de una cadena de texto, en este caso 15 trigramas. Así que no es difícil imaginar que este tipo de índices crece mucho, y mientras mas grandes las cadenas de texto más crece el índice (debido a que habrá mas trigramas). Otra conclusión obvia es que mantener este tipo de índices puede ser costoso, de hecho pueden afectar mucho el rendimiento de INSERT y UPDATE, especialmente si hay varios de estos índices en la misma tabla, para disminuir un poco este problema se invento una técnica llamada fastupdate que consiste en mantener una lista no ordenada de pendientes. Así que los INSERT y UPDATE en lugar de insertar en el índice principal lo hacen en esta estructura de adicional hasta que ocurra un VACUUM o hasta que la lista de pendientes se haga mas grande que work_mem. Los inconvenientes son: 1) al leer el índice también debe leerse esta estructura adicional, lo que puede afectar al rendimiento de las consultas; y 2) un INSERT o UPDATE puede causar que la lista de pendientes se crezca demasiado y por lo tanto empezará procesar la lista de pendientes lo que afectará a ese INSERT o UPDATE y a todas las demás operaciones que esten ocurriendo de forma concurrente sobre esa tabla.

En conclusión; un índice GIN junto con el módulo pg_trgm puede ayudar enormemente al rendimiento de algunas consultas, sin embargo no se debe abusar de ellos pues pueden resultar un arma de doble filo.