Recopilación de estadísticas de uso de índices y tablas

Imagina que tu base de datos es como un gran almacén. Los índices son los catálogos y listas que te ayudan a encontrar lo que buscas rápido. Las tablas son los productos en las estanterías. Si un índice no se usa, es como tener un catálogo tirado en una esquina que nadie abre. Si una tabla se usa mucho pero tiene mala estructura o datos de más, puede sobrecargar nuestro almacén (la base de datos) y hacer que todo vaya más lento.

Tareas principales del análisis:

1. Evaluar la eficiencia del uso de los índices. Por ejemplo, ¿tu índice carísimo está ahí sin usarse? ¡Bórralo sin miedo!
2. Determinar la frecuencia de operaciones de lectura y escritura. Ayuda a entender qué tablas se usan más.
3. Optimización de consultas. Las estadísticas te muestran dónde puedes acelerar el procesamiento de datos añadiendo o cambiando índices.

Vistas pg_stat_user_indexes y pg_stat_user_tables

En PostgreSQL tienes dos vistas súper útiles para recopilar estadísticas: pg_stat_user_indexes y pg_stat_user_tables. Vamos a verlas con más detalle.

pg_stat_user_indexes: ¿cómo se usan los índices?

Campos principales:

• relname — nombre de la tabla a la que pertenece el índice.
• indexrelname — nombre del índice.
• idx_scan — cuántas veces se usó el índice para buscar.
• idx_tup_read — número de filas leídas usando el índice.
• idx_tup_fetch — número de filas realmente devueltas (después de aplicar filtros).

Ejemplo de consulta:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_read, 
       idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Aquí:

• ordenamos los datos por número de veces que se llamó al índice (idx_scan), para ver cuáles son los más populares.
• si un índice casi no se usa (idx_scan = 0), piénsalo: ¿realmente lo necesitas?

Aplicación práctica:

Despliegas una nueva versión de tu app y añades un índice nuevo. Con pg_stat_user_indexes puedes comprobar si tu consulta realmente empezó a usar ese índice, o si PostgreSQL sigue pasando de tu obra maestra de optimización.

pg_stat_user_tables: viendo datos de las tablas

Campos principales:

• relname — nombre de la tabla.
• seq_scan — número de escaneos secuenciales de la tabla (sin usar índices).
• seq_tup_read — número de filas devueltas por escaneos secuenciales.
• idx_scan — número de escaneos por índice en la tabla.
• n_tup_ins — número de filas insertadas.
• n_tup_upd — número de filas actualizadas.
• n_tup_del — número de filas borradas.

Ejemplo de consulta:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
ORDER BY sequential_scans DESC;

¿Qué vemos aquí?

• Las tablas con muchos escaneos secuenciales (seq_scan) pueden estar pidiendo a gritos un índice.
• El número de inserciones, actualizaciones y borrados ayuda a ver con qué frecuencia cambian los datos en la tabla.

Aplicación práctica: Trabajas con la tabla users, donde tienes los datos de todos los usuarios de tu app. Con pg_stat_user_tables ves que los escaneos secuenciales (seq_scan) de esa tabla están por las nubes. Eso es una pista: toca crear índices en las columnas más usadas para acelerar las consultas.

Ejemplo: análisis de índices y tablas en una base real

Supón que tienes una base de datos con las tablas orders (pedidos) y products (productos). Queremos ver cómo se usan las tablas e índices realmente.

Análisis de índices:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_read, 
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

Ves que el índice orders_customer_id_idx se usó 50 mil veces, pero orders_date_idx solo 5 veces. Igual orders_date_idx sobra.

Análisis de tablas:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       seq_tup_read AS tuples_read, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY seq_scan DESC;

La tabla products está sufriendo escaneos secuenciales todo el rato. Eso es una señal: faltan índices en el catálogo de productos.

Errores típicos y cómo evitarlos

La trampa clásica para los que empiezan es pasar de las estadísticas. Por ejemplo, añades un índice nuevo pensando: “Ya está, ahora las consultas van a volar”, pero PostgreSQL ni lo usa porque las estadísticas no se actualizaron solas. Después de cambios grandes en las tablas, no olvides actualizar las estadísticas manualmente con el comando ANALYZE.

Otro error muy común es volverse loco añadiendo índices. Recuerda, cada índice ocupa espacio en disco y ralentiza las operaciones de inserción, actualización y borrado. Usa las estadísticas de pg_stat_user_indexes para asegurarte de que el índice realmente se usa y no está ahí de adorno.

¿Para qué te sirve saber esto?

En desarrollo real: si la base de datos va lenta, lo primero que vas a mirar son las tablas y los índices.

En entrevistas: las preguntas sobre optimización de índices son un clásico de las entrevistas de SQL. ¿Sabes explicar pg_stat_user_indexes? Ya tienes medio aprobado el examen.

En administración de bases de datos: monitorizar es el pan de cada día de un DBA. Sin estadísticas de tablas e índices, no puedes mejorar nada.