Índices y planes de ejecución: haz que el optimizador trabaje para ti

Todo motor relacional incluye un optimizador que decide cómo ejecutar tu consulta, y todo optimizador es un modelo de costes alimentado por estadísticas. Cuando una consulta va lenta, la pregunta interesante no es «¿qué índice debería añadir?», sino «¿qué creía el optimizador y en qué se equivocó?». El plan de ejecución es el único sitio donde está esa respuesta.

Esta guía da por hecho que ya escribes predicados que el optimizador puede aprovechar: sin funciones sobre la columna indexada, sin conversiones implícitas, los hábitos que se tratan en buenas prácticas de SQL. Lo que sigue es la capa de debajo: qué es físicamente un índice, qué lo hace utilizable y cómo leer un plan con el detalle suficiente para actuar. Los ejemplos usan la sintaxis de PostgreSQL 17 e indican los equivalentes en Oracle.

Lo que un B-tree puede y no puede hacer

Casi todos los índices que creas son árboles equilibrados de claves ordenadas. Las páginas internas guardan claves separadoras que dirigen la búsqueda; las páginas hoja guardan las claves en orden, cada una con un puntero a la fila (un identificador de tupla del heap en PostgreSQL, un ROWID en Oracle). Las páginas hoja están enlazadas entre sí, de modo que, una vez que la búsqueda llega a su destino, el motor puede avanzar o retroceder sin volver a la raíz.

Esa estructura permite exactamente cuatro cosas, y entenderlas explica casi cualquier duda sobre índices:

1. Búsqueda por igualdad. Se desciende hasta la primera clave que coincide. El coste es logarítmico respecto al número de filas: tres o cuatro lecturas de página en una tabla grande.
2. Recorrido por rango. Se desciende una vez y luego se recorren las hojas mientras la clave cumpla el límite. >, <, BETWEEN y un LIKE 'abc%' por prefijo se reducen todos a esto.
3. Ordenación. Las hojas ya están ordenadas, así que un índice puede proporcionar el ORDER BY sin paso de ordenación, algo que importa muchísimo cuando va combinado con LIMIT.
4. Unicidad. Un índice único es la forma en que el motor garantiza las claves primarias y las restricciones de unicidad.

Lo que no puede hacer es responder a nada que no empiece en un punto conocido del orden de las claves. LIKE '%invoice%' no tiene prefijo de partida. Tampoco un predicado sobre la segunda columna de un índice compuesto cuando la primera no está restringida, aunque PostgreSQL 18 suavizó esto con el skip scan, que sondea cada valor distinto de la columna inicial cuando esta tiene pocos valores distintos. Tómalo como una red de seguridad, no como un principio de diseño.

Índices compuestos: el orden de las columnas es el diseño

Un índice sobre (a, b, c) ordena las filas por a, luego por b dentro de cada a igual y luego por c. La consecuencia: el índice sirve para una consulta que restringe un prefijo inicial de esas columnas. WHERE a = ? lo usa, WHERE a = ? AND b = ? lo usa mejor, y WHERE b = ? a secas prácticamente no puede.

La regla que se deduce merece memorizarse: primero las columnas de igualdad, después la columna por la que recorres un rango u ordenas. En cuanto el recorrido llega a un predicado de rango, todo lo que viene detrás en el índice deja de servir para posicionar la búsqueda y solo sirve para filtrar las filas que devuelve el rango.

-- Query: recent unpaid invoices for one customer
SELECT id, issued_at, amount
FROM   invoice
WHERE  customer_id = $1 AND status = 'UNPAID' AND issued_at >= $2
ORDER  BY issued_at DESC
LIMIT  50;

-- Equalities first, range/sort column last
CREATE INDEX invoice_cust_status_issued
  ON invoice (customer_id, status, issued_at DESC);

Este índice resuelve toda la consulta con un único recorrido por rango y sin ordenación. Cambia las columnas a (issued_at, customer_id, status) y el motor tendrá que recorrer todas las facturas desde $2 y filtrar, lo cual no importa en una tabla pequeña y es desastroso en una grande.

Un índice de cobertura va un paso más allá: si el índice contiene todas las columnas que toca la consulta, el motor no visita la tabla en absoluto. PostgreSQL lo muestra como Index Only Scan, y solo es posible cuando el visibility map indica que las páginas implicadas son totalmente visibles, otra razón por la que el autovacuum importa, como se explica en la comparativa entre Oracle y PostgreSQL. Las columnas que solo se necesitan en la salida, nunca para filtrar ni ordenar, van en INCLUDE en lugar de en la clave, lo que mantiene el árbol más estrecho:

CREATE INDEX invoice_cust_issued_covering
  ON invoice (customer_id, issued_at DESC) INCLUDE (amount, status);

Selectividad y estadísticas

El optimizador elige entre un recorrido de índice y un recorrido secuencial según la selectividad estimada: la fracción de filas que conserva un predicado. Un índice compensa cuando esa fracción es pequeña, porque cada fila que coincide puede costar una lectura de página aleatoria. En cuanto la fracción supera unos pocos puntos porcentuales de una tabla grande, leerlo todo secuencialmente sale más barato. Por eso una columna status con dos valores casi nunca se beneficia de un B-tree normal, y por eso a nadie debería sorprenderle que un índice sobre ella no se use.

Las estimaciones de selectividad salen de las estadísticas que recogen ANALYZE (PostgreSQL, que el autovacuum lanza automáticamente) o DBMS_STATS (Oracle): el número de valores distintos por columna, la fracción de nulos, una lista de los valores más frecuentes con su frecuencia y un histograma del resto. Hay tres situaciones que las estropean:

• Estadísticas obsoletas tras una carga masiva o una actualización en bloque. La solución es ejecutar ANALYZE como último paso de cualquier proceso ETL.
• Columnas correlacionadas. El planificador supone que son independientes, así que WHERE city = 'Lyon' AND country = 'FR' se estima multiplicando dos selectividades aunque la primera implique la segunda, y el resultado es una estimación muy por debajo de la real. La respuesta de PostgreSQL son las estadísticas extendidas: CREATE STATISTICS city_country (dependencies, ndistinct) ON city, country FROM address;
• Distribuciones sesgadas. Una columna en la que un solo valor cubre el 90 % de las filas necesita un histograma y, en Oracle, se beneficia del adaptive cursor sharing para que distintos valores de bind puedan obtener planes distintos.

Subir default_statistics_target (o su equivalente por columna) afina los histogramas a costa de un análisis más lento. Hazlo para las pocas columnas con distribuciones realmente complicadas, no de forma global.

Tipos de índice más allá del B-tree

Hay dos modificadores aplicables a cualquiera de ellos. Un índice parcial almacena solo las filas que cumplen un predicado, lo que es perfecto cuando las consultas siempre apuntan a un subconjunto pequeño: CREATE INDEX ON job (created_at) WHERE status = 'PENDING'; te da un índice diminuto sobre una tabla de cola, por mucho historial completado que se acumule. Un índice de expresión almacena el resultado de una función y devuelve la utilidad del índice a un predicado que de otro modo no sería sargable: CREATE INDEX ON person (lower(email)); lo usa WHERE lower(email) = $1, siempre que la consulta repita exactamente la misma expresión.

PostgreSQL también construye sobre la marcha bitmap heap scans: reúne en un bitmap en memoria los identificadores de tupla que coinciden en uno o varios índices, los ordena por ubicación física y luego lee el heap en ese orden. Así es como dos índices independientes de una sola columna pueden colaborar en un AND o un OR: resulta útil, pero nunca tanto como un índice compuesto bien ordenado.

Cómo leer EXPLAIN ANALYZE

Pide la versión medida, con buffers, y léela como un árbol que se ejecuta desde el nodo más interno hacia fuera:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT c.name, sum(i.amount)
FROM   customer c
JOIN   invoice i ON i.customer_id = c.id
WHERE  i.issued_at >= date '2026-01-01'
GROUP  BY c.name;

HashAggregate  (cost=18422.10..18560.42 rows=11066 width=40)
               (actual time=214.883..218.014 rows=10871 loops=1)
  Group Key: c.name
  ->  Hash Join  (cost=612.00..17250.44 rows=234332 width=36)
                 (actual time=6.201..168.442 rows=233980 loops=1)
        Hash Cond: (i.customer_id = c.id)
        ->  Index Scan using invoice_issued_at_idx on invoice i
              (cost=0.43..14887.02 rows=234332 width=12)
              (actual time=0.038..92.117 rows=233980 loops=1)
              Index Cond: (issued_at >= '2026-01-01'::date)
              Buffers: shared hit=4210 read=8891
        ->  Hash  (cost=474.00..474.00 rows=11040 width=32)
                  (actual time=6.104..6.105 rows=11040 loops=1)
              ->  Seq Scan on customer c  (cost=0.00..474.00 rows=11040 width=32)
Planning Time: 0.412 ms
Execution Time: 219.774 ms

Léelo en este orden:

1. Compara el rows= estimado con el rows= real en cada nodo. Un factor de diez o más es la señal que buscas, y el nodo más bajo donde aparece es la causa; todo lo que está por encima hereda el error. Aquí las estimaciones son excelentes, así que la forma del plan es fiable.
2. Multiplica por loops. Dentro de un bucle anidado, los recuentos de filas y los tiempos reales son por iteración. Un nodo que muestra 3 filas y 0,2 ms con loops=50000 ha consumido diez segundos.
3. Mira Buffers. shared hit salió de la caché, read vino del sistema operativo y temp read/written significa que una operación se desbordó a disco, normalmente una ordenación o un hash que superó work_mem.
4. Distingue Index Cond de Filter. Una condición de índice acota el recorrido; un filtro se aplica a filas que el recorrido ya ha devuelto. Rows Removed by Filter: 480000 significa que has leído medio millón de filas para tirarlas.
5. Revisa los tiempos globales. Un tiempo de planificación alto en relación con el de ejecución apunta a demasiadas particiones o a una tabla con exceso de índices.

Los operadores de join cuentan su propia historia. El Nested Loop sondea la relación interna una vez por cada fila externa, lo que es óptimo cuando el lado externo es diminuto y el sondeo interno está indexado, y catastrófico cuando la estimación del lado externo era errónea. El Hash Join construye una tabla hash con la entrada más pequeña y hace pasar la más grande por ella; es la elección habitual para equijoins grandes. El Merge Join consume ambos lados en orden, lo que sale barato cuando los índices ya aportan ese orden y caro cuando obliga a dos ordenaciones. La salida de planes de Oracle nombra las mismas tres formas y añade métodos de distribución de HASH JOIN en la ejecución en paralelo.

Cuando el planificador se equivoca

Los malos planes casi siempre se remontan a una mala estimación, y la lista de causas es corta:

• Una estimación de filas errónea, por estadísticas obsoletas, columnas correlacionadas o un predicado que el optimizador no puede interpretar: una llamada a función, un valor oculto tras un bind opaco o un join sobre una expresión.
• Sensibilidad a los parámetros. El plan genérico de una sentencia preparada puede construirse para un valor medio y reutilizarse después para uno muy sesgado. PostgreSQL genera planes personalizados en las primeras ejecuciones y puede pasar luego a uno genérico; fijar plan_cache_mode = force_custom_plan en la sesión sirve para diagnosticarlo. Oracle llama a este fenómeno bind peeking y lo mitiga con adaptive cursor sharing.
• Un índice que falta, o un índice cuyo orden de columnas no encaja con el predicado.
• Un modelo de costes desajustado. El valor por defecto de random_page_cost, 4.0, supone discos mecánicos. En SSD, bajarlo a 1.1 inclina legítimamente la balanza hacia los recorridos de índice.
• Un work_mem insuficiente, que obliga a un hash o a una ordenación a desbordarse a disco; el plan es correcto, pero la ejecución no.

Un ORM añade una capa de indirección, pero no cambia nada de esto: el SQL generado sigue teniendo un plan y el lazy loading sigue convirtiendo una consulta en mil. Consulta Spring Data JPA para ver cómo cargar datos por conjuntos.

El coste de un índice

Los índices no son gratis, y la factura llega por el lado de la escritura. Cada inserción y cada borrado actualizan todos los índices de la tabla; una actualización toca los índices cuyas columnas modifica, y todos si la fila no puede quedarse en su página. Los índices ocupan además caché que, de otro modo, usarían las páginas de datos, y se hinchan: un índice muy actualizado acumula entradas muertas hasta que se reconstruye. En PostgreSQL, REINDEX INDEX CONCURRENTLY reconstruye sin tomar un bloqueo disruptivo, y CREATE INDEX CONCURRENTLY añade uno a una tabla en uso a costa de dos pasadas sobre la tabla.

Antes de añadir un índice, comprueba si puedes ampliar uno existente: un índice sobre (customer_id) sobra en cuanto existe (customer_id, issued_at). Antes de conservar uno, comprueba si algo lo usa:

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
ORDER  BY pg_relation_size(indexrelid) DESC;

Cero scans desde el último reset de estadísticas, en una tabla que sí se consulta, significa que el índice es puro lastre, salvo que respalde una restricción. Diseña bien el esquema primero, como se explica en diseño de bases de datos y normalización: los índices se ajustan a los patrones de acceso, no rescatan un modelo roto.

Puntos clave

• Un B-tree resuelve igualdad, rangos, ordenación y unicidad a partir de una estructura ordenada, y solo puede empezar un recorrido desde un prefijo conocido de su clave.
• En un índice compuesto, pon primero las columnas de igualdad y al final la de rango u ordenación; añade con INCLUDE las columnas de salida que no se filtran para permitir index-only scans.
• El optimizador decide según la selectividad estimada, así que unas estadísticas erróneas (obsoletas, correlacionadas o sesgadas) son la causa habitual de un mal plan.
• Más allá de los B-trees, usa GIN para datos multivaluados, BRIN para tablas enormes correlacionadas con el orden físico, índices bitmap para data warehouses de lectura mayoritaria e índices parciales o de expresión para que un subconjunto pequeño salga barato.
• Lee EXPLAIN ANALYZE desde el nodo más interno hacia fuera, compara filas estimadas con reales, multiplica por las iteraciones y vigila las filas descartadas por filtros y los desbordamientos a ficheros temporales.
• Cada índice penaliza las escrituras y consume caché: amplía un índice existente en lugar de añadir un casi duplicado, y elimina lo que pg_stat_user_indexes muestra que nadie usa.