Índices en bases de datos relacionales: la guía que necesitabas

Un índice mal entendido se convierte en una superstición: “si la consulta va lenta, añade un índice”. A veces funciona por casualidad. Otras veces el índice no lo usa nadie, ocupa espacio, ralentiza cada escritura de la tabla, y sigue ahí años después porque nadie se atreve a borrarlo. Esta guía es la explicación de fondo que hace innecesaria la superstición: cómo funciona un índice por dentro, cuándo ayuda de verdad, cuándo te cuesta más de lo que te da, y cómo comprobarlo con datos en lugar de intuición.

Qué es realmente un índice

Sin índice, encontrar una fila que cumple una condición exige recorrer la tabla entera: un sequential scan. Con N filas, eso es una operación de coste O(N): si duplicas la tabla, duplicas el tiempo de búsqueda.

Un índice es una estructura de datos adicional, ordenada, que mapea valores de una o varias columnas a la ubicación física de las filas que los contienen. La estructura que usa PostgreSQL por defecto —y la que usan también MySQL (InnoDB) y la mayoría de motores relacionales para el caso general— es el B-tree (árbol balanceado).

graph TD
Root["Nodo raíz"] --> A["Nodo interno: valores 1-1000"]
Root --> B["Nodo interno: valores 1001-2000"]
A --> A1["Hoja: 1-500"]
A --> A2["Hoja: 501-1000"]
B --> B1["Hoja: 1001-1500"]
B --> B2["Hoja: 1501-2000"]

Cada hoja del árbol contiene pares (valor de la columna indexada, puntero a la fila real en la tabla), ordenados. Buscar un valor concreto significa bajar por el árbol comparando el valor buscado contra los valores de cada nodo, un número de pasos proporcional a log(N) en lugar de a N. Con un millón de filas, eso es la diferencia entre comparar un millón de valores y comparar, aproximadamente, veinte. El árbol se mantiene balanceado automáticamente en cada inserción o borrado, así que ese rendimiento no se degrada con el tiempo ni con el crecimiento de la tabla.

Los B-tree, además de búsquedas puntuales (WHERE id = 42), resuelven muy bien rangos (WHERE created_at BETWEEN ...) y ordenaciones (ORDER BY), porque las hojas están ordenadas y se pueden recorrer secuencialmente una vez localizado el punto de partida.

Cuándo un índice ayuda

Un índice acelera de forma clara:

• Búsquedas por igualdad sobre columnas con alta cardinalidad (muchos valores distintos): email, id, uuid.
• Rangos y ordenaciones sobre columnas de fecha o numéricas: WHERE created_at > ..., ORDER BY price.
• Claves foráneas usadas en JOIN: si unes orders.customer_id con customers.id, ambas columnas se benefician de un índice (la clave primaria ya lo tiene; la foránea, no siempre, y PostgreSQL no la indexa automáticamente).
• Columnas usadas en WHERE de consultas que se ejecutan con mucha frecuencia, incluso si la tabla no es gigante: el coste de un sequential scan que se repite miles de veces al día se acumula.

Cuándo un índice perjudica

Aquí es donde la superstición se rompe. Cada índice tiene un coste que se paga en cada escritura, no solo en lectura:

• Cada INSERT, UPDATE (de una columna indexada) o DELETE tiene que actualizar también el índice, no solo la tabla. Diez índices sobre una tabla significan que cada inserción hace diez escrituras adicionales, no una.
• Columnas con baja cardinalidad (un booleano, un enum de tres valores) casi nunca se benefician de un B-tree: el planificador suele preferir un sequential scan de todas formas porque filtrar por un valor que representa el 30% de la tabla no reduce lo suficiente el trabajo.
• Índices redundantes: un índice sobre (a, b) ya cubre las consultas que filtran solo por a (por la regla de prefijo que vemos abajo), así que mantener también un índice separado sobre a en solitario suele ser puro desperdicio.
• Tablas con escritura muy intensiva y lectura poco frecuente (colas, logs de eventos que se consultan rara vez): cada índice adicional penaliza el caso común (escribir) para optimizar el caso raro (leer).

Índices compuestos: el orden de las columnas importa

Un índice compuesto (o multicolumna) indexa varias columnas juntas, como CREATE INDEX ON orders (customer_id, status, created_at). La regla que determina cuándo el planificador puede usarlo es la regla del prefijo izquierdo: el índice sirve para consultas que filtran por las columnas de izquierda a derecha sin saltarse ninguna.

-- Este índice:
CREATE INDEX idx_orders_customer_status_date
  ON orders (customer_id, status, created_at);

-- SÍ puede usarlo:
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid';
SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid'
  AND created_at > '2026-01-01';

-- NO puede usarlo eficientemente (se salta customer_id):
SELECT * FROM orders WHERE status = 'paid';

-- NO puede usar la parte de created_at para un rango eficiente
-- si status no viene con igualdad:
SELECT * FROM orders WHERE customer_id = 42 AND created_at > '2026-01-01';

La convención práctica: coloca primero las columnas que filtras por igualdad, y al final la columna por la que filtras por rango u ordenas. Si sueles filtrar por customer_id y status con =, y ordenar o filtrar por rango en created_at, ese orden —igualdad, igualdad, rango— es el que aprovecha mejor la estructura ordenada del árbol.

PostgreSQL 18 añadió skip scans sobre índices B-tree multicolumna, que permiten usar un índice compuesto incluso cuando la consulta omite la primera columna en ciertos casos, algo que en versiones anteriores obligaba directamente a un sequential scan. Es una mejora real, pero no sustituye a diseñar el orden de columnas con criterio: sigue siendo más rápido un índice que coincide con tu patrón de consulta que uno que el planificador tiene que usar de forma subóptima.

Índices de cobertura (INCLUDE)

Un índice normal, una vez localizada la fila, todavía tiene que ir a la tabla (el heap) a leer las columnas que no forman parte del índice. Un índice de cobertura añade columnas “de carga” con INCLUDE para evitar ese viaje extra cuando la consulta solo necesita datos que ya están en el índice:

CREATE INDEX idx_orders_customer_covering
  ON orders (customer_id)
  INCLUDE (status, total_amount);

Con este índice, SELECT status, total_amount FROM orders WHERE customer_id = 42 puede resolverse enteramente desde el índice, sin tocar la tabla: eso es un Index Only Scan, visible en el plan de EXPLAIN. La contrapartida: cada columna en INCLUDE se copia dentro del índice y se actualiza en cada escritura sobre esa columna, así que solo tiene sentido para columnas relativamente estables que se leen con mucha frecuencia, no para columnas que cambian en cada UPDATE.

Cómo leer un EXPLAIN ANALYZE sin adivinar

EXPLAIN muestra el plan que el optimizador piensa usar. EXPLAIN ANALYZE va más allá: ejecuta la consulta de verdad y añade tiempos y filas reales, lo que te permite comparar la estimación del planificador contra la realidad. La forma más completa de ejecutarlo:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'paid';

Lo que hay que mirar, en orden de importancia:

1. El tipo de nodo de acceso: Seq Scan significa que no se usó ningún índice (puede ser correcto en tablas pequeñas o cuando se lee casi toda la tabla). Index Scan significa que se usó un índice pero todavía hubo que visitar la tabla. Index Only Scan significa que el índice cubrió toda la consulta, sin tocar la tabla. Bitmap Index Scan + Bitmap Heap Scan aparece cuando el planificador combina varios índices o espera muchas filas coincidentes.
2. rows=X estimado contra actual rows=Y real: si la diferencia es grande (el planificador esperaba 10 filas y se encontró con 100.000), las estadísticas de la tabla están desactualizadas. ANALYZE nombre_tabla; las regenera.
3. actual time=inicio..fin: el tiempo real en milisegundos de ese nodo del plan, no del total de la consulta.
4. Buffers: shared hit=X read=Y (con la opción BUFFERS): hit son páginas servidas desde caché de memoria, read son páginas que hubo que traer de disco. Un read alto en una consulta que se repite mucho es señal de que los datos relevantes no caben en caché o de que el índice no está siendo reutilizado eficazmente.

Una checklist antes de crear (o borrar) un índice

1. ¿Esta columna aparece en el WHERE, JOIN o ORDER BY de una consulta frecuente? Si no, probablemente no lo necesitas.
2. ¿Tiene cardinalidad suficiente como para que el índice reduzca de verdad el conjunto de filas a examinar?
3. Si es un índice compuesto, ¿el orden de columnas sigue la regla de prefijo izquierdo según tus consultas reales, no según cómo “se ve mejor” el CREATE INDEX?
4. ¿La tabla tiene una carga de escritura alta? Si es así, cada índice adicional tiene un coste medible: confírmalo con EXPLAIN ANALYZE antes y después, no solo en la lectura sino también cronometrando el INSERT/UPDATE.
5. Revisa pg_stat_user_indexes periódicamente y elimina lo que no se usa.

Los índices no son una optimización que se aplica una vez y se olvida: son parte del contrato entre tu patrón de consultas real y la estructura de tus tablas, y ese contrato cambia según evoluciona la aplicación. La única forma fiable de mantenerlo es medir con EXPLAIN ANALYZE, no memorizar reglas generales y aplicarlas a ciegas.