Crea y administra sugerencias con nombre

En esta página, se describe cómo crear y administrar sugerencias con nombre en AlloyDB para PostgreSQL.

Las sugerencias con nombre son una asociación entre una búsqueda y un conjunto de sugerencias que te permiten especificar los detalles del plan de búsqueda. 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.

Las sugerencias con nombre de AlloyDB admiten todas las sugerencias de la extensión de código abiertopg_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 sugerencias con nombre para las consultas con parámetros y las consultas 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 sugerencias con nombre implica los siguientes pasos:

Identifica la búsqueda para la que deseas crear sugerencias con nombre.
Create named hints con sugerencias que se aplicarán cuando se ejecute la próxima búsqueda.
Verifica la aplicación de las sugerencias con nombre.

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 las sugerencias con nombre que creaste con una versión anterior, vuelve a crearlas siguiendo las instrucciones de esta página.

Antes de comenzar

Habilita la función de sugerencias con nombre en tu instancia. Establece la marca alloydb.enable_named_hints 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 consulta en tu instancia de grupo de lectura, habilita la función de sugerencias con nombre en la instancia de grupo de lectura.

Para verificar que la marca esté habilitada, ejecuta el comando show alloydb.enable_named_hints;. Si la marca está habilitada, el resultado muestra "on".
Para cada base de datos en la que desees usar sugerencias con nombre, 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 obligatorios

Para obtener los permisos que necesitas para crear y administrar sugerencias con nombre, 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 sugerencias con nombre, también puedes otorgar el permiso de escritura a los demás usuarios o roles de la base de datos para que puedan crear sugerencias con nombre.

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 sugerencias con nombre

Para crear sugerencias con nombre, usa la función google_create_named_hints(), que crea una asociación entre la búsqueda y las sugerencias en la base de datos.

SELECT google_create_named_hints(
HINTS_NAME=>'HINTS_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

Reemplaza lo siguiente:

HINTS_NAME: Es un nombre para las sugerencias con nombre. Debe ser único en la base de datos.
SQL_ID (opcional): Es el ID de la búsqueda para la que creas sugerencias con nombre.

Puedes usar el ID de la búsqueda o el texto de la búsqueda (el parámetro SQL_TEXT) para crear sugerencias con nombre. Sin embargo, te recomendamos que uses el ID de la consulta para crear sugerencias con nombre, ya que AlloyDB ubica automáticamente el texto de la consulta normalizada según el ID de la consulta.
SQL_TEXT (opcional): Es el texto de la búsqueda para la que creas las sugerencias con nombre.

Cuando uses el texto de la búsqueda, este debe ser el mismo que el de la búsqueda prevista, excepto por los valores literales y constantes de la búsqueda. Cualquier discrepancia, incluida la diferencia entre mayúsculas y minúsculas, puede provocar que no se apliquen las sugerencias con nombre. Para obtener información sobre cómo crear sugerencias con nombre para consultas con literales y constantes, consulta Crea sugerencias con nombre sensibles a los parámetros.
APPLICATION_NAME (opcional): Es el nombre de la aplicación cliente de la sesión para la que deseas usar las sugerencias con nombre. Una cadena vacía te permite aplicar las sugerencias con nombre a la búsqueda, 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, inicialmente crea las sugerencias con nombre como inhabilitadas.

Ejemplo:

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

Esta consulta crea una sugerencia con nombre llamada 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 sugerencias con nombre, puedes usar google_named_hints_view para confirmar si se crearon, como se muestra en el siguiente ejemplo:

postgres=>\x
postgres=>select * from google_named_hints_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
hints_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 crean las sugerencias con nombre en la instancia principal, se aplican automáticamente a las búsquedas asociadas en la instancia del grupo de lectura, siempre que también hayas habilitado la función de sugerencias con nombre en la instancia del grupo de lectura.

Crea sugerencias con nombre sensibles a los parámetros

De forma predeterminada, cuando se crean sugerencias con nombre 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, las sugerencias con nombre se usan 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 las sugerencias con nombre my_hint2 de forma predeterminada.

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

Luego, una búsqueda, como SELECT * FROM t WHERE a = 99;, puede usar las sugerencias con nombre my_hint2 de forma predeterminada.

AlloyDB también te permite crear sugerencias con nombre 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 sugerencias con nombre sensibles 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 sugerencias con nombre independientes para cada una. Sin embargo, puedes usar diferentes sugerencias para las dos sugerencias con nombre.

Para crear sugerencias con nombre que tengan en cuenta los parámetros, configura el parámetro SENSITIVE_TO_PARAM de la función google_create_named_hints() en TRUE, como se muestra en el siguiente ejemplo:

SELECT google_create_named_hints(
HINTS_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 las sugerencias con nombre my_hint3, ya que el valor literal "99" no coincide con "88".

Cuando uses sugerencias con nombre que tengan en cuenta los parámetros, ten en cuenta lo siguiente:

Las sugerencias con nombre que tienen en cuenta los parámetros no admiten una combinación de valores literales y constantes, ni marcadores de parámetros en el texto de la búsqueda.
Cuando creas sugerencias con nombre que dependen de parámetros y sugerencias con nombre predeterminadas para la misma búsqueda, se prefieren las sugerencias con nombre que dependen de parámetros a las sugerencias con nombre predeterminadas.
Si deseas usar el ID de búsqueda para crear sugerencias con nombre que tengan en cuenta los parámetros, asegúrate de que la búsqueda 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 las sugerencias con nombre.

Verifica la aplicación de las sugerencias con nombre

Después de crear las sugerencias con nombre, 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 sugerencias con nombre

AlloyDB te permite ver, habilitar, inhabilitar y borrar sugerencias con nombre.

Cómo ver las sugerencias con nombre

Para ver las sugerencias con nombre existentes, usa la función google_named_hints_view, como se muestra en el siguiente ejemplo:

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

Habilita las sugerencias con nombre

Para habilitar las sugerencias con nombre existentes, usa la función google_enable_named_hints(HINTS_NAME). De forma predeterminada, las sugerencias con nombre se habilitan cuando las creas.

Por ejemplo, para volver a habilitar las sugerencias con nombre my_hint1 que se inhabilitaron anteriormente en la base de datos, ejecuta la siguiente función:

SELECT google_enable_named_hints('my_hint1');

Inhabilita las pistas con nombre

Para inhabilitar las sugerencias con nombre existentes, usa la función google_disable_named_hints(HINTS_NAME).

Por ejemplo, para borrar el ejemplo llamado sugerencias my_hint1 de la base de datos, ejecuta la siguiente función:

SELECT google_disable_named_hints('my_hint1');

Cómo borrar pistas con nombre

Para borrar sugerencias con nombre, usa la función google_delete_named_hints(HINTS_NAME).