Análisis de consultas lentas con pg_stat_statements

Cuando curras en proyectos reales, puede que miles de usuarios estén interactuando con tu app al mismo tiempo. Mandan consultas a la base de datos, meten datos, los leen, los actualizan... Y de repente te das cuenta de que tu servidor empieza a "quejarse". Eso es una señal de que tus consultas no son nada óptimas. A veces una consulta que "en teoría" parecía guay, en la práctica puede ser un desastre para el rendimiento. Aquí es donde entra en juego pg_stat_statements.

pg_stat_statements te permite:

1. Rastrear consultas lentas.
2. Ver cuántas veces se han ejecutado ciertas consultas.
3. Saber cuánto tiempo han tardado.
4. Ver el tiempo medio de ejecución de una consulta.
5. No cometer el error fatal de reescribir toda la app!

Explorando la estructura de pg_stat_statements

Después de activar la extensión, aparece una vista especial en tu base de datos llamada pg_stat_statements. Aquí se guardan todos los datos sobre las consultas ejecutadas. Vamos a ver qué contiene:

SELECT * FROM pg_stat_statements LIMIT 1;

El resultado puede ser algo así (versión simplificada):

Breves explicaciones:

• query — la consulta SQL en sí.
• calls — cuántas veces se ejecutó esa consulta.
• total_time — cuánto tiempo total gastó la consulta.
• rows — cuántas filas devolvió la consulta.
• shared_blks_read — número de bloques leídos (va al disco si no usas caché).

Análisis de resultados

Ahora que tienes pg_stat_statements activado, vamos a ver cómo encontrar consultas lentas.

Las consultas más lentas

Para ver qué consultas gastan más tiempo, puedes usar esta consulta:

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Aquí:

• mean_time — es el tiempo medio de ejecución de una consulta (total_time / calls).
• ORDER BY total_time DESC — ordena por el tiempo total de ejecución.

Consultas más frecuentes

A veces el problema no son las consultas lentas, sino las que se ejecutan demasiado a menudo. Por ejemplo:

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

Optimización de consultas

1. Usa indexación

Si ves que las consultas sobre ciertas columnas van lentas, revisa si hay un índice para esas columnas. Por ejemplo, tienes una tabla students con un montón de filas y consultas mucho el campo last_name. Deberías crear un índice:

CREATE INDEX idx_students_last_name ON students (last_name);

2. Reescribe la consulta

Supón que ves que una consulta como SELECT * FROM orders WHERE amount > 1000 tarda demasiado. Seguramente, en vez de "todo sobre todo", deberías seleccionar solo las columnas necesarias:

SELECT order_id, amount FROM orders WHERE amount > 1000;

Limpiar estadísticas

A veces, para ver solo los resultados nuevos (por ejemplo, después de optimizar), necesitas limpiar los datos en pg_stat_statements. Se hace con este comando:

SELECT pg_stat_statements_reset();

Funciona como el botón "Reiniciar" de tu calculadora. Después de ejecutarlo, las estadísticas se empiezan a recolectar de cero.

Buscando consultas problemáticas

Imagina que eres admin de la base de datos de una uni y los estudiantes se quejan en masa de que su área personal carga lentísimo. Decides mirar pg_stat_statements:

Paso 1: Buscar las consultas más lentas

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

Ves que una consulta tipo SELECT * FROM students WHERE status = 'active' tarda 30 segundos. Madre mía. Hay que hacer algo ya.

Paso 2: Revisar la indexación Analizando la tabla students, te das cuenta de que la columna status no tiene índice. Lo arreglas así:

CREATE INDEX idx_students_status ON students (status);

Paso 3: Comprobar el resultado Después de optimizar, vuelves a mirar pg_stat_statements y ves que la consulta ahora tarda 0.5 segundos. ¡Victoria!

Errores comunes usando pg_stat_statements

A veces los admins cometen errores al analizar consultas:

1. Extensión no activada. Si te olvidas de activar pg_stat_statements en shared_preload_libraries, no se recopilarán estadísticas.
2. Ignorar la indexación. Aunque las consultas parezcan lentas, el problema puede resolverse añadiendo los índices correctos.
3. No reiniciar estadísticas. Si no ejecutas pg_stat_statements_reset(), los datos viejos molestan para analizar lo actual.

Usar pg_stat_statements en tu trabajo es como tener un GPS para la base de datos: te dice exactamente dónde estás atascado en un "atasco" y hasta te sugiere cómo salir. Si configuras bien esta herramienta, puedes mejorar mucho el rendimiento de tus bases de datos.