El módulo 7 se cerró con un cambio de pregunta. Durante siete módulos la pregunta ha sido ¿esto devuelve lo que quiero?; a partir de aquí es ¿cuánto tarda?. Y la respuesta casi siempre pasa por el mismo objeto: el índice, una estructura de datos auxiliar que el motor mantiene al lado de la tabla y que convierte "mira todas las filas hasta encontrarla" en "ve directamente a donde está".

En esta lección no crearás ningún índice todavía. Primero hay que entender qué problema resuelven y cómo funcionan por dentro, porque casi todos los errores que se cometen con índices —crear los que no sirven, no crear los que hacen falta, extrañarse de que el motor los ignore— vienen de no tener el modelo mental correcto. Al terminar sabrás por qué buscar entre 20 millones de filas puede costar cuatro accesos a disco, por qué LIKE '%aceite%' no puede aprovechar ninguno, y —lo que más te sorprenderá— qué índices tiene ya TiendaVerde sin que nadie los haya creado y cuáles le faltan.

1. La analogía del índice de un libro, y dónde se rompe
2. Cómo lee el motor una tabla sin índice
3. La estructura B-tree
4. Qué guarda realmente un índice: la clave y el puntero
5. Index Scan, Index Only Scan e índices cubridores
6. Qué acelera un B-tree y qué no
7. Los índices que TiendaVerde ya tiene
8. El agujero: PostgreSQL no indexa las claves foráneas
9. El precio de un índice
10. Errores Comunes y Consejos
11. Ejercicios
12. Conclusión

Tienes un manual de 800 páginas y quieres saber dónde se habla de HAVING. Hay dos formas:

• Hojear el libro entero hasta encontrarlo. Funciona siempre, y cuesta 800 páginas.
• Ir al índice alfabético del final, buscar "HAVING" —que está ordenado, así que lo localizas en segundos— y leer "pág. 412".

El índice del libro es exactamente un índice de base de datos: una copia ordenada de una parte de la información (los términos) junto con un puntero a dónde está el resto (la página). No contiene el libro; contiene lo justo para saltar a él.

La analogía es buena, pero conviene marcar dónde se rompe, porque es justo ahí donde empiezan las decisiones interesantes:

Esas cinco diferencias son, en realidad, el guion del módulo entero. La cuarta es la más difícil de aceptar: crear un índice no garantiza que se use. Lo verás demostrado sobre TiendaVerde en la lección 08-05.

Sin índice solo hay una estrategia posible: el recorrido secuencial (sequential scan, o Seq Scan en la jerga de PostgreSQL). El motor lee la tabla bloque a bloque desde el principio, comprueba el WHERE en cada fila y descarta las que no cumplen.

Siete filas de veinte. Para dártelas, el motor ha leído las veinte. Con 20 filas eso es gratis. La clave está en cómo escala:

(Cifras de intuición, no de laboratorio: el número exacto depende del ancho de las filas, de la caché y del disco. Lo que importa es la forma de las dos columnas.)

La columna del Seq Scan crece linealmente: el doble de filas, el doble de tiempo. La del índice crece logarítmicamente: multiplicar las filas por mil añade un acceso. Esa diferencia entre O(n) y O(log n) es todo el módulo en una línea, y explica por qué una consulta que va perfecta con los datos de desarrollo puede tumbar producción seis meses después.

Un Seq Scan no es un error. Es la estrategia correcta cuando la tabla es pequeña o cuando vas a devolver buena parte de ella. Lo verás con detalle en 08-03 y demostrado en 08-05.

El índice por defecto en PostgreSQL —y en todos los motores relacionales— es el B-tree (balanced tree, árbol equilibrado). Es un árbol ordenado con tres tipos de nodo:

• Raíz: un único nodo, punto de entrada de toda búsqueda.
• Nodos internos: contienen valores separadores y punteros a nodos del nivel inferior. Dicen "los valores menores que 5,50 están por aquí".
• Hojas: contienen los valores reales de la clave, cada uno con el puntero a la fila de la tabla. Además, las hojas están enlazadas entre sí en orden, lo que permite recorrer un rango sin volver a subir al árbol.

Así se vería un índice B-tree sobre productos.precio, con los 20 precios del catálogo repartidos en cinco hojas de cuatro entradas:

Busca el producto de 12,50 €: entras por la raíz, ves que 12,50 está entre 9,90 y 14,25, bajas a la hoja 4 y lo encuentras. Dos accesos, no veinte. Y para WHERE precio BETWEEN 9 AND 15 entras una vez por 9,00 y luego sigues las flechas punteadas entre hojas hasta pasarte de 15: eso es lo que hace que un B-tree sirva igual de bien para rangos que para igualdades.

En PostgreSQL cada nodo del árbol es una página de 8 kB. En esa página caben muchas entradas: para una clave entera de 4 bytes, del orden de 250 entradas útiles una vez descontadas las cabeceras y el margen que el motor deja libre. Ese número se llama factor de ramificación (fanout), y es lo que hace que el árbol sea ancho y bajo en lugar de estrecho y alto.

Léelo despacio, porque es el dato que conviene memorizar del módulo: una tabla de quince millones de filas se recorre con tres accesos. Y de esos tres, los niveles superiores están casi siempre en memoria caché porque todas las consultas pasan por ellos, así que en la práctica el coste real suele ser un acceso a disco, o ninguno.

El fanout depende del ancho de la clave: indexar un INTEGER da árboles muy anchos; indexar un VARCHAR(150) con nombres largos da entradas cinco veces mayores, menos entradas por página y, con las mismas filas, un nivel más de altura. Es el primer motivo por el que indexar columnas estrechas sale más barato.

El "equilibrado" del nombre significa que todas las hojas están a la misma profundidad, y el motor lo mantiene así dividiendo y fusionando páginas al insertar y borrar. Por eso no existen "índices desequilibrados" que haya que reconstruir a mano: el coste de mantenerlos equilibrados se paga en cada escritura, y de ahí viene buena parte del precio del que habla la sección 9.

Una entrada de hoja no contiene la fila. Contiene dos cosas:

1. El valor de la clave (12.50).
2. Un puntero físico a la fila, que en PostgreSQL se llama ctid y es un par (bloque, posición dentro del bloque).

El ctid es una columna de sistema que puedes consultar:

Los veinte productos están en el bloque 0: caben de sobra en una sola página de 8 kB. Ese detalle, aparentemente anecdótico, es la razón de que ningún índice sirva de nada en esta tabla, y volverá en la sección 7 y en 08-05.

Ojo: el ctid no es un identificador estable. Cambia cuando la fila se actualiza, porque PostgreSQL escribe una versión nueva en otro sitio. Nunca lo guardes en una columna ni lo uses como clave: para eso está id. Aquí solo lo usamos para ver la maquinaria por dentro.

La consecuencia de que el índice guarde un puntero y no la fila es importante: una búsqueda por índice implica dos pasos. Primero se baja por el árbol hasta la hoja y se obtiene el ctid; después hay que ir a la tabla (el heap) a leer la fila y recoger las columnas que pediste. A ese segundo paso se le llama heap fetch, y si tu consulta devuelve mil filas, son mil saltos a posiciones posiblemente dispersas del disco.

De ahí salen dos de los nodos de plan que verás constantemente en 08-05:

El segundo es el premio gordo: se ahorra el heap fetch entero. Y se consigue con lo que se llama un índice cubridor (covering index): un índice que cubre todas las columnas que la consulta necesita, tanto para filtrar como para mostrar.

En la lección 08-02 verás cómo fabricar índices cubridores a propósito con la cláusula INCLUDE, que añade columnas al índice solo para leerlas, sin usarlas para ordenar.

Matiz honesto: en PostgreSQL el Index Only Scan no siempre evita el 100 % de los accesos a la tabla. El índice no sabe si una fila es visible para tu sesión, así que consulta un mapa auxiliar (visibility map) y, para los bloques marcados como no del todo limpios, sí va al heap. Eso aparece en el plan como Heap Fetches: N. Si ese número es alto, la tabla necesita mantenimiento (VACUUM), y eso se trata en 08-05.

Un B-tree está ordenado. Todo lo que pueda expresarse como "ve a un punto del orden y avanza" lo resuelve; todo lo demás, no. Esta tabla cierra tres promesas que el curso lleva arrastrando desde los módulos 2, 4 y 6:

Las cuatro filas rojas del final tienen la misma causa: para usar un índice, la columna tiene que aparecer desnuda a un lado de la comparación. En cuanto la envuelves en una función, en un cálculo o en un comodín inicial, el motor ya no puede traducir tu condición a "un punto del orden y avanza".

Dos consecuencias prácticas que resolverás en las próximas lecciones, no ahora:

• Las condiciones de las filas rojas se pueden reescribir casi siempre. EXTRACT(YEAR FROM fecha_pedido) = 2025 es idéntico a fecha_pedido >= '2025-01-01' AND fecha_pedido < '2026-01-01', y esta segunda versión sí usa el índice: son los mismos 16 pedidos de 2025. Esa reescritura tiene nombre —sargabilidad— y es el corazón de la lección 08-04.
• Cuando no se pueden reescribir, hay herramientas específicas: un índice sobre la expresión para LOWER(email) (08-02) y la extensión pg_trgm con un índice GIN para el LIKE '%aceite%' que 04-01 dejó pendiente (08-03).

Aquí llega la sorpresa: nunca has escrito un CREATE INDEX y TiendaVerde ya tiene once índices. En 05-01 se dijo de pasada que PRIMARY KEY y UNIQUE "se implementan creando un índice por debajo, y las estructuras se ven en el módulo 8". Ha llegado el momento.

Un solo índice, productos_pkey, y btree escrito explícitamente: es la estructura de la sección 3. Para verlos todos de golpe, la vista de catálogo pg_indexes:

Once índices: nueve de las claves primarias y dos de los UNIQUE de categorias.nombre y clientes.email. Todos son UNIQUE INDEX ... USING btree, y todos existen porque una restricción los necesita: la única forma razonable de comprobar "este valor no está repetido" antes de aceptar un INSERT es tener los valores ordenados. Por eso la restricción y el índice son en PostgreSQL el mismo objeto físico, y por eso un INSERT en clientes con un email duplicado falla en microsegundos y no leyendo las 15 filas.

Esto explica también por qué WHERE c.id = 7 o WHERE c.email = '[email protected]' han sido siempre rápidas en el curso, aunque nadie hubiera hablado de índices.

Y ahora el punto más valioso de la lección. Mira otra vez la lista: productos.categoria_id no aparece. Ni pedidos.cliente_id. Ni lineas_pedido.pedido_id.

PostgreSQL crea un índice automáticamente para PRIMARY KEY y para UNIQUE, pero NO para las claves foráneas. El índice existe en el lado referenciado (la PK del padre), nunca en el lado referenciante (la columna hija).

Estas son las once claves foráneas de TiendaVerde, todas sin índice:

Por qué duele, en dos frentes.

Primero, los JOIN. La consulta canónica del curso une lineas_pedido con pedidos, clientes y productos. Cada ON lp.pedido_id = pe.id puede resolverse en dos direcciones: buscando en pedidos por id (indexado, rápido) o buscando en lineas_pedido por pedido_id (no indexado, recorrido completo). Con 47 líneas da igual; con 20 millones, "las líneas del pedido 8.412.006" sin índice significa leer los 20 millones.

Segundo, y menos conocido: los borrados en el padre. Cuando ejecutas DELETE FROM pedidos WHERE id = 5, PostgreSQL está obligado a comprobar todas las tablas que referencian a pedidos —lineas_pedido y devoluciones— para aplicar el CASCADE. Si esas columnas no tienen índice, cada borrado de un pedido provoca un recorrido completo de las tablas hijas. Es una de las causas clásicas de "borrar una fila tarda 40 segundos" en bases de datos grandes, y el motor no te avisa: la consulta que va lenta no es la que escribiste.

Nota de dialecto: esto no es así en todos los motores. MySQL con InnoDB crea un índice automáticamente sobre cada columna de clave foránea si no existe ya uno que sirva, y de hecho lo exige. Oracle y SQL Server se comportan como PostgreSQL: no lo crean, y su documentación oficial recomienda crearlo a mano. Si vienes de MySQL, esta es probablemente la diferencia de rendimiento que más te va a morder al migrar.

La regla práctica que te llevas —y que aplicarás en la lección siguiente— es corta: casi toda columna de clave foránea quiere su índice. "Casi", porque hay excepciones (una tabla hija diminuta, o una FK por la que nunca se filtra ni se borra), y esas excepciones son el tema de 08-03.

Si los índices fueran gratis, la respuesta sería indexarlo todo. No lo son, y conviene tener el coste presente desde el primer minuto:

La consecuencia es que un índice es una apuesta: aceleras las lecturas que lo usan a cambio de encarecer todas las escrituras de la tabla. En una tabla que se lee mil veces por cada escritura, la apuesta es excelente. En una tabla de registro de eventos que se escribe constantemente y se consulta una vez al mes, es un mal negocio. Cuantificar esa apuesta —y la lista de casos en los que no hay que indexar— es toda la lección 08-03.

• Creer que crear un índice garantiza que se use. El planificador decide. Con tablas pequeñas, con filtros poco selectivos o con estadísticas desfasadas, elegirá el Seq Scan y hará bien.
• Pensar que el índice contiene la fila. Contiene la clave y un puntero. De ahí el segundo acceso a la tabla, el Index Scan frente al Index Only Scan y la existencia misma de los índices cubridores.
• Dar por hecho que las claves foráneas están indexadas. En PostgreSQL no lo están. Es el descubrimiento más rentable de esta lección.
• Indexar una columna y seguir filtrando con una función encima. LOWER(email) no usa el índice sobre email. O reescribes la consulta, o creas un índice sobre la expresión (08-02).
• Esperar milagros de LIKE '%texto%'. Ningún B-tree puede ayudarte sin prefijo. La solución existe, pero es otra familia de índices (08-03).
• Confundir el ctid con un identificador. Cambia con cada UPDATE. Para identificar una fila está su clave primaria.
• Consejo: piensa en "un punto del orden y avanzar". Si tu condición se puede traducir a eso, el índice sirve. Si no, no. Es el mejor filtro mental que existe para predecir un plan sin ejecutarlo.
• Consejo: mira siempre \d tabla antes de crear un índice. Es la forma más rápida de descubrir que ya existe uno equivalente.
• Consejo: memoriza la tabla de alturas. Saber que 15 millones de filas son 3 accesos te ahorra discusiones enteras sobre si "la tabla es demasiado grande para buscar".

Para cada condición, di si un índice B-tree sobre la columna implicada podría usarse, y justifícalo en una frase.

Sin ejecutar nada, responde:

1. ¿Cuántos índices tiene hoy la tabla lineas_pedido y sobre qué columnas?
2. ¿Cuáles de sus columnas son claves foráneas y cuáles de ellas están indexadas?
3. DELETE FROM pedidos WHERE id = 5 borró en cascada tres líneas en la lección 01-06. Describe qué tiene que hacer el motor en lineas_pedido para localizarlas, y cómo cambiaría con 20 millones de líneas.

Un compañero propone: "Como los índices aceleran las consultas, vamos a crear uno sobre cada columna de pedidos: cliente_id, empleado_id, fecha_pedido, estado, metodo_pago y gastos_envio."

1. Da dos argumentos técnicos en contra, usando lo visto en las secciones 6 y 9.
2. ¿Cuáles de esas seis columnas te parecen candidatas razonables y cuáles no? Justifícalo con la naturaleza de los datos de TiendaVerde.

Solución 1

Solución 2

1. Uno solo: lineas_pedido_pkey, un índice único B-tree sobre id, creado por la PRIMARY KEY.

2. Tiene dos claves foráneas, pedido_id (→ pedidos, ON DELETE CASCADE) y producto_id (→ productos, ON DELETE RESTRICT), y ninguna de las dos está indexada. Es la tabla con más volumen del esquema y la que aparece en prácticamente todos los JOIN del curso: es la primera candidata a índice de todo TiendaVerde.

3. Para aplicar el CASCADE, el motor debe encontrar todas las filas con pedido_id = 5. Sin índice sobre pedido_id, la única forma es un recorrido secuencial completo de lineas_pedido: leer las 47 filas, quedarse con las 3 y borrarlas. Con 20 millones de líneas, ese mismo DELETE de una fila en pedidos obligaría a leer 20 millones de filas —y otro tanto en devoluciones, que también referencia pedidos con CASCADE—. La consulta lenta no sería la que escribiste, sino la comprobación de integridad que dispara: por eso este caso es tan difícil de diagnosticar sin EXPLAIN (08-05).

Solución 3

1. Dos argumentos:

• Cada índice encarece todas las escrituras de la tabla. Con seis índices, un INSERT en pedidos pasa de actualizar una estructura (la PK) a actualizar siete. Un pico de pedidos en Navidad se convierte en un problema de escritura que antes no existía.
• Varios de esos índices no se usarían nunca. Un índice solo sirve si el planificador lo elige, y para eso el filtro tiene que ser selectivo. estado tiene 5 valores para 20 pedidos y metodo_pago tiene 4: filtrar por uno de ellos devuelve una fracción enorme de la tabla, y leerla entera secuencialmente es más barato que ir al índice y volver a la tabla fila a fila.

2. El reparto:

Fíjate en el criterio que asoma: no se indexa una columna porque exista, sino porque hay consultas reales que filtran, unen u ordenan por ella y devuelven pocas filas. Ese criterio es el hilo de las tres lecciones siguientes.

Ya tienes el modelo mental completo:

• Un índice es una copia ordenada de una columna más un puntero a la fila, como el índice alfabético de un libro, salvo que puede haber muchos, hay que mantenerlos vivos y los usa el planificador, no tú.
• Sin índice solo hay Seq Scan, cuyo coste crece linealmente. Un B-tree crece logarítmicamente: con un fanout de unas 250 entradas por página, 15 millones de filas caben en 3 niveles, y los niveles altos viven en caché.
• El índice guarda la clave y el ctid, así que normalmente hace falta un segundo acceso a la tabla: eso separa el Index Scan del Index Only Scan, y de ahí nace el concepto de índice cubridor.
• Un B-tree sirve para igualdades, rangos, IN, prefijos LIKE 'abc%', ORDER BY, MIN y MAX; no sirve para LIKE '%abc', para funciones o cálculos sobre la columna filtrada ni para <>. La columna tiene que aparecer desnuda en la condición.
• TiendaVerde ya tiene once índices que nadie creó —nueve de las PK y dos de los UNIQUE—, y le faltan los de sus once claves foráneas, porque PostgreSQL no las indexa automáticamente (MySQL/InnoDB sí). Eso penaliza los JOIN y, muy en particular, los borrados en cascada.
• Un índice se paga en espacio y en escrituras más lentas, así que es una apuesta que hay que ganar.

En la lección 08-02, Creación y gestión de índices, pasas a la acción: la sintaxis completa de CREATE INDEX, la variante CONCURRENTLY que no bloquea la tabla, los índices compuestos y la regla del prefijo por la izquierda que decide en qué orden poner las columnas, los índices parciales que solo indexan las filas que te interesan, los índices sobre expresiones que cierran el problema de LOWER(email), y cómo listarlos, medir su tamaño y detectar los que no usa nadie. Empezando, claro, por las once claves foráneas que acabas de descubrir sin cubrir.