Usando a extensão pg_hint_plan - PolarDB: Central de Documentação do Alibaba Cloud

Informações básicas

O PostgreSQL usa um otimizador baseado em custo que utiliza estatísticas de dados em vez de regras estáticas. Esse otimizador avalia os custos de todos os planos de execução possíveis para uma instrução SQL e executa o plano com o menor custo. Embora o otimizador faça o melhor esforço, o plano selecionado pode não ser o ideal porque ele não considera as relações subjacentes entre os dados.

Você pode especificar variáveis do Grand Unified Scheme (GUC) para ajustar o plano de execução, mas isso afeta toda a sessão. Para não afetar a sessão inteira, use o pg_hint_plan para otimizar um único plano de execução.

Pré-requisitos

Este recurso está disponível nas seguintes versões do PolarDB for PostgreSQL:

PostgreSQL 16 (versão secundária do kernel 2.0.16.9.6.0 ou posterior)
PostgreSQL 14 (sem restrição de versão secundária do kernel)
PostgreSQL 11 (sem restrição de versão secundária do kernel)

Nota

Para visualize a versão secundária do kernel, acesse o console ou execute o comando SHOW polardb_version;. Se o cluster não atender ao requisito de versão, atualize a versão secundária do kernel.

Precauções

O Data Management Service (DMS) não oferece suporte a hints em comentários. Use outro cliente de banco de dados para se conectar.
A extensão pg_hint_plan lê hints apenas do primeiro bloco de comentários de uma instrução.
O scanner de hints interrompe a leitura imediatamente ao encontrar caracteres diferentes de letras, números, espaços, sublinhados (_), vírgulas (,) ou parênteses (()).
O pg_hint_plan trata nomes de objetos de forma diferente do PostgreSQL e faz comparações que diferenciam maiúsculas de minúsculas. Por exemplo, um hint para um objeto chamado TBL corresponde somente a um objeto denominado TBL, e não a tbl ou Tbl.

Limitações

As limitações a seguir se aplicam ao uso da extensão pg_hint_plan em um procedimento armazenado PL/pgSQL:

Os hints têm efeito apenas nos seguintes tipos de instruções:
- Consultas que retornam uma única linha (SELECT, INSERT, UPDATE, DELETE).
- Consultas que retornam várias linhas (RETURN QUERY).
- Execução de instruções SQL (EXECUTE QUERY).
- Instruções que abrem um cursor (OPEN).
- Loops sobre resultados de consultas (FOR).
Posicione o hint logo após a primeira palavra de uma consulta. O pg_hint_plan ignora hints colocados antes da primeira palavra.

Crie e carregar a extensão pg_hint_plan

Crie a extensão.
```
CREATE EXTENSION pg_hint_plan;
```
Carregue a extensão.
- Para carregar a extensão automaticamente para um único usuário:
  - Execute o comando a seguir para carregar a extensão.
```
ALTER USER xxx set session_preload_libraries='pg_hint_plan';
```
    Nota
    Substitua xxx pelo nome de usuário real.
  - Execute o comando a seguir para carregar a extensão em um banco de dados específico.
```
ALTER DATABASE xxx set session_preload_libraries='pg_hint_plan';
```
  Nota
  Se um erro de configuração impedir a conexão com o banco de dados, conecte-se à instância PolarDB com um usuário ou banco de dados diferente e execute os seguintes comandos de redefinição:
  ALTER USER xxx reset session_preload_libraries; ALTER DATABASE xxx reset session_preload_libraries;
- Para carregar a extensão automaticamente em um cluster de banco de dados:
  
  Acesse o Quota Center. Na linha referente à cota PolarDB PG pg_hint_plan usage, clique em Apply na coluna Actions para solicitar acesso à extensão pg_hint_plan.
- Verifique se a extensão foi carregada.
  1. Execute os comandos a seguir para enviar a saída de depuração ao cliente.
```
SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
```
  2. Execute o comando a seguir para confirmar o carregamento da extensão.
```
/*+Set(enable_seqscan 1)*/select 1;
```
    Se a extensão estiver carregada, o comando retornará a seguinte saída:
```
NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)
```
  3. Execute os comandos a seguir para desativar a saída de depuração.
```
RESET pg_hint_plan.debug_print;
RESET pg_hint_plan.message_level;
```

Notas de uso

Hints em comentários

Um bloco de comentários do pg_hint_plan começa com /*+ e termina com */. Um hint consiste em um nome seguido de parâmetros entre parênteses, separados por espaços. Para melhorar a legibilidade, coloque cada hint em uma nova linha.

Exemplo

O exemplo a seguir usa o método de junção HashJoin e o método de varredura SeqScan para a tabela pgbench_accounts:

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

O comando retorna a seguinte saída:

                                      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)

Tipos de hints

Tipos de hints

Os hints são classificados em seis tipos conforme o impacto nos planos de execução:
- Hints de método de varredura
  
  Esse tipo define o método usado para varrer a tabela especificada. Se a tabela tiver um alias, a extensão pg_hint_plan a identifica por esse alias. Os métodos suportados incluem SeqScan, IndexScan, entre outros.
  
  Esses hints funcionam em tabelas comuns, herdadas, unlogged, temporárias e de sistema. No entanto, não surtem efeito em tabelas externas, funções de tabela, instruções com valores constantes especificados, expressões universais, views e subconsultas.
  Exemplo:
```
/*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
 SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
```
- Hints de método de junção
  
  Definem o método usado para unir as tabelas especificadas. São eficazes em tabelas comuns, herdadas, unlogged, temporárias, externas, de sistema, funções de tabela, instruções com valores constantes e expressões universais. Não se aplicam a views nem subconsultas.
- Hints de ordem de junção
  Um hint de ordem de junção determina a sequência de união para duas ou mais tabelas. Use um dos métodos a seguir para forçar essa ordem:
  - Forçar uma ordem específica sem restringir a direção em cada nível de junção.
  - Forçar a direção da junção.
  Exemplo:
```
 /*+
    NestLoop(t1 t2)
    MergeJoin(t1 t2 t3)
    Leading(t1 t2 t3)
  */
 SELECT * FROM table1 t1
     JOIN table table2 t2 ON (t1.key = t2.key)
     JOIN table table3 t3 ON (t2.key = t3.key);
```
  Nota
  Neste exemplo:
  
  NestLoop(t1 t2): define o método de junção para as tabelas t1 e t2.
  
  MergeJoin(t1 t2 t3): define o método de junção para as tabelas t1, t2 e t3.
  
  Leading(t1 t2 t3): estabelece a ordem de junção para as três tabelas.
- Hints de correção de número de linhas
  
  Corrigem erros na estimativa de linhas causados pelas restrições do otimizador.
```
 /*+ Rows(a b #10) */ SELECT... ; # Sets the number of rows in the join result to 10.
 /*+ Rows(a b +10) */ SELECT... ; # Increases the number of rows by 10.
 /*+ Rows(a b -10) */ SELECT... ; # Decreases the number of rows by 10.
 /*+ Rows(a b *10) */ SELECT... ; # Multiplies the number of rows by 10.
```
- Hints de execução paralela
  
  Especificam o plano usado para executar instruções SQL em paralelo.
  
  Funcionam em tabelas comuns, herdadas, unlogged e de sistema. Contudo, não afetam tabelas externas, cláusulas com valores constantes, expressões universais, views e subconsultas. É possível especificar as tabelas internas de uma view pelos nomes reais ou aliases.
  Os exemplos a seguir demonstram como executar uma consulta com diferentes níveis de paralelismo em cada tabela:
  - Exemplo 1: Defina o grau de paralelismo (DOP) da tabela c1 como 3 e da tabela c2 como 5.
    EXPLAIN /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */ SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);
    Resultado retornado:
    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 grau de paralelismo (DOP) da tabela t1 como 5.
    EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;
    Resultado retornado:
    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 definição de parâmetros GUC
  
  Alteram temporariamente o valor de um parâmetro GUC. Esses valores vigoram apenas quando o executor gera planos de execução e ajudam a melhorar o desempenho da consulta sem afetar toda a sessão. Se houver mais de um hint para o mesmo parâmetro GUC, o último prevalece.
  Exemplo:
```
 /*+ Set(random_page_cost 2.0) */
 SELECT * FROM table1 t1 WHERE key = 'value';
```

Sintaxe de hints

A tabela a seguir descreve a sintaxe de todos os hints suportados. Adicione esses hints às consultas dentro de blocos de comentários. Parâmetros opcionais aparecem entre colchetes [].

Tipo	Sintaxe	Descrição
Hints de método de varredura	SeqScan(table)	Força uma varredura sequencial na tabela especificada.
	TidScan(table)	Força uma varredura por Tuple ID (TID) na tabela especificada.
	IndexScan(table[ index...])	Força uma varredura de índice na tabela especificada. Permite especificar um ou mais índices.
	IndexOnlyScan(table[ index...])	Força uma varredura apenas por índice na tabela especificada. Permite especificar um ou mais índices.
	BitmapScan(table[ index...])	Força uma varredura de índice bitmap na tabela especificada. Permite especificar um ou mais índices.
	NoSeqScan(table)	Proíbe uma varredura sequencial na tabela especificada.
	NoTidScan(table)	Proíbe uma varredura TID na tabela especificada.
	NoIndexScan(table)	Proíbe uma varredura de índice na tabela especificada.
	NoIndexOnlyScan(table)	Proíbe uma varredura apenas por índice na tabela especificada.
	NoBitmapScan(table)	Proíbe uma varredura de índice bitmap na tabela especificada.
Hints de método de junção	NestLoop(table table[ table...])	Força uma junção por loop aninhado para as tabelas especificadas.
	HashJoin(table table[ table...])	Força uma junção hash para as tabelas especificadas.
	MergeJoin(table table[ table...])	Força uma junção por mesclagem para as tabelas especificadas.
	NoNestLoop(table table[ table...])	Proíbe uma junção por loop aninhado para as tabelas especificadas.
	NoHashJoin(table table[ table...])	Proíbe uma junção hash para as tabelas especificadas.
	NoMergeJoin(table table[ table...])	Proíbe uma junção por mesclagem para as tabelas especificadas.
Hints de ordem de junção	Leading(table table[ table...])	Define a ordem de junção para as tabelas.
Hints de ordem de junção	Leading(<join pair>)	Define a ordem e a direção da junção para duas tabelas.
Hints de correção de número de linhas	Rows(table table[ table...] correction)	Corrige o número estimado de linhas para um resultado de junção. Os métodos disponíveis incluem valor absoluto (#<n>), adição (+<n>), subtração (-<n>) e multiplicação (*<n>), onde <n> representa o número de linhas.
Hints de execução paralela	Parallel(table <# of workers> [soft\|hard])	Força ou desativa uma varredura paralela na tabela especificada. Nota <# of workers> define o grau de paralelismo (DOP) desejado, correspondente ao número de processos worker paralelos. O valor 0 desativa o paralelismo. Se o terceiro parâmetro for `soft` (padrão), o hint modifica apenas o valor do parâmetro `max_parallel_workers_per_gather`, e o otimizador determina o grau real de paralelismo. O parâmetro `hard` força o grau de paralelismo especificado.
	PX(<# of workers>)	Especifica uma execução paralela entre nós. Nota <# of workers> define o grau de paralelismo (DOP).
	NoPX()	Impede que a consulta use execução paralela entre nós.
Hints de definição de parâmetros GUC	Set(GUC-param value)	Define um parâmetro GUC com o valor especificado enquanto o otimizador gera o plano de execução.

Nota

Você também pode usar o pg_hint_plan para definir planos de execução paralela entre nós. No entanto, nesse cenário, não há suporte para hints de correção de número de linhas. Hints de método de junção aplicam-se apenas a junções entre duas tabelas, e hints de ordem de junção podem definir a ordem somente para todas as tabelas envolvidas.