Índices B-tree en PostgreSQL

1. ¿Qué es un índice B-tree?

Un índice B-tree (Balanced Tree o árbol balanceado) es una estructura de datos que permite a PostgreSQL localizar valores de manera eficiente sin tener que recorrer toda la tabla.

Organiza las claves del índice siguiendo un orden, lo que permite descartar partes de la estructura durante una búsqueda.

Analogía: imagina el índice de un libro. Si quieres encontrar un tema, puedes ir directamente a la sección correspondiente en lugar de leer todas las páginas.

2. ¿Para qué sirve?

Los índices B-tree son útiles para:

• Igualdad: encontrar un valor exacto.
• Rangos: localizar valores mayores, menores o comprendidos entre dos valores.
• Ordenamiento: ayudar a ejecutar consultas con ORDER BY.
• Búsquedas por prefijo: encontrar textos que comienzan con determinados caracteres, bajo las condiciones apropiadas.

3. Ejemplo práctico

Supongamos que tenemos una tabla categoria con una columna nombre:

CREATE TABLE categoria (
    id_categoria BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL UNIQUE
);

La restricción UNIQUE crea automáticamente un índice único B-tree de manera predeterminada en PostgreSQL.

Por eso, no necesitamos crear otro índice B-tree sobre nombre.

Búsqueda exacta

SELECT *
FROM categoria
WHERE nombre = 'Llantas';

El índice puede ayudar a encontrar la categoría sin recorrer toda la tabla.

Búsqueda por rango

SELECT *
FROM categoria
WHERE nombre >= 'L'
  AND nombre < 'M';

El índice puede ayudar a localizar los nombres que caen dentro de ese rango, según las reglas de ordenamiento aplicables.

Ordenamiento

SELECT *
FROM categoria
ORDER BY nombre;

PostgreSQL puede aprovechar el índice para obtener los resultados ordenados, aunque también podría elegir otra estrategia.

4. ¿Por qué no funciona igual con LIKE '%valor%'?

La posición del comodín % es importante:

Consulta	Uso de B-tree
nombre = 'Llantas'	Puede aprovecharlo.
nombre LIKE 'Llan%'	Puede aprovecharlo en condiciones apropiadas.
nombre LIKE '%llan%'	Generalmente no puede aprovecharlo eficazmente.

El motivo es que B-tree se beneficia del orden de las claves. Si buscamos Llan%, conocemos el comienzo del texto y podemos acotar la búsqueda.

En cambio, %llan% significa que el texto puede aparecer en cualquier posición. El orden del índice no permite identificar fácilmente un rango reducido de valores que contengan esa secuencia.

Para búsquedas de texto que contienen fragmentos en cualquier posición, PostgreSQL ofrece alternativas como pg_trgm, que permite utilizar índices de trigramas.

5. ¿Siempre se utiliza el índice?

No. PostgreSQL dispone de un planificador que estima qué estrategia resulta más económica para cada consulta.

Si una tabla tiene únicamente 15 registros, podría ser más eficiente recorrerla completa que acceder al índice y después recuperar los registros correspondientes.

Por tanto, tener un índice no garantiza que PostgreSQL lo utilice en cada consulta.

Resumen para recordar

• B-tree: índice ordenado que facilita búsquedas y operaciones basadas en rangos.
• =: búsqueda de un valor exacto.
• ORDER BY: puede aprovechar el orden del índice.
• LIKE 'texto%': puede aprovecharlo en condiciones apropiadas.
• LIKE '%texto%': generalmente necesita otra estrategia para acelerar búsquedas por fragmentos.
• UNIQUE: crea automáticamente un índice único en PostgreSQL.
• Planificador: decide si conviene utilizar el índice según el costo estimado.

Idea clave para memorizar

B-tree aprovecha el orden de los valores; cuando una búsqueda no permite aprovechar ese orden, como en '%texto%', suele perder su ventaja.