Indexación de arrays y operadores (`@>`, `<@`, `&&`) para búsquedas rápidas

Los arrays en PostgreSQL te permiten guardar un montón de valores en una sola celda de la tabla. Esto es súper útil cuando quieres agrupar datos relacionados, como una lista de etiquetas para un artículo o las categorías de un producto. Pero en cuanto empiezas a buscar, filtrar o cruzar arrays, el rendimiento puede caer en picado. Por eso la indexación de arrays es un salvavidas. Los índices te ayudan a acelerar operaciones como:

• comprobar si un array contiene un elemento concreto,
• buscar arrays que contienen ciertos elementos,
• comprobar si hay intersección entre arrays.

Operadores para trabajar con arrays

Antes de meternos con los índices, vamos a ver los operadores básicos para arrays:

@> (contiene) — comprueba si un array contiene todos los elementos de otro array.

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Aquí buscamos cursos que tengan la etiqueta "SQL".

<@ (está contenido en) — comprueba si un array está contenido en otro.

SELECT *
FROM courses
WHERE ARRAY['PostgreSQL', 'SQL'] <@ tags;

Aquí buscamos cursos cuyas etiquetas incluyan todos los elementos del array ARRAY['PostgreSQL', 'SQL'].

&& (intersección) — comprueba si hay algún elemento en común entre arrays.

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Esta consulta encuentra cursos que tengan al menos una de las etiquetas "NoSQL" o "Big Data".

¿Cómo ayuda la indexación?

Imagina que tienes una tabla courses con millones de registros y haces una consulta usando uno de los operadores de arriba. Sin índice, PostgreSQL tiene que mirar fila por fila — una operación que puede tardar la vida (sobre todo si tienes la paciencia de un programador esperando a que compile el código).

Con índices te ahorras ese sufrimiento. PostgreSQL tiene dos tipos de índices que van bien para arrays:

1. GIN (Generalized Inverted Index) — la mejor opción para arrays.
2. BTREE — se usa para comparar arrays completos.

Ejemplo: Creamos un índice para arrays

Vamos a crear una tabla pequeñita con arrays para probar todo esto en la práctica.

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

Metemos algunos registros:

INSERT INTO courses (name, tags)
VALUES
    ('Fundamentos de SQL', ARRAY['SQL', 'PostgreSQL', 'Bases de datos']),
    ('Trabajo con Big Data', ARRAY['Hadoop', 'Big Data', 'NoSQL']),
    ('Desarrollo en Python', ARRAY['Python', 'Web', 'Datos']),
    ('Curso de PostgreSQL', ARRAY['PostgreSQL', 'Advanced', 'SQL']);

Así se vería la tabla:

Sin índice: búsqueda lenta

Ahora imagina que queremos encontrar todos los cursos que tienen la etiqueta SQL.

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

La consulta funciona, pero si hay muchos datos, será lentísima. PostgreSQL hará lo que se llama un escaneo secuencial (Sequential Scan), o sea, mirará cada fila de la tabla.

Un resultado típico sería:

Creando un índice GIN

Para acelerar la búsqueda, creamos un índice tipo GIN:

CREATE INDEX idx_courses_tags
ON courses USING GIN (tags);

Probamos la misma consulta:

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Ahora PostgreSQL usará el índice GIN que creamos, y la consulta irá mucho más rápido.

Si antes se usaba escaneo secuencial (Seq Scan), ahora en el plan de ejecución verás Bitmap Index Scan:

¿Cómo funcionan los operadores con índices?

Ejemplo 1: Operador @>

Consulta:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

El índice GIN es perfecto para este operador. Postgres comprueba rápido qué filas tienen el elemento y devuelve el resultado.

Resultado de la consulta:

Ejemplo 2: Operador &&

Consulta:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Este operador busca intersecciones entre arrays: devuelve filas donde el array tags tiene en común al menos un elemento con el array dado.

El índice GIN vuelve a hacer su magia — la búsqueda es rápida incluso con muchos datos.

Resultado de la consulta:

Indexación y optimización

Cuando trabajes con arrays, sigue estos consejos:

1. Usa índices GIN para buscar dentro de arrays. Son mucho más rápidos que un escaneo secuencial.
2. Pon índices solo en las columnas que realmente usas mucho en consultas. Los índices ocupan espacio y ralentizan los inserts, así que no indexes todo porque sí.
3. Perfila tus consultas con EXPLAIN y EXPLAIN ANALYZE para ver si de verdad se usa tu índice.

Ejemplos: crear índices para arrays

Vamos a ver cómo crear índices para tipos concretos de operaciones con arrays y por qué es útil en la práctica.

Índice para el operador @>

Supón que ya tienes esta tabla courses:

Para acelerar consultas con el operador @> (el array contiene el elemento), creamos un índice GIN:

CREATE INDEX idx_courses_tags_gin
ON courses USING GIN (tags);

Ahora lanzamos la consulta:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Resultado:

Índice para los operadores @>, <@, y &&

La tabla es la misma que en el ejemplo anterior.

Como los operadores @>, <@ y && funcionan bien con índices tipo GIN, puedes crear un solo índice universal que acelere consultas con cualquiera de estos operadores:

CREATE INDEX idx_tags
ON courses USING GIN (tags);

Ejemplos de consultas y sus resultados:

• @> — comprobar si el array contiene los elementos indicados:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

---

• <@ — comprobar si el array está contenido en otro array:

SELECT *
FROM courses
WHERE tags <@ ARRAY['SQL', 'PostgreSQL', 'Advanced', 'Big Data', 'NoSQL', 'Python'];

---

• && — comprobar la intersección de arrays:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Probemos algo más complicado

Vamos a hacer una consulta que devuelva los cursos donde entre las etiquetas hay al menos una coincidencia con la lista ['Python', 'SQL', 'NoSQL']:

SELECT *
FROM courses
WHERE tags && ARRAY['Python', 'SQL', 'NoSQL'];

Salida:

Con el índice GIN esta consulta va rapidísimo, incluso si la tabla tiene millones de registros.

Errores típicos al trabajar con arrays

El índice no se usa: si en el resultado de EXPLAIN ves Seq Scan, revisa que el índice esté creado y que el operador que usas realmente soporte indexación.

Uso poco frecuente del array: si la columna con arrays casi no se usa en consultas o se actualiza mucho, el índice puede ocupar espacio de más y no aportar mucho.

Índices innecesarios: los índices ocupan disco y ralentizan los inserts, así que crea solo los que de verdad necesitas y que vayas a usar en consultas.

Ahora tienes todas las herramientas para trabajar de forma eficiente con arrays en PostgreSQL — acelera tus consultas usando los operadores @>, <@, && y los índices GIN. ¡No dudes en probarlo tú mismo con tus datos!