El optimizador de PostgreSQL se equivoca: tu base de datos no cuenta, adivina
Ordenar los joins de una consulta con 8 tablas tiene más de 86 billones de combinaciones posibles. Postgres no las prueba todas: estima. Cuándo se equivoca, y cómo corregirlo con pg_hint_plan.
El síntoma: la misma consulta, sin tocar código, empieza a ir lenta
Hace algún tiempo analizamos el rendimiento de una base de datos de un marketplace de segunda mano. Una consulta que llevaba meses funcionando bien empezó a tardar varias veces más, sin que nadie hubiera tocado nada: ni código, ni configuración, ni esquema. Al mirar el plan de ejecución, el problema era el orden en el que Postgres estaba uniendo las tablas (artículos, vendedores y la tabla intermedia que conecta cada artículo con sus categorías), y ese orden estaba generando muchas más filas de las necesarias antes de llegar al resultado final.
Lo que había cambiado no era el código. Era la distribución de los datos: el plan que Postgres eligió hace tiempo, basado en estadísticas antiguas o en una suposición que ya no se cumplía, dejó de ser la mejor opción.
Por qué elegir el orden de los joins es tan difícil
Imagina una consulta que cruza tres tablas: películas, una tabla intermedia que conecta cada película con su productora, y productoras. Lo que queremos saber: ¿qué productoras japonesas sacaron más títulos en los 2000?
Según qué tabla se une primero, Postgres puede acabar procesando 4 veces más filas de las necesarias antes de llegar al resultado final: mismo resultado, mismo dato, pero un camino mucho más caro para llegar hasta él.
Con 3 tablas, el número de formas distintas de ejecutar esa consulta (orden de join, algoritmo de cada join, tipo de escaneo de cada tabla) ya supera las 4.600. Con 8 tablas (nada raro en un sistema real con varias relaciones) esa cifra supera los 86 billones. Ordenar joins de forma óptima es, formalmente, un problema NP-hard: no existe un atajo matemático que garantice la respuesta perfecta en tiempo razonable.
Postgres no prueba las 86 billones de opciones. Usa programación dinámica para podar el espacio de búsqueda y quedarse con un puñado de candidatos razonables.
Por qué Postgres a veces adivina mal
Para elegir entre esos candidatos, Postgres necesitaría saber cuántas filas producirá cada join antes de ejecutarlo. No puede contarlas de verdad sin ejecutar la consulta. Eso anularía el propio propósito de un optimizador rápido. Así que estima, usando las estadísticas guardadas en pg_statistic.
El problema aparece en los joins: Postgres no sabe cómo se distribuyen los valores de una tabla respecto a los de otra, así que asume una distribución uniforme. Si el 5% de tus productoras son japonesas, asume que ese 5% genera aproximadamente el 5% de las películas.
Cuando esa suposición se cumple, la estimación es razonable. Cuando no (por ejemplo, si ese 5% de productoras japonesas en realidad produjo el 50% de las películas del catálogo), la estimación falla, y falla en cascada: un error de estimación temprano en el árbol de joins contamina todas las decisiones posteriores.
La herramienta para corregirlo: pg_hint_plan
pg_hint_plan es una extensión real y activa (mantenida por NTT/ossc-db) que permite forzar decisiones concretas al planificador mediante comentarios estructurados, sin tocar el SQL de la consulta en sí:
/*+ HashJoin(a b) SeqScan(a) */
SELECT *
FROM pgbench_branches b
JOIN pgbench_accounts a ON b.bid = a.bid
ORDER BY a.aid;
Este hint le dice a Postgres exactamente qué algoritmo de join usar (HashJoin) y cómo escanear la tabla a (SeqScan), sin cambiar una sola línea de la consulta original. Postgres sigue siendo el motor que ejecuta. Solo se le da la indicación correcta cuando su propia estimación se equivoca.
El límite real de esta técnica
Un hint no es "configúralo y olvídate". Cuando Postgres mejora su optimizador en una versión mayor, un hint que forzaba la solución correcta en la versión antigua puede quedarse obsoleto, o directamente estorbar si el nuevo optimizador ya elige mejor por su cuenta. Cada hint aplicado en producción necesita revisión cuando se actualiza la versión de PostgreSQL, no se puede dejar puesto para siempre sin revisarlo.
Un apunte sobre hacia dónde va esto
El problema es lo bastante interesante como para que ya haya quien entrena modelos pequeños de IA específicamente para encontrar mejores planes que el propio Postgres, usando aprendizaje por refuerzo sobre las mismas consultas que una empresa ejecuta miles de veces.
Rohan Bansal documentó un experimento así hace poco, con un modelo de 4B de parámetros llegando a planes un 81% más rápidos que el optimizador por defecto en consultas complejas de varios joins. No es una solución lista para producción todavía, pero confirma algo que este post ya viene diciendo: el optimizador de Postgres tiene un techo real, y hay margen real por encima de él.
El coste real, en números
Un plan subóptimo en una consulta puntual, ejecutada una vez al día, apenas importa. El cálculo cambia por completo cuando esa consulta es la que alimenta un endpoint de listado, un dashboard interno o un job de reporting que corre miles de veces al día.
A modo ilustrativo: una consulta que tarda 400ms con el plan que Postgres eligió, frente a 100ms con el plan óptimo (una diferencia de 4x, del mismo orden que la del ejemplo de las productoras japonesas), ejecutada 10.000 veces al día, supone unos 3.000 segundos extra de cómputo de base de datos cada día. Casi una hora de CPU de base de datos gastada en algo que un hint de una línea corrige. En una base de datos gestionada facturada por cómputo, eso es coste directo y mensurable, no una molestia de rendimiento abstracta.
Criterio de decisión
¿Tienes una consulta que se ejecuta miles de veces (no una consulta puntual) y EXPLAIN ANALYZE muestra que las filas estimadas y las filas reales difieren en varios órdenes de magnitud? Ahí es donde un hint bien dirigido tiene sentido: no como parche generalizado, sino como corrección puntual de una estimación que ya demostraste que está mal.
Referencias
- Leis et al. — How Good Are Query Optimizers, Really? (VLDB 2015) (el paper que estableció que los optimizadores de consultas siguen sin ser tan buenos como se asume).
- pg_hint_plan — documentación oficial (extensión mantenida por NTT/ossc-db para forzar planes de ejecución en PostgreSQL).
- Rohan Bansal — Training a 4B model to produce 81% faster query plans than Postgres (el experimento de RL que inspiró este post; mucho más detallado, recomendado si el tema engancha).