PostgreSQL
Query planner y executor
Funcionamiento del planner y executor: estimaciones, costos, paths, joins, scans y decisiones que convierten SQL declarativo en un plan físico.
- Última actualización
- Actualizada
- Nivel
- Profundización
PostgreSQL
Funcionamiento del planner y executor: estimaciones, costos, paths, joins, scans y decisiones que convierten SQL declarativo en un plan físico.
El planner no busca el plan perfecto: compara alternativas posibles con información incompleta y elige la de menor coste estimado. El executor convierte ese árbol en lecturas, joins, sorts y resultados reales.
SQL declara qué resultado necesitas. PostgreSQL debe decidir:
Pipeline conceptual:
SQL
→ parser construye árbol
→ rewrite expande views/rules
→ planner genera paths
→ estima filas y costes
→ elige plan
→ executor produce tuplesEl plan depende de esquema, estadísticas, parámetros, configuración y versión. Dos bases con el mismo SQL pueden elegir planes distintos.
Una consulta puede ejecutarse de muchas maneras. Para unir tres tablas, el orden y algoritmo cambian radicalmente el trabajo.
Ejemplo:
orders: 100 millones
customers: 2 millones
branch filtrada: 500 ordersUn buen plan filtra orders temprano y busca customers. Un mal plan podría combinar conjuntos enormes antes de filtrar.
El planner explora alternativas sin ejecutarlas completamente; necesita estimaciones.
Un path es una alternativa candidata con propiedades:
Después de comparar paths, PostgreSQL convierte el mejor en un plan ejecutable.
No se conservan todas las alternativas en EXPLAIN; solo ves el plan elegido.
Ejemplo:
cost=0.42..185.77 rows=20 width=640.42: startup cost.185.77: total cost si consume todas las filas.rows=20: cardinalidad estimada.width=64: bytes promedio estimados por fila.Las unidades de coste son abstractas. Se basan en parámetros como:
seq_page_cost.random_page_cost.cpu_tuple_cost.cpu_operator_cost.parallel_setup_cost.No equivalen a milisegundos y no deben compararse entre servidores como benchmark directo.
Para:
ORDER BY created_at DESC LIMIT 20puede ganar un plan con startup bajo aunque su coste total para todas las filas sea mayor, porque LIMIT termina temprano.
Para una aggregate que necesita consumir toda la entrada, el total cost pesa más.
El planner considera esa demanda mediante la estructura de la query.
Si PostgreSQL estima 10 filas pero obtiene 1 000 000:
Muchos problemas de planes comienzan con rows incorrecto, no con el algoritmo final.
Lee páginas de la relación y evalúa filtros.
Correcto cuando:
No es sinónimo de problema.
Recorre el índice y visita heap por candidatos.
Bueno para pocos resultados y acceso ordenado. Puede ser caro con muchos saltos aleatorios.
Intenta obtener columnas desde índice. Consulta heap cuando visibility map no confirma all-visible.
Construye bitmap de ubicaciones y visita heap agrupando páginas.
Bueno cuando hay más resultados que un index scan puntual, pero menos que un sequential scan, o cuando combina varios índices.
Accede por tuple identifier en casos específicos. No debe convertirse en contrato estable de aplicación.
Consume filas de funciones, VALUES o materializaciones.
En un plan:
Index Cond: (branch_id = 10)
Filter: (status = 'pending')
Rows Removed by Filter: 50000Index Cond limita desde la estructura. Filter descarta después.
Un alto número eliminado puede sugerir:
No implica automáticamente crear otro índice; considera frecuencia y write cost.
Para inner joins, el planner puede reordenar relaciones dentro de límites semánticos y de búsqueda.
El número de combinaciones crece rápidamente. Parámetros como join_collapse_limit y GEQO controlan cuánto explora para muchas tablas.
Outer joins, LATERAL y dependencias restringen reordenamiento porque cambian semántica.
outer rows
↓ por cada fila
inner pathExcelente cuando:
Desastroso cuando outer real es enorme y la estimación era pequeña.
En EXPLAIN, revisa loops del inner. Un nodo de 1 ms con 100 000 loops cuesta mucho.
build input
→ hash table
probe input
→ buscar coincidenciasUsado principalmente para igualdad.
Ventajas:
Costes:
El planner suele elegir la entrada menor como build side, sujeto al plan.
Requiere entradas ordenadas por keys compatibles.
entrada A ordenada
entrada B ordenada
→ avanzar simultáneamenteBueno para grandes conjuntos, igualdad y algunos rangos.
El orden puede venir de índices o Sort. Si necesita dos sorts costosos, hash puede ganar.
Versiones modernas pueden insertar Memoize sobre un inner parametrizado:
Nested Loop
→ claves repetidas externas
→ cachear resultado inner por claveAyuda cuando el mismo valor aparece muchas veces. Consume memoria y depende de estimaciones de repetición.
Un nodo Sort recibe filas y produce orden.
Métodos observables:
El planner estima tamaño con rows × width. Misestimaciones provocan spills inesperados.
Hash por group key. Puede dividirse en batches si excede memoria.
Consume entrada ordenada.
Puede aparecer en grouping sets.
Workers paralelos calculan parciales y el leader combina.
El mejor depende de grupos estimados, orden y memoria.
Estructura típica:
Gather / Gather Merge
↓
Parallel Seq Scan / Partial Aggregate / Parallel HashEl planner considera:
Más workers no siempre reduce latencia. El leader participa y el sistema comparte CPU/I/O con otras queries.
Para tablas particionadas, el planner/executor puede eliminar partitions incompatibles:
WHERE occurred_at >= $1 AND occurred_at < $2Pruning puede ocurrir en planificación o ejecución según parámetros.
Si el predicado transforma la partition key o no permite inferencia, puede leer partitions innecesarias.
Un Nested Loop puede pasar valores externos al inner:
Index Scan on order_items
Index Cond: order_id = orders.idEl path depende de la fila outer. LATERAL y correlated subqueries usan este mecanismo.
Considera los parámetros concretos. Útil con skew:
status='pending' → índice parcial
status='paid' → sequential scanSe reutiliza sin valor concreto. Reduce planning overhead, pero usa estimación promedio.
PostgreSQL decide según coste acumulado. plan_cache_mode sirve para diagnóstico.
Síntoma de parameter sensitivity:
El planner usa:
No lee la tabla completa durante planning. Datos nuevos o correlaciones ocultas producen error.
Cambiar random_page_cost o effective_cache_size puede alterar planes globalmente.
Antes:
No ajustes para obligar una query aislada.
No reserva memoria. Informa al planner cuánto cache total estima disponible entre OS y PostgreSQL.
Un valor mayor hace más atractivos algunos index paths. Debe representar un supuesto razonable del entorno compartido.
Es límite por operación de sort/hash, no por query ni servidor total.
Una query puede tener varios nodos; muchas sesiones concurrentes multiplican consumo.
El planner lo usa para estimar spills. Configúralo por workload y usa SET LOCAL para tareas especiales cuando sea seguro.
Just-in-time compilation puede reducir CPU en queries costosas de muchas expresiones, pero añade startup.
El planner activa según umbrales de coste. Para queries cortas puede empeorar latencia.
EXPLAIN muestra sección JIT cuando aplica.
Los nodos siguen conceptualmente una interfaz que solicita la siguiente tuple a sus hijos:
parent pide tuple
→ child produce
→ parent procesa
→ repiteAlgunos nodos son streaming; otros bloqueantes:
Esto explica time-to-first-row y startup cost.
EXPLAIN ANALYZE añade medición. BUFFERS, timing y JIT reporting tienen overhead.
El plan observado sigue siendo útil, pero no interpretes microsegundos como benchmark perfecto.
Consulta:
SELECT o.id, c.name
FROM orders o
JOIN customers c
ON c.tenant_id = o.tenant_id
AND c.id = o.customer_id
WHERE o.tenant_id = $1
AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;Plan esperado posible:
Limit
→ Nested Loop
→ Index Scan orders_pending_page_idx (50 filas)
→ Index Scan customers_pkey (50 loops)Es adecuado porque LIMIT y índice producen pocas orders. Sin filtro selectivo, Hash Join podría ser mejor.
Generic plan no representa el valor.
Costes no capturan exactamente cada estado momentáneo.
El planner puede ejecutarla demasiadas veces. Funciones custom permiten declarar COST/ROWS.
Estimaciones remotas pueden ser pobres y transferencia dominar.
Policies añaden filtros y pueden restringir optimización.
GEQO puede buscar heurísticamente y variar plan.
Ignora selectividad y acceso secuencial.
Son unidades del modelo.
No corrige estimaciones y puede empeorar otras queries.
El problema suele estar en un child con loops o misestimación.
Multiplica riesgo de memoria bajo concurrencia.
Queries dinámicas cortas pueden gastar más planificando que ejecutando.
EXPLAIN y análisis de planes enseña a comprobar estas estimaciones contra el trabajo real.