Lección 09 — Índices y planes de ejecución

Stack: PostgreSQL · Proyecto: TicketFlow Estado: Publicada Prerrequisito: Lección 08 — Modelado de datos

Objetivos

1. Leer EXPLAIN ANALYZE y saber qué buscar (seq scan, index scan, coste, filas reales vs estimadas).
2. Entender qué es un índice B-tree, qué consultas lo aprovechan y qué operaciones lo matan.
3. Diagnosticar y matar el N+1 en Django (select_related/prefetch_related).
4. Diseñar los índices que TicketFlow necesita de verdad (y borrar los que no).

1. El índice B-tree en una foto

Un B-tree es un árbol ordenado: buscar expires_at = X baja por el árbol (log n) en vez de leer la tabla entera (n). Sirve para:

• Igualdad (WHERE email =...)
• Rangos y orden (WHERE starts_at >..., ORDER BY created_at)
• Prefijos de columnas compuestas y LIKE 'abc%' (no '%abc')

La regla de oro de índices compuestos (orden izquierda→derecha): un índice (status, expires_at) sirve para filtros por status, por status + expires_at, y para ordenar por expires_at dentro de un status fijo. No sirve para buscar solo por expires_at. La columna de igualdad primero; la de rango después.

Costes: cada índice se paga en cada INSERT/UPDATE (escribir es mantener todos los árboles) y en disco. Por eso no se indexa "por si acaso".

2. EXPLAIN ANALYZE: la lectura que importa

EXPLAIN ANALYZE
SELECT s.* FROM seat s
WHERE s.event_id = 42
  AND NOT EXISTS (
    SELECT 1 FROM reservation_item ri
    JOIN reservation r ON r.id = ri.reservation_id
    WHERE ri.seat_id = s.id AND r.status IN ('PENDING_PAYMENT','CONFIRMED')
  )
ORDER BY s.sector, s.row, s.number;

Qué mirar, en orden:

1. Seq Scan vs Index Scan: en tablas pequeñas el seq scan es correcto (leer todo es más barato que el árbol); en grandes con filtro selectivo, debe ser Index Scan. Un Seq Scan con filtro que devuelve 3 de 2M filas = índice faltante.
2. rows=estimado vs actual: si el estimado dice 1 y lo real son 50.000, el planificador eligió mal (estadísticas viejas → ANALYZE tabla).
3. Buffers (con EXPLAIN (ANALYZE, BUFFERS)): cuántas páginas leyó — el coste real de E/S.
4. Sort: Sort Method: external merge = ordenar en disco = índice que ordenaría por ti.

3. El N+1 en Django

# MAL: 1 query de reservas + N queries de usuario + N de evento
for r in Reservation.objects.all():
    print(r.user.email, r.event.title)

# BIEN: 3 queries en total (join para FK, prefetch para M2M/inversas)
reservas = (Reservation.objects
            .select_related("user", "event")     # FK: JOIN
            .all())
items = (ReservationItem.objects
         .select_related("reservation", "seat")
         .prefetch_related("seat__reservation_items"))  # inversas: 2 queries + unión en Python

select_related = JOIN SQL (FK hacia adelante). prefetch_related = segunda query + empalme en Python (M2M e inversas). El detector universal: len(connection.queries) antes/después, o django-debug-toolbar.

4. Los índices de TicketFlow (los que son, los que faltan)

Índices que NO criarías: columnas de baja cardinalidad solas (status solo), tablas pequeñas, columnas que nunca aparecen en WHERE/ORDER. Cada índice es una hipótesis de consulta; si no hay consulta, no hay índice.

Autoevaluación

1. ¿Por qué un índice (status, expires_at) NO sirve para WHERE expires_at < now() solo? ¿Qué índice crearías para ese caso?
2. El planificador estima 1 fila y la realidad son 50.000. ¿Qué miras y qué comando arregla lo más frecuente?
3. ¿Cuándo es CORRECTO un Seq Scan? Da un caso concreto de TicketFlow.
4. select_related vs prefetch_related: ¿por qué cada uno en su sitio? ¿Qué pasaría si usas select_related en una relación inversa?
5. ¿Qué pagas por cada índice extra y cómo decides que un índice "no merece"?

Continúa con los ejercicios. Las soluciones solo tras intentarlo.