Esta es la lección que llevamos anunciando desde el módulo 5. Cuando en 05-04 discutíamos si desnormalizar el esquema de BiblioRed, la regla operativa era tajante: los índices son lo primero que hay que probar antes de desnormalizar, y no se puede decidir una desnormalización sin haber leído antes un plan de ejecución. Aquí aprendemos a hacer las dos cosas.

El problema concreto ya tiene nombre y tiene número. El listado de préstamos vencidos por sucursal —el que el personal de mostrador abre cada mañana para llamar a los socios que se retrasan— tarda catorce segundos. En el portátil de desarrollo, con 3.000 préstamos de prueba, tardaba 30 milisegundos. En producción, con 2.841.077 filas en prestamos, tarda catorce segundos y la persona que atiende tiene a un socio delante mirándola.

Catorce segundos es un número interesante porque está en el peor sitio posible: es demasiado para trabajar y demasiado poco para que alguien lo declare una avería. Sencillamente, "el sistema va lento". Y de esa frase salen todos los rediseños innecesarios del mundo.

En esta lección veremos qué es un índice y cómo consigue convertir millones de comparaciones en cuatro lecturas de disco; qué cuesta un índice, porque no son gratis; los tipos que existen y cuándo usar cada uno; qué se indexa y qué no; cómo se lee un plan de ejecución línea a línea; los antipatrones que anulan un índice sin que nadie se dé cuenta; y el caso completo de esos catorce segundos, con su diagnóstico, su solución y su medición. Al final tendremos también el orden de intervención correcto ante una consulta lenta, que es lo que cierra el círculo abierto en 05-04.

1. Qué es un índice: dos analogías que sirven
2. Cómo funciona un B-tree
3. Los números: cuántas lecturas ahorra de verdad
4. El coste de un índice: espacio, escrituras y planificación
5. CREATE INDEX: sintaxis, índices únicos y CONCURRENTLY
6. Índices compuestos y la regla del prefijo más a la izquierda
7. Índices parciales
8. Índices de expresión
9. Panorama de tipos de índice en PostgreSQL
10. Qué se indexa y qué no
11. Leer un plan de ejecución: EXPLAIN y EXPLAIN ANALYZE
12. Los nodos que aparecen una y otra vez
13. Coste, estimaciones y la señal de alarma
14. Las estadísticas y ANALYZE
15. Caso completo: los catorce segundos del listado de vencidos
16. Antipatrones que anulan un índice
17. Cómo se optimiza de verdad: el orden de intervención
18. Mantenimiento e índices fuera de PostgreSQL

Definición. Un índice es una estructura de datos auxiliar, mantenida automáticamente por el gestor, que permite localizar las filas que cumplen una condición sin recorrer la tabla entera.

Dos analogías, y las dos son de una biblioteca, que en BiblioRed viene muy a mano.

La primera: el índice de un libro. Para saber en qué páginas se habla de "normalización" en un manual de 900 páginas, no lees las 900. Vas al índice analítico del final, buscas la palabra —que está ordenada alfabéticamente, así que la encuentras en segundos— y lees "normalización: 412, 418-431, 507". Tres datos que te llevan directamente a lo que buscas.

Los tres elementos de esa analogía son exactamente los de un índice de base de datos:

La segunda: la propia biblioteca. Los 40.000 ejemplares de BiblioRed están colocados en las estanterías en un orden concreto —por materia y signatura—. Ese orden es un índice: permite encontrar un libro de historia sin recorrer las cuatro sucursales. Pero fíjate en que solo hay un orden físico posible: los libros no pueden estar simultáneamente ordenados por materia, por autor y por año.

Por eso las bibliotecas tienen, además, un catálogo: fichas ordenadas por autor, otras por título, otras por materia. Cada catálogo es un índice adicional que no cambia dónde está el libro, solo añade una forma más de encontrarlo. Y cada uno hay que actualizarlo cuando entra un ejemplar nuevo.

Esa es exactamente la situación de una tabla: los datos están en un orden (el que impuso el INSERT), y cada índice es un catálogo adicional que ofrece otra forma de llegar a ellos, con su coste de espacio y de mantenimiento.

El 95 % de los índices que crearás son B-tree (árbol B, en su variante B+). Es el tipo por omisión en PostgreSQL y en prácticamente todos los gestores. Entender cómo funciona explica casi todo su comportamiento.

Un B-tree es un árbol equilibrado en el que:

• Cada nodo contiene claves ordenadas y punteros.
• Los nodos internos solo sirven para dirigir la búsqueda: dicen "si buscas algo menor que X, ve por aquí".
• Los nodos hoja contienen las claves y los punteros a las filas reales.
• Las hojas están encadenadas entre sí, lo que permite recorridos por rango sin volver a subir.
• El árbol está equilibrado: todas las hojas están a la misma profundidad, así que toda búsqueda cuesta lo mismo.

Un índice sobre prestamos(fecha_devolucion_prevista), dibujado a escala reducida:

Para buscar fecha_devolucion_prevista = '2024-03-15':

1. En la raíz: 2024-03-15 está entre 2023-04-01 y 2025-02-01 → baja por el nodo interno del centro.
2. En el interno: está entre 2023-11-01 y 2024-07-01 → baja por la hoja correspondiente.
3. En la hoja: busca la clave exacta y obtiene el ctid de la fila.
4. Lee esa página de la tabla.

Cuatro accesos. Sobre 2,8 millones de filas.

Cada nodo de un B-tree ocupa una página de disco —8 KB en PostgreSQL— y en esa página caben muchas claves. Con una fecha (8 bytes) más un puntero (6 bytes), en 8 KB caben del orden de 500 entradas por nodo. Ese número se llama factor de ramificación.

Con tres niveles se direccionan 125 millones de filas. Los 2,8 millones de prestamos caben cómodamente en tres niveles, y en la práctica los niveles superiores están permanentemente en la caché de memoria (el gestor de buffers de 01-04), así que la búsqueda real cuesta una o dos lecturas de disco, no cuatro.

La fórmula: el número de niveles crece con el logaritmo en base 500 del número de filas. Multiplicar los datos por 500 añade un solo nivel. Esa es la razón por la que un índice sigue siendo rápido cuando la tabla crece: no escala con el número de filas, escala con su logaritmo.

Pongamos cifras a prestamos, con 2.841.077 filas y unos 120 bytes por fila.

La diferencia no es de un 20 %: es de cuatro órdenes de magnitud. Esa es la razón por la que la primera reacción ante una consulta lenta debe ser mirar si hay un índice, y no rediseñar el esquema.

Pero hay una letra pequeña fundamental, y explica muchas decisiones aparentemente extrañas del planificador:

Un índice solo compensa si la consulta devuelve una fracción pequeña de la tabla. Si va a devolver el 40 % de las filas, es más rápido recorrer la tabla entera secuencialmente que hacer un millón de saltos aleatorios.

La razón es física: leer 52.736 páginas seguidas aprovecha la lectura anticipada del sistema y del disco; leer 400.000 páginas salteadas no aprovecha nada. El punto de equilibrio en PostgreSQL suele estar entre el 5 % y el 10 % de la tabla, y lo decide el planificador con las estadísticas del apartado 14.

Por eso verás planes con Seq Scan sobre tablas indexadas y pensarás que el índice "no se usa". A menudo es que el planificador ha calculado, con razón, que no vale la pena.

Aquí está el motivo por el que la respuesta a "¿indexo esta columna?" no es siempre que sí.

Cuatro índices sobre una tabla de 412 MB suman 244 MB: más de la mitad del tamaño de los datos. En una base de datos con muchos índices es habitual que los índices ocupen más que las tablas. Eso no solo cuesta disco: cuesta memoria caché, que es un recurso mucho más escaso, y cada índice compite con los datos por ella.

Este es el coste que de verdad importa.

Una medición típica sobre una carga de 100.000 préstamos:

Más del triple. Y prestamos es una tabla que se escribe constantemente durante el horario de mostrador.

De aquí sale la práctica estándar de las cargas masivas: eliminar los índices, cargar, recrearlos. Recrear un índice de cero sobre datos ya presentes es mucho más rápido que mantenerlo fila a fila.

Cada índice adicional es una alternativa más que el planificador debe evaluar. Con dos o tres índices es imperceptible; con quince en la misma tabla, el tiempo de planificación empieza a notarse en consultas que se ejecutan miles de veces por minuto.

Un índice no es gratis. Se crea para una consulta concreta que se ejecuta con frecuencia suficiente para justificar su coste de escritura, y se elimina cuando esa consulta deja de existir.

Y para saber si sobra, PostgreSQL lleva la cuenta:

Ese idx_prestamos_fecha_prestamo con idx_scan = 0 es un índice que solo cuesta: 61 MB de disco, un árbol que mantener en cada INSERT, y cero beneficio. Candidato claro a DROP INDEX, previa comprobación de que las estadísticas cubren un periodo representativo (no lo elimines el día después de reiniciar el servidor, ni sin mirar si lo usa el informe anual).

La forma básica:

Convención de nombres: idx_<tabla>_<columnas>. No es obligatoria —PostgreSQL genera un nombre si no lo das— pero un índice sin nombre reconocible es un índice que nadie se atreverá a borrar dentro de tres años.

Aquí conviene aclarar una relación que confunde a mucha gente, y que enlaza con las restricciones de 04-04:

Toda restricción UNIQUE y toda PRIMARY KEY se implementan internamente con un índice único. Al declarar la restricción, el índice se crea solo.

La recomendación: usa la restricción UNIQUE cuando expreses una regla de negocio —queda documentada en el esquema, y otras tablas pueden referenciarla— y el índice único solo cuando necesites lo que la restricción no puede dar: expresiones o condiciones parciales.

Crear un índice sobre una tabla en producción tiene un problema serio:

Esta instrucción toma un bloqueo SHARE sobre prestamos durante toda su ejecución. Con 2,8 millones de filas puede tardar un minuto, y durante ese minuto nadie puede escribir en la tabla: los cuatro mostradores se quedan parados. Es el mecanismo de bloqueo de la lección 06-02 en acción.

La alternativa:

Tarda bastante más —hace dos pasadas sobre la tabla— pero no bloquea las escrituras. Sus condiciones:

• No puede ejecutarse dentro de una transacción explícita (recuerda las excepciones al DDL transaccional de 06-01).
• Si falla a mitad, deja un índice inválido que hay que eliminar y rehacer. Detectarlos:

En producción, siempre CONCURRENTLY. En un entorno de desarrollo o en una ventana de mantenimiento, la forma normal es más rápida.

Un índice puede abarcar varias columnas:

Y aquí aparece la regla que más confusión genera de toda la lección.

Regla del prefijo más a la izquierda. Un índice compuesto sobre (A, B, C) puede usarse para consultas que filtren por A, por A y B, o por A, B y C. No puede usarse eficazmente para consultas que filtren solo por B, solo por C, o por B y C.

Porque el índice está ordenado primero por A, y dentro de cada valor de A, por B. Es exactamente el orden de un listín telefónico ordenado por apellido y luego por nombre:

• Buscar "Alsina, Marta" es inmediato.
• Buscar todos los "Alsina" es inmediato.
• Buscar todas las "Marta" del listín obliga a recorrerlo entero, porque las Martas están dispersas por todas las páginas.

Un matiz honesto: PostgreSQL puede usar un índice compuesto sin el prefijo mediante un recorrido completo del índice (index scan sin condición de inicio), si el índice es mucho más pequeño que la tabla. Pero es un recurso desesperado y mucho más lento que un índice adecuado. La regla práctica sigue siendo válida.

Ejemplo aplicado. Estas dos consultas de BiblioRed:

Un solo índice (socio_id, fecha_prestamo) sirve para ambas: la igualdad primero, el rango después. Crear además un índice sobre (socio_id) sería redundante y solo costaría.

PostgreSQL permite añadir columnas al índice que no participan en la búsqueda pero sí en el resultado:

Sirve para conseguir un Index Only Scan (apartado 12): si todas las columnas que la consulta necesita están en el índice, no hace falta leer la tabla. Es una optimización notable en consultas muy repetidas.

Índice parcial. Índice que solo incluye las filas que cumplen una condición WHERE. Es más pequeño, más rápido y más barato de mantener.

Es probablemente la característica de PostgreSQL más infrautilizada, y es exactamente lo que BiblioRed necesita.

Observa los números:

De 2,8 millones de préstamos, solo 42.017 están abiertos: el 1,5 %. Y todas las consultas del mostrador —préstamos vigentes de un socio, vencidos por sucursal, avisos de devolución— filtran por fecha_devolucion IS NULL. Los otros 2,8 millones de filas son historia que nadie consulta en el día a día.

Comparación de tamaños:

61 MB frente a menos de 1 MB. Un índice 60 veces menor que:

• cabe entero en la caché de memoria y se consulta prácticamente sin tocar el disco;
• solo se actualiza cuando se crea un préstamo o se devuelve, no en cada modificación de filas históricas;
• y responde a la consulta caliente igual de bien.

PostgreSQL solo usará el índice parcial si puede demostrar que la consulta implica su condición. La condición del índice debe aparecer en el WHERE de forma reconocible:

Regla mental: si una consulta frecuente lleva siempre el mismo filtro sobre un estado, una bandera o un IS NULL, ese filtro debería estar en la definición del índice, no solo en la consulta.

Un índice normal sobre titulo no sirve para buscar LOWER(titulo), porque el índice guarda los títulos como están escritos y la consulta pregunta por otra cosa. La solución es indexar la expresión:

Y la consulta debe usar exactamente la misma expresión:

Otros casos habituales:

Ojo a los paréntesis dobles del último ejemplo: cuando la expresión no es una llamada a función, hay que envolverla en paréntesis propios.

Y una advertencia: la función debe ser inmutable (IMMUTABLE), es decir, devolver siempre lo mismo para la misma entrada. Por eso no se puede indexar now() ni una función que dependa de la configuración regional de la sesión:

En el apartado 16 veremos la cara oscura de esto: una función aplicada en el WHERE sobre una columna indexada anula el índice, y es uno de los antipatrones más frecuentes.

B-tree resuelve casi todo, pero conviene saber qué hay disponible y para qué:

El catálogo web de BiblioRed permite buscar por palabras del título y del resumen. Con LIKE '%palabra%' no hay índice que valga (apartado 16). Con búsqueda de texto completo, sí:

La columna generada mantiene el vector actualizado sin disparadores —es la técnica de 04-04— y el índice GIN lo hace consultable en milisegundos.

Un índice BRIN no guarda una entrada por fila, sino un resumen por bloque de páginas: el valor mínimo y el máximo de cada rango. Es diminuto, y funciona bien cuando el orden físico de las filas se corresponde con el valor de la columna, que es justo lo que ocurre con una tabla de historial donde las filas se insertan cronológicamente.

61 MB frente a 48 kB. A cambio, es menos preciso: descarta bloques enteros, no filas, así que después hay que filtrar. Para el informe anual de préstamos por trimestre es perfecto; para buscar un préstamo concreto, no.

Regla: si no sabes cuál elegir, es B-tree. Los demás tipos responden a necesidades muy concretas y se reconocen porque B-tree no puede acelerar el operador que necesitas.

Esto sorprende a casi todo el mundo, y es una de las causas más frecuentes de lentitud inexplicable:

PostgreSQL crea un índice automáticamente para la PRIMARY KEY y para las restricciones UNIQUE, pero NO para las claves ajenas. El índice está en el lado referenciado (la clave primaria del padre), no en el lado que referencia.

Consecuencias de no indexar prestamos.socio_id:

1. Cada JOIN con socios recorre prestamos entera.
2. Cada DELETE o UPDATE de la clave en socios recorre prestamos entera para comprobar la integridad referencial de 02-06. Borrar un socio en una tabla de 2,8 millones de filas puede tardar segundos.

Encontrar las claves ajenas sin índice:

Cuatro claves ajenas sin índice en BiblioRed. Las cuatro son candidatas inmediatas.

Regla práctica: indexa todas las claves ajenas, salvo que hayas comprobado que la tabla hija es pequeña y no se usa en JOIN.

socios.activo es un booleano. De 12.000 socios, 10.900 están activos: el 91 %.

El planificador ignora el índice, y hace bien: recuperar el 91 % de las filas por índice significa saltar por casi toda la tabla en orden aleatorio, lo que es más lento que leerla de corrido. El índice existe, ocupa espacio, se mantiene en cada escritura y no se usa nunca.

La misma columna con la selectividad invertida sí sirve, y por eso el índice parcial es la respuesta:

Esta segunda forma es la buena. La columna activo no aporta selectividad, pero restringe el índice a las filas interesantes, y las columnas indexadas son las que de verdad se buscan.

El plan de ejecución es la estrategia que el optimizador —esa caja del diagrama de 01-04— ha elegido para responder a la consulta. Leerlo es la habilidad central de esta lección: sin ella, optimizar es adivinar.

Aviso importante. EXPLAIN ANALYZE sobre un DELETE borra las filas. Para analizar una instrucción de escritura sin efectos, envuélvela en una transacción y deshazla:

BEGIN;
EXPLAIN ANALYZE DELETE FROM prestamos WHERE prestamo_id = 88301;
ROLLBACK;

La forma completa que conviene usar siempre:

Un plan es un árbol de nodos, y se lee de dentro hacia fuera y de abajo arriba:

• Cada -> marca un nivel de anidamiento.
• Los nodos más indentados se ejecutan primero.
• Cada nodo consume las filas que producen sus hijos y entrega filas a su padre.
• La primera línea es el último paso: lo que se devuelve al cliente.

El Bitmap Heap Scan merece explicación porque desconcierta. Cuando la consulta va a devolver, digamos, 40.000 filas de 2,8 millones, un Index Scan haría 40.000 saltos aleatorios por el disco. El bitmap resuelve el problema en dos fases: primero recoge del índice todas las direcciones, luego las ordena por posición física y lee la tabla de principio a fin visitando solo las páginas necesarias. Es el punto intermedio entre índice y recorrido secuencial, y verlo suele significar que el planificador está haciendo lo correcto.

La señal de alarma más común: un Nested Loop cuyo lado externo devuelve muchas más filas de las estimadas. Si el planificador esperaba 5 y hay 50.000, ejecutará 50.000 búsquedas en lugar de 5. Es la causa número uno de consultas que "de pronto" pasan de milisegundos a minutos.

Cuando veas external merge Disk: 84320kB, la ordenación se ha ido a disco. A menudo se arregla subiendo work_mem para esa consulta:

Cada nodo lleva dos bloques de números:

Tres advertencias imprescindibles sobre el coste:

1. El coste no son milisegundos. Es una unidad arbitraria en la que 1,0 equivale, por convenio, a leer una página secuencialmente. Sirve para comparar planes entre sí, no para predecir tiempo.
2. Los costes son acumulativos: el de un nodo incluye el de sus hijos. El coste total de la consulta es el de la primera línea.
3. Con loops > 1, los tiempos y las filas son POR ITERACIÓN. Un nodo con actual time=0.012..0.014 rows=1 loops=42017 no tardó 0,014 ms: tardó unos 590 ms en total. Es el error de lectura más frecuente y el que hace que la gente busque el problema en el sitio equivocado.

Compara siempre rows= estimado con rows= real en cada nodo. Una diferencia de más de un orden de magnitud es el diagnóstico más valioso que da un plan de ejecución.

Cuando el planificador estima 5 filas y hay 50.000, todas sus decisiones posteriores están mal fundadas: eligió Nested Loop porque creía que iba a iterar cinco veces. Y el plan no es malo por el algoritmo, es malo por la información.

El planificador no mira los datos: mira un resumen estadístico de los datos. Si el resumen está mal, el plan está mal.

Qué guarda PostgreSQL de cada columna:

El proceso autovacuum ejecuta ANALYZE automáticamente cuando una tabla acumula suficientes cambios (por omisión, un 10 % de las filas). Hay que forzarlo a mano en tres situaciones:

1. Después de una carga masiva. Las estadísticas son de antes de cargar.
2. Después de crear un índice de expresión. Necesita estadísticas propias de la expresión.
3. Antes de medir un plan, para no diagnosticar sobre información obsoleta.

Si una columna tiene una distribución muy irregular, se le puede pedir más detalle:

El valor por omisión es 100 (100 valores frecuentes y 100 cubetas de histograma). Subirlo mejora las estimaciones y encarece un poco el ANALYZE.

El planificador supone que las columnas son independientes. Cuando no lo son, se equivoca por mucho. Ejemplo real de BiblioRed: ejemplares.sucursal_id y ejemplares.estado no son independientes —la sucursal Este es pequeña y presta poco, así que casi todos sus ejemplares están disponibles—.

Es una herramienta poco conocida y muy eficaz cuando el síntoma es "el estimado y el real se separan solo cuando filtro por dos columnas a la vez".

Vamos al problema real, de principio a fin.

Execution Time: 14025.663 ms — El número que hay que bajar. 14 segundos.

Seq Scan on prestamos p ... (actual time=0.048..13755.902 rows=42017 loops=1) — Aquí está el 98 % del tiempo. El nodo tarda 13,7 de los 14 segundos él solo. Todo lo demás es ruido.

Rows Removed by Filter: 2799060 — La línea más elocuente del plan. PostgreSQL ha leído 2.841.077 filas y ha descartado 2.799.060. Ha hecho el 98,5 % del trabajo para nada.

Buffers: shared hit=1284 read=51452 — 51.452 páginas leídas de disco (read) y solo 1.284 encontradas en caché (hit). Son 402 MB de disco por una consulta que devuelve 312 filas.

rows=41960 estimado frente a rows=42017 real — Aquí no hay problema de estadísticas: la estimación es excelente. El planificador sabía perfectamente lo que hacía; eligió Seq Scan porque no tenía ninguna alternativa. No hay ningún índice que sirva.

Seq Scan on ejemplares e ... Rows Removed by Filter: 30002 — El segundo problema, mucho menor: recorre los 40.000 ejemplares para quedarse con los 9.998 de la sucursal 1. Son 29 ms, no es el drama, pero también falta un índice ahí.

Sort Method: quicksort Memory: 76kB — La ordenación es irrelevante: 312 filas en memoria.

Hash Join — Correctos. Con 42.017 filas por un lado y 9.998 por otro, sin índices, el hash es la elección adecuada.

Un diagnóstico se escribe en una frase:

La consulta recorre los 2,8 millones de préstamos para quedarse con 42.017 abiertos y vencidos —el 1,5 %—, porque no existe ningún índice sobre fecha_devolucion ni sobre fecha_devolucion_prevista. Secundariamente, recorre los 40.000 ejemplares por falta de índice en sucursal_id.

Fíjate en lo que no es el problema: no son los cuatro JOIN, no es el esquema normalizado, no es el ORDER BY, no es "la tabla es muy grande". Es un índice que falta.

¿Por qué parcial? Porque las consultas del mostrador siempre llevan fecha_devolucion IS NULL, y así el índice pasa de 61 MB a menos de 1 MB, cabe entero en memoria y solo se toca al prestar y al devolver, no en cada modificación de las filas históricas.

Catorce segundos convertidos en cuarenta milisegundos. Sin tocar el esquema. Sin desnormalizar. Sin una sola tabla nueva. Dos CREATE INDEX.

Dos observaciones sobre el plan nuevo:

• El Hash Join se ha convertido en Nested Loop. Al abaratarse el acceso a prestamos, el planificador ha cambiado de estrategia: ahora recorre los 42.017 préstamos abiertos y, por cada uno, busca su ejemplar por clave primaria. Es un ejemplo perfecto de que crear un índice no solo acelera un nodo: cambia el plan entero.
• loops=42017 en el nodo de ejemplares. Recuerda el apartado 13: ese actual time=0.000..0.000 es por iteración. El nodo se ejecuta 42.017 veces, y su contribución real son unos 17 ms de los 39. Está bien, pero es el sitio donde miraríamos si hiciera falta seguir bajando.

Supongamos que el mostrador necesitara bajar de 40 ms —lo cual es dudoso, pero sirve para cerrar el círculo de 05-04—. El siguiente paso no sería crear una tabla de resumen. Sería observar que la consulta filtra por sucursal y que la sucursal está en ejemplares, no en prestamos, lo que obliga a recorrer los 42.017 préstamos de las cuatro sucursales para quedarse con los de una.

La solución sería duplicar sucursal_id en prestamos —la técnica 3 de 05-04, "duplicar un atributo para evitar un JOIN"— e indexar (sucursal_id, fecha_devolucion_prevista) WHERE fecha_devolucion IS NULL. Eso llevaría la consulta al orden del milisegundo.

Y fíjate en el orden en que hemos llegado hasta ahí: medir, índice, medir otra vez, y solo entonces plantear tocar el esquema, con el número exacto de lo que se gana. Ese es exactamente el procedimiento que 05-04 exigía y que el apartado siguiente formaliza.

Existe el índice, es el correcto, y aun así el plan muestra Seq Scan. Casi siempre es uno de estos cinco.

El índice guarda fechas; la consulta pregunta por años. Son cosas distintas.

La alternativa, si la reescritura no fuera posible, es un índice de expresión sobre extract(year FROM fecha_prestamo). Pero reescribir es casi siempre mejor: el rango sirve además para cualquier otro periodo.

Variante muy frecuente del mismo error:

Nota el matiz: aquí la función no desaparece, pero coincide con la del índice, y por eso funciona.

Un B-tree ordena por el principio de la cadena. Buscar por lo que hay en medio obliga a mirarlo todo, igual que buscar en un diccionario todas las palabras que contengan "apa".

Un detalle importante para el español: para que LIKE 'El mapa%' use el índice cuando la base de datos no está en la colación C, hay que crear el índice con la clase de operadores adecuada:

Es la misma trampa del antipatrón 1 disfrazada: socio_id::text es una función sobre la columna.

Ocurre mucho con controladores mal configurados que envían todos los parámetros como texto, y con columnas que guardan números en VARCHAR —motivo adicional para elegir bien los tipos, como decía 04-04—.

PostgreSQL a veces resuelve el OR con un BitmapOr sobre dos índices, y entonces no hace falta reescribir. Cuando no lo hace, el UNION es la salida.

Caso relacionado, y muy habitual:

44 páginas frente a 4, y Heap Fetches: 0 confirma que no se ha tocado la tabla. Además, SELECT * transfiere columnas que nadie usa, rompe las aplicaciones cuando alguien añade una columna, y hace ilegible qué necesita realmente cada consulta.

Aquí cerramos el círculo abierto en 05-04.

No se optimiza lo que no se ha medido. Encontrar la consulta culpable, no la sospechosa. La herramienta es la extensión pg_stat_statements:

Mira la tabla anterior con atención, porque encierra la lección entera de este apartado:

La segunda fila es la que sorprende. Una consulta de 1,36 ms parece perfecta, pero se ejecuta 884.102 veces y suma veinte minutos de servidor. Bajarla a 0,4 ms ahorraría más que optimizar el informe trimestral cuarenta veces.

Y la tercera enseña lo contrario: un informe de dos segundos parece un escándalo, pero se ejecuta 44 veces al año. Optimizarlo es tiempo perdido, por muy feo que sea su SQL.

La métrica que decide es total_exec_time, no mean_exec_time. Coste total = coste unitario × frecuencia.

De menos invasiva a más. No pases al siguiente escalón sin haber agotado el anterior y sin haber medido.

Los cuatro primeros escalones no tocan los datos, no pueden introducir inconsistencia y se deshacen en un minuto. Del quinto en adelante, cada uno añade una obligación permanente de mantenimiento.

Y el caso de esta lección lo confirma: catorce segundos resueltos en el escalón 3, con dos instrucciones reversibles. La tentación de saltar directamente al 6 —"hagamos una tabla de préstamos vencidos que se actualice cada noche"— habría costado un disparador, un proceso nocturno, un riesgo de descuadre, datos con horas de antigüedad y una discusión eterna sobre por qué el listado no incluye el préstamo que acaba de vencer. Todo eso para ser más lento que un índice parcial de 992 kB.

Dos técnicas que a veces se proponen como solución a una consulta lenta y que casi nunca lo son:

• El particionado divide una tabla grande en fragmentos por rango o por lista. Ayuda con el mantenimiento (borrar un año entero es eliminar una partición) y con consultas que filtran por la clave de partición. No sustituye a un índice.
• La replicación reparte la carga de lectura entre varios servidores, y es la técnica que vimos en 03-01. No hace más rápida una consulta: permite ejecutar más consultas a la vez. Enviar el informe mensual a una réplica de solo lectura es excelente idea; no hará que tarde menos.

Ninguna de las dos arregla una consulta lenta por falta de índice. Solo multiplican el hardware necesario para seguir haciéndolo mal.

Con el MVCC de 06-02, los índices también acumulan entradas muertas y se hinchan. Un índice hinchado ocupa más de lo que debería y su búsqueda toca más páginas.

Estimar el problema:

Reconstruir:

El CONCURRENTLY es esencial: sin él, REINDEX bloquea las escrituras de la tabla, con el efecto de 06-02 sobre los cuatro mostradores.

Ojo: reconstruir índices no es una tarea rutinaria. Se hace cuando se ha comprobado hinchazón, no "por si acaso".

El concepto es idéntico; cambia la sintaxis.

Y el equivalente de EXPLAIN ANALYZE:

Lo que hay que mirar es la misma idea que en PostgreSQL: COLLSCAN en lugar de IXSCAN es el Seq Scan de MongoDB, y totalDocsExamined mucho mayor que nReturned es el equivalente de Rows Removed by Filter.

La conclusión importante: los índices no son una peculiaridad relacional. Son la respuesta universal al problema de encontrar pocos elementos entre muchos, y todos los sistemas de datos, sin excepción, tienen su versión.

Indexarlo todo "por si acaso". Cada índice cuesta espacio, escrituras, caché y tiempo de planificación. Revisa idx_scan en pg_stat_user_indexes y elimina los que llevan meses sin usarse.

No indexar las claves ajenas. PostgreSQL no lo hace por ti. Es la causa más frecuente de JOIN lentos y de borrados que tardan segundos. Ejecuta la consulta de detección del apartado 10 sobre tu base de datos hoy mismo.

Crear el índice en producción sin CONCURRENTLY. Bloquea las escrituras durante toda la creación. En una tabla grande, es una parada de servicio autoinfligida.

Confundir cost con milisegundos. El coste es una unidad interna para comparar planes. El tiempo está en actual time, y solo con ANALYZE.

Olvidar que con loops > 1 los tiempos son por iteración. Multiplica siempre por loops antes de decidir que un nodo es inocente.

Optimizar la consulta más fea en lugar de la más costosa. Ordena por total_exec_time en pg_stat_statements y ataca lo de arriba, aunque su SQL sea impecable.

Envolver la columna en una función y esperar que el índice funcione. WHERE extract(year FROM fecha) = 2026 no usa el índice sobre fecha. Reescribe como rango.

Usar EXPLAIN ANALYZE sobre un UPDATE o un DELETE en producción sin transacción. Lo ejecuta de verdad. BEGIN; ... ROLLBACK;.

Medir con datos de desarrollo. Los 3.000 préstamos de tu portátil caben en memoria y hacen que todos los planes parezcan buenos. Los planes solo son significativos con volumen y estadísticas realistas.

Crear un índice y no medir después. A veces el planificador sigue sin usarlo —por selectividad, por tipos o por estadísticas— y te quedas con el coste sin el beneficio. Vuelve a ejecutar EXPLAIN ANALYZE siempre.

Consejo final: escribe el número. "Antes 14.025 ms, después 39 ms, con idx_prestamos_abiertos." Esa frase en el registro de cambios vale más que cualquier explicación, permite comprobar dentro de un año si el índice sigue haciendo falta, y es lo único que convierte una intuición en una decisión de ingeniería.

Estas tres consultas son las más ejecutadas del catálogo web y del mostrador de BiblioRed. Para cada una, indica qué índice crearías, por qué en ese orden de columnas, y si sería parcial o no.

Interpreta este plan de ejecución. Indica: cuál es el nodo problemático, cuánto tiempo real consume, cuál es el diagnóstico y qué intervención propondrías (con el escalón del apartado 17 al que corresponde).

Estas tres consultas de BiblioRed no usan ningún índice pese a que existen los índices adecuados. Identifica el antipatrón de cada una y reescríbela. Los índices disponibles son: prestamos(fecha_prestamo), socios(lower(email)), materiales(titulo text_pattern_ops) y materiales USING gin (busqueda).

Solución 1

(a) Historial de préstamos de un socio

• Orden de columnas: socio_id primero porque es la condición de igualdad y es muy selectiva (11.842 valores distintos sobre 2,8 millones de filas). fecha_prestamo después porque interviene en el ORDER BY.
• DESC en el índice: permite que el ORDER BY ... DESC se resuelva recorriendo el índice en su orden natural, eliminando el nodo Sort. Con LIMIT 20, el gestor lee 20 entradas y para.
• No parcial: es un historial, y consulta explícitamente préstamos ya devueltos. Restringirlo a los abiertos rompería el caso de uso.

Verificación esperada:

Sin nodo Sort: es la señal de que el orden del índice se ha aprovechado.

(b) Ejemplares disponibles de un material en una sucursal

• Orden: material_id primero por ser mucho más selectivo (miles de materiales frente a cuatro sucursales). Con solo cuatro sucursales, sucursal_id apenas descarta filas y no debe ir delante.
• Parcial sobre estado = 'disponible': es la condición fija de la consulta caliente del catálogo web, y estado es de baja cardinalidad, así que como columna indexada sería inútil (apartado 10). Como condición del índice es perfecta: lo reduce a los ejemplares realmente prestables y evita mantenerlo cuando cambian los estados de los demás.
• No es necesario incluir estado entre las columnas: la condición del índice ya lo garantiza.

(c) Multas pendientes de un socio

• Orden: socio_id (igualdad, selectivo) y fecha_emision (para el ORDER BY).
• Parcial: de las 48.000 multas históricas solo unas 300 están pendientes. El índice pasa de megabytes a kilobytes y solo se toca al emitir o cobrar una multa.
• Podría añadirse INCLUDE (motivo, importe) para conseguir un Index Only Scan, ya que la consulta solo pide esas columnas. Con 300 filas la ganancia es marginal, pero es el razonamiento correcto.

Solución 2

Nodo problemático: el Index Scan using idx_prestamos_socio on prestamos p.

Tiempo real consumido: aquí está la trampa. El nodo muestra actual time=224.402..224.622 con loops=41. Esos 224 ms son por iteración, así que el total es 41 × 224,6 ≈ 9.209 ms, es decir, prácticamente los 9.215 ms de toda la consulta. Quien lea "224 ms" y lo dé por aceptable buscará el problema donde no está.

Diagnóstico: el índice idx_prestamos_socio se está usando para la condición de unión, pero el filtro fecha_devolucion IS NULL se aplica después, sobre las filas ya recuperadas de la tabla. La línea Rows Removed by Filter: 218 lo dice: por cada uno de los 41 socios se leen 219 préstamos de la tabla para quedarse con 1. Son 8.979 accesos a páginas, la mayoría a disco.

Hay un segundo problema, menor: el Seq Scan on socios con lower(apellidos) recorre los 12.000 socios (4 ms). No es el drama, pero delata que falta un índice de expresión.

Intervención propuesta, escalón 3 del apartado 17 (crear un índice):

Con el índice parcial, cada iteración del bucle devuelve directamente el préstamo abierto sin leer los 218 devueltos. El nodo pasaría de 224 ms a microsegundos, y la consulta entera al orden de un milisegundo.

Observación adicional: también se aprecia que la estimación (rows=12) se queda corta frente a la realidad (rows=41) en el Seq Scan on socios. No es grave aquí, pero si la diferencia creciera, el Nested Loop dejaría de ser la elección adecuada. Un ANALYZE tras crear el índice de expresión mejorará también esa estimación.

Solución 3

(a) Función sobre la columna (antipatrón 1)

to_char(fecha_prestamo, 'YYYY-MM') convierte la fecha en texto, así que el índice sobre fecha_prestamo no puede intervenir.

Nota sobre el límite superior: se usa < '2026-08-01' y no <= '2026-07-31'. Si la columna fuera TIMESTAMP en vez de DATE, <= '2026-07-31' dejaría fuera todo el último día a partir de las 00:00:01. El rango medio abierto es correcto en ambos casos, y es una costumbre que evita errores silenciosos.

(b) Índice de expresión con expresión distinta (variante del antipatrón 1)

El índice es sobre lower(email), pero la consulta compara email tal cual, y además con el valor en mayúsculas. No coinciden.

O, mejor todavía, normalizando el valor en la aplicación antes de enviarlo:

La expresión del WHERE debe ser idéntica a la del índice. Es la regla que hace funcionar los índices de expresión, y la que los rompe cuando se olvida.

(c) LIKE con comodín inicial (antipatrón 2)

El índice text_pattern_ops sirve para prefijos (LIKE 'tiempo%'), no para subcadenas.

Ventaja añadida: la búsqueda de texto completo aplica lematización en español, así que "tiempo" encuentra también "tiempos", y no encuentra falsos positivos como "contiempo" que sí devolvería LIKE '%tiempo%'.

Si de verdad hiciera falta la subcadena literal, la alternativa es un índice de trigramas:

Los catorce segundos ya no existen. Y lo importante no es que ahora sean cuarenta milisegundos, sino cómo hemos llegado hasta ahí: midiendo, leyendo un plan de ejecución, encontrando la línea que decía Rows Removed by Filter: 2799060, y creando dos índices reversibles. Sin tocar el esquema, sin desnormalizar, sin una tabla nueva y sin una discusión de arquitectura.

Hemos entendido el mecanismo desde dentro. Un B-tree con un factor de ramificación de unas 500 entradas por nodo direcciona 125 millones de filas en tres niveles, y esa es la razón matemática de que un índice siga siendo rápido cuando la tabla crece: no escala con el número de filas, escala con su logaritmo. También hemos visto el otro lado, que se olvida más: un índice cuesta espacio, cuesta caché, triplica el tiempo de una carga masiva y, si nadie lo usa, solo cuesta. Por eso idx_scan = 0 en pg_stat_user_indexes es una invitación a DROP INDEX.

Hemos recorrido las herramientas: los índices únicos y su relación exacta con las restricciones UNIQUE de 04-04; los compuestos y la regla del prefijo más a la izquierda, que se entiende de una vez para siempre con el listín ordenado por apellido y nombre; los parciales, que en BiblioRed convirtieron 61 MB en 992 kB porque solo el 1,5 % de los préstamos está abierto; los de expresión, con la condición de que el WHERE repita la expresión exacta; y el panorama de GIN para el buscador del catálogo, GiST para los solapes de eventos que ya conocíamos de 04-04, y BRIN para el historial ordenado por fecha, con sus 48 kB frente a 61 MB.

Hemos aprendido a leer un plan de abajo arriba y de dentro afuera; a distinguir Seq Scan de Index Scan, de Index Only Scan y de Bitmap Heap Scan; a reconocer cuándo un Nested Loop es una buena idea y cuándo es una catástrofe; a no confundir el coste con los milisegundos; a multiplicar por loops antes de absolver a un nodo; y sobre todo a mirar la señal de alarma más valiosa de todas: la distancia entre las filas estimadas y las reales, que casi siempre apunta a estadísticas viejas o a columnas correlacionadas.

Y hemos formalizado el método. Medir con pg_stat_statements y atacar la consulta con más tiempo total, no la de peor aspecto —esa consulta de 1,36 ms que se ejecuta 884.102 veces cuesta más que el informe trimestral de dos segundos—. Después, el orden de intervención: reescribir → estadísticas → índice → configuración → esquema → desnormalizar → caché, sin saltarse escalones y midiendo entre uno y otro. Los cuatro primeros no pueden estropear los datos; del quinto en adelante, cada uno añade una obligación permanente. Este es exactamente el compromiso que adquirimos en 05-04, y ahora ya tenemos las herramientas para cumplirlo.

Queda una última cosa, y es la que separa una base de datos de un accidente esperando a ocurrir.

El sistema de BiblioRed es ahora correcto —el esquema está normalizado y las transacciones garantizan que las operaciones ocurren enteras— y es rápido. Pero sigue habiendo dos preguntas de la concejalía de Vallmar sin responder desde el primer día de este módulo: quién puede consultar los teléfonos y los correos de los 12.000 socios, y qué pasaría exactamente si el disco muriera esta noche.

Ninguna de las dos se arregla con un índice. La lección 06-04, Seguridad, Permisos y Copias de Seguridad, cierra el módulo y el bloque teórico del curso: la autenticación y los roles de PostgreSQL, con pg_hba.conf y por qué el método trust no debe salir de tu portátil; la autorización con GRANT y REVOKE, y el principio de mínimo privilegio aplicado a tres roles concretos de BiblioRed —mostrador, dirección y aplicación web—; las vistas y la seguridad a nivel de fila para que cada sucursal vea solo lo suyo; la inyección SQL, cómo se produce en el buscador del catálogo y por qué las consultas parametrizadas son la única defensa que funciona; el cifrado en tránsito y en reposo, y el tratamiento correcto de las contraseñas; los datos personales de los socios, con lo que la técnica puede aportar y lo que corresponde a un profesional de compliance; y el bloque de copias de seguridad, donde el registro WAL que estudiamos en 06-01 reaparecerá convertido en la herramienta que permite recuperar la base de datos en el instante anterior al desastre.