En esta página

Índices y rendimiento

14 min lectura TextoCap. 4 — Diseño y modelado

Índices y rendimiento

Un índice en SQL es exactamente como el índice de un libro: en lugar de leer todas las páginas para encontrar un tema, saltas directamente a la página correcta. Sin índices, PostgreSQL tiene que revisar cada fila de una tabla para responder una consulta (sequential scan). Con índices, puede saltar directamente a los datos relevantes.

¿Cómo funcionan los índices B-tree?

El tipo de índice predeterminado de PostgreSQL es el B-tree (árbol B). Es una estructura de árbol equilibrado que mantiene los datos ordenados y permite búsquedas en O(log n) en lugar de O(n).

Sin índice en una tabla de 1.000.000 de usuarios, buscar por email requiere revisar hasta 1.000.000 filas.

Con índice B-tree, la misma búsqueda toma aproximadamente 20 comparaciones (log₂ de 1.000.000 ≈ 20).

PostgreSQL crea índices automáticamente en:

  • PRIMARY KEY: siempre tiene un índice único automático
  • Columnas con UNIQUE: también tienen índice automático

NO crea índices automáticamente en claves foráneas.

Crear índices básicos

-- Preparar tabla de ejemplo con muchos datos
CREATE TABLE usuarios (
  id         BIGSERIAL    PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  nombre     VARCHAR(150) NOT NULL,
  ciudad     VARCHAR(100),
  activo     BOOLEAN      NOT NULL DEFAULT true,
  plan       VARCHAR(20)  NOT NULL DEFAULT 'free',
  creado_en  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Poblar con datos de prueba (para ilustración)
INSERT INTO usuarios (email, nombre, ciudad, plan)
SELECT
  'usuario' || n || '@mail.com',
  'Usuario ' || n,
  CASE (n % 5)
    WHEN 0 THEN 'Madrid'
    WHEN 1 THEN 'Barcelona'
    WHEN 2 THEN 'Sevilla'
    WHEN 3 THEN 'Valencia'
    ELSE 'Bilbao'
  END,
  CASE (n % 3) WHEN 0 THEN 'premium' WHEN 1 THEN 'free' ELSE 'basic' END
FROM generate_series(1, 100000) n;

-- Índice simple: acelera búsquedas por esa columna
CREATE INDEX idx_usuarios_ciudad ON usuarios(ciudad);
CREATE INDEX idx_usuarios_plan   ON usuarios(plan);

-- Convención de nombres recomendada: idx_{tabla}_{columna(s)}
-- O con acción: idx_{tabla}_{columna}_{tipo} para casos especiales

EXPLAIN ANALYZE — Ver el plan de ejecución

EXPLAIN ANALYZE es la herramienta más importante para optimizar consultas. Muestra cómo PostgreSQL planea ejecutar la consulta y cuánto tiempo tomó realmente:

-- Ver el plan SIN ejecutar
EXPLAIN SELECT * FROM usuarios WHERE ciudad = 'Madrid';

-- Ver el plan Y ejecutar (estadísticas reales)
EXPLAIN ANALYZE SELECT * FROM usuarios WHERE ciudad = 'Madrid';

-- Resultado típico SIN índice (Sequential Scan):
-- Seq Scan on usuarios  (cost=0.00..2791.00 rows=19856 width=96)
--   Filter: ((ciudad)::text = 'Madrid'::text)
-- Planning Time: 0.123 ms
-- Execution Time: 45.234 ms

-- Resultado típico CON índice (Index Scan):
-- Index Scan using idx_usuarios_ciudad on usuarios
--   (cost=0.29..456.87 rows=19856 width=96)
--   Index Cond: ((ciudad)::text = 'Madrid'::text)
-- Planning Time: 0.156 ms
-- Execution Time: 8.123 ms

-- Formato más detallado y legible
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM usuarios WHERE ciudad = 'Madrid';

Interpretar el output de EXPLAIN

-- Los números clave a observar:
-- cost=inicio..total: estimación de costo del planificador (unidades arbitrarias)
-- rows: estimación de filas devueltas
-- width: ancho promedio de una fila en bytes
-- actual time=inicio..total: tiempo real de ejecución en milisegundos
-- actual rows: filas realmente devueltas
-- loops: cuántas veces se ejecutó este nodo

-- Tipos de scan:
-- Seq Scan: recorre toda la tabla. Puede ser OK para tablas pequeñas o cuando trae muchas filas.
-- Index Scan: usa el índice para encontrar filas, luego va a la tabla para leerlas
-- Index Only Scan: usa SOLO el índice (más rápido; ocurre cuando todas las columnas están en el índice)
-- Bitmap Heap Scan + Bitmap Index Scan: combina múltiples índices

Índices únicos y parciales

-- Índice UNIQUE: garantiza unicidad Y crea el índice
-- (La restricción UNIQUE crea este índice automáticamente)
CREATE UNIQUE INDEX idx_usuarios_email ON usuarios(email);

-- Índice parcial: solo incluye filas que cumplen una condición
-- Es más pequeño y más rápido que un índice completo

-- Solo indexar usuarios activos (si el 90% son activos, indexar solo activos = 90% más pequeño)
-- Pero si la consulta filtra activo=true, el índice parcial es efectivo
CREATE INDEX idx_usuarios_activos ON usuarios(email)
  WHERE activo = true;

-- Solo indexar planes premium (son los que más se consultan en el dashboard)
CREATE INDEX idx_usuarios_premium ON usuarios(creado_en DESC)
  WHERE plan = 'premium';

-- Uso del índice parcial (el WHERE debe coincidir con el predicado del índice)
SELECT * FROM usuarios WHERE activo = true AND email LIKE 'a%';
-- PostgreSQL puede usar idx_usuarios_activos para esta consulta

-- Índice parcial para NOT NULL (evitar indexar NULLs)
CREATE INDEX idx_usuarios_ciudad_notnull ON usuarios(ciudad)
  WHERE ciudad IS NOT NULL;

Índices compuestos (multi-columna)

Un índice compuesto cubre múltiples columnas. El orden importa: el índice es útil para consultas que filtran por las primeras N columnas del índice:

-- Índice compuesto: útil para consultas que filtran por ambas columnas
CREATE INDEX idx_usuarios_ciudad_plan ON usuarios(ciudad, plan);

-- Este índice ayuda en:
SELECT * FROM usuarios WHERE ciudad = 'Madrid' AND plan = 'premium';   -- ✅
SELECT * FROM usuarios WHERE ciudad = 'Madrid';                          -- ✅ (primera columna)
SELECT * FROM usuarios WHERE plan = 'premium';                           -- ❌ No ayuda (segunda columna sola)
-- La regla: el índice es útil si la consulta usa un prefijo de las columnas del índice

-- Índice compuesto con orden DESC para ordenamiento
CREATE INDEX idx_usuarios_ciudad_creado ON usuarios(ciudad ASC, creado_en DESC);
-- Útil para: "últimos usuarios de Madrid, ordenados por fecha"
SELECT * FROM usuarios WHERE ciudad = 'Madrid' ORDER BY creado_en DESC LIMIT 20;

Índices GIN para JSONB y arrays

Los índices B-tree no son eficientes para buscar dentro de documentos JSONB o arrays. Para eso existe el índice GIN (Generalized Inverted Index):

-- Tabla con datos JSONB
CREATE TABLE productos_ext (
  id         SERIAL  PRIMARY KEY,
  nombre     VARCHAR(200) NOT NULL,
  atributos  JSONB,
  tags       TEXT[]  -- Array de etiquetas
);

INSERT INTO productos_ext (nombre, atributos, tags) VALUES
  ('Laptop Pro', '{"color": "gris", "ram": "16GB", "ssd": true}', ARRAY['tech', 'trabajo', 'portátil']),
  ('Zapatillas', '{"color": "rojo", "talla": 42, "impermeables": false}', ARRAY['deporte', 'running']),
  ('Silla Gaming', '{"color": "negro", "reclinable": true, "material": "cuero"}', ARRAY['hogar', 'gaming']);

-- Índice GIN en columna JSONB completa
CREATE INDEX idx_productos_atributos ON productos_ext USING GIN(atributos);

-- Índice GIN en array
CREATE INDEX idx_productos_tags ON productos_ext USING GIN(tags);

-- Con índice GIN, estas consultas son rápidas:
-- Buscar por clave-valor en JSONB (operador @>: "contiene")
SELECT nombre FROM productos_ext
WHERE atributos @> '{"color": "gris"}';

-- Buscar JSONB con clave específica (operador ?)
SELECT nombre FROM productos_ext
WHERE atributos ? 'ssd';

-- Buscar en array (operador @>: "el array contiene")
SELECT nombre FROM productos_ext
WHERE tags @> ARRAY['deporte'];

-- Buscar si el array se superpone con otro (operador &&)
SELECT nombre FROM productos_ext
WHERE tags && ARRAY['tech', 'gaming'];

-- GIN específico para operadores jsonb_path_ops (más rápido para @>)
CREATE INDEX idx_productos_atributos_path ON productos_ext
  USING GIN(atributos jsonb_path_ops);

Índices para búsqueda de texto

-- Índice para LIKE con prefijo (solo para 'valor%', no '%valor%')
-- Requiere operator class 'text_pattern_ops' en PostgreSQL
CREATE INDEX idx_usuarios_nombre_like ON usuarios(nombre text_pattern_ops);

-- Búsqueda de texto completo (Full-Text Search)
CREATE TABLE articulos (
  id      SERIAL PRIMARY KEY,
  titulo  TEXT NOT NULL,
  cuerpo  TEXT NOT NULL,
  -- Columna precalculada del vector de búsqueda
  busqueda tsvector GENERATED ALWAYS AS (
    to_tsvector('spanish', titulo || ' ' || cuerpo)
  ) STORED
);

-- Índice GIN en el vector de búsqueda (muy eficiente)
CREATE INDEX idx_articulos_busqueda ON articulos USING GIN(busqueda);

INSERT INTO articulos (titulo, cuerpo) VALUES
  ('Introducción a SQL', 'SQL es un lenguaje para bases de datos relacionales'),
  ('PostgreSQL avanzado', 'Aprende triggers, funciones y optimización en PostgreSQL');

-- Búsqueda de texto completo con el índice
SELECT titulo, ts_rank(busqueda, query) AS relevancia
FROM articulos, to_tsquery('spanish', 'PostgreSQL & optimización') query
WHERE busqueda @@ query
ORDER BY relevancia DESC;

Mantenimiento de índices

-- Ver todos los índices de una tabla
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'usuarios';

-- Estadísticas de uso de índices (¿se están usando?)
SELECT
  indexrelname AS nombre_indice,
  idx_scan     AS veces_usado,
  idx_tup_read AS filas_leidas,
  idx_tup_fetch AS filas_devueltas,
  pg_size_pretty(pg_relation_size(indexrelid)) AS tamaño
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- Índices que nunca se usan (candidatos para eliminar)
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public';

-- Tamaño de índices
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) AS tamaño
FROM pg_indexes
WHERE tablename = 'usuarios';

-- Reconstruir un índice sin bloquear la tabla (si está fragmentado)
REINDEX INDEX CONCURRENTLY idx_usuarios_ciudad;

-- Eliminar un índice
DROP INDEX IF EXISTS idx_usuarios_ciudad;
DROP INDEX CONCURRENTLY idx_usuarios_ciudad;  -- Sin bloquear lecturas/escrituras

[!warning] Los índices tienen costo: cada índice que creas hace que los INSERT, UPDATE y DELETE sean más lentos (PostgreSQL debe actualizar todos los índices de la tabla). No crees índices "por si acaso". El proceso es: 1) identifica una consulta lenta con EXPLAIN ANALYZE, 2) crea el índice específico para esa consulta, 3) verifica que se usa y mide la mejora. Elimina índices que no se usen (idx_scan = 0).

[!tip] Crea índices en producción con CONCURRENTLY: CREATE INDEX CONCURRENTLY construye el índice sin bloquear lecturas ni escrituras. Tarda más que el CREATE INDEX normal, pero no interrumpe el servicio. Para tablas grandes en producción, siempre usa CONCURRENTLY. Lo mismo aplica para REINDEX CONCURRENTLY y DROP INDEX CONCURRENTLY.

Con los índices correctos, consultas que tardaban minutos pueden responder en milisegundos. En la siguiente lección profundizaremos en las transacciones y la concurrencia: cómo PostgreSQL garantiza que múltiples usuarios simultáneos no interfieran entre sí.

nextSteps:

  • slug: transacciones-y-concurrencia label: "Siguiente: Transacciones y concurrencia"