Monitorizando consultas lentas con pg_stat_statements

pg_stat_statements es una extensión nativa de PostgreSQL que te deja ver qué consultas están ocurriendo realmente en la base de datos y cómo se comportan. Básicamente, es como un ayudante silencioso pero atento que apunta cada paso: qué consultas SQL se ejecutaron, cuánto tardaron, cuántas veces se lanzaron y cuánto cargaron el sistema.

¿Para qué sirve esto? Primero, para encontrar consultas problemáticas. A veces la base va lenta no por un solo villano, sino por decenas de consultas pesadas iguales que se ejecutan demasiado a menudo. Además, las estadísticas te ayudan a ver qué consultas están consumiendo recursos — procesador, memoria, disco. También puedes ver si los índices funcionan como esperabas: puede que en algún sitio no se usen para nada, y en otros falten.

pg_stat_statements te permite dejar de adivinar y ver cifras reales — y con eso sacar conclusiones y optimizar.

¿Cómo encontrar consultas lentas?

¡Ahora empieza lo divertido! Usando la tabla pg_stat_statements, podemos buscar consultas que tardan mucho o que cargan mucho el servidor.

La idea principal:

Cada fila en la tabla pg_stat_statements representa estadísticas de una consulta. Las consultas se agrupan por su texto (el campo query), y para cada una se cuentan estas métricas:

• total_time — tiempo total de ejecución de la consulta, en milisegundos.
• calls — número de veces que se ejecutó la consulta.
• mean_time — tiempo medio de ejecución (total_time / calls).
• rows — número de filas que devolvió la consulta.

Ejemplo de análisis sencillo

Vamos a buscar las consultas más lentas por tiempo medio de ejecución:

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

Esta consulta te muestra el TOP-5 de consultas que más tardan en ejecutarse. Fíjate en el campo mean_time: si ves valores en milisegundos que pasan de 500-1000, es señal de que hay que optimizar esas consultas.

Ejemplo de análisis de consultas lentas

Vamos a ver un ejemplo:

Aquí tienes el resultado de la consulta anterior:

¿Qué vemos aquí?

Consulta a la tabla orders: se ejecuta muy pocas veces (solo 10 llamadas), pero cada vez arrastra 10 mil filas. Seguramente la tabla es enorme y la consulta no usa índices.

Consulta a la tabla products: se llama millones de veces, probablemente en un bucle en la app. Cada selección devuelve solo 100 filas, pero por la frecuencia también puede ser un problema.

Consulta a la tabla customers: se ejecuta rápido (543 ms), pero se llama demasiado a menudo.

Optimizando consultas lentas

Ahora que hemos encontrado las consultas problemáticas, hay que mirar su plan de ejecución con EXPLAIN ANALYZE. Por ejemplo, para la consulta a la tabla orders:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'nuevo';

¿Qué podemos ver?

Seq Scan: si la consulta usa un escaneo secuencial, hay que añadir un índice:

CREATE INDEX idx_orders_status ON orders (status);

Problemas con la filtración: si la consulta arrastra demasiadas filas, revisa el texto de la consulta. Puede que necesites añadir más condiciones o limitar los resultados:

SELECT * FROM orders WHERE status = 'nuevo' LIMIT 100;

Mostrando estadísticas por tiempo de ejecución

A veces las consultas problemáticas no son tan obvias. Por ejemplo, consultas que llaman funciones o subconsultas a menudo. En estos casos es útil mirar la columna total_time:

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

Esta consulta te muestra las consultas más "caras" por tiempo total de ejecución.

Optimizando la indexación

A menudo las consultas lentas se deben a la falta de índices necesarios. Usa pg_stat_statements para ver qué consultas no usan índices. Si ves muchas consultas con los mismos filtros (por ejemplo, por el campo status), pero son muy lentas, añade el índice correspondiente:

CREATE INDEX idx_orders_status ON orders (status);

Después de esto, revisa el rendimiento de la consulta otra vez con EXPLAIN ANALYZE.

Usando pg_stat_statements, puedes monitorizar el rendimiento de las consultas, encontrar los "cuellos de botella" y mejorar el rendimiento de tu base de datos. Recuerda: cuanto antes empieces a analizar las consultas, más fácil será optimizar todo el sistema.