Índices de expresión PostgreSQL

Los índices de expresión en PostgreSQL te permiten crear un índice basado en expresiones o cálculos que afectan a una o varias columnas. Se utilizan para optimizar las consultas que implican cálculos o transformaciones complejas directamente dentro de la cláusula `WHERE`.

Utilización

Los índices de expresiones son especialmente útiles cuando las consultas implican con frecuencia valores o funciones calculados. Mejoran el rendimiento de la consulta indexando el resultado de una expresión en lugar de los datos brutos de la columna.

CREATE INDEX index_name ON table_name (expression);

En esta sintaxis, `expresión` representa el cálculo o función cuyo resultado quieres indexar, mejorando la eficacia de recuperación de las consultas que incluyan esa expresión.

Ejemplos

1. Índice de expresiones básicas

CREATE INDEX idx_lower_email ON users (LOWER(email));

Aquí se crea un índice sobre la versión en minúsculas de la columna "correo electrónico" de la tabla "usuarios", optimizando las búsquedas que no distinguen mayúsculas de minúsculas.

2. Indexar un valor calculado

CREATE INDEX idx_total_cost ON orders (quantity * price);

Este ejemplo crea un índice sobre el resultado de multiplicar `cantidad` por `precio` en la tabla `pedidos`, acelerando las consultas que filtran en función del coste total.

3. Índice con una función

CREATE INDEX idx_date_part ON events (date_part('year', event_date));

Este ejemplo indexa el año extraído de la `fecha_del_evento` mediante la función `fecha_parte`, mejorando el rendimiento de las consultas basadas en el año.

Consejos y buenas prácticas

• Utiliza índices de expresión para las expresiones de uso frecuente. Indexa las expresiones que aparecen a menudo en la cláusula `WHERE` para aumentar el rendimiento.
• Ten en cuenta los gastos generales de mantenimiento. Los índices de expresión pueden aumentar el tiempo de las operaciones `INSERT` y `UPDATE` debido a la indexación adicional.
• Impacto en el rendimiento de la prueba. Comprueba siempre el impacto en el rendimiento de los índices de expresión recién creados, ya que no siempre aportan una ventaja.
• Mantén las expresiones sencillas. Las expresiones complejas pueden no producir mejoras significativas en el rendimiento y pueden complicar el mantenimiento de los índices.
• Comprende las limitaciones. Los índices de expresiones pueden no ser beneficiosos cuando la selectividad de los índices es baja o las expresiones son demasiado complejas, lo que puede dar lugar a un aumento mínimo del rendimiento.

Consideraciones adicionales

• Diferencia de los índices regulares y parciales: A diferencia de los índices regulares, que utilizan datos de columna sin procesar, los índices de expresión utilizan valores calculados. Los índices parciales, en cambio, se crean con una condición para indexar sólo un subconjunto de filas, lo que es distinto de utilizar expresiones.
• Coherencia y actualización de datos: PostgreSQL actualiza automáticamente las expresiones indexadas cuando cambian los datos. Sin embargo, ten en cuenta que esto puede añadir sobrecarga a las operaciones `INSERT` y `UPDATE`.
• Eliminar un índice de expresión: Para eliminar un índice de expresión, puedes utilizar la siguiente sintaxis:

DROP INDEX index_name;

Esto puede ser útil si un índice ya no es necesario o afecta negativamente al rendimiento.