ApsaraDB RDS:Use the pg_hint_plan extension to customize query plans

Actualización o desinstalación de la extensión

En la página Extension Management, haga clic en la pestaña Installed Extensions:

• Para actualizar: haga clic en Upgrade dentro de la columna Actions. Si no aparece el botón Upgrade, significa que la extensión ya se encuentra en su versión más reciente.

• Para desinstalar: haga clic en Uninstall dentro de la columna Actions.

Para desinstalar mediante SQL:

DROP EXTENSION pg_hint_plan;

Uso de alias de tabla en los hints

Si una consulta emplea un alias de tabla, el hint debe hacer referencia al alias y no al nombre original de la tabla. Usar el nombre original provoca que el hint se ignore silenciosamente.

Hint con alias (efectivo):

/*+ IndexScan(t) */
EXPLAIN (COSTS OFF)
SELECT *
FROM tbl t
WHERE a = 1;

QUERY PLAN
-------------------------------------
 Index Scan using tbl_a_idx on tbl t
   Index Cond: (a = 1)

/*+ IndexScan(tbl) */
EXPLAIN (COSTS OFF)
SELECT *
FROM tbl t
WHERE a = 1;

QUERY PLAN
-------------------
 Seq Scan on tbl t
   Filter: (a = 1)

Habilitación de la tabla de hints

SET pg_hint_plan.enable_hint_table = on;

Gestión de hints en la tabla

Inserte un hint:

INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
VALUES (
    'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
    '',
    'SeqScan(t1)'
);

Actualice un hint:

UPDATE hint_plan.hints
SET hints = 'IndexScan(t1)'
WHERE id = 1;

Elimine un hint:

DELETE FROM hint_plan.hints
WHERE id = 1;

Hints de método de escaneo

Fuerzan o prohíben un método de escaneo específico sobre una tabla. Si la tabla tiene un alias en la consulta, utilice dicho alias en el hint.

Válido en: tablas ordinarias, tablas heredadas, tablas sin registro (unlogged), tablas temporales y tablas del sistema.

No válido en: tablas externas, funciones de tabla, expresiones de valor constante, expresiones universales, vistas y subconsultas.

Ejemplo: forzar un escaneo secuencial en t1 y un escaneo por índice en t2:

/*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
SELECT * FROM table1 t1 JOIN table2 t2 ON (t1.key = t2.key);

Hints de método de unión

Fuerzan o prohíben un método de unión específico entre tablas.

Válido en: tablas ordinarias, tablas heredadas, tablas sin registro (unlogged), tablas temporales, tablas externas, tablas del sistema, funciones de tabla, expresiones de valor constante y expresiones universales.

No válido en: vistas y subconsultas.

Hints de orden de unión

Controlan el orden en que se unen las tablas. Existen dos formas disponibles:

• Especificar solo el orden de unión (el planificador decide la dirección):

  /*+ Leading(t1 t2 t3) */
  SELECT * FROM table1 t1
      JOIN table2 t2 ON (t1.key = t2.key)
      JOIN table3 t3 ON (t2.key = t3.key);

• Especificar tanto el orden como la dirección de unión en cada nivel:

  /*+ Leading((t1 t2) t3) */
  SELECT * FROM table1 t1
      JOIN table2 t2 ON (t1.key = t2.key)
      JOIN table3 t3 ON (t2.key = t3.key);

Hints de corrección de número de filas

Corrigen las estimaciones de recuento de filas cuando el optimizador calcula valores incorrectos. Hay cuatro métodos de corrección disponibles:

/*+ Rows(a b #10) */ SELECT ...;   -- Set the row count to 10
/*+ Rows(a b +10) */ SELECT ...;   -- Add 10 to the row count
/*+ Rows(a b -10) */ SELECT ...;   -- Subtract 10 from the row count
/*+ Rows(a b *10) */ SELECT ...;   -- Multiply the row count by 10

Hints de ejecución paralela

Establecen el número de trabajadores paralelos para una tabla. Configure el recuento de trabajadores en 0 para deshabilitar la ejecución paralela.

El tercer parámetro controla qué configuraciones se modifican:

• soft (predeterminado): modifica únicamente max_parallel_workers_per_gather; el planificador controla el resto de las configuraciones.

• hard: modifica todas las configuraciones relacionadas del planificador.

Válido en: tablas ordinarias, tablas heredadas, tablas sin registro (unlogged) y tablas del sistema. Las tablas internas de una vista pueden referenciarse por su nombre real o por alias.

No válido en: tablas externas, cláusulas de valor constante, expresiones universales, vistas y subconsultas.

Ejemplo: ejecutar una consulta de unión con 3 trabajadores en c1 y 5 trabajadores en c2:

EXPLAIN /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
       SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);

QUERY PLAN
-------------------------------------------------------------------------------
 Hash Join  (cost=2.86..11406.38 rows=101 width=4)
   Hash Cond: (c1.a = c2.a)
   ->  Gather  (cost=0.00..7652.13 rows=1000101 width=4)
         Workers Planned: 3
         ->  Parallel Seq Scan on c1  (cost=0.00..7652.13 rows=322613 width=4)
   ->  Hash  (cost=1.59..1.59 rows=101 width=4)
         ->  Gather  (cost=0.00..1.59 rows=101 width=4)
               Workers Planned: 5
               ->  Parallel Seq Scan on c2  (cost=0.00..1.59 rows=59 width=4)

EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;

QUERY PLAN
-----------------------------------------------------------------------------------
 Finalize Aggregate  (cost=693.02..693.03 rows=1 width=8)
   ->  Gather  (cost=693.00..693.01 rows=5 width=8)
         Workers Planned: 5
         ->  Partial Aggregate  (cost=693.00..693.01 rows=1 width=8)
               ->  Parallel Seq Scan on tl  (cost=0.00..643.00 rows=20000 width=4)

Hints de parámetros GUC

Modifican temporalmente el valor de un parámetro GUC durante la planificación de la consulta. Si el mismo parámetro GUC se establece más de una vez, prevalece el último valor.

/*+ Set(random_page_cost 2.0) */
SELECT * FROM table1 t1 WHERE key = 'value';