Mejora de los planes de consultas en Aurora PostgreSQL

Mejore la administración de los planes de consultas evaluando el rendimiento del plan y realizando correcciones. Para obtener más información sobre la mejora de los planes de consultas, vea los siguientes temas.

Evaluación del rendimiento de los planes

Después de que el optimizador capture los planes como sin aprobar, utilice la función apg_plan_mgmt.evolve_plan_baselines para comparar planes en función de su rendimiento real. Según el resultado de sus experimentos de rendimiento, podrá cambiar el estado de un plan de no aprobado a aprobado o rechazado. También puede decidir utilizar la función apg_plan_mgmt.evolve_plan_baselines para deshabilitar temporalmente un plan si no se ajusta a sus requisitos.

Aprobar planes mejores

El siguiente ejemplo muestra cómo cambiar el estado de los planes administrados a aprobados mediante la función apg_plan_mgmt.evolve_plan_baselines.

SELECT apg_plan_mgmt.evolve_plan_baselines (
   sql_hash, 
   plan_hash, 
   min_speedup_factor := 1.0, 
   action := 'approve'
) 
FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';

NOTICE:     rangequery (1,10000)
NOTICE:     Baseline   [ Planning time 0.761 ms, Execution time 13.261 ms]
NOTICE:     Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms]
NOTICE:     Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms
NOTICE:     Unapproved -> Approved
evolve_plan_baselines 
-----------------------
0
(1 row)     

El resultado muestra un informe de rendimiento para la instrucción rangequery con vinculaciones de parámetros de 1 y 10 000. El nuevo plan no aprobado (Baseline+1) es mejor que el plan aprobado previamente (Baseline). Para confirmar que el nuevo plan sea ahora Approved, compruebe la vista apg_plan_mgmt.dba_plans.

SELECT sql_hash, plan_hash, status, enabled, stmt_name 
FROM apg_plan_mgmt.dba_plans;

sql_hash  | plan_hash |  status  | enabled | stmt_name  
------------+-----------+----------+---------+------------
1984047223 | 512153379 | Approved | t       | rangequery
1984047223 | 512284451 | Approved | t       | rangequery
(2 rows)

El plan administrado incluye ahora dos planes aprobados que componen la base de referencia del plan de la instrucción. También puede llamar a la función apg_plan_mgmt.set_plan_status para establecer directamente el campo de estado de un plan en 'Approved', 'Rejected', 'Unapproved' o 'Preferred'.

Rechazar o desactivar planes más lentos

Para rechazar o deshabilitar planes, pase 'reject' o 'disable' como parámetro de acción a la función apg_plan_mgmt.evolve_plan_baselines. En este ejemplo se deshabilita cualquier plan capturado Unapproved que resulte al menos un 10 por ciento más lento que el mejor plan Approved para la instrucción.

SELECT apg_plan_mgmt.evolve_plan_baselines(
sql_hash,  -- The managed statement ID
plan_hash, -- The plan ID
1.1,       -- number of times faster the plan must be 
'disable'  -- The action to take. This sets the enabled field to false.
)
FROM apg_plan_mgmt.dba_plans
WHERE status = 'Unapproved' AND   -- plan is Unapproved
origin = 'Automatic';       -- plan was auto-captured

También puede establecer un plan directamente como rechazado o deshabilitado. Para establecer directamente el campo habilitado de un plan como true o false, llame a la función apg_plan_mgmt.set_plan_enabled. Para establecer directamente el campo de estado de un plan como 'Approved', 'Rejected', 'Unapproved' o 'Preferred', llame a la función apg_plan_mgmt.set_plan_status.

Para eliminar los planes que no son válidos y espera que sigan siendo inválidos, utilice la función apg_plan_mgmt.validate_plans. Esta función le permite eliminar o deshabilitar planes no válidos. Para obtener más información, consulte Validación de planes.

Corrección de planes mediante pg_hint_plan

El optimizador de consultas está bien diseñado para encontrar un plan óptimo para todas las instrucciones, y en la mayoría de los casos el optimizador encuentra un plan bueno. Sin embargo, ocasionalmente podría detectar que existe un plan mucho mejor que el generado por el optimizador. Dos formas recomendadas de hacer que el optimizador genere un plan deseado son incluir la extensión pg_hint_plan o establecer variables de Grand Unified Configuration (GUC) en PostgreSQL:

Al usar una de estas técnicas para forzar que el optimizador de consultas utilice un plan, también puede utilizar la administración de planes de consulta para capturar y forzar el uso del nuevo plan.

Puede utilizar la extensión pg_hint_plan para cambiar el orden de las combinaciones, los métodos de combinación o las rutas de acceso para una instrucción SQL. Puede utilizar un comentario SQL con sintaxis pg_hint_plan especial para modificar cómo crea un plan el optimizador. Por ejemplo, supongamos que la instrucción SQL del problema tiene una combinación bidireccional.

SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;            

A continuación, suponga que el optimizador elige el orden de combinación (t1, t2), pero que sabe que el orden de combinación (t2, t1) es más rápido. El siguiente consejo fuerza al optimizador a utilizar el orden de combinación más rápido (t2, t1). Incluya EXPLAIN de modo que el optimizador genere un plan para la instrucción SQL pero sin ejecutar la instrucción. (No se muestra el resultado).

/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id; 

Los siguientes pasos muestran cómo utilizar pg_hint_plan.