Índices y optimización de consultas

📐 Fundamento

Qué hace un índice:

Un índice es una estructura de datos auxiliar que mantiene los valores de una o más columnas ordenados, junto con un puntero a la fila correspondiente en la tabla principal (heap).

Sin índice:

Buscar pedidos de mesa 5:
→ Leer fila 1: mesa=3 ✗
→ Leer fila 2: mesa=1 ✗
→ Leer fila 3: mesa=5 ✓
→ Leer fila 4: mesa=2 ✗
... continuar hasta el final → O(n) operaciones

Con índice en mesa_id:

Buscar pedidos de mesa 5 en el índice B-Tree:
→ Navegar el árbol hasta encontrar mesa=5 → O(log n) operaciones
→ Seguir puntero directamente a las filas correspondientes

Crear y eliminar índices:

-- Índice simple
CREATE INDEX idx_pedidos_mesa ON pedidos (mesa_id);

-- Índice compuesto (útil cuando se filtra por varios campos juntos)
CREATE INDEX idx_pedidos_mesa_fecha ON pedidos (mesa_id, fecha);

-- Índice único (también garantiza unicidad)
CREATE UNIQUE INDEX idx_usuarios_email ON usuarios (email);

-- Eliminar
DROP INDEX idx_pedidos_mesa;

-- Ver índices existentes
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'pedidos';

📐 Fundamento

Estructura de un B-Tree:

                [50]
               /    \
          [20, 35]   [70, 90]
         / |   | \   / |   | \
        [...]     [...]     [...]  ← hojas con punteros a filas

• Cada nodo interno contiene valores que sirven de guía para la búsqueda.
• Cada hoja contiene los valores reales + punteros (TID — Tuple ID) a las filas.
• Buscar: O(log n). Insertar: O(log n) + posible rebalanceo.

El B-Tree soporta:

• Búsquedas exactas: WHERE mesa_id = 5
• Rangos: WHERE fecha BETWEEN '2026-01-01' AND '2026-12-31'
• Ordenamiento: ORDER BY mesa_id (el índice ya está ordenado)
• Prefijos en strings: WHERE nombre LIKE 'Mar%' (pero NO LIKE '%ar%')

Otros tipos de índices:

-- Índice hash (solo igualdad, PostgreSQL)
CREATE INDEX idx_usuarios_email_hash ON usuarios USING HASH (email);

-- Índice GIN para búsqueda en JSONB
CREATE INDEX idx_pedidos_metadata ON pedidos USING GIN (metadata);

📐 Fundamento

Sintaxis:

EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE mesa_id = 5;

Salida típica sin índice:

Seq Scan on pedidos  (cost=0.00..245.00 rows=12 width=64)
                     (actual time=0.015..18.432 rows=12 loops=1)
  Filter: (mesa_id = 5)
  Rows Removed by Filter: 9988
Planning Time: 0.12 ms
Execution Time: 18.45 ms

Salida con índice:

Index Scan using idx_pedidos_mesa on pedidos
  (cost=0.29..12.41 rows=12 width=64)
  (actual time=0.025..0.089 rows=12 loops=1)
  Index Cond: (mesa_id = 5)
Planning Time: 0.21 ms
Execution Time: 0.11 ms

Qué leer en la salida:

El planificador puede ignorar el índice cuando:

• La tabla tiene muy pocas filas (el full scan es más rápido).
• La selectividad es baja: si el 80% de las filas tienen mesa_id = 5, un índice no ayuda.
• Las estadísticas están desactualizadas — correr ANALYZE tabla para actualizar.

-- Actualizar estadísticas
ANALYZE pedidos;

-- Ver estadísticas de una columna
SELECT n_distinct, correlation 
FROM pg_stats 
WHERE tablename = 'pedidos' AND attname = 'mesa_id';

📐 Fundamento

Problemas comunes y soluciones:

1. N+1 queries (el problema más frecuente):

# MAL: 1 query para obtener pedidos + 1 por cada pedido para obtener el mozo
pedidos = db.query("SELECT * FROM pedidos WHERE mesa_id = 5")  # 1 query
for p in pedidos:
    mozo = db.query(f"SELECT * FROM mozos WHERE id = {p.mozo_id}")  # N queries

# BIEN: 1 sola query con JOIN
pedidos = db.query("""
    SELECT p.*, m.nombre as mozo_nombre
    FROM pedidos p
    JOIN mozos m ON p.mozo_id = m.id
    WHERE p.mesa_id = 5
""")

2. SELECT * cuando no se necesitan todas las columnas:

-- MAL: trae todas las columnas, incluyendo texto grande
SELECT * FROM platillos WHERE categoria = 'pupusa';

-- BIEN: solo las columnas necesarias
SELECT id, nombre, precio FROM platillos WHERE categoria = 'pupusa';

3. Índice compuesto — orden importa:

-- Si la query filtra por mesa_id Y fecha:
WHERE mesa_id = 5 AND fecha > '2026-01-01'

-- Este índice es efectivo (mesa_id primero, porque es el filtro más selectivo):
CREATE INDEX idx_pedidos_mesa_fecha ON pedidos (mesa_id, fecha);

-- Este NO sirve para la query anterior (orden equivocado):
CREATE INDEX idx_pedidos_fecha_mesa ON pedidos (fecha, mesa_id);
-- (sí sirve para queries que filtran solo por fecha)

4. Índices parciales — cuando no se necesita indexar todo:

-- Solo indexar pedidos activos (el 5% de la tabla)
-- Los pedidos cerrados rara vez se consultan
CREATE INDEX idx_pedidos_activos ON pedidos (mesa_id)
WHERE estado = 'ABIERTO';

-- Mucho más pequeño y rápido que un índice completo

Cuándo NO crear un índice:

• Tablas muy pequeñas (< 1,000 filas): el full scan es más rápido.
• Columnas con baja cardinalidad: estado con solo 3 valores posibles.
• Tablas con muchos INSERT/UPDATE: cada escritura actualiza todos los índices.
• La columna ya es cubierta por otro índice compuesto.