Esta página se ha traducido con Cloud Translation API.

Crear y gestionar parches de planes de consulta

En esta página se describe cómo crear y gestionar parches de planes de consulta en AlloyDB para PostgreSQL.

Un parche de plan de consulta es una asociación entre una consulta y un conjunto de sugerencias que te permiten especificar los detalles del plan de consulta. Una sugerencia especifica información adicional sobre el plan de ejecución final preferido de la consulta. Por ejemplo, cuando analice una tabla en la consulta, utilice un análisis de índice en lugar de otros tipos de análisis, como un análisis secuencial.

Para limitar la elección del plan final a las especificaciones de las sugerencias, el optimizador de consultas primero aplica las sugerencias a la consulta mientras genera su plan de ejecución. Las sugerencias se aplican automáticamente cada vez que se envía la consulta. Este enfoque te permite forzar diferentes planes de consulta del planificador. Por ejemplo, puedes usar sugerencias para forzar un análisis de índice en determinadas tablas o para forzar un orden de unión específico entre varias tablas.

El parche del plan de consulta de AlloyDB admite todas las sugerencias de la extensión de código abierto pg_hint_plan.

Además, AlloyDB admite las siguientes sugerencias para el motor columnar:

ColumnarScan(table): fuerza un análisis columnar en la tabla.
NoColumnarScan(table): inhabilita el análisis columnar en la tabla.

AlloyDB te permite crear parches de planes para consultas con parámetros y consultas sin parámetros. En esta página, las consultas sin parámetros se denominan consultas sensibles a parámetros.

Flujo de trabajo

Para usar un parche de plan de consulta, sigue estos pasos:

Identifica la consulta para la que quieres crear un parche de plan.
Crea un parche de plan con sugerencias que se aplicarán la próxima vez que se ejecute la consulta.
Verifica la aplicación del parche del plan.

En esta página se usan la siguiente tabla y el siguiente índice como ejemplos:

CREATE TABLE t(a INT, b INT);
CREATE INDEX t_idx1 ON t(a);

  DROP EXTENSION IF EXISTS google_auto_hints;

Para seguir usando los parches del plan de consultas que has creado con una versión anterior, vuelve a crearlos siguiendo las instrucciones de esta página.

Antes de empezar

Habilita la función de parche para plan de consultas en tu instancia. Asigna el valor on a la marca alloydb.enable_query_plan_patch. Puedes habilitar esta marca a nivel de todo el servidor o a nivel de sesión. Para minimizar la sobrecarga que puede provocar el uso de esta función, habilita esta marca solo a nivel de sesión.

Para obtener más información, consulta Configurar las marcas de la base de datos de una instancia.

Nota: Para forzar el mismo plan de consulta en tu instancia de grupo de lectura, habilita la función de parche del plan de consulta en la instancia de grupo de lectura.

Para verificar que la marca esté habilitada, ejecuta el comando show alloydb.enable_query_plan_patch;. Si la marca está habilitada, la salida devuelve "on".
Para cada base de datos en la que quieras usar parches de plan de consulta, crea una extensión en la base de datos desde la instancia principal de AlloyDB como usuario alloydbsuperuser o postgres:
```
CREATE EXTENSION google_auto_hints CASCADE;
```

Roles obligatorios

Para obtener los permisos que necesitas para crear y gestionar parches de planes de consulta, pide a tu administrador que te conceda los siguientes roles de Gestión de Identidades y Accesos (IAM):

Rol alloydbsuperuser

Aunque el permiso predeterminado solo permite que el usuario con el rol alloydbsuperuser cree parches de planes, puedes conceder el permiso de escritura a otros usuarios o roles de la base de datos para que puedan crear parches de planes.

GRANT INSERT,DELETE,UPDATE ON hint_plan.plan_patches, hint_plan.hints TO role_name;
GRANT USAGE ON SEQUENCE hint_plan.hints_id_seq, hint_plan.plan_patches_id_seq TO role_name;

Identifica la consulta

Puede usar el ID de consulta para identificar la consulta cuyo plan predeterminado necesita ajustes. El ID de consulta estará disponible después de que se haya ejecutado la consulta al menos una vez.

Usa los siguientes métodos para identificar el ID de consulta:

Ejecuta el comando EXPLAIN (VERBOSE), tal como se muestra en el siguiente ejemplo:

EXPLAIN (VERBOSE) SELECT * FROM t WHERE a = 99;
                        QUERY PLAN
----------------------------------------------------------
Seq Scan on public.t  (cost=0.00..38.25 rows=11 width=8)
  Output: a, b
  Filter: (t.a = 99)
Query Identifier: -6875839275481643436

En el resultado, el ID de la consulta es -6875839275481643436.

Consulta la vista pg_stat_statements.

Si has habilitado la extensión pg_stat_statements, puedes encontrar el ID de consulta consultando la vista pg_stat_statements, como se muestra en el siguiente ejemplo:
```
select query, queryid from pg_stat_statements;
```

Crear un parche para plan de consultas

Para crear un parche de plan de consulta, usa la función google_create_plan_patch(), que crea una asociación entre la consulta y las sugerencias de la base de datos.

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'PLAN_PATCH_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

Haz los cambios siguientes:

PLAN_PATCH_NAME: un nombre para el parche del plan. Este valor debe ser único en la base de datos.
SQL_ID (Opcional): ID de la consulta para la que estás creando el parche del plan.

Puedes usar el ID de consulta o el texto de la consulta (el parámetro SQL_TEXT) para crear un parche de plan. Sin embargo, le recomendamos que utilice el ID de consulta para crear un parche de plan, ya que AlloyDB localiza automáticamente el texto de consulta normalizado en función del ID de consulta.
SQL_TEXT (Opcional): texto de la consulta para la que estás creando el parche del plan.

Cuando usas el texto de la consulta, este debe ser el mismo que la consulta prevista, excepto por los valores literales y constantes de la consulta. Si no coinciden, incluso si solo se diferencian en las mayúsculas y minúsculas, es posible que el parche del plan no se aplique. Para saber cómo crear parches de planes de consulta para consultas con literales y constantes, consulta Crear un parche de plan de consulta sensible a los parámetros.
APPLICATION_NAME (Opcional): nombre de la aplicación cliente de la sesión para la que quieres usar el parche del plan. Una cadena vacía te permite aplicar el parche del plan a la consulta, independientemente de la aplicación cliente que la emita.
HINTS: lista de sugerencias separadas por espacios para la consulta.
DISABLED (Opcional): BOOL. Si TRUE, crea inicialmente el parche del plan como inhabilitado.

Ejemplo:

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint1',
SQL_ID=>-6875839275481643436,
SQL_TEXT=>NULL,
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL);

Esta consulta crea un parche de plan llamado my_hint1. El optimizador aplica la sugerencia IndexScan(t) para forzar un análisis de índice en la tabla t en la siguiente ejecución de esta consulta de ejemplo.

Después de crear un parche de plan, puedes usar google_query_plan_patch_view para confirmar si se ha creado, como se muestra en el siguiente ejemplo:

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

Una vez que se haya creado el parche del plan en la instancia principal, se aplicará automáticamente a las consultas asociadas en la instancia del grupo de lectura, siempre que hayas habilitado la función de parche del plan de consulta en la instancia del grupo de lectura.

Crear un parche de plan de consulta sensible a los parámetros

De forma predeterminada, cuando se crea un parche de plan para una consulta, el texto de consulta asociado se normaliza sustituyendo cualquier valor literal y constante del texto de consulta por un marcador de parámetro, como ?. Después, el parche del plan se usa para esa consulta normalizada, incluso con un valor diferente para el marcador de parámetro.

Por ejemplo, al ejecutar la siguiente consulta, otra consulta, como SELECT * FROM t WHERE a = 99;, puede usar el parche de plan my_hint2 de forma predeterminada.

SELECT google_create_plan_patch(
  PLAN_PATCH_NAME=>'my_hint2',
  SQL_ID=>NULL,
  SQL_TEXT=>'SELECT * FROM t WHERE a = ?;',
  APPLICATION_NAME=>'',
  HINTS=>'SeqScan(t)',
  DISABLED=>NULL);

Después, una consulta como SELECT * FROM t WHERE a = 99; puede usar el parche de plan my_hint2 de forma predeterminada.

AlloyDB también te permite crear un parche de plan para textos de consulta no parametrizados, en los que cada valor literal y constante del texto de la consulta es significativo a la hora de buscar coincidencias.

Cuando aplicas un parche de plan sensible a los parámetros, dos consultas que solo se diferencian en los valores literales o constantes correspondientes también se consideran diferentes. Si quieres forzar planes para ambas consultas, debes crear parches de planes independientes para cada consulta. Sin embargo, puedes usar diferentes sugerencias para los dos parches de planificación.

Para crear un parche de plan que tenga en cuenta los parámetros, asigna el valor TRUE al parámetro SENSITIVE_TO_PARAM de la función google_create_plan_patch(), como se muestra en el siguiente ejemplo:

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint3',
SQL_ID=>NULL,
SQL_TEXT=>'SELECT * FROM t WHERE a = 88;',
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL,
SENSITIVE_TO_PARAM=>TRUE);

La consulta SELECT * FROM t WHERE a = 99; no puede usar el parche del plan my_hint3, porque el valor literal "99" no coincide con "88".

Cuando uses parches de planes sensibles a los parámetros, ten en cuenta lo siguiente:

Los parches de planes sensibles a los parámetros no admiten una combinación de valores literales y constantes, así como marcadores de parámetros en el texto de la consulta.
Cuando creas un parche de plan sensible a los parámetros y un parche de plan predeterminado para la misma consulta, se prefiere el parche de plan sensible a los parámetros al parche predeterminado.
Si quieres usar el ID de consulta para crear un parche de plan sensible a los parámetros, asegúrate de que la consulta se haya ejecutado en la sesión actual. Los valores de los parámetros de la ejecución más reciente (en la sesión actual) se utilizan para crear el parche del plan.

Verificar la aplicación del parche del plan de consulta

Después de crear el parche del plan, usa los siguientes métodos para verificar que el plan de consulta se ha aplicado correctamente.

Usa el comando EXPLAIN o el comando EXPLAIN (ANALYZE).

Para ver las sugerencias que intenta aplicar el planificador, puedes definir las siguientes marcas a nivel de sesión antes de ejecutar el comando EXPLAIN:
```
SET pg_hint_plan.debug_print = ON;
SET client_min_messages = LOG;
```
Usa la extensión auto_explain.

Gestionar parches de planes de consultas

AlloyDB te permite ver, habilitar, inhabilitar y eliminar un parche de plan de consulta.

Ver un parche para plan de consultas

Para ver los parches de planes, usa la función google_query_plan_patch_view, como se muestra en el siguiente ejemplo:

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

Habilitar un parche para plan de consultas

Para habilitar un parche de plan, usa la función google_enable_plan_patch(PLAN_PATCH_NAME). De forma predeterminada, un parche de plan se habilita cuando lo creas.

Por ejemplo, para volver a habilitar el parche my_hint1 del plan que se había inhabilitado anteriormente en la base de datos, ejecuta la siguiente función:

SELECT google_enable_plan_patch('my_hint1');

Inhabilitar un parche para plan de consultas

Para inhabilitar un parche de plan, usa la función google_disable_plan_patch(PLAN_PATCH_NAME).

Por ejemplo, para eliminar el parche de plan de ejemplo my_hint1 de la base de datos, ejecuta la siguiente función:

SELECT google_disable_plan_patch('my_hint1');

Eliminar un parche para plan de consultas

Para eliminar un parche de un plan, usa la función google_delete_plan_patch(PLAN_PATCH_NAME).