PostgreSQL no tiene un único tipo de índice. Tiene cuatro tipos principales más alguno especializado, y elegir el adecuado para cada situación marca la diferencia entre una query que tarda 20ms y una que tarda 3 segundos. El tipo que se usa por defecto, B-tree, funciona bien para la mayoría de los casos, pero hay situaciones concretas donde GIN, GiST o BRIN son claramente mejores.
B-tree: el índice de uso general
El índice B-tree es el que PostgreSQL crea cuando haces CREATE INDEX sin especificar tipo. Funciona bien para la mayoría de las operaciones de comparación: igualdad (=), rangos (<, >, BETWEEN), y prefijos de texto (LIKE 'foo%', pero no LIKE '%foo'). También soporta ordenación, así que puede satisfacer un ORDER BY sin sort adicional.
-- Indice B-tree estandar CREATE INDEX idx_pedidos_fecha ON pedidos (fecha); -- Indice B-tree en multiple columnas CREATE INDEX idx_pedidos_usuario_fecha ON pedidos (usuario_id, fecha DESC); -- Indice parcial: solo indexa filas activas CREATE INDEX idx_pedidos_activos ON pedidos (fecha) WHERE estado = 'activo';
Los índices parciales son una herramienta muy útil que poca gente usa. Si solo consultas habitualmente filas con un estado concreto, un índice parcial es más pequeño y más rápido que uno sobre toda la tabla.
GIN: para arrays, JSONB y búsqueda de texto
GIN (Generalized Inverted Index) está pensado para casos donde una columna contiene múltiples valores y necesitas buscar por cualquiera de ellos. Los casos principales son:
- Columnas de tipo array: buscar filas que contengan un elemento concreto.
- Columnas JSONB: buscar por claves o valores anidados con los operadores
@>y?. - Búsqueda de texto completo con
tsvector.
-- Indice GIN en columna JSONB
CREATE INDEX idx_metadata_gin ON productos USING GIN (metadata);
-- Permite usar operadores como @> y ? de forma eficiente
SELECT * FROM productos WHERE metadata @> '{"categoria": "electronica"}';
-- Indice GIN en array
CREATE INDEX idx_tags_gin ON articulos USING GIN (tags);
SELECT * FROM articulos WHERE tags @> ARRAY['postgresql', 'sql'];
GIN tarda más en actualizarse que B-tree porque tiene que descomponer los valores en sus componentes, pero las búsquedas son muy rápidas.
GiST: geoespacial, rangos y similitud de texto
GiST (Generalized Search Tree) es el más versátil de los cuatro. Se usa principalmente para:
- Datos geoespaciales con PostGIS: buscar puntos dentro de un polígono, calcular distancias.
- Tipos de rango (
daterange,tsrange...): buscar rangos que se solapan o que contienen un punto. - Similitud de texto con la extensión
pg_trgm: búsquedas con LIKE '%foo%' o con operadores de similitud.
-- Con pg_trgm: permite LIKE con prefijo y sufijo de forma eficiente CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_nombre_trgm ON clientes USING GIST (nombre gist_trgm_ops); -- Ahora este LIKE puede usar el indice SELECT * FROM clientes WHERE nombre LIKE '%garcia%';
BRIN: para tablas enormes con datos correlacionados
BRIN (Block Range INdex) es el índice más pequeño y el menos conocido. En lugar de indexar cada valor, guarda el rango mínimo-máximo de valores en cada bloque de páginas físicas del disco. Es muy eficiente en espacio y muy rápido de mantener, pero solo funciona bien cuando los datos tienen una correlación fuerte entre su valor y su posición física en la tabla.
El caso ideal: una tabla de logs o de eventos donde la columna de timestamp crece de forma casi monotónica. Las filas más recientes están en los bloques más nuevos, así que el rango mínimo-máximo de cada bloque es muy preciso.
-- Indice BRIN en columna timestamp de tabla de eventos CREATE INDEX idx_eventos_fecha_brin ON eventos USING BRIN (fecha_evento); -- Para datos muy correlacionados, pages_per_range mas pequeño da mas precision CREATE INDEX idx_logs_brin ON logs USING BRIN (created_at) WITH (pages_per_range = 32);
No uses BRIN en columnas con baja correlación física (por ejemplo, IDs de usuarios en una tabla de pedidos donde los pedidos de un mismo usuario están dispersos por toda la tabla). En ese caso, B-tree es mejor opción.
Índices de expresión
Un tipo especial que funciona con cualquier método: el índice de expresión. En lugar de indexar el valor bruto de la columna, indexa el resultado de una expresión o función sobre esa columna.
-- Indice sobre el email en minusculas (para busquedas case-insensitive) CREATE INDEX idx_email_lower ON usuarios (LOWER(email)); -- La query debe usar la misma expresion para aprovechar el indice SELECT * FROM usuarios WHERE LOWER(email) = '[email protected]';
Para elegir bien entre estos tipos, necesitas entender los planes de ejecución que genera PostgreSQL. El artículo sobre EXPLAIN ANALYZE de esta misma serie explica cómo leer esos planes y verificar que el índice que creaste realmente se usa. Y si gestionas tablas muy grandes, el artículo sobre particionamiento de tablas en PostgreSQL complementa bien este tema, porque los índices y las particiones interactúan de formas específicas.
Imagen: Pexels / Markus Winkler