Análisis comparativo de EXPLAIN ANALYZE y pg_stat_statements

En este punto puede que te surja una pregunta lógica: ¿para qué necesitamos dos herramientas diferentes para analizar? ¿Cuál se usa más: EXPLAIN ANALYZE o pg_stat_statements? Vamos a ver en qué consisten estos dos enfoques, sus puntos fuertes y débiles, y cuándo y dónde se usa cada uno.

Problemas que resuelven las herramientas

EXPLAIN ANALYZE: es la herramienta para analizar a fondo una consulta concreta. Si tienes curiosidad por saber cómo PostgreSQL ejecuta una consulta, qué nodos usa, cuántas filas procesa y cuánto tarda cada operación, esta es tu opción. Te ayuda a responder a la pregunta: "¿Por qué mi consulta concreta va lenta?"

pg_stat_statements: es una herramienta para monitorizar a un nivel más global, mostrando información sobre el rendimiento de todas las consultas que se ejecutan en la base de datos. Es tu opción si buscas una visión general del rendimiento: "¿Cuáles son las consultas más lentas en mi base de datos?" o "¿Qué consultas generan más carga en el servidor?"

Cuándo usar EXPLAIN ANALYZE

EXPLAIN ANALYZE es tu herramienta de depuración para entender cómo PostgreSQL ejecuta una consulta concreta. Úsala en estos escenarios:

Optimización puntual de una consulta Si alguien se queja de que una página de tu app tarda una eternidad en cargar, lo primero que harás será encontrar la consulta responsable y aplicar EXPLAIN ANALYZE. Esto te mostrará el plan de ejecución y métricas reales como el tiempo de ejecución y el número de filas procesadas.

Elegir el índice adecuado Cuando creas un índice nuevo o cambias uno existente, usa EXPLAIN ANALYZE para ver si PostgreSQL lo está usando. Si no, igual has creado un índice que no ayuda a optimizar las consultas.

Depuración de consultas complejas Si escribes una consulta compleja con muchos JOIN o WHERE, analizar el plan de ejecución real con EXPLAIN ANALYZE te ayudará a detectar cuellos de botella, como escaneos secuenciales innecesarios (hola, Seq Scan).

Ejemplo: Optimización de una consulta con EXPLAIN ANALYZE

-- Consulta que va lenta
SELECT *
FROM students
WHERE name = 'Alice';

-- Analizamos el plan de ejecución
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

Si ves que se usa Seq Scan, puede que se te haya olvidado crear un índice:

-- Creamos un índice en la columna name
CREATE INDEX idx_students_name ON students(name);

-- Comprobamos otra vez
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

Cuándo usar pg_stat_statements

Esta herramienta es imprescindible para analizar el rendimiento de todo el sistema. Úsala en estos casos:

Monitorización en producción pg_stat_statements muestra estadísticas de ejecución de consultas durante un periodo de tiempo. Puedes encontrar fácilmente las consultas más lentas gracias a la columna total_time, que muestra el tiempo total de ejecución de cada consulta.

Buscar consultas "pesadas" ¿Quieres saber qué consultas suelen cargar más tu base de datos? Ordena las consultas por el número de lecturas de memoria (shared_blks_hit) o por el número de filas procesadas (rows).

Detectar consultas con alta frecuencia de ejecución A veces no solo una consulta lenta puede causar problemas, sino también las que se ejecutan muy a menudo. Por ejemplo, si una consulta se ejecuta 100 veces por minuto, incluso una pequeña optimización puede reducir mucho la carga en el servidor.

Ejemplo: Buscar consultas lentas con pg_stat_statements

-- Ver estadísticas de consultas
SELECT query,
       calls,
       total_time,
       rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Esta consulta te mostrará el top 5 de consultas que más tiempo consumen.

Comparación de enfoques: ¿en qué se diferencian?

Usar ambas herramientas juntas

Como todo en programación, no hay un botón mágico que lo solucione todo. El mejor enfoque es usar ambas herramientas en conjunto. Por ejemplo:

1. Usa pg_stat_statements para identificar las consultas más lentas o más frecuentes en tu sistema.

2. Luego estudia esas consultas con EXPLAIN ANALYZE para entender por qué van mal.

Ejemplo práctico: enfoque integral

-- Paso 1: Encontrar la consulta más lenta
SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

-- Paso 2: Analizar esa consulta
EXPLAIN ANALYZE
<copiar la consulta del paso anterior>;

Errores típicos al usar estas herramientas

Al trabajar con EXPLAIN ANALYZE y pg_stat_statements hay algunos errores que suelen cometer los principiantes:

1. Olvidar la relevancia de los datos. Si analizas una consulta en una tabla vacía, el resultado de EXPLAIN ANALYZE puede ser engañoso. Asegúrate de que tu base de pruebas refleja los volúmenes de datos reales.

2. Ignorar el consumo de recursos de la monitorización. Si tienes la extensión pg_stat_statements activada en producción, asegúrate de que está bien configurada y no genera una carga excesiva.

3. Leer el plan teórico en vez del real. Recuerda que un simple EXPLAIN solo da el plan teórico de la consulta. Usa EXPLAIN ANALYZE para obtener datos reales.

Ahora ya tienes todo lo necesario para no solo luchar contra las consultas lentas, sino también prevenir que aparezcan. PostgreSQL te da herramientas potentes, y saber combinarlas bien te permite conseguir el mejor rendimiento incluso en sistemas con mucha carga.

1

Cuestionario/control

Optimización de consultas, nivel 42, lección 4

No disponible

Optimización de consultas

Optimización de consultas

Abrir