Mantenimiento de los planes de ejecución de Aurora PostgreSQL

La administración de planes de consultas ofrece técnicas y funciones para añadir, mantener y mejorar planes de ejecución.

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.

Validación de planes

Use la función apg_plan_mgmt.validate_plans para eliminar o deshabilitar planes no válidos.

Los planes pueden volverse no válidos u obsoletos cuando se eliminan los objetos de los que dependen, como un índice o una tabla. Sin embargo, puede que un plan deje de ser válido solo temporalmente si el objeto eliminado se recrea. Si un plan no válido puede pasar a ser válido posteriormente, es posible que prefiera deshabilitar un plan no válido o no hacer nada en lugar de eliminarlo.

Para encontrar y eliminar todos los planes que no sean válidos y no se hayan utilizado en la última semana, utilice la función apg_plan_mgmt.validate_plans de la siguiente forma.

SELECT apg_plan_mgmt.validate_plans(sql_hash, plan_hash, 'delete') 
FROM apg_plan_mgmt.dba_plans
WHERE last_used < (current_date - interval '7 days');

Para habilitar o deshabilitar un plan directamente, utilice la función apg_plan_mgmt.set_plan_enabled.

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:

• Extensión pg_hint_plan: especifique un "consejo" para modificar cómo funciona el planificador mediante la extensión pg_hint_plan de PostgreSQL. Para instalar y obtener más información sobre cómo usar la extensión pg_hint_plan, consulte la documentación de pg_hint_plan.

• Variables GUC: anule uno o varios parámetros del modelo de costos u otros parámetros del optimizador, como from_collapse_limit o GEQO_threshold.

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.

Para modificar el plan generado por el optimizador y capturar el plan con pg_hint_plan

1. Active el modo de captura manual.

   SET apg_plan_mgmt.capture_plan_baselines = manual;

2. Especifique un consejo para la instrucción SQL que le interese.

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

   Tras la ejecución, el optimizador captura el plan en la vista apg_plan_mgmt.dba_plans. El plan capturado no incluye la sintaxis del comentario especial pg_hint_plan porque la administración del plan de consultas normaliza la instrucción eliminando los comentarios al principio.

3. Ver los planes administrados utilizando la vista apg_plan_mgmt.dba_plans.

   SELECT sql_hash, plan_hash, status, sql_text, plan_outline 
   FROM apg_plan_mgmt.dba_plans;

4. Establezca el estado del plan en Preferred. De este modo, se asegurará de que el optimizador decida ejecutarlo en lugar de seleccionarlo del conjunto de planes aprobados cuando el plan de costo mínimo no sea ya Approved o Preferred.

   SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' ); 

5. Desactivar la captura de planes manual y forzar el uso de planes administrados.

   SET apg_plan_mgmt.capture_plan_baselines = false;
   SET apg_plan_mgmt.use_plan_baselines = true;

   Ahora, cuando se ejecuta la instrucción SQL original, el optimizador elegirá un plan Approved o Preferred. Si el plan de costo mínimo no es Approved ni Preferred, el optimizador elegirá el plan Preferred.

Eliminación de planes

Los planes se eliminan automáticamente si no se usan en más de un mes, específicamente, 32 días. Este es el ajuste predeterminado del parámetro apg_plan_mgmt.plan_retention_period. Puede cambiar el período de retención del plan por otro más largo o por un período de tiempo más corto, a partir del valor de 1. Determinar el número de días desde que un plan se usó por última vez se usó restando la fecha de last_used de la fecha actual. La fecha de last_used es la fecha más reciente en que el optimizador eligió el plan como plan de costo mínimo o en que se ejecutó el plan. La fecha se almacena para el plan en la vista apg_plan_mgmt.dba_plans.

Le recomendamos que elimine planes que no se hayan utilizado durante mucho tiempo o que no resulten útiles. Todos los planes tienen una fecha last_used que utiliza el optimizador cada vez que ejecuta un plan o lo elige como plan de costo mínimo para una instrucción. Verifique las últimas fechas de last_usedpara identificar los planes que puede eliminar de forma segura.

La siguiente consulta devuelve una tabla de tres columnas con el recuento del número total de planes, los planes que no se han podido eliminar y los que se han eliminado correctamente. Incluye una consulta anidada que es un ejemplo de cómo usar la función apg_plan_mgmt.delete_plan para eliminar todos los planes que no se hayan seleccionado como plan de costo mínimo en los últimos 31 días y cuyo estado no es Rejected.

SELECT (SELECT COUNT(*) from apg_plan_mgmt.dba_plans) total_plans,
       COUNT(*) FILTER (WHERE result = -1) failed_to_delete,
       COUNT(*) FILTER (WHERE result = 0) successfully_deleted
       FROM (
            SELECT apg_plan_mgmt.delete_plan(sql_hash, plan_hash) as result
            FROM apg_plan_mgmt.dba_plans
            WHERE last_used < (current_date - interval '31 days')
            AND status <> 'Rejected'
            ) as dba_plans ;   

 total_plans | failed_to_delete | successfully_deleted
-------------+------------------+----------------------
           3 |                0 |                    2

Para obtener más información, consulte apg_plan_mgmt.delete_plan.

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.

importante

Si no elimina los planes extraños, podría quedarse eventualmente sin memoria compartida dedicada a la administración de planes de consulta. Para controlar cuánta memoria tendrá disponible para los planes administrados, utilice el parámetro apg_plan_mgmt.max_plans. Establezca este parámetro en el grupo de parámetros de base de datos personalizado y reinicie la instancia de base de datos para que los cambios surtan efecto. Para obtener más información, consulte el parámetro apg_plan_mgmt.max_plans.

Importación y exportación de planes

Puede exportar sus planes administrados e importarlos en otra instancia de base de datos.

Para exportar planes administrados.

Un usuario autorizado puede copiar cualquier subconjunto de la tabla apg_plan_mgmt.plans a otra tabla, y después guardarlo mediante el comando pg_dump. A continuación se muestra un ejemplo.

CREATE TABLE plans_copy AS SELECT * 
FROM apg_plan_mgmt.plans [ WHERE predicates ] ;

% pg_dump --table apg_plan_mgmt.plans_copy -Ft mysourcedatabase > plans_copy.tar

DROP TABLE apg_plan_mgmt.plans_copy;

Para importar planes administrados.

1. Copie el archivo .tar de los planes administrados exportados al sistema en el que desee restaurar los planes.

2. Utilice el comando pg_restore para copiar el archivo tar en una nueva tabla.

   % pg_restore --dbname mytargetdatabase -Ft plans_copy.tar

3. Combine la tabla plans_copy con la tabla apg_plan_mgmt.plans, como se muestra en el siguiente ejemplo.

   nota

   En algunos casos, puede volcar de una versión de la extensión apg_plan_mgmt y restaurar a una versión diferente. En estos casos, las columnas de la tabla de planes puede ser diferente. De ser así, ponga un nombre explícito a las columnas en lugar de usar SELECT *.

   INSERT INTO apg_plan_mgmt.plans SELECT * FROM plans_copy
    ON CONFLICT ON CONSTRAINT plans_pkey
    DO UPDATE SET
    status = EXCLUDED.status,
    enabled = EXCLUDED.enabled,
    -- Save the most recent last_used date 
    --
    last_used = CASE WHEN EXCLUDED.last_used > plans.last_used 
    THEN EXCLUDED.last_used ELSE plans.last_used END, 
    -- Save statistics gathered by evolve_plan_baselines, if it ran:
    --
    estimated_startup_cost = EXCLUDED.estimated_startup_cost,
    estimated_total_cost = EXCLUDED.estimated_total_cost,
    planning_time_ms = EXCLUDED.planning_time_ms,
    execution_time_ms = EXCLUDED.execution_time_ms,
    total_time_benefit_ms = EXCLUDED.total_time_benefit_ms, 
    execution_time_benefit_ms = EXCLUDED.execution_time_benefit_ms;

4. Vuelva a cargar los planes administrados en la memoria compartida y elimine la tabla de planes temporal.

   SELECT apg_plan_mgmt.reload(); -- refresh shared memory
   DROP TABLE plans_copy;