Índices avanzados

PostgreSQL tiene el sistema de índices más rico de las bases de datos de código abierto. Además del clásico B-tree, ofrece índices GIN para JSONB, arrays y texto; índices parciales que solo cubren las filas que importan; índices de expresión; BRIN para tablas gigantes ordenadas por fecha; y la posibilidad de crear índices sin bloquear la tabla. En esta lección aprenderás a usarlos, a leer EXPLAIN ANALYZE y a encontrar las consultas lentas con pg_stat_statements.

Requisito

Repasa primero los fundamentos (qué es un índice, B-tree, índices compuestos, prefijo izquierdo) en Índices y rendimiento.

Concepto: tipos de índice

Tipo	Ideal para	Operadores
B-tree (por defecto)	Igualdad, rangos, orden, prefijos	=, <, >, BETWEEN, IN, LIKE 'abc%', ORDER BY
Hash	Solo igualdad	=
GIN (Generalized Inverted Index)	Valores que contienen muchos elementos	JSONB @> ?, arrays @> &&, texto completo @@, trigramas
GiST	Datos geométricos, rangos, búsquedas de proximidad	&& (traslape), <-> (distancia), PostGIS
SP-GiST	Datos con particiones naturales (IPs, árboles)	Varios
BRIN (Block Range Index)	Tablas enormes con datos físicamente ordenados (logs por fecha)	Rangos

Analogía del GIN: un B-tree es el índice de un libro ordenado por título de capítulo; un GIN es el índice temático del final, donde cada palabra apunta a todas las páginas donde aparece. Perfecto cuando un registro "contiene" muchas cosas (etiquetas, claves JSON, palabras).

Preparación: datos para practicar

Los índices solo se notan con volumen. Generemos 500,000 pedidos:

CREATE TABLE pedidos_demo (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cliente_id  INT NOT NULL,
  estado      TEXT NOT NULL,
  total       NUMERIC(10,2) NOT NULL,
  email       TEXT NOT NULL,
  etiquetas   TEXT[] NOT NULL DEFAULT '{}',
  datos       JSONB NOT NULL DEFAULT '{}',
  creado_en   TIMESTAMPTZ NOT NULL
);

INSERT INTO pedidos_demo (cliente_id, estado, total, email, etiquetas, datos, creado_en)
SELECT
  (random() * 20000)::int + 1,
  (ARRAY['pendiente','pagado','enviado','entregado','cancelado'])[1 + (random() * 4)::int],
  round((random() * 5000)::numeric, 2),
  'Cliente' || g || '@Correo.com',
  CASE WHEN random() < 0.1 THEN ARRAY['urgente'] ELSE ARRAY['normal'] END,
  jsonb_build_object('canal', (ARRAY['web','app','tienda'])[1 + (random() * 2)::int],
                     'cupon', CASE WHEN random() < 0.05 THEN 'BUENFIN' END),
  TIMESTAMPTZ '2023-01-01' + (g * INTERVAL '1 minute')
FROM generate_series(1, 500000) AS g;

ANALYZE pedidos_demo;   -- actualiza las estadísticas del planificador

(La distribución de estados queda desigual, con menos pendiente y cancelado: perfecto para los índices parciales.)

Leer EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM pedidos_demo WHERE cliente_id = 1234;

Sin índice:

Gather  (cost=1000.00..12872.43 rows=25 width=120) (actual time=0.512..38.204 rows=27 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on pedidos_demo  (cost=0.00..11869.93 rows=10 width=120) (actual time=2.1..35.9 rows=9 loops=3)
        Filter: (cliente_id = 1234)
        Rows Removed by Filter: 166658
  Buffers: shared hit=7353
Planning Time: 0.110 ms
Execution Time: 38.250 ms

Cómo leerlo:

Elemento	Significado
cost=inicio..total	Estimación en unidades abstractas del planificador
rows=25 (en cost)	Filas estimadas
actual time=...	Milisegundos reales (inicio..fin) por ciclo
rows=27 (en actual)	Filas reales. Si difiere mucho de la estimación, ejecuta ANALYZE
loops=3	Veces que se ejecutó el nodo (multiplica el tiempo)
Rows Removed by Filter	Filas leídas y descartadas: ¡trabajo desperdiciado!
Buffers: shared hit	Páginas leídas de memoria (read = de disco)

CREATE INDEX idx_pd_cliente ON pedidos_demo (cliente_id);

EXPLAIN ANALYZE SELECT * FROM pedidos_demo WHERE cliente_id = 1234;

Bitmap Heap Scan on pedidos_demo  (cost=4.62..101.2 rows=25 width=120) (actual time=0.04..0.09 rows=27 loops=1)
  Recheck Cond: (cliente_id = 1234)
  ->  Bitmap Index Scan on idx_pd_cliente  (cost=0.00..4.61 rows=25 width=0) (actual time=0.02..0.02 rows=27 loops=1)
        Index Cond: (cliente_id = 1234)
Execution Time: 0.120 ms

De 38 ms a 0.12 ms: 300 veces más rápido.

explain.dalibo.com

Pega la salida de EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) en explain.dalibo.com o explain.depesz.com para verla como diagrama y detectar el nodo más lento.

Índices compuestos y cubrientes

-- Consulta típica del panel: pedidos de un cliente, del más reciente al más viejo
CREATE INDEX idx_pd_cliente_fecha ON pedidos_demo (cliente_id, creado_en DESC);

-- INCLUDE: columnas "de pasajero" para que el índice responda sin ir a la tabla
CREATE INDEX idx_pd_cliente_fecha_inc ON pedidos_demo (cliente_id, creado_en DESC)
  INCLUDE (estado, total);

EXPLAIN ANALYZE
SELECT creado_en, estado, total FROM pedidos_demo
WHERE cliente_id = 1234
ORDER BY creado_en DESC LIMIT 10;
-- Index Only Scan using idx_pd_cliente_fecha_inc ...

Index Only Scan es lo más rápido posible: ni siquiera toca la tabla (requiere que VACUUM esté al día, ver abajo).

Índices parciales

Un índice parcial solo incluye las filas que cumplen un WHERE. Más pequeño, más rápido y más barato de mantener.

-- El panel de operaciones solo consulta pedidos pendientes (una minoría)
CREATE INDEX idx_pd_pendientes ON pedidos_demo (creado_en)
WHERE estado = 'pendiente';

EXPLAIN ANALYZE
SELECT id, cliente_id, creado_en FROM pedidos_demo
WHERE estado = 'pendiente'
ORDER BY creado_en
LIMIT 50;
-- Index Scan using idx_pd_pendientes ...

Compara tamaños:

CREATE INDEX idx_pd_estado_fecha ON pedidos_demo (estado, creado_en);   -- índice completo

SELECT indexrelname AS indice, pg_size_pretty(pg_relation_size(indexrelid)) AS tamano
FROM pg_stat_user_indexes
WHERE relname = 'pedidos_demo'
ORDER BY pg_relation_size(indexrelid) DESC;

indice	tamano
idx_pd_cliente_fecha_inc	22 MB
idx_pd_estado_fecha	15 MB
pedidos_demo_pkey	11 MB
idx_pd_cliente_fecha	11 MB
idx_pd_cliente	3.4 MB
idx_pd_pendientes	1.2 MB

(Los tamaños exactos variarán en tu equipo.)

Unicidad condicional

Un uso brillante de los índices parciales: reglas de unicidad que dependen de una condición.

-- Un cliente puede tener muchas direcciones, pero solo UNA principal
CREATE UNIQUE INDEX uq_direccion_principal ON direcciones (cliente_id)
WHERE es_principal;

-- Emails únicos solo entre usuarios no eliminados (soft delete)
CREATE UNIQUE INDEX uq_usuarios_email_activos ON usuarios (email)
WHERE eliminado_en IS NULL;

La consulta debe "coincidir" con el WHERE del índice

PostgreSQL usa el índice parcial solo si puede demostrar que tu WHERE implica el del índice. WHERE estado = 'pendiente' sí; WHERE estado = $1 (un parámetro) normalmente no, porque al planificar no sabe qué valor llegará. Escribe la condición literal en las consultas que deben usar índices parciales.

Índices de expresión

Cuando filtras por una expresión, el índice normal sobre la columna no sirve:

-- Los emails se guardaron con mayúsculas mezcladas
EXPLAIN ANALYZE SELECT * FROM pedidos_demo WHERE lower(email) = 'cliente777@correo.com';
-- Seq Scan ... (aunque exista un índice sobre email)

CREATE INDEX idx_pd_email_lower ON pedidos_demo (lower(email));

EXPLAIN ANALYZE SELECT * FROM pedidos_demo WHERE lower(email) = 'cliente777@correo.com';
-- Index Scan using idx_pd_email_lower ...

Otros ejemplos:

-- Búsquedas por una clave JSONB concreta
CREATE INDEX idx_pd_canal ON pedidos_demo ((datos ->> 'canal'));

-- Reportes por día (en zona horaria fija; la expresión debe ser IMMUTABLE)
CREATE INDEX idx_pd_dia ON pedidos_demo (((creado_en AT TIME ZONE 'America/Mexico_City')::date));

La expresión debe usar solo funciones IMMUTABLE y la consulta debe escribirla exactamente igual.

Alternativa: citext

Para emails, la extensión citext ofrece un tipo de texto que compara sin distinguir mayúsculas: CREATE EXTENSION citext; y email CITEXT UNIQUE. Así un índice normal sirve y 'Ana@Correo.com' = 'ana@correo.com' es verdadero.

Índices GIN

JSONB

-- Opción 1: operadores @>, ?, ?|, ?&
CREATE INDEX idx_pd_datos ON pedidos_demo USING GIN (datos);

-- Opción 2: solo @>, pero más pequeño y rápido
CREATE INDEX idx_pd_datos_path ON pedidos_demo USING GIN (datos jsonb_path_ops);

EXPLAIN ANALYZE SELECT COUNT(*) FROM pedidos_demo WHERE datos @> '{"cupon": "BUENFIN"}';
-- Bitmap Index Scan on idx_pd_datos_path ...

El operador importa

El índice GIN acelera datos @> '{"canal": "app"}', pero no datos ->> 'canal' = 'app'. Para la segunda forma necesitas un índice de expresión sobre (datos ->> 'canal'). Elige un estilo y crea el índice que le corresponde.

Arrays

CREATE INDEX idx_pd_etiquetas ON pedidos_demo USING GIN (etiquetas);

EXPLAIN ANALYZE SELECT COUNT(*) FROM pedidos_demo WHERE etiquetas @> ARRAY['urgente'];
-- Bitmap Index Scan on idx_pd_etiquetas

Texto: trigramas y texto completo

GIN también es la base de la búsqueda de texto completo (tsvector) y de las búsquedas difusas con pg_trgm (LIKE '%texto%' indexado). Lo verás en la siguiente lección.

Costo de GIN

Los índices GIN son rápidos para leer pero más lentos de actualizar que un B-tree. En tablas con muchísimas escrituras, mide el impacto. PostgreSQL usa una "lista pendiente" (fastupdate) para amortiguarlo.

BRIN: índices diminutos para tablas gigantes

Si una tabla de logs o eventos crece en orden de fecha (las filas nuevas siempre al final), un BRIN guarda solo el mínimo y máximo de cada bloque de páginas:

CREATE INDEX idx_pd_creado_brin ON pedidos_demo USING BRIN (creado_en);
-- Tamaño: unos pocos KB contra ~11 MB de un B-tree

EXPLAIN ANALYZE
SELECT COUNT(*) FROM pedidos_demo
WHERE creado_en >= '2023-06-01' AND creado_en < '2023-06-02';
-- Bitmap Index Scan on idx_pd_creado_brin

Ideal para tablas de cientos de millones de filas de logs, métricas o eventos. Inútil si los datos no están físicamente ordenados por esa columna.

Crear índices en producción sin bloquear

CREATE INDEX normal bloquea las escrituras en la tabla mientras se construye (en una tabla grande, minutos). En producción usa:

CREATE INDEX CONCURRENTLY idx_pd_total ON pedidos_demo (total);
DROP INDEX CONCURRENTLY IF EXISTS idx_pd_total;
REINDEX INDEX CONCURRENTLY idx_pd_cliente;   -- reconstruir un índice inflado

• Tarda más, pero la app sigue funcionando.
• No puede ejecutarse dentro de una transacción (BEGIN ... COMMIT), así que algunas herramientas de migración necesitan configurarse para ello.
• Si falla, deja un índice INVALID: revísalo con \d tabla y bórralo.

Encontrar consultas lentas: pg_stat_statements

La extensión más útil para rendimiento. Registra estadísticas de todas las consultas ejecutadas.

# postgresql.conf (requiere reinicio)
shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 consultas que más tiempo total consumen
SELECT
  round(total_exec_time::numeric, 0)  AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2)   AS promedio_ms,
  rows,
  left(query, 80)                     AS consulta
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Reiniciar estadísticas tras optimizar
SELECT pg_stat_statements_reset();

En Supabase, Neon, RDS y la mayoría de servicios administrados ya viene activada.

Otras vistas útiles:

-- Tablas con muchos escaneos secuenciales (candidatas a índice)
SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Índices que nunca se han usado (candidatos a eliminar)
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS tamano, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

VACUUM y ANALYZE

Por el diseño MVCC de PostgreSQL, un UPDATE no modifica la fila en su lugar: crea una versión nueva y marca la vieja como muerta. Un DELETE solo la marca. Esas filas muertas (dead tuples) ocupan espacio hasta que VACUUM las limpia.

UPDATE productos SET precio = 949 WHERE id = 1;

Página de datos:
[ id=1 precio=899 ✝ muerta ][ id=2 ... ][ id=1 precio=949 ✔ viva ]
                 ↑ VACUUM recupera este espacio para reutilizarlo

Comando	Qué hace
VACUUM tabla	Marca el espacio de filas muertas como reutilizable (no bloquea)
ANALYZE tabla	Actualiza las estadísticas que usa el planificador para elegir índices
VACUUM ANALYZE tabla	Ambas
VACUUM FULL tabla	Reescribe la tabla y devuelve espacio al disco. Bloquea la tabla por completo

El autovacuum (activado por defecto) lo hace solo. Normalmente no tienes que intervenir, salvo:

• Tras cargas o borrados masivos: ejecuta VACUUM ANALYZE tabla manualmente.
• Si una tabla con muchas actualizaciones crece sin control ("bloat"): ajusta el autovacuum para esa tabla.

-- ¿Cuántas filas muertas hay y cuándo pasó el autovacuum?
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Autovacuum más agresivo para una tabla muy actualizada
ALTER TABLE pedidos SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02);

Nunca desactives el autovacuum

Desactivarlo "para ganar rendimiento" lleva a tablas infladas, consultas cada vez más lentas y, en el extremo, al temido transaction ID wraparound, donde PostgreSQL deja de aceptar escrituras para proteger los datos.

Checklist de índices en PostgreSQL

• [ ] Índice en cada FK (PostgreSQL no los crea solo).
• [ ] Índices compuestos para los filtros + orden más frecuentes (igualdad primero, rango/orden después).
• [ ] Parciales para subconjuntos consultados a menudo (pendientes, activos, no borrados).
• [ ] De expresión si filtras por lower(...), claves JSONB o fechas derivadas.
• [ ] GIN para JSONB (@>), arrays y texto; BRIN para logs enormes ordenados por fecha.
• [ ] CREATE INDEX CONCURRENTLY en producción.
• [ ] Revisa pg_stat_statements y elimina índices con idx_scan = 0.
• [ ] Deja trabajar al autovacuum; ANALYZE tras cargas masivas.

Ejercicios

1. Crea un índice que acelere: SELECT * FROM pedidos_demo WHERE estado = 'cancelado' AND creado_en >= now() - interval '30 days'; sabiendo que los cancelados son pocos.
2. Garantiza con un índice que cada cliente tenga como máximo un pedido en estado 'pendiente'.
3. Tienes la consulta WHERE datos ->> 'canal' = 'app'. ¿Te sirve el índice GIN (datos jsonb_path_ops)? ¿Qué índice crearías o cómo reescribirías la consulta?
4. ¿Por qué CREATE INDEX CONCURRENTLY falla dentro de BEGIN; ... COMMIT;?

Ver solución

-- 1: parcial sobre la fecha, solo para cancelados
CREATE INDEX idx_pd_cancelados_fecha ON pedidos_demo (creado_en)
WHERE estado = 'cancelado';

-- 2: unicidad condicional
CREATE UNIQUE INDEX uq_pd_un_pendiente ON pedidos_demo (cliente_id)
WHERE estado = 'pendiente';
-- (fallará si los datos aleatorios ya tienen clientes con 2 pendientes; en una tabla real se aplica desde el inicio)

3. No: jsonb_path_ops solo acelera @>. Opciones: reescribir la consulta como WHERE datos @> '{"canal": "app"}' (usa el GIN), o crear un índice de expresión CREATE INDEX ON pedidos_demo ((datos ->> 'canal'));.

4. Porque CONCURRENTLY necesita varias transacciones internas (construye el índice en fases esperando a que terminen las transacciones activas), algo imposible si ya está dentro de un bloque de transacción explícito.

Resumen

• PostgreSQL ofrece B-tree, Hash, GIN, GiST, SP-GiST y BRIN; elige según los operadores que usas.
• Lee EXPLAIN (ANALYZE, BUFFERS): compara filas estimadas vs reales y vigila Rows Removed by Filter.
• Parciales (WHERE) = índices pequeños y unicidad condicional; de expresión = filtros por funciones.
• INCLUDE permite Index Only Scan; GIN acelera JSONB, arrays y texto; BRIN es diminuto para logs.
• CREATE INDEX CONCURRENTLY en producción; pg_stat_statements para encontrar lo lento.
• El autovacuum limpia filas muertas y actualiza estadísticas: nunca lo desactives.