PolarDB:pg_hint_plan

Etapa 1: Criar a extensão

CREATE EXTENSION pg_hint_plan;

Etapa 2: Carregar a extensão

Escolha um dos métodos de carregamento abaixo conforme o escopo desejado:

Para um único usuário:

ALTER USER <username> SET session_preload_libraries = 'pg_hint_plan';

Para um único banco de dados:

ALTER DATABASE <database_name> SET session_preload_libraries = 'pg_hint_plan';

Se uma configuração incorreta impedir o login, conecte-se por meio de outra conta ou banco de dados e execute:

ALTER USER <username> RESET session_preload_libraries;
ALTER DATABASE <database_name> RESET session_preload_libraries;

Para um cluster de banco de dados:

Acesse o Quota Center, localize o item de cota chamado PolarDB PG pg_hint_plan use e clique em Apply na coluna Actions.

Etapa 3: Verificar se a extensão foi carregada

1. Ative a saída de depuração:

   SET pg_hint_plan.debug_print TO on;
   SET pg_hint_plan.message_level TO notice;

2. Execute uma consulta de teste com uma dica:

   /*+Set(enable_seqscan 1)*/SELECT 1;

   Se a extensão estiver carregada, a seguinte saída será exibida:

   NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)

3. Redefina as configurações de depuração:

   RESET pg_hint_plan.debug_print;
   RESET pg_hint_plan.message_level;

/*+
   HashJoin(a b)
   SeqScan(a)
 */
EXPLAIN SELECT *
   FROM pgbench_branches b
   JOIN pgbench_accounts a ON b.bid = a.bid
   ORDER BY a.aid;

QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=197)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

/*+ SeqScan(TBL) */
SELECT * FROM tbl;

/*+ SeqScan(tbl) */
SELECT * FROM tbl;

Dicas para métodos de varredura

Especifique o método de varredura para uma tabela. Se a tabela possuir um alias, consulte-a pelo alias.

Aplica-se a: tabelas comuns, tabelas herdadas, tabelas unlogged, tabelas temporárias, tabelas de sistema

Não se aplica a: tabelas externas, funções de tabela, expressões de valor constante, expressões universais, views, subconsultas

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

Dicas para métodos de junção

Defina o método de junção para duas ou mais tabelas.

Aplica-se a: 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 se aplica a: views e subconsultas

Dicas para ordem de junção

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

• Especificar a ordem de junção sem restringir a direção em cada nível.

• Especificar a ordem e a direção da junção em cada nível.

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

Neste exemplo:

• NestLoop(t1 t2) — une t1 e t2 usando nested loop join

• MergeJoin(t1 t2 t3) — une t1, t2 e t3 usando merge join

• Leading(t1 t2 t3) — define a ordem de junção como t1 → t2 → t3

Dicas para correção de número de linhas

Corrija estimativas de contagem de linhas calculadas incorretamente pelo otimizador. Quatro operadores têm suporte:

/*+ Rows(a b #10) */ SELECT ...;   -- Set join result row count to 10
/*+ Rows(a b +10) */ SELECT ...;   -- Increase row count by 10
/*+ Rows(a b -10) */ SELECT ...;   -- Decrease row count by 10
/*+ Rows(a b *10) */ SELECT ...;   -- Multiply row count by 10

Dicas para execução paralela

Especifique o plano de execução paralela para uma consulta.

Aplica-se a: tabelas comuns, tabelas herdadas, tabelas unlogged, tabelas de sistema

Não se aplica a: tabelas externas, cláusulas de valor constante, expressões universais, views, subconsultas

Use o nome real da tabela ou o alias para especificar tabelas internas dentro de uma view.

Exemplo 1: Defina o grau de paralelismo (DOP) como 3 para c1 e 5 para c2:

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

Resultado:

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)

Exemplo 2: Defina o DOP para tl como 5:

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

Resultado:

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)

Dicas para definição de parâmetros GUC

Altere temporariamente o valor de um parâmetro GUC durante o planejamento da consulta. Ao contrário das alterações de GUC em nível de sessão, essas dicas afetam apenas a geração do plano de execução da consulta alvo. Se múltiplas dicas definirem o mesmo parâmetro GUC, a última terá efeito.

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