Índices y Rendimiento

Los índices son estructuras de datos que aceleran las búsquedas, como el índice de un libro. El índice B-tree (el predeterminado) es ideal para la mayoría de los casos. Sin embargo, los índices tienen un costo: ocupan espacio y ralentizan INSERT/UPDATE/DELETE.

-- Sin índice: PostgreSQL hace Sequential Scan (lee toda la tabla)
-- Con índice: usa Index Scan (mucho más rápido)

-- Crear índice básico (B-tree por defecto)
CREATE INDEX idx_users_email ON users(email);

-- Índice en FK (siempre créalos en foreign keys)
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- Índice compuesto: útil cuando filtras por múltiples columnas
-- ORDEN IMPORTA: útil para (a), (a,b) pero NO para (b) solo
CREATE INDEX idx_orders_user_fecha
ON orders(user_id, fecha DESC);

-- Índice parcial: solo indexa un subconjunto de filas
-- Más pequeño y eficiente si solo consultas activos
CREATE INDEX idx_users_activos
ON users(email)
WHERE activo = TRUE;

-- Índice UNIQUE (también aplica la restricción)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- EXPLAIN ANALYZE: ver el plan de ejecución
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
AND fecha >= '2024-01-01';
-- Busca: "Index Scan" (bueno) vs "Seq Scan" (lento en tablas grandes)
-- Busca: tiempo real de ejecución
-- Busca: "rows=XXX" (filas estimadas vs reales)

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

-- ¿Cuándo UN ÍNDICE PUEDE PERJUDICAR?
-- 1. Tablas pequeñas (Seq Scan puede ser más rápido)
-- 2. Columnas con baja cardinalidad (ej: activo TRUE/FALSE)
-- 3. Tablas con muchos INSERT/UPDATE/DELETE
-- 4. Tener demasiados índices en la misma tabla