Descripción general de la administración de planes de consultas en Aurora PostgreSQL - Amazon Aurora
Instrucciones SQL compatiblesLimitacionesTerminologíaVersiones disponiblesActivación de la administración de planes de consultasHabilitación de la administración de planes de consultasDesactivación de la administración de planes de consultas en Aurora PostgreSQL

Descripción general de la administración de planes de consultas en Aurora PostgreSQL

La administración de planes de consultas de Aurora PostgreSQL se ha diseñada para garantizar la estabilidad del plan independientemente de los cambios en la base de datos que puedan provocar una regresión del plan de consultas La regresión del plan de consultas se produce cuando el optimizador elige un plan subóptimo para una sentencia SQL determinada después de cambios en el sistema o la base de datos. Los cambios en estadísticas, restricciones, configuración del entorno, enlaces de parámetros de consultas y actualizaciones del motor de base de datos PostgreSQL pueden provocar una regresión del plan.

Con la gestión del plan de consultas de Aurora PostgreSQL, puede controlar cómo y cuándo cambian los planes de ejecución de las consultas. Entre los beneficios de la administración de planes de consultas en Aurora PostgreSQL se incluyen los siguientes.

  • Obligar al optimizador a elegir a partir de un número limitado de planes buenos y conocidos para mejorar la estabilidad de los planes.

  • Optimizar los planes de manera centralizada y, a continuación, distribuir los mejores planes globalmente.

  • Identificar índices fuera de uso y evaluar el impacto de crear o anular un índice.

  • Detectar automáticamente un nuevo plan de costo mínimo que el optimizador haya descubierto.

  • Probar características de optimizador nuevas con un menor nivel de riesgo, porque puede optar por aprobar únicamente las modificaciones de planes que mejoren el rendimiento.

Puede utilizar las herramientas que proporciona la administración de planes de consultas de forma proactiva para especificar el mejor plan para determinadas consultas. O bien, puede utilizar la administración de planes de consultas para reaccionar ante las circunstancias cambiantes y evitar las regresiones del plan. Para obtener más información, consulte Prácticas recomendadas para la administración de planes de consultas de Aurora PostgreSQL.

Instrucciones SQL compatibles

La administración de planes de consultas admite los siguientes tipos de instrucciones SQL.

  • Cualquier instrucción SELECT, INSERT, UPDATE o DELETE, independientemente de su complejidad.

  • Instrucciones preparadas. Para más información, consulte PREPARE en la documentación de PostgreSQL.

  • Instrucciones dinámicas, incluidas las que se ejecutan en modo inmediato. Para obtener más información, consulte Dynamic SQL y EXECUTE IMMEDIATE en la documentación de PostgreSQL.

  • Comandos e instrucciones SQL incrustados. Para obtener más información, consulte Embedded SQL Commands en la documentación de PostgreSQL.

  • Instrucciones dentro de funciones denominadas. Para obtener más información, consulte CREATE FUNCTION en la documentación de PostgreSQL.

  • Instrucciones que contienen tablas temporales.

  • Instrucciones dentro de procedimientos y bloques DO.

Puede utilizar la administración de planes de consultas con EXPLAIN en modo manual para capturar un plan sin ejecutarlo en realidad. Para obtener más información, consulte Analizar el plan elegido por el optimizador. Para obtener más información sobre los modos de administración del plan de consultas (manual o automático), consulte Captura de planes de ejecución de Aurora PostgreSQL.

La administración de planes de consulta de Aurora PostgreSQL es compatible con todas las características del lenguaje PostgreSQL, incluidas las tablas particionadas, la herencia, la seguridad a nivel de fila y las expresiones comunes de tabla (CTE) recursivas. Para obtener más información sobre estas funciones del lenguaje PostgreSQL, consulte Table Partitioning, Row Security Policies y WITH Queries (Common Table Expressions) y otros temas en la documentación de PostgreSQL.

Para obtener información sobre las diferentes versiones de la característica de administración de planes de consultas de Aurora PostgreSQL, consulte las versiones de la extensión apg_plan_mgmt de Aurora PostgreSQL en las notas de la versión de Aurora PostgreSQL.

Limitaciones de la administración de planes de consultas

La versión actual de la administración de planes de consultas de Aurora PostgreSQL tiene las siguientes limitaciones.

  • Los planes no se capturan para las instrucciones que hacen referencia a las relaciones del sistema: las instrucciones que hacen referencia a las relaciones del sistema como, por ejemplo pg_class, no se capturan. Esto es por diseño, para evitar que se capture una gran cantidad de planes generados por el sistema que se utilizan internamente. Esto también se aplica a las tablas del sistema dentro de las vistas.

  • Es posible que se necesite una clase de instancia de base de datos más grande para el clúster de base de datos de Aurora PostgreSQL: según la carga de trabajo, la administración de planes de consultas podría necesitar una clase de instancia de base de datos que tenga más de 2 vCPU El número de max_worker_processes está limitado por el tamaño de la clase de instancia de base de datos. Es posible que el número de max_worker_processes que proporciona una clase de instancia de base de datos de 2 vCPU (db.t3.medium, por ejemplo) no sea suficiente para una carga de trabajo determinada. Le recomendamos que elija una clase de instancia de base de datos con más de 2 vCPU para su clúster de base de datos de Aurora PostgreSQL si utiliza la administración de planes de consultas.

    Cuando la clase de instancia de la base de datos no puede soportar la carga de trabajo, la administración de planes de consulta emite un mensaje de error como el siguiente. 

    WARNING: could not register plan insert background process
HINT: You may need to increase max_worker_processes.

    En este caso, debe ampliar el clúster de base de datos de Aurora PostgreSQL a un tamaño de clase de instancia de base de datos con más memoria. Para obtener más información, consulte Motores de base de datos compatibles para clases de instancia de base de datos.

  • Los planes que ya estén almacenados en las sesiones no se ven afectados: la administración del plan de consultas proporciona una forma de influir en los planes de consultas sin cambiar el código de la aplicación. Sin embargo, si un plan genérico ya está almacenado en una sesión existente y desea cambiar su plan de consultas, primero debe configurar plan_cache_mode en force_custom_plan en el grupo de parámetros del clúster de base de datos.

  • queryid en apg_plan_mgmt.dba_plans y pg_stat_statements pueden diferir cuando:

    • Los objetos se eliminan y se vuelven a crear después de guardarlos en apg_plan_mgmt.dba_plans.

    • La tabla apg_plan_mgmt.plans se importa de otro clúster.

Para obtener información sobre las diferentes versiones de la característica de administración de planes de consultas de Aurora PostgreSQL, consulte las versiones de la extensión apg_plan_mgmt de Aurora PostgreSQL en las notas de la versión de Aurora PostgreSQL.

Terminología de administración de planes de consultas

Los siguientes términos se utilizan en este tema:

instrucción administrada

Una instrucción SQL capturada por el optimizador mediante la administración del plan de consultas. Una instrucción administrada tiene uno o más planes de ejecución de consultas almacenados en la vista apg_plan_mgmt.dba_plans.

línea base del plan

El conjunto de planes aprobados para una instrucción administrada determinada. Es decir, todos los planes de la instrucción administrada que tienen «Aprobado» en su columna status de la vista de dba_plan.

historial del plan

El conjunto de todos los planes capturados para una instrucción administrada determinada. El historial del plan contiene todos los planes capturados para la instrucción, independientemente de su estado.

regresión del plan de consulta

El caso en el que el optimizador elige un plan menos óptimo que antes de un cambio determinado en el entorno de la base de datos, como una nueva versión de PostgreSQL o cambios en las estadísticas.

Versiones de administración de planes de consultas en Aurora PostgreSQL

La administración de planes de consultas es compatible con todas las versiones de Aurora PostgreSQL disponibles actualmente. Para obtener información detallada sobre la versión, consulte la lista de actualizaciones de Amazon Aurora PostgreSQL en las notas de la versión de Aurora PostgreSQL.

La funcionalidad de administración de planes de consultas se agrega al clúster de base de datos de Aurora PostgreSQL al instalar la extensión apg_plan_mgmt. Las distintas versiones de Aurora PostgreSQL admiten distintas versiones de la extensión apg_plan_mgmt. Le recomendamos que actualice la extensión de administración de planes de consultas a la versión más reciente de su versión de Aurora PostgreSQL.

nota

Para ver las notas de la versión de cada una de las versiones de la extensión apg_plan_mgmt, consulte las versiones de extensión apg_plan_mgmt de Aurora PostgreSQL en las notas de la versión de Aurora PostgreSQL.

Puede identificar la versión que se ejecuta en su clúster conectándose a una instancia mediante psql y utilizando el metacomando\ dx para enumerar las extensiones, tal como se muestra a continuación.

labdb=> \dx
                       List of installed extensions
     Name      | Version |    Schema     |                            Description
---------------+---------+---------------+-------------------------------------------------------------------
 apg_plan_mgmt | 1.0     | apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management
 plpgsql       | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

El resultado muestra que este clúster utiliza la versión 1.0 de la extensión. Solo hay ciertas versiones de apg_plan_mgmt disponibles para una versión determinada de Aurora PostgreSQL. En algunos casos, es posible que necesite actualizar el clúster de base de datos de Aurora PostgreSQL a una nueva versión secundaria o aplicar un parche para poder actualizar a la versión más reciente de la administración del plan de consultas. La versión 1.0 de apg_plan_mgmtque se muestra en el resultado proviene de un clúster de base de datos de Aurora PostgreSQL versión 10.17, que no tiene una versión más reciente de apg_plan_mgmt disponible. En este caso, el clúster de base de datos de Aurora PostgreSQL debe actualizarse a una versión más reciente de PostgreSQL.

Para obtener más información sobre la actualización de su clúster de bases de datos Aurora PostgreSQL a una nueva versión de PostgreSQL, consulte Actualizaciones de Amazon Aurora PostgreSQL.

Para obtener información sobre cómo actualizar la extensión apg_plan_mgmt, consulte Actualización de la administración de planes de consultas en Aurora PostgreSQL.

Activación de la administración de planes de consultas en Aurora PostgreSQL

La configuración de la administración de planes de consultas para el clúster de base de datos de Aurora PostgreSQL implica instalar una extensión y cambiar varios parámetros del clúster de base de datos. Necesita permisos de rds_superuser para instalar la extensión apg_plan_mgmt y activar la función del clúster de base de datos de Aurora PostgreSQL.

Al instalar la extensión, se crea un nuevo rol, apg_plan_mgmt. Esta función permite a los usuarios de bases de datos ver, administrar y mantener planes de consultas. Como administrador con privilegios rds_superuser, asegúrese de conceder el rol de apg_plan_mgmt a los usuarios de la base de datos según sea necesario.

Sólo los usuarios con el rol rds_superuser pueden completar el procedimiento siguiente. El rds_superuser es necesario para crear la extensión apg_plan_mgmt y su apg_plan_mgmt función. Se debe conceder a los usuarios el rol apg_plan_mgmt para administrar la extensión apg_plan_mgmt.

Para activar la administración de planes de consulta para su clúster de bases de datos Aurora PostgreSQL

Los siguientes pasos activan la administración de planes de consultas para todas las instrucciones SQL que se envían al clúster de base de datos de Aurora PostgreSQL. Esto se conoce como modo automático. Para obtener más información sobre la diferencia entre modos, consulte Captura de planes de ejecución de Aurora PostgreSQL.

  1. Abra la consola de Amazon RDS en https://console.aws.amazon.com/rds/.

  2. Cree un grupo de parámetros de clúster de base de datos personalizado para su clúster de base de datos de Aurora PostgreSQL. Debe cambiar ciertos parámetros para activar la administración de planes de consultas y establecer su comportamiento. Para obtener más información, consulte Creación de un grupo de parámetros de base de datos en Amazon Aurora.

  3. Abra el grupo de parámetros de clúster de base de datos personalizado y establezca el parámetro rds.enable_plan_management en 1, como se muestra en la siguiente imagen.

    Imagen del grupo de parámetros de clúster de base de datos.

    Para obtener más información, consulte Modificación de los parámetros en un grupo de parámetros de clúster de base de datos en Amazon Aurora.

  4. Cree un grupo de parámetros de base de datos personalizado que pueda usar para establecer los parámetros del plan de consultas a nivel de instancia. Para obtener más información, consulte Creación de un grupo de parámetros de clúster de base de datos en Amazon Aurora.

  5. Modifique la instancia del escritor del clúster de base de datos de Aurora PostgreSQL para que utilice el grupo de parámetros de base de datos personalizado Para obtener más información, consulte Modificación de una instancia de base de datos en un clúster de base de datos.

  6. Modifique el clúster de base de datos de Aurora PostgreSQL para que utilice el grupo de parámetros del clúster de base de datos personalizado Para obtener más información, consulte Modificación del clúster de base de datos con la consola, CLI y API.

  7. Reinicie la instancia de base de datos para habilitar la configuración del grupo de parámetros personalizado.

  8. Conecte al punto de conexión de la instancia de base de datos de Aurora PostgreSQL mediante psql o pgAdmin. En el siguiente ejemplo, se usa la cuenta de postgres predeterminada del rol de rds_superuser.

    psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db

  9. Cree la extensión apg_plan_mgmt para su instancia de base de datos, como se muestra a continuación.

    labdb=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION
    sugerencia

    Instale la extensión apg_plan_mgmt en la base de datos de plantillas de la aplicación. La base de datos de plantillas predeterminada se denomina template1. Para obtener más información, consulte Template Databases en la documentación de PostgreSQL.

  10. Cambie el parámetro apg_plan_mgmt.capture_plan_baselines por automatic. Esta configuración hace que el optimizador genere planes para cada instrucción SQL que esté planificada o que se ejecute dos o más veces.

    nota

    La administración de planes de consultas también tiene un modo manual que puede utilizar para instrucciones SQL específicas. Para obtener más información, consulte Captura de planes de ejecución de Aurora PostgreSQL.

  11. Cambie el valor del parámetro apg_plan_mgmt.use_plan_baselines a «on». Este parámetro hace que el optimizador elija un plan para la instrucción a partir de la línea base del plan. Para obtener más información, consulte Uso de los planes administrados de Aurora PostgreSQL.

    nota

    Puede modificar el valor de cualquiera de estos parámetros dinámicos de la sesión sin necesidad de reiniciar la instancia.

Cuando la configuración de administración de planes de consultas esté completa, asegúrese de conceder el rol de apg_plan_mgmt a todos los usuarios de la base de datos que necesiten ver, administrar o mantener los planes de consultas.

Actualización de la administración de planes de consultas en Aurora PostgreSQL

Le recomendamos que actualice la extensión de administración de planes de consultas a la versión más reciente de su versión de Aurora PostgreSQL.

  1. Conecte a la instancia de escritor de su clúster de base de datos de Aurora PostgreSQL como un usuario que tiene privilegios de rds_superuser. Si mantuvo el nombre predeterminado al configurar la instancia, conéctese como postgres. En este ejemplo se muestra cómo utilizar psql, pero también puede usar pgAdmin si lo prefiere.

    psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

  2. Ejecute la siguiente consulta para actualizar la extensión.

    ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';

  3. Use la función apg_plan_mgmt.validate_plans para actualizar los hashes de todos los planes. El optimizador valida todos los planes aprobados, no aprobados y rechazados para garantizar que sigan siendo planes viables para la nueva versión de la extensión. 

    SELECT apg_plan_mgmt.validate_plans('update_plan_hash');

    Para obtener más información sobre el uso de esta función, consulte Validación de planes.

  4. Utilice la función apg_plan_mgmt.reload para actualizar cualquier plan de la memoria compartida con los planes validados de la vista dba_plans. 

    SELECT apg_plan_mgmt.reload();

Para obtener más información sobre todas las funciones disponibles para la administración del plan de consultas, consulte Referencia de funciones para la administración de planes de consultas de Aurora PostgreSQL.

Desactivación de la administración de planes de consultas en Aurora PostgreSQL

Puede deshabilitar la administración de planes de consultas en cualquier momento al desactivar apg_plan_mgmt.use_plan_baselines y apg_plan_mgmt.capture_plan_baselines. 

labdb=> SET apg_plan_mgmt.use_plan_baselines = off;

labdb=> SET apg_plan_mgmt.capture_plan_baselines = off;