ApsaraDB RDS:Use the pg_hint_plan extension to customize query plans

Atualize ou desinstale a extensão

Na página Extension Management, clique em aba Installed Extensions:

• Para atualizar: clique em Upgrade na coluna Actions. Se o botão Upgrade não aparecer, a extensão já está na versão mais recente.

• Para desinstalar: clique em Uninstall na coluna Actions.

Para desinstalar via SQL:

DROP EXTENSION pg_hint_plan;

Usar aliases de tabela nos hints

Se uma consulta utilizar um alias de tabela, o hint deve referenciar o alias, e não o nome original da tabela. O uso do nome original faz com que o sistema ignore o hint silenciosamente.

Hint com alias (efetivo):

/*+ 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)

Ative a tabela de hints

SET pg_hint_plan.enable_hint_table = on;

Gerencie hints na tabela

Inserir um hint:

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

Atualize um hint:

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

Exclua um hint:

DELETE FROM hint_plan.hints
WHERE id = 1;

Hints de método de varredura

Forçam ou proíbem um método específico de varredura em uma tabela. Se a tabela possuir um alias na consulta, utilize-o no hint.

Válido em: tabelas comuns, tabelas herdadas, tabelas unlogged, tabelas temporárias, tabelas de sistema.

Não válido em: tabelas externas, funções de tabela, expressões de valor constante, expressões universais, views, subconsultas.

Exemplo — forçar uma varredura sequencial em t1 e uma varredura de índice em t2:

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

Hints de método de junção

Forçam ou proíbem um método específico de junção entre tabelas.

Válido em: tabelas comuns, tabelas herdadas, tabelas unlogged, tabelas temporárias, tabelas externas, tabelas de sistema, funções de tabela, expressões de valor constante, expressões universais.

Não válido em: views, subconsultas.

Hints de ordem de junção

Controlam a ordem em que as tabelas são unidas. Duas formas estão disponíveis:

• Especificar apenas a ordem de junção (direção escolhida pelo planejador):

  /*+ 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 a ordem e a direção da junção em cada nível:

  /*+ 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 correção de número de linhas

Corrigem estimativas de contagem de linhas incorretas feitas pelo otimizador. Quatro métodos de correção estão disponíveis:

/*+ 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 execução paralela

Definem o número de workers paralelos para uma tabela. Configure a contagem de workers como 0 para desativar a execução paralela.

O terceiro parâmetro controla quais configurações são alteradas:

• soft (padrão): altera apenas max_parallel_workers_per_gather; o planejador controla as demais configurações.

• hard: altera todas as configurações relacionadas do planejador.

Válido em: tabelas comuns, tabelas herdadas, tabelas unlogged, tabelas de sistema. Tabelas internas de uma view podem ser referenciadas pelo nome real ou alias.

Não válido em: tabelas externas, cláusulas de valor constante, expressões universais, views, subconsultas.

Exemplo — executar uma consulta de junção com 3 workers em c1 e 5 workers em 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

Alteram temporariamente o valor de um parâmetro GUC durante o planejamento da consulta. Se o mesmo parâmetro GUC for definido mais de uma vez, o último valor prevalecerá.

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