Indexación de arrays: creando índices GIN y BTREE

Imagina que tienes una tabla con millones de registros, y una de las columnas guarda arrays. Por ejemplo, tenemos una tabla products, y cada producto puede pertenecer a varias categorías:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- Array de strings para guardar las categorías del producto
);

Supón que quieres encontrar todos los productos que pertenecen a la categoría electronics. Si usas el operador @> para buscar, puede que termine haciendo un escaneo completo de la tabla:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

Un escaneo completo (Seq Scan) es lento. Especialmente si la tabla es enorme. Los índices vienen al rescate para convertir esto en una búsqueda mucho más rápida.

Tipos de índices para arrays

PostgreSQL soporta dos tipos principales de índices que puedes usar con arrays:

1. GIN (Generalized Inverted Index) — ideal para buscar rápido elementos dentro de un array o comprobar intersecciones.
2. BTREE (Binary Tree) — sirve para otras operaciones, como comparar arrays exactamente.

Vamos a ver cada uno con más detalle.

1. Índice GIN: velocidad luz

GIN (Generalized Inverted Index) es un índice que va genial para trabajar con operadores como:

• @> (el array contiene un elemento u otro array),
• <@ (el array está contenido en otro array),
• && (los arrays se cruzan/intersecan).

Así puedes crear un índice GIN para nuestra columna categories:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Después de crear el índice, las consultas van a ir mucho más rápido. Por ejemplo, esta consulta:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

va a usar tu índice GIN.

Dato curioso: El índice GIN funciona como una lista invertida — guarda qué elementos (por ejemplo, strings) están en qué registros. Es como el índice al final de un libro para encontrar un tema por número de página. ¡Mola, ¿no?!

2. Índice BTREE: cuando importa el orden

BTREE (Binary Tree) es el índice estándar que se usa en la mayoría de bases de datos. Va bien para operaciones que necesitan comparar arrays exactamente, como:

• Comprobar igualdad de arrays =,
• Comparar arrays por el orden de los elementos (>, <).

Puedes crear un índice BTREE para un array así:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Ejemplo de consulta que puede usar el índice BTREE:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Pero ojo, los índices BTREE no sirven para operadores como @> o <@. Para eso mejor usa GIN.

Ejemplos de uso de índices

Ahora vamos a juntar la teoría con la práctica y ver algunos ejemplos.

1. Búsqueda de intersección de arrays

Supón que quieres encontrar todos los productos que están relacionados con las categorías electronics y smartphones, usando el operador && (intersección de arrays):

SELECT *
FROM products
WHERE categories && ARRAY['electronics', 'smartphones'];

Para esto, el índice GIN que ya creaste antes es perfecto:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Con este índice, la consulta va a ir mucho más rápido gracias a la lista invertida.

2. Comparar arrays por igualdad

Si necesitas encontrar productos que pertenezcan solo a las categorías electronics y gadgets (en ese orden), aquí es mejor usar el índice BTREE:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Crea el índice correspondiente:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Rendimiento de los índices

Los índices ayudan a acelerar las consultas, pero también tienen su lado negativo. Por ejemplo:

• Crear un índice lleva tiempo y recursos. Si tienes una tabla muy grande, construir el índice puede tardar bastante.
• Actualizar la tabla. Cada vez que insertas nuevas filas o actualizas datos existentes, los índices también se actualizan. Esto puede hacer más lentos los INSERT y UPDATE.

Aun así, en la mayoría de los casos, la ventaja de tener consultas rápidas compensa estos costes.

¿Cómo elegir: GIN o BTREE?

Aquí tienes una tabla rápida para ayudarte a elegir el índice adecuado para cada caso: