Uso de EXPLAIN ANALYZE para medir el tiempo real de ejecución de consultas

Si el comando EXPLAIN te deja mirar en la bola de cristal y ver cómo PostgreSQL “planea” ejecutar la consulta, EXPLAIN ANALYZE te convierte en un auténtico detective que averigua qué pasó realmente.

Diferencias clave entre EXPLAIN y EXPLAIN ANALYZE:

EXPLAIN – es la teoría, te muestra cómo PostgreSQL planea ejecutar la consulta. Ves valores estimados como el número de filas (rows) y el coste de ejecución (cost).

EXPLAIN ANALYZE – es la práctica. PostgreSQL ejecuta de verdad la consulta y te muestra:

• El número real de filas procesadas en cada etapa.
• El tiempo real de ejecución de cada operación.
• Comparación con las estimaciones del plan (rows y cost).

Por ejemplo: si tu consulta se supone que procesa 100 filas, pero en realidad procesa 10 000, EXPLAIN ANALYZE te va a sacar ese dato feo al instante.

Sintaxis básica y uso

Igual que EXPLAIN, EXPLAIN ANALYZE es súper fácil de usar. Solo tienes que añadir la palabra ANALYZE a tu comando EXPLAIN.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Esto es lo que hace PostgreSQL:

• Ejecuta la consulta.
• Registra cada operación en el plan de ejecución, incluyendo los datos reales.
• Devuelve una descripción completa del proceso de ejecución de la consulta.

¿Qué datos te da EXPLAIN ANALYZE?

Tiempo real de ejecución de las operaciones:

• Actual Start Time: cuándo empezó la operación.
• Actual End Time: cuándo terminó la operación.

Número total de filas procesadas:

Esto te ayuda a ver si las estimaciones del plan (rows) eran acertadas.

Información sobre buffers:

Cómo se usaron los buffers de disco y de memoria.

Ejemplo de uso de EXPLAIN ANALYZE

Vamos a ver un ejemplo concreto. Tenemos una tabla students con datos de estudiantes:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    grade FLOAT
);

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

Ejecutamos una consulta para sacar los estudiantes mayores de 20 años:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Ejemplo de resultado:

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

Vamos a desglosar el resultado:

• Seq Scan – indica que PostgreSQL está haciendo un escaneo secuencial de la tabla.
• cost=0.00..14.00 – es el coste estimado de la operación.
• rows=2 – PostgreSQL espera que la consulta devuelva 2 filas (¡y acierta!).
• actual time=0.025..0.026 – tiempo real de ejecución de la operación (en milisegundos).
• Rows Removed by Filter: 2 – dos filas fueron filtradas porque no cumplían la condición WHERE.

Comparando teoría y práctica

Aquí está la magia de EXPLAIN ANALYZE: te muestra cómo se ejecutó realmente la consulta y te deja comparar eso con el plan teórico de ejecución.

Vamos a ver un ejemplo un poco más complicado.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

Ejemplo de resultado:

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

¿Qué vemos aquí?

1. PostgreSQL ejecutó la consulta en 0.057 milisegundos.
2. Solo una fila (rows=1) cumple las condiciones del WHERE.
3. Tres filas fueron filtradas (Rows Removed by Filter: 3).

Resumen

Usar EXPLAIN ANALYZE te ayuda a encontrar cuellos de botella y entender cómo optimizar tus consultas. Por ejemplo:

• Si el Seq Scan es demasiado "pesado", igual es hora de añadir un índice.
• Si las estimaciones de PostgreSQL son muy diferentes de los datos reales, revisa las estadísticas de la tabla (ANALYZE) o la estructura de los índices.