Todos los índices de las dos lecciones anteriores han sido B-tree, y no es casualidad: es el método por omisión y el que usarás el 95 % de las veces. Pero hay preguntas que un B-tree no sabe responder. ¿Cómo buscas '%aceite%' sin prefijo? ¿Cómo indexas un documento JSONB? ¿Cómo indexas una tabla histórica de tres mil millones de filas sin que el índice ocupe cien gigas? Para eso PostgreSQL tiene seis métodos de acceso distintos, y la primera mitad de esta lección es el mapa para elegir entre ellos.

La segunda mitad es más importante y casi nunca se enseña: cuándo no indexar. Porque el error caro de los índices no es olvidarse de uno —eso se detecta con EXPLAIN y se arregla en un minuto—, sino acumular veinte que nadie usa, que engordan cada escritura y que ralentizan el mantenimiento. Verás el coste real de un índice, los cinco casos en los que estorba —incluido el de TiendaVerde, demostrado—, la regla de selectividad que decide la mayoría de los casos dudosos, y una checklist para preguntarte antes de escribir CREATE INDEX.

1. Los seis métodos de acceso de PostgreSQL
2. B-tree, Hash y por qué el segundo casi nunca compensa
3. GIN: listas de cosas dentro de una columna
4. GiST, SP-GiST y BRIN
5. pg_trgm: acelerar LIKE '%texto%' de una vez
6. Cuánto cuesta de verdad un índice
7. Los cinco casos en que un índice no sirve o estorba
8. La regla de la selectividad
9. Cómo decidir: partir de las consultas, no de la intuición
10. Checklist: antes de crear un índice, pregúntate…
11. Errores Comunes y Consejos
12. Ejercicios
13. Conclusión

La tabla que resume cuándo usar cada uno:

Léela con esta idea: el B-tree indexa un valor por fila; los demás indexan otra cosa. GIN indexa las partes de un valor (las palabras de un texto, las claves de un JSON, los trigramas de una cadena). GiST indexa regiones que pueden solaparse. BRIN no indexa filas en absoluto: indexa bloques de disco.

El B-tree ya lo conoces de 08-01: ordenado, logarítmico, sirve para igualdades, rangos, prefijos, ordenaciones y extremos. Es el valor por omisión de CREATE INDEX y la respuesta correcta salvo que tengas un motivo concreto para otra cosa.

El Hash guarda el resultado de una función hash de la clave. Eso lo hace muy rápido para =… y completamente inútil para todo lo demás:

La ventaja real del hash es una sola: con claves muy largas (una URL de 500 caracteres, un hash SHA-256 en texto) guarda 4 bytes en lugar del valor entero, y el índice sale bastante más pequeño. Fuera de ese caso, el B-tree hace lo mismo y muchísimo más por un coste parecido. (Contexto histórico de su mala fama: hasta PostgreSQL 9.6 no se escribían en el registro de transacciones, así que se corrompían tras una caída y no se replicaban. Desde la 10 son seguros.)

GIN (Generalized Inverted Index) es un índice invertido, la misma idea que el índice de un libro llevada al extremo: en lugar de una entrada por fila, guarda una entrada por cada elemento contenido en la fila, y en cada una la lista de filas donde aparece. Sirve cuando una columna contiene muchas cosas y quieres buscar por una de ellas:

Sus tres terrenos naturales:

Sus dos costes, que hay que tener presentes: se construye despacio y se actualiza despacio, porque una sola fila puede generar decenas de entradas. Para cargas de escritura intensiva sobre columnas GIN, PostgreSQL amortigua con una lista pendiente (fastupdate), pero el patrón sigue siendo "escribe poco, lee mucho".

GiST (Generalized Search Tree) es un árbol donde cada nodo describe una región que contiene a sus hijos, y esas regiones pueden solaparse. Eso lo hace la herramienta natural para datos con extensión:

Esa restricción EXCLUDE —que solo puede implementarse con GiST— rechaza cualquier inserción cuyo rango de fechas se solape con otra promoción del mismo producto. Es una regla de negocio que ni UNIQUE ni CHECK pueden expresar. GiST es también la base de PostGIS (mapas, coordenadas, "los diez almacenes más cercanos a Valencia") y del operador de vecinos <->.

SP-GiST (Space-Partitioned GiST) es su primo para estructuras que se dividen en trozos disjuntos y muy desiguales: árboles de prefijos para cadenas, quadtrees para puntos, direcciones IP. Es especializado; sabrás que lo necesitas cuando lo necesites.

BRIN (Block Range INdex) merece más atención, porque es el que más sorprende. No guarda una entrada por fila: guarda, por cada grupo de 128 bloques de disco, el valor mínimo y el máximo de la columna. Nada más.

Con un histórico de pedidos que se inserta en orden cronológico, el bloque 40.000 contiene fechas de junio de 2024 y solo de junio de 2024. Así que para WHERE fecha_pedido BETWEEN '2024-06-01' AND '2024-06-30' el motor descarta el 99,9 % de los bloques leyendo un resumen minúsculo, y recorre secuencialmente los pocos que quedan.

Esa última fila es la condición y la trampa: si los datos no están físicamente ordenados por la columna, un BRIN no sirve para nada. Si cada bloque contiene fechas de 2019 a 2026, todos los resúmenes se solapan y hay que leerlos todos. Por eso BRIN es la respuesta perfecta para tablas de histórico o de registro que solo crecen por el final, y una mala idea para una columna que se actualiza sin orden.

Llegó el momento de cerrar la promesa que 04-01 dejó abierta. Un B-tree no puede resolver LIKE '%aceite%' porque sin prefijo no hay punto de entrada en el orden. La solución es dejar de indexar la cadena y empezar a indexar sus trigramas: todos los grupos de tres caracteres consecutivos.

La extensión normaliza a minúsculas, añade dos espacios delante y uno detrás, y trocea. Ahora un índice GIN sobre esos trigramas hace que buscar '%aceite%' sea buscar filas que contengan todos los trigramas de 'aceite':

Dos filas. Con 20 productos el motor hará Seq Scan de todas formas; con 40.000 referencias en el buscador de la tienda, la diferencia es entre 300 ms y 3 ms. Y hay un premio adicional: el mismo índice acelera la búsqueda por parecido, que es lo que quieres cuando el cliente escribe mal el nombre:

El operador % significa "se parece lo bastante" según el umbral pg_trgm.similarity_threshold (0,3 por omisión). Sobre la duda habitual, GIN o GiST para trigramas: gin_trgm_ops busca más rápido pero ocupa más y se escribe peor; gist_trgm_ops es más pequeño, más barato de mantener y el único que admite la búsqueda por vecinos con <->. GIN por defecto; GiST si escribes mucho o necesitas ordenar por distancia.

Nota de dialecto: esto es territorio muy divergente. MySQL 8 ofrece índices FULLTEXT (con MATCH ... AGAINST), que no son lo mismo que trigramas y no aceleran un LIKE '%x%' genérico. SQL Server tiene Full-Text Search como componente aparte. SQLite tiene el módulo FTS5. Oracle tiene Oracle Text. Ninguno replica exactamente pg_trgm: si tu producto depende de la búsqueda difusa, es un factor real en la elección de motor.

Hasta aquí, el catálogo. A partir de aquí, la parte que evita los desastres. Cada índice que creas cobra un peaje permanente:

Un matiz de PostgreSQL que conviene conocer: un UPDATE escribe una versión nueva de la fila, no modifica la existente. Si la fila nueva cabe en el mismo bloque y ninguna columna indexada ha cambiado, el motor aplica una optimización llamada HOT update que evita tocar los índices. Pero basta con que un solo índice cubra una columna modificada para perder esa optimización y tener que actualizar todos los índices. Es decir: un índice mal elegido puede encarecer UPDATE que ni siquiera lo usan.

Este es el más fácil de demostrar, y ya lo has visto insinuado dos veces:

Los veinte productos caben en una sola página de 8 kB. Leer esa página cuesta un acceso. Usar un índice costaría: leer la página de metadatos del índice, leer la raíz, obtener el ctid y volver a leer la página de la tabla. Como mínimo tres accesos para hacer el trabajo de uno. Por eso, aunque crees el índice más perfecto del mundo sobre productos.precio, el planificador lo ignorará — y hará bien. Lo verás en el plan real en la lección 08-05.

La frontera aproximada está en unos pocos cientos de filas, o dicho mejor: mientras la tabla quepa en unas pocas páginas y viva permanentemente en caché, no hay nada que optimizar. Los índices de TiendaVerde que has creado en 08-02 son una inversión para el futuro, no una mejora de hoy.

La cardinalidad es el número de valores distintos. Cuanto más se parece al número de filas, más útil es el índice:

El caso booleano es el más claro. productos.activo tiene 19 verdaderos y 1 falso. Un índice sobre esa columna tendría dos "bloques" de entradas, y buscar por activo = TRUE devolvería el 95 % de la tabla: no hay nada que descartar. La única forma de sacarle partido a una columna así es al revés, con un índice parcial (08-02) que indexe solo el lado minoritario o que use activo como condición y otra columna como clave.

productos.coste, pedidos.gastos_envio, lineas_pedido.descuento, empleados.salario. Son columnas que se muestran, se suman y se calculan, pero por las que nadie pone un WHERE. Un índice sobre ellas es coste puro: espacio, escrituras y VACUUM a cambio de cero lecturas aceleradas.

Antes de indexar una columna, la pregunta es literal: ¿puedo escribir la consulta real, con su WHERE, que este índice va a acelerar? Si no te sale, no lo crees.

Una tabla de registro de eventos, de auditoría o de telemetría recibe miles de INSERT por segundo y se consulta una vez al día. Cada índice multiplica el trabajo del camino caliente para beneficiar al camino frío. En esos casos:

• Reduce los índices al mínimo imprescindible.
• Si el acceso es por fecha y la tabla solo crece por el final, BRIN en lugar de B-tree: prácticamente gratis de mantener.
• Considera crear el índice solo cuando vaya a usarse (antes del informe mensual) y borrarlo después, aunque suene raro.

Es el caso de la sección 10 de 08-02: (a) sobra si existe (a, b). Merece repetirse aquí porque es el índice inútil más frecuente en bases de datos reales: se creó el simple, meses después alguien creó el compuesto para otra consulta, y nadie borró el primero.

Los cinco casos anteriores se resumen en un solo criterio cuantitativo, y es el que usa el propio planificador:

Si la consulta va a devolver más de un 5–10 % de las filas de la tabla, el recorrido secuencial suele ganar.

Suena contraintuitivo hasta que se entiende el motivo, que es puramente físico:

• Un Seq Scan lee bloques contiguos. El disco (y sobre todo la lectura anticipada del sistema operativo) está optimizado para eso: leer 1.000 bloques seguidos no cuesta 1.000 veces leer uno.
• Un Index Scan produce ctid en el orden del índice, no en el del disco. Cada fila puede estar en un bloque distinto y en cualquier posición: son accesos aleatorios. PostgreSQL lo modela con dos parámetros, seq_page_cost = 1.0 y random_page_cost = 4.0: un acceso aleatorio se estima cuatro veces más caro que uno secuencial.

Con esos números, si tu filtro devuelve la mitad de la tabla, ir por el índice significa hacer medio millón de accesos aleatorios a cuatro veces el precio para evitar leer un millón de bloques secuenciales. Pierdes.

Aplicado a TiendaVerde:

Sobre random_page_cost: ese 4.0 por omisión viene de la época de los discos mecánicos. En un SSD la diferencia entre acceso secuencial y aleatorio es mucho menor, y la recomendación habitual es bajarlo a 1.1. Es uno de los ajustes de configuración que más cambian los planes elegidos, y explica por qué la misma consulta puede usar el índice en un servidor y no en otro. Verás cómo consultarlo con EXPLAIN (SETTINGS) en 08-05.

El antipatrón tiene nombre propio: "indexemos todas las columnas por si acaso". Suena prudente y es exactamente lo contrario, porque cambia un problema visible y fácil (una consulta lenta que EXPLAIN te señala en diez segundos) por uno invisible y difícil (escrituras un 40 % más lentas, VACUUM que no termina, caché desperdiciada, y un plan mal elegido de vez en cuando).

El método correcto va al revés: de las consultas a los índices. Y la fuente de verdad es la extensión pg_stat_statements, que registra todas las sentencias ejecutadas con sus tiempos:

Ordena por tiempo total, no por tiempo medio: una consulta de 5 ms ejecutada un millón de veces al día hace más daño que una de 10 segundos que se lanza una vez. Ese ranking te da la lista real de candidatas, y sobre cada una aplicas EXPLAIN ANALYZE (08-05).

En cuatro pasos:

1. Mide qué consultas consumen el tiempo (pg_stat_statements).
2. Diagnostica cada una con EXPLAIN ANALYZE y busca el Seq Scan sobre una tabla grande con un filtro selectivo.
3. Prueba el índice y vuelve a medir: ¿cambió el plan?, ¿bajó el tiempo?
4. Revisa al cabo de unas semanas con pg_stat_user_indexes: ¿lo está usando alguien?

• Usar hash "porque las búsquedas por igualdad son más rápidas". El B-tree resuelve la igualdad casi igual de bien y además sirve para rangos, orden y UNIQUE.
• Poner un BRIN sobre una columna sin orden físico. Si los valores están repartidos por toda la tabla, los resúmenes se solapan y el índice no descarta nada.
• Esperar que un GIN acelere las escrituras. Es el método más caro de mantener; su sitio es "escribe poco, lee mucho".
• Indexar un booleano. Dos valores distintos no descartan nada. La versión útil es un índice parcial con ese booleano en el WHERE.
• Crear un índice sin una consulta concreta detrás. Es la definición del antipatrón "por si acaso".
• Confundir "el índice existe" con "el índice se usa". Solo EXPLAIN y pg_stat_user_indexes responden a lo segundo.
• Ordenar pg_stat_statements por tiempo medio. El daño real lo hace el tiempo total: llamadas × coste.
• Consejo: cuenta las filas antes de indexar. SELECT COUNT(*) FROM tabla WHERE <tu filtro> frente al total te da la selectividad en cinco segundos y decide la mitad de los casos.
• Consejo: cuando dudes entre dos índices, crea uno, mide y borra el peor. DROP INDEX es instantáneo y no toca los datos: experimentar sale barato.
• Consejo: apunta en el propio esquema para qué se creó cada índice. COMMENT ON INDEX idx_pedidos_pendientes IS 'Panel de gestión: pedidos por procesar'; hace que dentro de dos años alguien pueda borrarlo con criterio.

Elige el método de acceso adecuado para cada necesidad y escribe el CREATE INDEX:

1. El buscador de la tienda permite escribir cualquier trozo del nombre de un producto.
2. Un histórico de 500 millones de eventos, insertado siempre en orden de fecha, que se consulta por rangos de días.
3. La columna atributos JSONB de un producto, donde se busca por pares clave-valor.
4. El campo email de una tabla de 50 millones de usuarios, consultado siempre por igualdad exacta y nunca ordenado.
5. Un calendario de reservas donde dos reservas de la misma sala no pueden solaparse en el tiempo.

Para cada propuesta, di si crearías el índice y por qué. Usa datos reales de TiendaVerde.

Una aplicación con una tabla eventos de 800 millones de filas recibe 4.000 INSERT por segundo y tiene once índices. El equipo se queja de que las inserciones van cada vez más lentas y de que VACUUM no termina nunca.

1. Explica la relación entre los once índices y los dos síntomas.
2. Propón un método para decidir cuáles borrar, con las consultas concretas.
3. ¿Qué alternativa hay para el índice de la columna fecha si la tabla solo crece por el final?

Solución 1

En el 4, un B-tree sería igual de válido y más versátil; el hash solo gana en tamaño, y únicamente porque nunca se ordena ni se busca por rango. Si hubiera la menor duda, B-tree.

Solución 2

Solución 3

1. La relación. Cada INSERT tiene que escribir en la tabla y en los once índices: es un factor multiplicador sobre las 4.000 inserciones por segundo, y explica que el ritmo se degrade a medida que los árboles crecen y hay más divisiones de página. Y VACUUM recorre cada índice por separado para limpiar las entradas muertas: con once índices sobre 800 millones de filas, cada pasada es once veces el trabajo. Los dos síntomas son la misma causa.

2. El método:

Con las cuatro cautelas de 08-02: comprobar desde cuándo se acumulan los contadores (que no falte el informe trimestral), no tocar los que respaldan una PK o un UNIQUE, revisar los de claves foráneas, y mirar también las réplicas. Después, contrastar con pg_stat_statements qué consultas se ejecutan de verdad, y buscar redundancias por la regla del prefijo: cualquier (a) que conviva con un (a, b) sobra.

3. La alternativa. Un BRIN sobre fecha. Si la tabla solo crece por el final, el orden físico coincide con el cronológico, que es exactamente su requisito. Pasarías de un B-tree de decenas de gigabytes —que hay que mantener en cada uno de los 4.000 INSERT por segundo y recorrer entero en cada VACUUM— a un índice de unos pocos megabytes con coste de mantenimiento casi nulo, conservando la capacidad de filtrar por rangos de días.

Ya tienes el mapa completo y, sobre todo, el criterio para no usarlo:

• PostgreSQL ofrece seis métodos de acceso. El B-tree resuelve el 95 % de los casos; el hash casi nunca compensa; GIN indexa las partes de un valor (arrays, JSONB, texto, trigramas); GiST indexa regiones solapables (rangos, geometría, EXCLUDE); BRIN resume bloques y es diminuto pero exige orden físico; SP-GiST es para estructuras jerárquicas.
• pg_trgm + GIN cierra la promesa de 04-01: LIKE '%aceite%' e ILIKE por fin tienen índice, y de regalo la búsqueda por parecido con similarity() y el operador %.
• Un índice cuesta: espacio, INSERT/UPDATE/DELETE más lentos, VACUUM más lento, caché ocupada. Y puede encarecer incluso los UPDATE que no lo usan, al impedir la optimización HOT.
• No indexes tablas pequeñas —los 20 productos de TiendaVerde caben en una página, y el índice ocupa más que la tabla—, columnas de baja cardinalidad, columnas por las que nunca se filtra, tablas de escritura masiva, ni nada que ya cubra otro índice.
• La regla de la selectividad: por encima del 5–10 % de filas devueltas, gana el Seq Scan, porque un acceso aleatorio se estima cuatro veces más caro que uno secuencial.
• El método correcto va de las consultas a los índices, con pg_stat_statements ordenado por tiempo total, y nunca al revés.

Con las tres primeras lecciones sabes qué es un índice, cómo se crea y cuándo no crearlo. Pero un índice es solo una de las herramientas del rendimiento, y muchas veces ni siquiera la que hace falta: hay consultas que van lentas por cómo están escritas, y ningún índice del mundo las arregla. En la lección 08-04, Técnicas de optimización de consultas, verás las reglas de escritura que sí importan —empezando por la sargabilidad, ese WHERE EXTRACT(YEAR FROM fecha_pedido) = 2025 que hay que convertir en un rango—, cómo funcionan las estadísticas que alimentan al planificador y qué pasa cuando se desfasan, y el problema que más veces está detrás de una pantalla lenta y que no se arregla en la base de datos en absoluto: el N+1 de la aplicación.