Saltar al contenido principal

PostgreSQL CREAR ÍNDICE

Los índices en PostgreSQL son objetos de base de datos que mejoran la velocidad de las operaciones de recuperación de datos. Sirven como punteros para localizar rápidamente filas en una tabla, mejorando el rendimiento de las consultas.

Utilización

Los índices se utilizan para optimizar el rendimiento de las consultas SELECT y las cláusulas WHERE. Son especialmente útiles para tablas grandes que requieren un acceso rápido a los datos.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

En esta sintaxis, `CREAR ÍNDICE` define un nuevo índice denominado `nombre_índice` sobre el `nombre_tabla` y la(s) columna(s) especificados.

Ejemplos

1. Creación de índices básicos

CREATE INDEX idx_customer_name
ON customers (customer_name);

Este ejemplo crea un índice en la columna `nombre_cliente` de la tabla `clientes`, lo que acelera las búsquedas en esta columna.

2. Índice compuesto

CREATE INDEX idx_order_customer
ON orders (customer_id, order_date);

Este índice compuesto de la tabla `pedidos` utiliza las columnas `id_cliente` y `fecha_pedido`, lo que mejora el rendimiento de las consultas que filtran por estas columnas. Los índices compuestos son beneficiosos cuando las consultas incluyen con frecuencia varias columnas en sus cláusulas WHERE.

3. Índice único

CREATE UNIQUE INDEX idx_unique_email
ON users (email);

Un índice único garantiza que todos los valores de la columna indexada "correo electrónico" sean distintos en toda la tabla "usuarios", lo que evita entradas duplicadas y optimiza las búsquedas únicas.

4. Índice parcial

CREATE INDEX idx_active_users
ON users (last_login_date)
WHERE active = true;

Se crea un índice parcial en la columna `last_login_date` sólo para las filas en las que `active` es verdadero, optimizando las consultas dirigidas a este subconjunto de datos.

5. Índice de texto completo

CREATE INDEX idx_document_content
ON documents USING gin(to_tsvector('english', content));

Este ejemplo crea un índice de texto completo en la columna "contenido" de la tabla "documentos", lo que mejora el rendimiento de las consultas de búsqueda de texto completo.

Impacto de la indexación

Los índices pueden mejorar significativamente el rendimiento de las consultas de lectura, pero también pueden afectar al rendimiento de las operaciones de escritura, como `INSERT`, `UPDATE` y `DELETE`. El compromiso consiste en equilibrar una recuperación de datos más rápida con una modificación de datos potencialmente más lenta.

Consejos y buenas prácticas

  • Indexar selectivamente. Indiza sólo las columnas que se utilicen con frecuencia en las condiciones de consulta, para evitar sobrecargas innecesarias.
  • Supervisar y mantener. Analiza y mantén regularmente los índices para asegurarte de que se utilizan eficazmente.
  • Cuidado con la sobreindexación. Una indexación excesiva puede ralentizar las modificaciones de datos como `INSERT`, `UPDATE` y `DELETE`.
  • Utilizar índices parciales. Considera los índices parciales para columnas con un elevado número de NULL o un subconjunto específico de datos.
  • Aprovecha los índices únicos. Utiliza índices únicos para reforzar la integridad de los datos y mejorar el rendimiento de las consultas.
  • Funciones avanzadas. Explora las opciones de indexación, como los índices de expresión para valores calculados o los índices de cobertura para incluir columnas adicionales en el índice.
  • Utiliza REINDEX. Utiliza periódicamente el comando `REINDEX` para reconstruir índices dañados o mejorar el rendimiento tras cambios significativos en los datos.