Tuning basado en EXPLAIN

1. ¿Qué es EXPLAIN?

PostgreSQL permite analizar el plan de una consulta mediante:

EXPLAIN

y ejecutar la consulta mostrando información real mediante:

EXPLAIN ANALYZE

El plan permite conocer:

• cómo accede PostgreSQL a las tablas;
• qué índices utiliza;
• cómo relaciona las tablas;
• cuántas filas estima;
• cuántas filas procesa realmente;
• cuánto tiempo consume cada operación.

2. ¿Qué buscamos?

Al analizar un plan buscamos principalmente:

• Seq Scan sobre grandes volúmenes;
• índices que no se utilizan;
• Nested Loop con demasiadas iteraciones;
• Sort costosos;
• Hash Join con grandes volúmenes;
• diferencias entre filas estimadas y reales.

La pregunta principal es:

¿PostgreSQL está procesando más información de la necesaria?

3. Acceso a datos

Entre los principales métodos encontramos:

• Seq Scan;
• Index Scan;
• Index Only Scan;
• Bitmap Index Scan;
• Bitmap Heap Scan.

Un Seq Scan puede indicar una oportunidad para utilizar un índice, pero también puede ser perfectamente correcto cuando necesitamos una gran cantidad de registros.

4. JOINs

PostgreSQL utiliza principalmente:

• Nested Loop;
• Hash Join;
• Merge Join.

El Optimizer decide cuál utilizar basándose en estadísticas, cardinalidad y costos estimados.

5. EXPLAIN ANALYZE

Una de las características más importantes es poder comparar:

Estimated Rows
vs.
Actual Rows

Por ejemplo:

rows=100
actual rows=2.000.000

Una diferencia de este tipo puede explicar por qué PostgreSQL seleccionó una estrategia poco eficiente.

También podemos analizar:

• actual time;
• loops;
• buffers.

6. Índices y estadísticas

Si encontramos un Seq Scan costoso, podemos analizar:

• si existe un índice adecuado;
• si la consulta permite utilizarlo;
• si las estadísticas están actualizadas;
• si el índice realmente sería beneficioso.

Por ejemplo:

ANALYZE clientes;

puede actualizar la información estadística utilizada por el Planner.

Después podemos volver a ejecutar:

EXPLAIN ANALYZE

y comparar el resultado.

7. Control del Planner

PostgreSQL no dispone nativamente de un mecanismo equivalente a SQL Server Forced Plans o Oracle SQL Plan Baselines para fijar un plan completo.

Sin embargo, permite influir en determinadas decisiones del Planner.

Por ejemplo, existen controles relacionados con:

• Nested Loop;
• Hash Join;
• Merge Join;
• otras estrategias del Planner.

Esto permite orientar el comportamiento del Optimizer en situaciones específicas.

8. Generic Plans y Custom Plans

PostgreSQL también permite controlar cómo se utilizan los planes asociados a prepared statements.

En determinados escenarios puede utilizar:

• Generic Plan: un plan reutilizable;
• Custom Plan: un plan generado teniendo en cuenta los valores concretos de los parámetros.

Esta distinción puede ser importante cuando una misma consulta presenta comportamientos muy diferentes dependiendo de los valores utilizados.

9. ¿Cómo optimizamos?

Dependiendo del problema podemos:

• crear o modificar índices;
• actualizar estadísticas;
• modificar la consulta;
• reducir el volumen de datos procesado;
• revisar la estrategia de JOIN;
• influir en determinadas decisiones del Planner;
• analizar Generic vs. Custom Plans;
• ajustar parámetros del Planner cuando corresponda.

Después se vuelve a ejecutar EXPLAIN ANALYZE y se comparan los resultados.

10. Objetivo

El objetivo es conseguir que PostgreSQL:

lea menos → procese menos → utilice menos recursos → responda más rápido.

EXPLAIN ANALYZE permite comprobar no solamente qué plan eligió PostgreSQL, sino también cómo se comportó realmente durante la ejecución.