Se usó la API de Cloud Translation para traducir esta página.

Crea y administra parches de planes de consultas

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

Un parche del 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 para la consulta. Por ejemplo, cuando analices una tabla en la consulta, usa 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 dentro de la especificación de las sugerencias, el optimizador de consultas primero aplica las sugerencias a la consulta mientras genera su plan de ejecución. Luego, las sugerencias se aplican automáticamente cada vez que se emite la consulta. Este enfoque te permite forzar diferentes planes de consultas desde el planificador. Por ejemplo, puedes usar sugerencias para forzar un análisis de índice en ciertas tablas o para forzar un orden de unión específico entre varias tablas.

El parche del plan de consultas 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 sin parámetros. En esta página, las consultas sin parámetros se denominan consultas sensibles a los parámetros.

Flujo de trabajo

El uso de un parche del plan de consultas implica los siguientes pasos:

Identifica la búsqueda para la que deseas crear un parche del plan.
Crea un parche de plan con sugerencias para aplicar cuando se ejecute la próxima búsqueda.
Verifica la aplicación del parche del plan.

En esta página, se usan la siguiente tabla y el siguiente índice para los 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 ejecución de consultas que creaste con una versión anterior, vuelve a crearlos siguiendo las instrucciones de esta página.

Antes de comenzar

Habilita la función de parches del plan de consultas en tu instancia. Establece la marca alloydb.enable_query_plan_patch en on. Puedes habilitar esta marca a nivel de todo el servidor o a nivel de la sesión. Para minimizar la sobrecarga que podría resultar del uso de esta función, habilita esta marca solo a nivel de la sesión.

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

Nota: Para forzar el mismo plan de consultas en tu instancia de grupo de lectura, habilita la función de parche del plan de consultas 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, el resultado muestra "on".
Para cada base de datos en la que desees usar parches de planes de consultas, crea una extensión en la base de datos desde la instancia principal de AlloyDB como el usuario alloydbsuperuser o postgres:
```
CREATE EXTENSION google_auto_hints CASCADE;
```

Roles requeridos

Para obtener los permisos que necesitas para crear y administrar parches de planes de consultas, pídele a tu administrador que te otorgue los siguientes roles de Identity and Access Management (IAM):

Rol de alloydbsuperuser

Si bien el permiso predeterminado solo permite que el usuario con el rol alloydbsuperuser cree parches de planes, puedes otorgar el permiso de escritura a los 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 búsqueda

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

Usa los siguientes métodos para identificar el ID de la búsqueda:

Ejecuta el comando EXPLAIN (VERBOSE), como se muestra en este 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 habilitaste la extensión pg_stat_statements, puedes encontrar el ID de la consulta consultando la vista pg_stat_statements, como se muestra en el siguiente ejemplo:
```
select query, queryid from pg_stat_statements;
```

Crea un parche del plan de consultas

Para crear un parche del plan de consultas, usa la función google_create_plan_patch(), que crea una asociación entre la consulta y las sugerencias en 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);

Reemplaza lo siguiente:

PLAN_PATCH_NAME: Es un nombre para el parche del plan. Debe ser único en la base de datos.
SQL_ID (opcional): Es el ID de la consulta para la que creas el parche del plan.

Puedes usar el ID de la consulta o el texto de la consulta (el parámetro SQL_TEXT) para crear un parche del plan. Sin embargo, te recomendamos que uses el ID de la consulta para crear un parche del plan, ya que AlloyDB ubica automáticamente el texto normalizado de la consulta según el ID de la consulta.
SQL_TEXT (opcional): Es el texto de la consulta para la que creas el parche del plan.

Cuando uses el texto de la búsqueda, este debe ser el mismo que la búsqueda prevista, excepto por los valores literales y constantes de la búsqueda. Cualquier discrepancia, incluida la diferencia de mayúsculas y minúsculas, puede provocar que no se aplique el parche del plan. Para obtener información sobre cómo crear parches de planes para consultas con literales y constantes, consulta Crea un parche de plan de consulta sensible a los parámetros.
APPLICATION_NAME (opcional): Es el nombre de la aplicación cliente de la sesión para la que deseas 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: Es una lista de sugerencias para la búsqueda separadas por espacios.
DISABLED (opcional): BOOL. Si es 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 su sugerencia IndexScan(t) para forzar un análisis de índice en la tabla t en la próxima ejecución de esta consulta de ejemplo.

Después de crear un parche del plan, puedes usar google_query_plan_patch_view para confirmar si se creó el parche del plan, 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

Después de que se crea el parche del plan en la instancia principal, se aplica automáticamente a las consultas asociadas en la instancia del grupo de lectura, siempre que también hayas habilitado la función de parche del plan de consultas en la instancia del grupo de lectura.

Crea un parche del plan de consultas que tenga en cuenta los parámetros

De forma predeterminada, cuando se crea un parche de plan para una búsqueda, el texto de búsqueda asociado se normaliza reemplazando cualquier valor literal y constante en el texto de búsqueda por un marcador de parámetro, como ?. Luego, el parche del plan se usa para esa búsqueda normalizada, incluso con un valor diferente para el marcador de parámetros.

Por ejemplo, ejecutar la siguiente consulta permite que otra consulta, como SELECT * FROM t WHERE a = 99;, use el parche del 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);

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

AlloyDB también te permite crear un parche de plan para textos de consultas no parametrizados, en los que cada valor literal y constante del texto de la consulta es significativo cuando se comparan consultas.

Cuando aplicas un parche de plan sensible a los parámetros, también se consideran diferentes dos búsquedas que solo difieren en los valores literales o constantes correspondientes. Si deseas forzar planes para ambas consultas, debes crear parches de planes independientes para cada una. Sin embargo, puedes usar diferentes sugerencias para los dos parches del plan.

Para crear un parche del plan que tenga en cuenta los parámetros, establece el parámetro SENSITIVE_TO_PARAM de la función google_create_plan_patch() en TRUE, 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, ya que 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 del plan que tienen en cuenta los parámetros no admiten una combinación de valores literales y constantes, y 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 por sobre el parche predeterminado.
Si deseas usar el ID de la consulta para crear un parche del 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 usan para crear el parche del plan.

Verifica la aplicación del parche del plan de consultas

Después de crear el parche del plan, usa los siguientes métodos para verificar que el plan de consulta se fuerce según corresponda.

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

Para ver las sugerencias que el planificador intenta aplicar, puedes establecer las siguientes marcas a nivel de la 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.

Administra los parches del plan de consultas

AlloyDB te permite ver, habilitar, inhabilitar y borrar un parche del plan de ejecución de la consulta.

Cómo ver un parche para plan de consultas

Para ver los parches de planes existentes, 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

Cómo habilitar un parche del plan de consultas

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

Por ejemplo, para volver a habilitar el parche de planificación my_hint1 inhabilitado anteriormente desde la base de datos, ejecuta la siguiente función:

SELECT google_enable_plan_patch('my_hint1');

Inhabilita un parche del plan de consultas

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

Por ejemplo, para borrar 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');

Borra un parche del plan de consulta

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