Índices PostgreSQL B-Tree

Los índices en PostgreSQL se utilizan para mejorar el rendimiento de las consultas a la base de datos permitiendo una recuperación más rápida de los registros. El índice B-Tree es el tipo de índice por defecto y el más utilizado, ideal para operaciones de recuperación de datos que impliquen comparaciones.

Utilización

Los índices B-Tree se utilizan cuando necesitas buscar, recuperar y ordenar datos de forma eficaz para operaciones que utilizan consultas de igualdad o rango. Se crean automáticamente en las claves primarias, pero pueden crearse manualmente en otras columnas para optimizar el rendimiento de las consultas.

CREATE INDEX index_name ON table_name (column_name);

En esta sintaxis, CREATE INDEX crea un nuevo índice con un nombre especificado en una columna concreta de una tabla, mejorando la velocidad de búsqueda y recuperación. Los índices B-Tree también almacenan valores NULL, lo que puede ser especialmente útil cuando las consultas implican comprobar la presencia o ausencia de dichos valores.

Ejemplos

1. Creación de índices básicos

CREATE INDEX idx_employee_name ON employees (name);

Este ejemplo crea un índice B-Tree en la columna name de la tabla employees para acelerar las consultas que filtran u ordenan por nombres de empleados.

SELECT * FROM employees WHERE name = 'John Doe';

Esta consulta se beneficia del índice, mejorando la velocidad de búsqueda.

2. Índice multicolumna

CREATE INDEX idx_order_date_customer ON orders (order_date, customer_id);

Esta sintaxis crea un índice B-Tree en las columnas order_date y customer_id, lo que resulta beneficioso para las consultas en las que intervienen ambos campos de la tabla orders.

SELECT * FROM orders WHERE order_date = '2023-10-01' AND customer_id = 123;

Aquí, el índice optimiza la recuperación tanto por order_date como por customer_id.

3. Índice único

CREATE UNIQUE INDEX idx_unique_email ON users (email);

Se crea un índice B-Tree único en la columna email, que garantiza que todas las direcciones de correo electrónico de la tabla users son distintas, al tiempo que mejora el rendimiento de la consulta.

SELECT * FROM users WHERE email = 'example@example.com';

El índice único acelera esta consulta localizando rápidamente la dirección de correo electrónico concreta.

Consejos y buenas prácticas

1. Indexa selectivamente. Crea índices sólo en las columnas que se utilicen con frecuencia en las condiciones de búsqueda o en las operaciones de unión.
2. Controla el uso del índice. Analiza regularmente el uso de los índices con herramientas PostgreSQL como pg_stat_user_indexes y EXPLAIN para asegurarte de que son beneficiosos.
3. Ten cuidado con las actualizaciones. Recuerda que los índices pueden ralentizar las operaciones de INSERT, UPDATE, y DELETE, así que equilibra la necesidad de velocidad con la sobrecarga de mantener índices.
4. Ten en cuenta el tamaño del índice. Los índices grandes pueden consumir mucho espacio en disco, por lo que debes gestionarlos con prudencia.
5. Utiliza índices parciales. Crea índices parciales cuando sólo se consulte un subconjunto de datos, mejorando el rendimiento y reduciendo el tamaño del índice. Por ejemplo:

   CREATE INDEX idx_active_users ON users (status) WHERE status = 'active';
           

Índice Mantenimiento

• Utiliza REINDEX para reconstruir índices periódicamente, lo que puede mejorar el rendimiento y recuperar espacio de almacenamiento a medida que los datos cambian con el tiempo. Por ejemplo:

  REINDEX INDEX idx_employee_name;