Cómo encontrar consultas lentas en PostgreSQL con pg_stat_statements

Cómo encontrar consultas lentas en PostgreSQL con pg_stat_statements#

Una API empieza a responder más lento. El uso de CPU de la base de datos aumenta. Un dashboard comienza a agotar el tiempo de espera de forma ocasional.

Sabes que PostgreSQL está involucrado, pero todavía no sabes qué consulta SQL merece atención.

Aquí es donde muchas optimizaciones empiezan mal. Es tentador inspeccionar la tabla más grande, añadir un índice sobre una columna sospechosa o intentar optimizar la consulta que parece más complicada dentro del código.

Ninguna de esas estrategias responde primero a la pregunta correcta:

¿Qué consultas están consumiendo realmente el tiempo de la base de datos?

pg_stat_statements ayuda a responder esa pregunta agregando estadísticas de planificación y ejecución de las sentencias SQL procesadas por PostgreSQL. En lugar de observar una petición aislada, permite estudiar el workload completo: cuántas veces se ejecuta cada consulta, cuánto tiempo de ejecución acumula, cuánto cuesta en promedio y cuánto trabajo genera dentro de la base de datos.

El objetivo no es encontrar la consulta con el SQL más feo.

Es encontrar los patrones de consultas que realmente merecen ser investigados.

pg_stat_statements guarda historial del workload, no actividad en tiempo real#

Antes de usarlo conviene separar dos problemas distintos.

pg_stat_statements responde preguntas como:

• ¿Qué patrones de consultas han acumulado más tiempo de ejecución?
• ¿Qué sentencias se ejecutan con mayor frecuencia?
• ¿Qué consultas tienen una latencia promedio elevada?
• ¿Qué consultas generan una cantidad significativa de lecturas, accesos a buffers o archivos temporales?

No está pensado principalmente para responder:

¿Qué consulta se está ejecutando o esperando exactamente ahora?

Para inspeccionar sesiones actuales y consultas que se están ejecutando en este momento, pg_stat_activity suele ser el mejor punto de partida.

La diferencia puede resumirse así:

pg_stat_activity
    ↓
¿Qué está ocurriendo ahora?

pg_stat_statements
    ↓
¿Qué ha acumulado coste con el tiempo?

Durante un incidente activo, ambas herramientas pueden resultar útiles.

Pero si quieres entender qué patrones de consultas han consumido recursos durante un periodo representativo, pg_stat_statements es la herramienta adecuada.

Qué mide realmente pg_stat_statements#

pg_stat_statements mantiene estadísticas de las sentencias SQL ejecutadas por el servidor PostgreSQL.

Entre las columnas más útiles están:

• calls: número de ejecuciones;
• total_exec_time: tiempo total de ejecución acumulado;
• mean_exec_time: tiempo promedio por ejecución;
• min_exec_time y max_exec_time;
• stddev_exec_time: variación del tiempo de ejecución;
• rows: número total de filas recuperadas o afectadas;
• shared_blks_hit y shared_blks_read;
• lecturas y escrituras de bloques temporales;
• estadísticas relacionadas con WAL;
• estadísticas de planificación cuando su seguimiento está habilitado.

Los tiempos de ejecución se expresan en milisegundos. stddev_exec_time es una desviación estándar, no un percentil como p95.

Las estadísticas de ejecución se registran tras ejecuciones exitosas. Las sentencias fallidas o canceladas no quedan reflejadas por completo; revisa también logs y pg_stat_activity al investigar timeouts.

Las estadísticas se agrupan utilizando dimensiones como la base de datos, el usuario, el identificador de consulta y si la sentencia se ejecutó como una consulta de nivel superior.

PostgreSQL también normaliza la estructura de las consultas para recopilar las estadísticas.

Supongamos que una aplicación ejecuta:

SELECT id, total
FROM orders
WHERE account_id = 42;

y más tarde:

SELECT id, total
FROM orders
WHERE account_id = 917;

La consulta registrada puede representarse sustituyendo el literal por un parámetro:

SELECT id, total
FROM orders
WHERE account_id = $1;

Normalmente eso es exactamente lo que queremos.

La unidad interesante no es una petición individual para una cuenta concreta. Es el patrón SQL que la aplicación genera repetidamente.

Cómo habilitar pg_stat_statements#

pg_stat_statements es un módulo incluido con PostgreSQL, pero para recopilar sus estadísticas necesita configuración a nivel del servidor.

El módulo debe cargarse mediante shared_preload_libraries porque necesita memoria compartida:

shared_preload_libraries = 'pg_stat_statements'

Añade pg_stat_statements a la lista de bibliotecas existente sin eliminar las demás. Cambiar shared_preload_libraries requiere reiniciar PostgreSQL.

PostgreSQL también necesita identificadores de consulta. La configuración predeterminada:

compute_query_id = auto

permite que módulos como pg_stat_statements activen el cálculo de identificadores cuando sea necesario.

Después de reiniciar PostgreSQL, crea la extensión en la base de datos desde la que quieres consultar sus vistas:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Puedes comprobar que está instalada con:

SELECT extname
FROM pg_extension
WHERE extname = 'pg_stat_statements';

El recopilador de estadísticas funciona a nivel del servidor PostgreSQL, mientras que la extensión expone las vistas y funciones SQL en las bases de datos donde ha sido instalada.

En servicios PostgreSQL administrados quizá no puedas editar postgresql.conf directamente. El proveedor puede exponer shared_preload_libraries mediante un grupo de parámetros, un panel de configuración u otro mecanismo propio.

El requisito de PostgreSQL sigue siendo el mismo. Lo que cambia es el procedimiento operativo.

Entiende qué incluye pg_stat_statements.track#

Otra opción de configuración determina qué sentencias aparecen en las estadísticas.

Por defecto:

pg_stat_statements.track = top

Esto hace que PostgreSQL registre las sentencias de nivel superior enviadas directamente por los clientes.

El SQL ejecutado dentro de funciones normalmente no se registra como una sentencia anidada independiente con esta configuración.

Si necesitas registrar también esas consultas internas, puedes utilizar:

pg_stat_statements.track = all

Eso proporciona mayor granularidad, pero también cambia la cantidad de información recopilada.

Los comandos utility se controlan por separado mediante:

pg_stat_statements.track_utility

que está habilitado de forma predeterminada.

No cambies estas opciones simplemente para obtener más filas en la vista.

La configuración útil es aquella que representa el workload que realmente necesitas diagnosticar.

Empieza por el tiempo total de ejecución#

Una vez que las estadísticas hayan acumulado actividad representativa, una buena primera consulta es:

SELECT
    queryid,
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY total_exec_time DESC
LIMIT 20;

El filtro por dbid importa porque pg_stat_statements puede contener estadísticas correspondientes a varias bases de datos dentro del mismo servidor PostgreSQL.

Esta consulta plantea una pregunta mejor que:

¿Qué sentencia tuvo la ejecución individual más lenta?

La pregunta es:

¿Qué patrones de consultas han consumido más tiempo de ejecución en total?

La diferencia importa.

Una consulta puede tener un mean_exec_time elevado pero ejecutarse muy pocas veces.

Otra puede tardar poco individualmente, pero ejecutarse continuamente. Su coste acumulado puede ser mucho mayor.

Si ordenas únicamente por latencia promedio, puedes terminar optimizando una consulta obviamente lenta mientras ignoras otra que consume una parte mucho mayor de la capacidad total de la base de datos.

total_exec_time, mean_exec_time y calls responden preguntas distintas#

Estas tres columnas deben interpretarse juntas.

total_exec_time: ¿dónde se está consumiendo el tiempo de ejecución?#

Empieza con:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY total_exec_time DESC
LIMIT 20;

Que una sentencia aparezca arriba no significa necesariamente que esté mal escrita.

Puede simplemente realizar una operación importante con mucha frecuencia.

Esa información sigue siendo útil.

Optimizar también es una decisión económica. Si reducir el coste de un patrón SQL elimina una cantidad significativa de trabajo acumulado, esa consulta puede merecer investigación aunque cada ejecución individual no parezca lenta.

mean_exec_time: ¿qué consultas son caras en cada ejecución?#

Ahora cambia el orden:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY mean_exec_time DESC
LIMIT 20;

Esto muestra las sentencias con ejecuciones promedio más caras.

Pueden ser buenas candidatas para investigar latencia, pero el contexto importa.

Una consulta de reporting que se ejecuta una vez al día puede tener legítimamente un tiempo promedio muy superior al de una consulta utilizada miles de veces por minuto desde una API.

La pregunta útil no es simplemente:

¿El promedio es alto?

Es:

¿Este tiempo de ejecución es demasiado alto para lo que hace la consulta y para la forma en que la aplicación depende de ella?

calls: ¿qué patrones dominan por frecuencia?#

Una tercera perspectiva es:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY calls DESC
LIMIT 20;

Una consulta muy frecuente no es automáticamente un problema de rendimiento.

Pero la frecuencia cambia el valor potencial de una optimización.

Eliminar una pequeña cantidad de trabajo de una consulta que se ejecuta continuamente puede tener más impacto que reducir drásticamente el tiempo de una sentencia que casi nunca se ejecuta.

Por eso calls, mean_exec_time y total_exec_time no deben tratarse como rankings alternativos.

Describen dimensiones distintas del mismo workload.

Busca consultas inestables, no solo promedios altos#

Los promedios pueden ocultar información importante.

pg_stat_statements también expone:

min_exec_time
max_exec_time
stddev_exec_time

Por ejemplo:

SELECT
    query,
    calls,
    mean_exec_time,
    min_exec_time,
    max_exec_time,
    stddev_exec_time
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY stddev_exec_time DESC
LIMIT 20;

Una consulta con un promedio moderado pero una distribución muy amplia de tiempos puede merecer investigación.

Eso todavía no nos dice por qué varía.

Entre las posibles causas podrían estar diferencias en la selectividad de los parámetros, estado de caché, contención por locks, I/O temporal, carga concurrente o cambios en los planes de ejecución.

Son hipótesis.

pg_stat_statements identifica el patrón. No demuestra la causa raíz.

Usa las estadísticas de bloques para estimar cuánto trabajo genera una consulta#

El tiempo de ejecución nos indica dónde se acumuló tiempo.

Las estadísticas de bloques añaden otra perspectiva:

SELECT
    query,
    calls,
    total_exec_time,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_read,
    temp_blks_written
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY total_exec_time DESC
LIMIT 20;

shared_blks_hit cuenta accesos satisfechos desde los shared buffers de PostgreSQL.

shared_blks_read cuenta bloques compartidos que PostgreSQL tuvo que leer en lugar de encontrar ya disponibles como hits dentro de esos buffers.

No interpretes shared_blks_read como un contador directo de operaciones físicas contra el disco.

PostgreSQL puede solicitar un bloque que no está en shared buffers y que, aun así, sea servido por la caché del sistema operativo.

La distinción útil es:

shared_blks_hit
→ PostgreSQL encontró la página en shared buffers

shared_blks_read
→ PostgreSQL tuvo que realizar una lectura para obtener la página

Ninguna de las dos métricas demuestra por sí sola que una consulta sea buena o mala.

Una sentencia puede generar millones de hits en shared buffers y seguir consumiendo una cantidad importante de CPU procesando esas páginas.

Del mismo modo, muchas lecturas no significan automáticamente que añadir un índice sea la solución correcta.

Estos contadores describen el workload.

El plan de ejecución será el que explique después cómo PostgreSQL produjo ese trabajo.

Los bloques temporales pueden revelar trabajo adicional#

Los campos:

temp_blks_read
temp_blks_written

pueden ser útiles cuando una operación necesita archivos temporales.

Sorts, hashes u otras operaciones que no pueden mantenerse dentro de la memoria de trabajo disponible pueden generar I/O temporal.

De nuevo, la presencia de bloques temporales es evidencia, no un diagnóstico completo.

Nos dice que se utilizó almacenamiento temporal.

Para saber qué nodo del plan lo provocó y por qué, todavía necesitamos analizar el plan de ejecución.

El timing de I/O requiere track_io_timing#

pg_stat_statements también puede exponer tiempo dedicado a leer y escribir bloques.

Sin embargo, estos campos dependen de:

track_io_timing = on

Cuando el timing de I/O está deshabilitado, no se acumulan nuevos tiempos de I/O; los valores acumulados previamente pueden permanecer.

Medir I/O puede introducir cierto overhead dependiendo de cómo implemente el sistema operativo las operaciones de medición de tiempo, por lo que conviene habilitarlo deliberadamente en lugar de asumir que siempre es gratuito.

Revisa la ventana de medición antes de confiar en el ranking#

Las estadísticas acumuladas sin contexto temporal pueden interpretarse mal con facilidad.

Antes de tomar decisiones basadas en las consultas que aparecen arriba, revisa:

SELECT *
FROM pg_stat_statements_info;

Uno de los campos útiles es:

stats_reset

que indica cuándo se reiniciaron por última vez las estadísticas generales del módulo.

La vista también expone:

dealloc

que permite saber cuántas veces fue necesario descartar estadísticas de sentencias menos ejecutadas porque el módulo superó su capacidad configurada.

PostgreSQL 17 y versiones posteriores también exponen:

stats_since

para las entradas individuales de pg_stat_statements.

Esto importa porque total_exec_time es acumulativo.

Una consulta que lleva días acumulando estadísticas no debería compararse sin contexto con una sentencia que apareció hace cinco minutos.

Para análisis recurrentes suele ser más fácil trabajar con snapshots tomados durante ventanas definidas que reiniciar continuamente las estadísticas.

PostgreSQL permite hacerlo:

SELECT pg_stat_statements_reset();

pero el reset destruye la información acumulada.

Úsalo deliberadamente.

Vigila la expulsión de sentencias#

pg_stat_statements no puede conservar una cantidad ilimitada de entradas distintas.

La configuración:

pg_stat_statements.max

controla cuántas sentencias pueden mantenerse.

Si PostgreSQL observa más sentencias distintas de las que permite la capacidad configurada, las estadísticas correspondientes a las menos ejecutadas pueden descartarse.

Puedes comprobarlo con:

SELECT
    dealloc,
    stats_reset
FROM pg_stat_statements_info;

Si dealloc aumenta continuamente durante el periodo que estás analizando, algunas estadísticas de consultas menos frecuentes están siendo eliminadas.

Eso no significa automáticamente que debas aumentar pg_stat_statements.max.

Una capacidad mayor también consume más memoria compartida.

La pregunta útil es si la configuración actual conserva suficiente información del workload para realizar el análisis que necesitas.

pg_stat_statements no te dice por qué una consulta es lenta#

Este límite es precisamente lo que mantiene útil a la herramienta.

Puede decirnos:

esta consulta se ejecuta con mucha frecuencia

esta consulta consume mucho tiempo total de ejecución

esta consulta tiene un promedio elevado

esta consulta genera una cantidad importante de actividad sobre bloques

esta consulta presenta tiempos de ejecución inestables

Pero no puede decirnos por sí sola:

falta este índice

este Seq Scan es incorrecto

el planner subestimó estas filas

este orden de joins provocó el problema

este sort utilizó disco porque work_mem fue insuficiente

este predicado tiene poca selectividad

Esas son preguntas sobre el plan de ejecución.

Una vez identificada una consulta candidata, el proceso cambia:

pg_stat_statements
        ↓
identificar un patrón SQL costoso
        ↓
EXPLAIN (ANALYZE, BUFFERS)
        ↓
entender el trabajo que realiza PostgreSQL
        ↓
cambiar SQL, índices, estadísticas o arquitectura
        ↓
medir de nuevo

Para esa siguiente etapa, continúa con la guía práctica de optimización de consultas PostgreSQL, donde se analizan planes de ejecución, estimaciones de filas, índices, estadísticas del planner y verificación de cambios.

pg_stat_statements debe reducir el espacio de búsqueda.

No debe sustituir el análisis del plan de ejecución.

No habilites estadísticas de planificación sin una razón#

pg_stat_statements puede recopilar estadísticas del tiempo de planificación además del tiempo de ejecución.

Ese comportamiento se controla mediante:

pg_stat_statements.track_planning

Está deshabilitado por defecto.

Habilitar el seguimiento de planificación puede introducir overhead adicional, especialmente en workloads con muchas sesiones concurrentes que actualizan repetidamente estadísticas de planificación para estructuras de consulta equivalentes.

No lo habilites simplemente porque disponer de más métricas suena mejor.

Primero determina si el tiempo de planificación forma parte del problema que estás investigando.

Para muchas investigaciones iniciales de consultas lentas, las estadísticas de ejecución ya son suficientes para decidir qué patrones SQL merecen un análisis más profundo.

Los permisos importan#

Las estadísticas de consultas de producción son datos operativos.

El acceso debería concederse deliberadamente.

Los superusuarios y los roles con pg_read_all_stats pueden ver el texto y los identificadores de consultas correspondientes a sentencias ejecutadas por otros usuarios.

Los usuarios con menos privilegios no reciben automáticamente la misma visibilidad sobre el SQL ejecutado por otros roles.

Conviene tenerlo en cuenta antes de exponer pg_stat_statements mediante dashboards internos o conceder permisos amplios únicamente para facilitar tareas de diagnóstico.

Un proceso práctico para encontrar consultas lentas en PostgreSQL#

Cuando la base de datos parece lenta pero todavía no sabemos qué SQL es responsable, conviene seguir un proceso repetible.

1. Recopila un workload representativo#

No empieces a optimizar inmediatamente después de habilitar pg_stat_statements.

Deja que observe suficiente actividad para representar la carga que realmente te interesa.

Puede ser un periodo de tráfico alto en producción, una prueba de carga controlada u otra ventana de medición claramente definida.

2. Ordena las consultas por tiempo total de ejecución#

Empieza con:

SELECT
    queryid,
    query,
    calls,
    total_exec_time,
    mean_exec_time
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY total_exec_time DESC
LIMIT 20;

Esto identifica los patrones de consultas que han consumido la mayor cantidad de tiempo de ejecución acumulado.

3. Compara frecuencia y latencia promedio#

Observa:

calls
mean_exec_time
total_exec_time

Separa:

costosa porque cada ejecución tarda mucho

de:

costosa porque se ejecuta constantemente

Son problemas de optimización diferentes.

4. Busca variabilidad#

Revisa:

min_exec_time
max_exec_time
stddev_exec_time

Una consulta estable y otra impredecible pueden necesitar investigaciones diferentes incluso si comparten un tiempo promedio similar.

5. Inspecciona bloques y actividad temporal#

Utiliza:

shared_blks_hit
shared_blks_read
temp_blks_read
temp_blks_written

para comprender cuánto trabajo está generando el patrón de consulta.

No conviertas esos contadores en el diagnóstico final.

6. Verifica la ventana de medición#

Revisa:

stats_reset
stats_since
dealloc

cuando estén disponibles en tu versión de PostgreSQL.

Asegúrate de saber qué periodo representan las estadísticas y si algunas entradas han tenido que ser descartadas.

7. Elige una candidata antes de cambiar nada#

En este punto deberías ser capaz de formular algo concreto:

Este patrón de consulta merece investigación porque acumula una cantidad significativa de tiempo de ejecución y se ejecuta con mucha frecuencia.

Eso ofrece una base mucho mejor para optimizar que:

Este SQL parece ineficiente.

8. Pasa al plan de ejecución#

Ahora inspecciona una ejecución representativa con EXPLAIN o con EXPLAIN (ANALYZE, BUFFERS) cuando ejecutar la consulta sea seguro.

La pregunta cambia de:

¿Qué consulta debería investigar?

a:

¿Por qué necesita PostgreSQL realizar esta cantidad de trabajo para ejecutarla?

Ahí empieza realmente la optimización de la consulta.

Conclusión#

Encontrar consultas lentas en PostgreSQL no consiste simplemente en ordenar sentencias SQL por su tiempo promedio de ejecución.

El objetivo útil es identificar el workload cuyo coste para la base de datos sea suficientemente relevante como para actuar.

pg_stat_statements permite observar ese coste desde diferentes perspectivas: frecuencia, tiempo acumulado de ejecución, latencia promedio, variabilidad, número de filas y actividad sobre bloques.

Ninguna de esas métricas debería interpretarse por separado.

Empieza con total_exec_time. Compáralo con calls y mean_exec_time. Revisa la ventana de medición. Utiliza la variabilidad y la actividad sobre bloques cuando ayuden a describir mejor el workload.

Después detente.

No añadas un índice porque una consulta aparezca en la parte superior de la lista. No la reescribas únicamente porque su tiempo promedio sea alto.

pg_stat_statements ha identificado dónde investigar.

Todavía no ha explicado la causa.

El proceso fiable es:

medir el workload
        ↓
identificar el patrón SQL costoso
        ↓
inspeccionar su plan de ejecución
        ↓
entender el trabajo
        ↓
cambiar una cosa
        ↓
medir de nuevo

La consulta más útil para optimizar no siempre es la que tiene la ejecución individual más lenta.

Es aquella cuyo coste importa lo suficiente como para investigarlo.

Referencia#

Documentación de PostgreSQL 17: pg_stat_statements. Consulta la documentación de tu versión del servidor al comparar las columnas disponibles.