En la lección anterior descubriste que TiendaVerde tiene once índices que nadie creó y le faltan los de sus once claves foráneas. Toca arreglarlo. Aquí aprenderás la sintaxis completa de CREATE INDEX y, sobre todo, las cuatro variantes que separan a quien "crea índices" de quien diseña índices: los compuestos, con la regla del prefijo por la izquierda que decide en qué orden van las columnas; los parciales, que indexan solo las filas que interesan; los de expresión, que cierran el problema de LOWER(email) que arrastras desde el módulo 6; y los que llevan INCLUDE para conseguir un Index Only Scan.

La segunda mitad es igual de importante y suele faltar en los tutoriales: gestionar los índices que ya existen. Listarlos, medir cuánto ocupan, reconstruirlos y detectar los dos problemas más caros de una base de datos madura: los índices que nadie usa y los que son redundantes con otro.

Aviso sobre los objetos de este módulo. Todos los índices que se crean en el módulo 8 son material didáctico. No forman parte del esquema canónico de TiendaVerde definido en 01-06: si recargas tiendaverde.sql, desaparecen, y los módulos 9 a 12 no los dan por supuestos.

1. CREATE INDEX: la sintaxis completa y cómo nombrarlos
2. Los índices que le faltan a TiendaVerde
3. CREATE INDEX CONCURRENTLY
4. Índices compuestos y la regla del prefijo por la izquierda
5. Índices parciales
6. Índices sobre expresiones
7. INCLUDE: índices cubridores a propósito
8. Orden y nulos dentro del índice
9. Gestión: listar, medir, renombrar, reconstruir y borrar
10. Índices no usados y redundantes
11. Errores Comunes y Consejos
12. Ejercicios
13. Conclusión

El caso mínimo, y el 70 % de los índices que crearás en tu vida:

Fíjate en lo que no hace falta poner: ni el método (btree es el valor por omisión) ni el tipo de dato ni el tamaño. Y en lo que sí conviene: el nombre. Si lo omites, PostgreSQL genera <tabla>_<columnas>_idx, y acabas con pedidos_cliente_id_fecha_pedido_idx1. La convención más extendida —y la que usará el curso— es idx_<tabla>_<columnas>:

Las razones son las mismas de 05-01 con las restricciones: mensajes de error y planes de ejecución legibles, migraciones que pueden referirse al índice por su nombre, y dos entornos que no acaban con nombres intercambiados. Los índices que respaldan una PRIMARY KEY o un UNIQUE son la excepción: no los crees a mano. Declara la restricción y deja que el motor cree el índice; si lo haces al revés, tendrás un índice sin restricción asociada.

Empecemos por lo que 08-01 dejó identificado: las once claves foráneas sin índice. Estos son los que de verdad merecen la pena en un TiendaVerde de tamaño real:

Los dos primeros son los más rentables de todo el esquema: lineas_pedido es la tabla con más filas, aparece en casi todos los JOIN del curso y su pedido_id tiene ON DELETE CASCADE, así que sin índice cada borrado de pedido recorre la tabla entera.

Y ahora la comprobación que conviene hacer siempre, aunque duela:

El índice ocupa el doble que la tabla. No es un error: las 47 líneas caben en una sola página de 8 kB, mientras que un B-tree necesita como mínimo dos —la página de metadatos y la raíz—. Es la demostración más limpia de que con estos volúmenes el índice no puede aportar nada, y por eso este módulo necesita una tabla de pruebas grande (la construirás en 08-05). Lo que sigue tiene sentido pensando en el TiendaVerde de dentro de cinco años, con millones de líneas.

Un CREATE INDEX normal bloquea las escrituras de la tabla mientras se construye. Las lecturas siguen funcionando; los INSERT, UPDATE y DELETE esperan. Con 47 filas eso dura microsegundos; con 50 millones puede durar veinte minutos, y esos veinte minutos son una caída de servicio.

CONCURRENTLY construye el índice en dos pasadas sobre la tabla, sin tomar el bloqueo que impide escribir. A cambio tiene tres pegas: es más lento (dos recorridos completos, más una espera a que terminen las transacciones abiertas); puede dejar un índice INVALID si algo va mal, es decir creado pero inservible —ocupa espacio, se mantiene en cada escritura y el planificador no lo usa—; y no cabe en una transacción, así que BEGIN; CREATE INDEX CONCURRENTLY ...; da error.

Detectar un índice inválido y curarlo —la cura es siempre la misma, borrarlo y rehacerlo—:

La regla, en una línea: en desarrollo, CREATE INDEX; en producción sobre una tabla con tráfico, CONCURRENTLY siempre, y comprueba después que el índice es válido.

El mecanismo por el que un CREATE INDEX normal impide escribir es el de los bloqueos, y con él vienen las transacciones y el modelo de concurrencia de PostgreSQL. Todo eso es el módulo 9; aquí basta con saber que existe y que CONCURRENTLY es la forma de esquivarlo. Es la misma familia de precauciones que viste en 05-06 con los ALTER TABLE que reescriben la tabla.

Un índice compuesto indexa varias columnas a la vez. No es lo mismo que dos índices separados: ordena primero por la primera columna y, dentro de cada valor, por la segunda, exactamente como un ORDER BY cliente_id, fecha_pedido.

Piensa en una guía telefónica ordenada por apellido y luego por nombre. Puedes buscar "todos los García" y puedes buscar "García, Ana". Lo que no puedes hacer es buscar "todas las Ana": tendrías que leer la guía entera. Eso es la regla del prefijo por la izquierda: un índice compuesto sirve para las consultas que usan un prefijo de su lista de columnas, empezando por la primera.

El caso estrella, sobre los datos del curso:

Con el índice (cliente_id, fecha_pedido), el motor entra en el bloque de cliente_id = 2 y lee las dos filas ya ordenadas, hacia atrás: cero comparaciones y cero ordenación. (Con 20 pedidos hará un Seq Scan, claro; el razonamiento vale para el caso grande.)

Es la decisión más importante de un índice compuesto, y hay una regla práctica que acierta casi siempre:

Primero las columnas que se comparan por igualdad; después, la que se compara por rango; y al final, la que solo aparece en el ORDER BY.

El motivo es geométrico. Con (cliente_id, fecha_pedido) y el filtro cliente_id = 1 AND fecha_pedido >= '2025-06-01', el motor salta al inicio de "cliente 1, junio de 2025" y lee un tramo contiguo de hojas: encuentra el único pedido de Lucía posterior a esa fecha. Con (fecha_pedido, cliente_id) tendría que leer todos los pedidos desde junio de 2025, de todos los clientes, e ir descartando. Cuanto antes se cierre el rango, menos entradas se tocan. Dos corolarios:

• En cuanto una columna se compara por rango, las siguientes dejan de servir para filtrar (solo para descartar sin ir a la tabla). Por eso el rango va el último de los filtros.
• Un índice sobre (a, b) hace innecesario un índice sobre (a), pero no sobre (b). Es la base de la caza de redundantes de la sección 10.

Y un límite práctico: rara vez merece la pena pasar de tres columnas. Cada columna adicional engorda las entradas, reduce el fanout (08-01) y sirve para menos consultas.

Un índice parcial lleva una cláusula WHERE y solo indexa las filas que la cumplen. Idea sencilla, dos efectos grandes: el índice es más pequeño —cabe mejor en memoria y se recorre antes— y su mantenimiento es más barato, porque las filas excluidas no lo tocan al insertarse.

La condición del índice debe implicar la de la consulta para que el planificador lo pueda usar:

Dónde brillan de verdad. En TiendaVerde, estado <> 'entregado' son 6 pedidos de 20 (30 %) y activo son 19 productos de 20 (95 %): el ahorro es nulo o negativo. Pero esas proporciones se desploman en una tienda real: dentro de cinco años, los pedidos no entregados serán unos 200 de 2.000.000 (0,01 %) y los productos activos unos 3.000 de 40.000 (7,5 %). El índice parcial de pedidos pendientes indexaría doscientas filas en lugar de dos millones: cabría entero en memoria y respondería en microsegundos. Es el patrón clásico de las colas de trabajo —"dame lo que falta por procesar"— y uno de los mejores trucos de PostgreSQL. Otro uso muy frecuente es indexar solo lo que no es nulo: CREATE INDEX idx_pedidos_empleado_id ON pedidos (empleado_id) WHERE empleado_id IS NOT NULL; se ahorra la mitad de las entradas, porque diez de los veinte pedidos son web (01-06), y sigue sirviendo para todas las consultas por comercial.

Nota de dialecto: los índices parciales existen en PostgreSQL y SQLite con esta misma sintaxis, y en SQL Server como filtered indexes. MySQL no los tiene en absoluto: es una de las carencias que más se notan al migrar hacia él.

Aquí se cierra el aviso del módulo 6: una función sobre la columna filtrada impide usar el índice. La solución es indexar el resultado de la función.

Y la regla de oro, de donde vienen casi todos los disgustos:

La expresión del índice debe coincidir literalmente con la de la consulta. El planificador compara expresiones, no significados.

Ese último par tiene enjundia. Las dos consultas devuelven los mismos 16 pedidos de 2025, pero cada una necesita su índice, y CREATE INDEX idx_pedidos_anio ON pedidos (EXTRACT(YEAR FROM fecha_pedido)); solo sirve para preguntas anuales. La solución preferible no es esa: es reescribir la consulta como un rango sobre fecha_pedido y usar un índice normal, que servirá además para "los pedidos de marzo", "los de la última semana" y para ORDER BY fecha_pedido. Es el criterio de la sargabilidad que desarrolla 08-04.

Dos requisitos técnicos: la función debe ser IMMUTABLE —para los mismos argumentos, siempre el mismo resultado; LOWER(texto) lo es, NOW() no, y EXTRACT(YEAR FROM ...) sobre un TIMESTAMPTZ tampoco, porque depende de la zona horaria de la sesión, aunque sobre un DATE como el de TiendaVerde sí— y se recalcula en cada escritura, así que encarece los INSERT algo más que un índice normal.

Nota de dialecto: Oracle los tiene desde hace décadas (function-based indexes) y SQLite desde la 3.9. SQL Server no los tiene directamente: se emula con una columna calculada persistida más un índice sobre ella. MySQL 8 los soporta con paréntesis dobles: CREATE INDEX ... ((LOWER(email))).

INCLUDE añade columnas al índice que no forman parte de la clave: no sirven para buscar ni para ordenar, solo se guardan en las hojas para poder leerlas sin ir a la tabla. Es la forma explícita de fabricar el índice cubridor de 08-01 y provocar un Index Only Scan.

Esa última fila es el uso más elegante: CREATE UNIQUE INDEX ... ON clientes (email) INCLUDE (nombre, apellidos) sigue garantizando "un email, un cliente" y además resuelve "¿cómo se llama el dueño de este email?" sin tocar la tabla. INCLUDE existe desde PostgreSQL 11, y también en SQL Server y Oracle; MySQL no lo necesita igual, porque su índice primario es agrupado.

Por omisión un índice B-tree se construye ASC NULLS LAST, igual que el ORDER BY del módulo 2. Y aquí conviene ahorrarse trabajo: para un ORDER BY de una sola columna no hace falta declarar nada, porque PostgreSQL puede recorrer cualquier índice hacia atrás. Un índice (fecha_pedido) sirve igual para ORDER BY fecha_pedido que para ORDER BY fecha_pedido DESC.

El orden explícito solo importa cuando el ORDER BY mezcla direcciones, porque entonces ninguna lectura del índice, ni hacia delante ni hacia atrás, produce ese orden:

Lo mismo con los nulos: si tus informes hacen ORDER BY empleado_id NULLS FIRST sobre los diez pedidos web de TiendaVerde, un índice (empleado_id NULLS FIRST) evita la ordenación; el índice por omisión, no.

En psql, \di lista todos los índices y \d+ pedidos los de una tabla concreta con su definición; desde SQL, la vista pg_indexes de 08-01. Para medir:

(Una ejecución de ejemplo tras crear los índices de esta lección; el total incluye además las estructuras auxiliares de cada tabla.) La lectura es perfectamente real: los índices ocupan más de diez veces lo que los datos. Con 20 filas por tabla es una anécdota; en una base de datos madura, que los índices pesen más que los datos es una señal de alarma que hay que investigar con la sección 10.

REINDEX reconstruye el índice desde cero. Se usa en tres situaciones: índice corrompido, índice que ha crecido de más por acumulación de espacio muerto (el bloat de 08-05), o cambio de la configuración regional que afecta al orden del texto. En el día a día no hace falta reindexar por rutina: PostgreSQL mantiene los árboles equilibrados por su cuenta.

Borrar un índice es instantáneo y no toca los datos; un DROP INDEX normal bloquea la tabla un instante y CONCURRENTLY lo evita. Lo que no puedes borrar así es un índice que respalda una restricción:

La vía correcta es ALTER TABLE clientes DROP CONSTRAINT clientes_email_key;, que se lleva restricción e índice a la vez.

En una base de datos con años de vida los índices se acumulan: uno para un informe que ya no existe, otro para una consulta que se reescribió, un tercero "por si acaso". Todos siguen cobrando su peaje en cada escritura.

(Una ejecución de ejemplo: en TiendaVerde salen ceros porque con 20 filas el planificador elige Seq Scan — 08-05.) Un idx_scan = 0 es un candidato a borrar, con cuatro cautelas:

1. Los contadores se acumulan desde el último reinicio de estadísticas. Un índice usado solo por el cierre anual parecerá inútil en marzo.
2. Un índice que respalda un UNIQUE o una PK no se borra, aunque no se use para buscar: está garantizando una restricción.
3. Los índices de claves foráneas pueden marcar 0 y ser imprescindibles: las comprobaciones de integridad referencial no siempre suman al contador.
4. En una réplica de solo lectura los contadores son distintos. Revisa ambos.

Y de la regla del prefijo por la izquierda se deduce el otro problema: si existe un índice sobre (a, b), uno sobre (a) es redundante.

El matiz honesto: el índice corto es más pequeño y, si tu consulta más frecuente solo filtra por cliente_id, será marginalmente más rápido. Casi nunca compensa mantener los dos.

• Crear un CREATE INDEX normal en producción. Bloquea las escrituras durante toda la construcción. CONCURRENTLY existe justo para eso.
• No comprobar que un CONCURRENTLY terminó bien. Un índice INVALID es lo peor de los dos mundos: cuesta mantenerlo y no lo usa nadie.
• Poner las columnas del índice compuesto en el orden en que aparecen en el WHERE. El orden lo manda la regla igualdad → rango, no el orden en que escribiste las condiciones.
• Crear (a) y (a, b). El primero sobra. Es el índice redundante más frecuente que existe.
• Crear un índice de expresión que no coincide con la consulta. LOWER(email) no vale para LOWER(TRIM(email)). Copia y pega la expresión desde la consulta.
• Usar un índice de expresión sobre el año en lugar de reescribir la consulta como un rango. El índice del rango sirve para muchas más preguntas.
• Crear a mano el índice de una clave UNIQUE. Declara la restricción; el índice viene incluido y sigue asociado a ella.
• Consejo: crea los índices de las claves foráneas de entrada. Es el conjunto de índices con mejor relación beneficio/riesgo de cualquier esquema PostgreSQL.
• Consejo: revisa pg_stat_user_indexes una vez al trimestre. Borrar tres índices muertos puede acelerar las escrituras más que cualquier optimización de consulta. Y nombra los índices desde el primer día: un plan lleno de pedidos_cliente_id_fecha_pedido_idx1 es un plan que nadie lee.

La aplicación de TiendaVerde ejecuta estas tres consultas en la pantalla de ficha de cliente:

Diseña el conjunto mínimo de índices que las cubra bien, escribe los CREATE INDEX y justifica el orden de las columnas de cada uno.

Un equipo ha acumulado estos cinco índices sobre lineas_pedido:

1. ¿Cuáles son redundantes y por qué?
2. ¿Cuál es casi seguro inútil, y qué consulta habría que ver para confirmarlo?
3. Deja el conjunto en dos índices y escribe los DROP INDEX correspondientes.

Escribe los índices que resuelven estas tres situaciones y explica en una frase por qué eliges esa variante:

1. La aplicación busca clientes escribiendo el email sin importar mayúsculas y minúsculas.
2. El panel de administración muestra continuamente los pedidos aún no entregados, que en producción son unos 200 de 2 millones.
3. Un informe lista cliente_id, fecha_pedido y gastos_envio de un cliente concreto, y quieres que no toque la tabla.

Solución 1

El primero cubre a) y b). cliente_id va primero porque se compara por igualdad, y fecha_pedido después porque es el rango y además el criterio de ordenación: el motor lee un tramo contiguo y ya ordenado, sin Sort. Y como ORDER BY fecha_pedido DESC se resuelve recorriendo el índice hacia atrás, no hace falta declarar DESC.

El segundo cubre c). Podría hacerse con un índice normal sobre (estado, fecha_pedido), pero el parcial es mucho mejor: en TiendaVerde hoy indexaría 1 fila de 20, y en producción unas 200 de 2 millones. La columna indexada es fecha_pedido porque estado ya está fijado por la condición del índice: repetirla sería desperdiciar espacio. No hace falta un índice suelto sobre cliente_id: sería redundante con el primero.

Solución 2

1. Redundantes: idx_lp_1 sobra porque idx_lp_2 empieza por pedido_id y cubre todo lo que él resuelve. idx_lp_3 sobra porque idx_lp_4 tiene la misma clave (producto_id) y además incluye dos columnas para evitar el acceso a la tabla.

2. Casi seguro inútil: idx_lp_5, sobre cantidad. Es una columna de baja cardinalidad (valores de 1 a 8 en las 47 líneas) y nadie busca líneas "por cantidad": es un dato que se muestra y se suma, no por el que se filtra. Para confirmarlo, pg_stat_user_indexes y su idx_scan, con las cuatro cautelas de la sección 10.

3. DROP INDEX idx_lp_1; DROP INDEX idx_lp_3; DROP INDEX idx_lp_5;. Quedan idx_lp_2 sobre (pedido_id, producto_id), que sirve para "las líneas de este pedido" y para "este producto dentro de este pedido", e idx_lp_4 sobre (producto_id) INCLUDE (cantidad, precio_unitario), que resuelve "todas las ventas de este producto" con Index Only Scan. Las dos claves foráneas quedan cubiertas, que era el objetivo de partida.

Solución 3

1. De expresión, porque el índice debe contener exactamente lo que compara la consulta. Cuidado con la regla de oro: si la aplicación pasa a usar ILIKE o a normalizar con TRIM, este índice deja de servir sin que nadie se entere.
2. Parcial, porque la condición es constante y minoritaria: el índice cabe en memoria y no se toca al insertar los pedidos que ya nacen entregados.
3. INCLUDE en lugar de (cliente_id, fecha_pedido, gastos_envio), porque gastos_envio no se usa para filtrar ni para ordenar: metiéndola en INCLUDE solo engordan las hojas y no los niveles superiores, y el árbol conserva su fanout.

Ya sabes construir índices, no solo pedirlos:

• CREATE INDEX tiene ocho piezas, y las importantes son CONCURRENTLY, la lista de columnas, INCLUDE y WHERE. El método por omisión es btree.
• CONCURRENTLY es obligatorio en producción: un CREATE INDEX normal bloquea las escrituras. A cambio es más lento y puede dejar un índice INVALID que hay que borrar y rehacer. El modelo de bloqueos que hay detrás es el módulo 9.
• Un índice compuesto sirve para los prefijos por la izquierda de su lista de columnas. El orden se decide con una regla: igualdad primero, rango después, ordenación al final. Y (a, b) hace redundante a (a).
• Los parciales indexan solo un subconjunto de filas: minúsculos, rápidos y baratos de mantener. Son la herramienta ideal para las colas de trabajo y para excluir nulos.
• Los de expresión cierran el aviso del módulo 6, con una regla de oro que no admite matices: la expresión del índice debe coincidir literalmente con la de la consulta, y la función debe ser IMMUTABLE.
• INCLUDE fabrica índices cubridores sin engordar la clave, y es la única forma de añadir columnas a un índice UNIQUE sin cambiar lo que garantiza.
• Gestionar es tan importante como crear: \di y pg_indexes para listar, pg_relation_size para medir, REINDEX para reconstruir, DROP INDEX para borrar — y pg_stat_user_indexes para descubrir los que no usa nadie y la regla del prefijo para descubrir los redundantes.

Con esto sabes crear cualquier índice B-tree que necesites. Pero el B-tree no es el único: PostgreSQL ofrece seis métodos de acceso, y hay problemas —LIKE '%texto%', JSONB, rangos de fechas, tablas históricas de miles de millones de filas— que un B-tree no resuelve bien o no resuelve en absoluto. En la lección 08-03, Tipos de índice y cuándo no indexar, verás la tabla comparativa de los seis métodos, cerrarás la promesa de pg_trgm que 04-01 dejó pendiente y —la parte más importante y la que menos se enseña— aprenderás cuándo un índice no sirve o estorba: tablas pequeñas, columnas de baja cardinalidad, filtros poco selectivos, tablas de escritura intensiva, y el antipatrón de indexarlo todo por si acaso.