Respuesta.

Historia de la pregunta:
Desde el inicio, las consultas SQL fueron diseñadas como declarativas: "qué obtener", y no "cómo obtenerlo". El optimizador de la base de datos construye un plan de ejecución (execution plan) que define el orden de las uniones, filtraciones, escaneos y uso de índices.

Problema:
Sin entender el plan de ejecución, no se puede explicar por qué una consulta, aparentemente simple, se ejecuta demasiado lento, mientras que una compleja funciona rápido. Un plan incorrecto puede bloquear el servidor durante horas debido a operaciones innecesarias o un uso incorrecto de índices.

Solución:
Las herramientas de análisis incluyen instrucciones como EXPLAIN, EXPLAIN ANALYZE (PostgreSQL), SHOW EXPLAIN (MySQL), Management Studio Execution Plan (SQL Server). Con ellas, se puede ver paso a paso cómo se ejecutó exactamente la consulta, qué volumen de datos se leyó, qué índice se utilizó, dónde ocurrieron escaneos completos de tablas (table scan) y dónde se produjeron retrasos.

Ejemplo de código:

EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'shipped';

Características clave:

El plan dará un esquema detallado de qué operación realizó la base de datos en qué orden.
Se puede ver dónde se utilizó un index scan y dónde un table scan.
Permite identificar cuellos de botella para optimizar y verificar la hipótesis sobre el impacto de reescribir la consulta en el rendimiento.

Preguntas trampa.

¿Si se agrega un índice, siempre se acelerará la consulta?

¡No! El índice solo ayudará si la filtración por su campo limita significativamente el número de filas devueltas. Si la mayoría de los registros cumplen la condición, el optimizador puede ignorar el índice.

Ejemplo:

-- El campo 'gender' solo tiene dos valores, el índice no ayudará
CREATE INDEX idx_gender ON people(gender);
SELECT * FROM people WHERE gender = 'M';

¿Depende el orden de las tablas en el JOIN del resultado de la ejecución?

No, los datos finales serán los mismos, sin embargo, el optimizador puede cambiar el orden de las uniones para mejorar el rendimiento. Pero si se escribe un JOIN específico o se utilizan sugerencias como "JOIN HINT", el orden puede afectar la eficiencia de la ejecución.

¿Por qué analizar "Estimated rows" y "Actual rows" en el plan de ejecución?

La diferencia entre ellos puede indicar que las estadísticas de las tablas están desactualizadas o no reflejan la realidad, y que el plan elegido no es óptimo; es necesario actualizar las estadísticas o revisar la estructura de la consulta.

-- PostgreSQL
ANALYZE table_name; -- actualizar estadísticas

Errores típicos y anti-patrones

No se revisa el execution plan al surgir problemas: se intenta optimizar "a ciegas"
Adición de índices innecesarios/no dirigidos para "acelerar", lo que solo ralentiza los cambios de datos
No se actualizan las estadísticas de las tablas
Las consultas se diseñan sin tener en cuenta cómo el optimizador las ejecutará

Ejemplo de la vida real

Caso negativo

En un proyecto, los analistas se quejaron durante mucho tiempo de la "congelación" de los informes. Una consulta con cinco JOINs tardaba 25 minutos. Resultó que se eligió un plan con un escaneo completo de una tabla enorme en lugar de un index scan, los índices no estaban en los campos correctos, y las estadísticas no se habían actualizado en un año.

Ventajas:

Soporte simple, minimización de índices

Desventajas:

La consulta funcionaba lentamente, se gastaban horas de trabajo humano
Descontento de los usuarios
Carga en el servidor

Caso positivo

Se realizó un análisis del execution plan, se agregó un índice al campo que realmente filtraba, y se actualizaron las estadísticas. El tiempo de la consulta se redujo a 20 segundos. La carga en el servidor se redujo en un orden de magnitud.