Índice de base de datos

Qué es

Un índice de base de datos es una estructura separada, almacenada
junto a una tabla, que permite a la base de datos ir directamente a
las filas que cumplen una condición en lugar de leer la tabla de la
primera fila a la última. En el habla cotidiana y en la mayor parte
de la documentación de PostgreSQL se llama simplemente índice. La
documentación lo compara con el índice al final de un libro: un
lector que busca un tema lo consulta allí y va a las páginas
indicadas, en lugar de hojear todo el volumen.

Piense en una tabla de pedidos y en la condición `WHERE customer_id
i= 42`. Sin un índice sobre `customer_id`, PostgreSQL tiene una forma		engineer.company	70
de responder: un recorrido secuencial, que lee todas las filas y
conserva las que coinciden. Eso cuesta lo mismo tanto si el cliente
tiene dos pedidos como si tiene dos mil, y el coste crece con la
tabla. Con un índice en la columna, PostgreSQL recorre unos pocos
niveles de un árbol de búsqueda hasta las entradas de 42 y recupera
solo esas filas. Crear un índice requiere una sola sentencia, `DROP
INDEX` vuelve a eliminarlo y ambas operaciones pueden realizarse en
cualquier momento.

Una vez que existe un índice, PostgreSQL lo mantiene sincronizado
con la tabla en cada escritura, y el planificador de consultas
decide, consulta por consulta, si utilizarlo resulta más barato que
un recorrido completo. Nada en el SQL nombra el índice, de modo que
un mismo índice puede acelerar un `SELECT`, un `UPDATE` o un
`DELETE` con una condición de búsqueda, así como una unión.

PostgreSQL incorpora varios tipos, cada uno construido para una
clase distinta de pregunta:

  * B-tree es el predeterminado y lo que construye `CREATE INDEX`
salvo que se indique otra cosa. Responde a comparaciones de igualdad
y rango (`<`, `<=`, `=`, `>=`, `>`), `BETWEEN`, `IN`, `IS NULL` y un
`LIKE` cuyo patrón está anclado al inicio. Mantiene las entradas en
orden, por lo que también puede devolver filas ya ordenadas.

  * Hash almacena un código hash de 32 bits para cada valor y solo
responde a igualdad.

  * GiST y SP-GiST son marcos de trabajo para datos como la
geometría, donde la pregunta es «se solapa», «contiene» o «es el más
cercano a» en lugar de «es igual».

  * GIN es un índice invertido para valores formados por muchas
partes, como los arrays, y encuentra las filas cuyo valor contiene
un elemento determinado.

  * BRIN conserva únicamente un resumen por cada rango de bloques de
tabla. Resulta adecuado para una columna cuyos valores siguen el
orden físico de las filas, como suele ocurrir con una marca de
tiempo de inserción.

Además de su tipo, un índice puede adaptarse a la pregunta. Un
índice único rechaza valores duplicados. Un índice multicolumna
abarca varias columnas, hasta 32. Un índice parcial tiene una
cláusula `WHERE` y contiene solo las filas que la cumplen. Un índice
de expresión almacena un valor calculado, como `lower(email)`. Y una
lista `INCLUDE` incorpora columnas adicionales al índice sin
utilizarlas para la búsqueda, de modo que una consulta pueda
responderse solo desde el índice.

Por qué importa

La diferencia que supone un índice crece con la tabla. En una tabla
pequeña, un recorrido completo es barato y el planificador a menudo
lo prefiere, con razón: leer unas pocas páginas en orden cuesta
menos que recorrer un árbol para llegar a ellas. Con decenas de
millones de filas, la diferencia entre un recorrido completo y una
búsqueda por índice es la que hay entre una página que carga y otra
que agota el tiempo de espera. Una consulta que era rápida en el
lanzamiento puede volverse lenta un año después sin cambiar una
línea de código, simplemente porque la tabla que lee ha crecido.

Los índices no son gratuitos y la factura llega con las escrituras.
Cada inserción añade una entrada a cada índice de la tabla. Una
actualización solo puede evitarlo si es una actualización heap-only
tuple (HOT), en la que la nueva versión de la fila permanece en la
misma página y las entradas del índice que ya existen siguen
conduciendo a ella. PostgreSQL solo lo permite cuando la
actualización deja intactas todas las columnas que utilizan los
índices de la tabla, excepto los índices BRIN, que solo contienen un
resumen. Por tanto, cada índice reduce las posibilidades de seguir
la vía barata; por eso la documentación aconseja eliminar los
índices que se utilizan poco o nunca. Una tabla con una docena de
índices paga una docena de veces en cada inserción.

Construir un índice también tiene un coste. Un `CREATE INDEX`
corriente permite que otras sesiones lean la tabla, pero bloquea sus
escrituras hasta terminar la construcción, y la documentación
advierte de que en una tabla muy grande puede tardar muchas horas.

Por último, un índice solo sirve en la medida en que el planificador
decida utilizarlo, y este trabaja a partir de estadísticas recogidas
por `ANALYZE`. La pregunta no es «¿hay un índice?», sino «¿lo
utilizan las consultas que importan?», y `EXPLAIN` permite
averiguarlo.

Cómo se aplica en la práctica

La página de cuenta de una tienda en línea enumera los veinte
pedidos más recientes de un cliente:

```sql
SELECT id, created_at, total
FROM   orders
WHERE  customer_id = 42
ORDER  BY created_at DESC
LIMIT  20;
```

Con solo una clave primaria en `id`, PostgreSQL tiene que leer
`orders` entero, conservar las filas del cliente, ordenarlas y
descartar todas menos veinte. `EXPLAIN` lo muestra: un `Seq Scan`
sobre `orders` con la condición como `Filter` y una ordenación por
encima. `EXPLAIN ANALYZE` también ejecuta la consulta e imprime los
recuentos reales de filas y los tiempos junto a las estimaciones.

El índice que encaja con esta pregunta sitúa primero la columna de
igualdad y después la de ordenación:

```sql
CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at);
```

Al ejecutar `EXPLAIN` otra vez, el recorrido se convierte en un
recorrido por índice que utiliza `orders_customer_created_idx`, y
`customer_id = 42` aparece ahora como `Index Cond` en lugar de como
filtro. La ordenación también desaparece. Un B-tree mantiene las
entradas del cliente 42 ordenadas por `created_at`, PostgreSQL puede
leerlas hacia atrás para obtener primero las más recientes y, con un
`LIMIT`, se detiene después de veinte entradas en lugar de acceder
al resto.

El orden de las columnas importa. El mismo índice sirve para una
consulta solo sobre `customer_id`, su columna inicial, pero un
informe sobre todos los pedidos realizados ayer, que filtre
únicamente por `created_at`, obtiene mucho menos de él: en un B-tree
multicolumna, la igualdad sobre las columnas iniciales más un rango
sobre la siguiente es lo que reduce la parte del índice que se lee.
PostgreSQL 18 puede realizar un skip scan, repitiendo la búsqueda
una vez por cada valor inicial distinto, pero el planificador
normalmente solo lo elige cuando la columna inicial tiene pocos
valores distintos, y los identificadores de cliente tienen muchos.
Si el informe importa, recibe su propio índice sobre `created_at`.
Invertido, como `(created_at, customer_id)`, el índice dispersaría
los pedidos del cliente 42 por todo su contenido.

La nota sobre optimizar la base de datos antes de fragmentarla entre
servidores sitúa los índices en primer lugar entre las correcciones
que ordena por su beneficio, una vez que `EXPLAIN (ANALYZE,
BUFFERS)` ha mostrado qué recorridos ejecuta una consulta con mucha
carga. Su propio ejemplo es un índice parcial, `units_unsold`, que
contiene solo las unidades todavía a la venta, por lo que se reduce
a medida que avanza una venta en lugar de crecer. La nota también
cuenta el coste. En su tabla, las 2.000 operaciones de reserva que
establecían `reserved`, `reserved_at` y `order_id` fueron todas
actualizaciones HOT, mientras que, de 2.000 actualizaciones que
establecían `sold`, una columna que lee el índice parcial, ninguna
lo fue. Su consejo es eliminar los índices cuyo `idx_scan` en
`pg_stat_user_indexes` permanezca en cero.

El caso práctico sobre una mejor elección de clave muestra por qué
los valores que entran en un B-tree importan tanto como las
columnas. Los UUID aleatorios dispersan las inserciones por el
índice, lo que puede causar fragmentación. La plataforma estableció
UUID v7 ordenados por tiempo como opción predeterminada para las
claves primarias de las entidades, con una marca de tiempo en los
bits iniciales para que los identificadores nuevos incorporen orden
temporal. Reducir la fragmentación y acelerar las consultas eran
objetivos de diseño; el resultado registrado es la convención de
claves configurada, sin que el caso establezca una comparación de
rendimiento de la carga antes y después.

Dónde surgen los problemas

Añadir un índice detiene las escrituras de la tabla. Un `CREATE
INDEX` corriente sobre una tabla con mucha actividad bloquea
inserciones, actualizaciones y eliminaciones durante toda la
construcción, y un despliegue rutinario se convierte en una
interrupción del servicio. Utilice `CREATE INDEX CONCURRENTLY`:
recorre la tabla dos veces y espera a las transacciones que podrían
utilizar el índice, por lo que tarda bastante más, pero las
escrituras continúan. No puede ejecutarse dentro de un bloque de
transacción, así que, si una herramienta de migración envuelve cada
paso en uno de esos bloques, hay que indicarle que ejecute esta
sentencia por separado. Solo puede ejecutarse una construcción
concurrente por tabla a la vez.

Una construcción concurrente falla y deja un índice no válido. Un
interbloqueo, o un valor duplicado al construir un índice único,
hace que falle `CREATE INDEX CONCURRENTLY`, pero el índice que
empezó permanece en el catálogo, marcado como no válido. Las
consultas lo ignoran, pero todas las escrituras siguen pagando su
mantenimiento, y un índice único no válido puede seguir imponiendo
unicidad. La orden `\d` de psql lo enumera con `INVALID` después de
su definición. Elimínelo y vuelva a ejecutar la construcción
concurrente, o reconstrúyalo con `REINDEX INDEX CONCURRENTLY`.

La consulta oculta la columna dentro de una función. Un índice sobre
`email` no sirve para `WHERE lower(email) = 'ana@example.com'`,
porque el índice contiene `email`, no `lower(email)`. Indexe la
expresión que utiliza la consulta:

```sql
CREATE INDEX users_lower_email_idx ON users (lower(email));
```

La expresión se calcula en cada inserción y cada actualización que
no sea HOT; por eso la documentación recomienda índices de expresión
cuando la velocidad de recuperación importa más que la de escritura.

Se acumulan índices que nadie utiliza. Cada uno que se añade «por si
acaso» cuesta en todas las escrituras y puede impedir
actualizaciones HOT. `pg_stat_user_indexes` cuenta los recorridos
iniciados sobre cada índice en `idx_scan` y registra el momento del
último en `last_idx_scan`:

```sql
SELECT relname, indexrelname, idx_scan, last_idx_scan
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
ORDER  BY relname;
```

Compruebe antes de eliminar. Un índice que respalda una clave
primaria o una restricción de unicidad cumple su función aunque
ninguna consulta lo lea nunca, y un recuento de cero significa poco
en un servidor que acaba de empezar a recibir tráfico.

El planificador ignora un buen índice. Después de una carga masiva,
las estadísticas se quedan por detrás de los datos, las estimaciones
son erróneas y un recorrido completo gana sobre el papel. Ejecute
`ANALYZE`; la documentación dice que evaluar el uso de índices sin
hacerlo es «una causa perdida». Pruebe también con volúmenes
realistas: con cien filas que caben en una página de disco, ningún
índice ofrece ventaja frente a leer esa página. Para saber si un
índice puede utilizarse en absoluto, establezca `enable_seqscan =
off` en una sesión de prueba y ejecute `EXPLAIN` de nuevo. Si el
plan sigue haciendo un recorrido completo, la condición no coincide
con el índice.

Lecturas adicionales

  * Índices: introducción — para qué sirve un índice, cómo se crea y
elimina y la sobrecarga que añade a cada escritura.

  * Tipos de índice — B-tree, Hash, GiST, SP-GiST, GIN y BRIN, y los
operadores a los que puede responder cada uno.

  * Índices multicolumna — cómo determina el orden de las columnas
qué parte puede acotar un B-tree y cuándo se aplica skip scan.

  * CREATE INDEX — la sintaxis completa, incluidos `CONCURRENTLY`,
`INCLUDE`, índices parciales y recuperación tras una construcción no
válida.

  * Uso de EXPLAIN — lectura de un plan: tipos de recorrido, costes
estimados y cifras reales de `EXPLAIN ANALYZE`.

  * Análisis del uso de índices — por qué `ANALYZE` y los datos
reales van primero y cómo comprobar si se puede utilizar un índice.

hEsta página en la web	URL:https://engineer.company/es/glossary/database-index/	engineer.company	70
