Otimizar o desempenho de consultas - AnalyticDB: Central de Documentação do Alibaba Cloud

O AnalyticDB for PostgreSQL oferece diversas ferramentas para diagnosticar e corrigir consultas lentas. Use este guia para identificar a causa raiz e aplicar a correção adequada.

Sintoma	Onde investigar
Consultas lentas após carregamento massivo de dados	Coletar estatísticas de tabela
Consultas complexas com múltiplas tabelas apresentam lentidão	Escolha um otimizador de consultas
Buscas pontuais ou consultas por intervalo estão lentas	Usar índices para acelerar consultas
Incerteza sobre o que o banco de dados está executando	Visualize planos de consulta
Junções geram tráfego de rede intenso	Remover operadores de distribuição
Colunas de junção utilizam tipos de dados diferentes	Usar tipos de dados correspondentes nas colunas de junção
Um único nó de computação concentra todo o processamento	Localizar assimetria de dados
Acúmulo excessivo de consultas concorrentes	Visualize instruções SQL em execução
Consultas travadas aguardando execução	Verificar status de bloqueios
Consultas altamente seletivas permanecem lentas mesmo com índices	Ative junções de loop aninhado

Coletar estatísticas de tabela

O otimizador de consultas gera planos de execução com base nas estatísticas das tabelas. Estatísticas desatualizadas ou ausentes levam o otimizador a fazer estimativas imprecisas, resultando em planos ineficientes.

Execute ANALYZE após um grande carregamento de dados ou quando mais de 20% das linhas de uma tabela forem atualizadas.

-- Collect statistics on all tables
ANALYZE;

-- Collect statistics on all columns of table t
ANALYZE t;

-- Collect statistics on a specific column
ANALYZE t(a);

Para a maioria das cargas de trabalho, executar ANALYZE t na tabela modificada é suficiente. Use ANALYZE no nível de coluna apenas quando precisar de controle mais granular — por exemplo, em colunas usadas como chaves de junção, condições de filtro ou colunas indexadas.

Escolha um otimizador de consultas

O AnalyticDB for PostgreSQL inclui dois otimizadores de consultas. Cada um é otimizado para diferentes cargas de trabalho.

	Otimizador Legacy	Otimizador de consultas ORCA
Padrão na versão	V4.3	V6.0
Mais indicado para	Consultas simples com alta concorrência; junções de até 3 tabelas; cargas de trabalho de INSERT, UPDATE e DELETE	Consultas complexas; junções com mais de 3 tabelas; cargas de trabalho de extração, transformação e carga (ETL) e relatórios; SQL com subconsultas (elimina a necessidade de juntar tabelas em subconsultas); consultas em tabelas particionadas com condições de filtro especificadas por parâmetros (filtragem dinâmica de partições)
Compromisso	Geração de plano mais rápida	Explora mais caminhos de execução; leva mais tempo para gerar um plano

Alterne entre os otimizadores no nível de sessão:

-- Enable the Legacy query optimizer
SET optimizer = off;

-- Enable the ORCA query optimizer
SET optimizer = on;

-- Check the current optimizer
SHOW optimizer;
-- on = ORCA query optimizer
-- off = Legacy optimizer

Para alterar o otimizador no nível da instância, envie um ticket.

Usar índices para acelerar consultas

Índices aceleram consultas que varrem uma pequena fração de uma tabela com base em uma condição de filtro. O AnalyticDB for PostgreSQL suporta três tipos de índice.

Tipo de índice	Recomendado quando
Índice B-tree	A coluna possui muitos valores únicos e é usada para filtrar, juntar ou ordenar dados
Índice Bitmap	A coluna tem poucos valores únicos e é referenciada por mais de uma condição de filtro
Índice GiST	A consulta envolve localizações geográficas, intervalos, características de imagem ou valores Geometry

Exemplo: adicionar um índice B-tree

Sem um índice, o otimizador realiza uma varredura completa na tabela:

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
   ->  Table Scan on t  (cost=0.00..431.00 rows=1 width=16)
         Filter: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

Crie um índice B-tree na coluna b:

postgres=# CREATE INDEX i_t_b ON t USING btree (b);
CREATE INDEX

Com o índice, o otimizador passa a usar uma varredura de índice:

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..2.00 rows=1 width=16)
   ->  Index Scan using i_t_b on t  (cost=0.00..2.00 rows=1 width=16)
         Index Cond: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

Visualize planos de consulta

Um plano de consulta é uma árvore de operadores que descreve como o banco de dados executa uma consulta. Ler o plano ajuda a identificar por que uma consulta está lenta.

Como ler um plano de consulta

O plano é uma árvore. A execução começa nos nós folha (base) e flui até a raiz.
cost=<startup>..<total> representa o custo estimado pelo otimizador, não milissegundos reais. Uma estimativa de custo alta indica que o otimizador espera que a operação seja custosa — use esse valor para comparações, não como medida absoluta.
rows= indica o número estimado de linhas. Uma grande discrepância entre linhas estimadas e reais geralmente aponta para estatísticas desatualizadas — execute ANALYZE para corrigir.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN exibe o plano sem executar a consulta.
EXPLAIN ANALYZE executa a consulta e adiciona ao plano os tempos reais de execução e contagens de linhas.

-- Display plan only (no execution)
postgres=# EXPLAIN SELECT a, b FROM t;
                                      QUERY PLAN
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
 Optimizer status: legacy query optimizer
(3 rows)

-- Run query and show actual timings
postgres=# EXPLAIN ANALYZE SELECT a, b FROM t;
                                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   Rows out:  100 rows at destination with 2.728 ms to first row, 2.838 ms to end, start offset by 0.418 ms.
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
         Rows out:  Avg 33.3 rows x 3 workers.  Max 37 rows (seg2) with 0.088 ms to first row, 0.107 ms to end, start offset by 2.887 ms.
 Slice statistics:
   (slice0)    Executor memory: 131K bytes.
   (slice1)    Executor memory: 163K bytes avg x 3 workers, 163K bytes max (seg0).
 Statement statistics:
   Memory used: 128000K bytes
 Optimizer status: legacy query optimizer
 Total runtime: 3.739 ms
(11 rows)

Identificar o gargalo na saída do EXPLAIN ANALYZE

Use os dados reais de tempo e linhas para classificar o gargalo:

O que você observa	Gargalo provável	Próxima etapa
Tempo elevado nos operadores Redistribute Motion ou Broadcast Motion	Rede — dados estão sendo redistribuídos entre nós	Alinhar chaves de distribuição nas colunas de junção (veja Remover operadores de distribuição)
Linhas estimadas diferem muito das linhas reais	Estatísticas desatualizadas	Execute `ANALYZE` nas tabelas afetadas
Tempo alto em Seq Scan ou Table Scan com grande volume de linhas	E/S — varredura completa da tabela	Adicione um índice na coluna de filtro (veja Usar índices para acelerar consultas)
Uso elevado de memória nas estatísticas de Slice	Pressão de memória	Reduza a concorrência ou aumente os recursos da instância

Tipos de operadores suportados

Categoria	Operadores
Varredura de dados	Seq Scan, Table Scan, Index Scan, Bitmap Scan
Junção	Hash Join, Nested Loop, Merge Join
Agregação	Hash Aggregate, Group Aggregate
Distribuição	Redistribute Motion, Broadcast Motion, Gather Motion
Outros	Hash, Sort, Limit, Append

Exemplo: lendo um plano de consulta com junção

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.b = t2.b;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.b = t2.b
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.b
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.b
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

Leitura deste plano de baixo para cima:

Table Scan — varre t1 e t2.
Redistribute Motion — redistribui as linhas de ambas as tabelas entre os nós de computação com base no valor hash de b, garantindo que linhas correspondentes fiquem no mesmo nó.
Hash — constrói uma tabela hash em t2 para a junção.
Hash Join — junta t1 e t2 usando a chave hash.
Gather Motion — envia os resultados dos nós de computação para o nó coordenador, que os retorna ao cliente.

Remover operadores de distribuição

Quando o AnalyticDB for PostgreSQL junta ou agrega dados entre nós de computação, ele insere operadores Redistribute Motion ou Broadcast Motion para redistribuir os dados. Esses operadores consomem largura de banda significativa da rede. Alinhar as chaves de distribuição nas colunas de junção elimina a necessidade de redistribuir dados.

Como funciona

Se duas tabelas são unidas pela coluna a e ambas são distribuídas por a, cada nó de computação já contém as linhas correspondentes — nenhuma movimentação de dados é necessária.

Exemplo

SELECT * FROM t1, t2 WHERE t1.a = t2.a;

t1 é distribuída pela coluna a. Se t2 for distribuída pela coluna b (uma incompatibilidade), o AnalyticDB for PostgreSQL precisará redistribuir t2:

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(10 rows)

Se t2 também for distribuída por a, a junção ocorre sem redistribuição:

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

Para corrigir uma incompatibilidade, altere a chave de distribuição de t2:

ALTER TABLE t2 SET DISTRIBUTED BY (a);

Usar tipos de dados correspondentes nas colunas de junção

Juntar colunas de tipos de dados diferentes aciona conversão de tipo, o que força a redistribuição de dados. Use o mesmo tipo de dados em ambos os lados de uma chave de junção.

Conversão explícita de tipo

Uma conversão explícita (cast) na instrução SQL altera a função hash aplicada à coluna, fazendo com que o otimizador redistribua ambas as tabelas:

-- No type conversion: no redistribution
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

-- Explicit cast to numeric: triggers redistribution of both tables
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a::numeric;
                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a::numeric = t2.a::numeric
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.a::numeric
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a::numeric
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

Conversão implícita de tipo

Quando duas colunas de junção usam tipos incompatíveis, o banco de dados converte um dos tipos automaticamente. Por exemplo, timestamp without time zone e timestamp with time zone usam funções hash diferentes. O otimizador recorre ao Broadcast Motion em vez de uma junção baseada em hash:

postgres=# CREATE TABLE t1 (a timestamp without time zone);
CREATE TABLE
postgres=# CREATE TABLE t2 (a timestamp with time zone);
CREATE TABLE

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                               QUERY PLAN
-------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.04..0.11 rows=4 width=16)
   ->  Nested Loop  (cost=0.04..0.11 rows=2 width=16)
         Join Filter: t1.a = t2.a
         ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=8)
         ->  Materialize  (cost=0.04..0.07 rows=1 width=8)
               ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..0.04 rows=1 width=8)
                     ->  Seq Scan on t2  (cost=0.00..0.00 rows=1 width=8)
(7 rows)

Para eliminar conversões implícitas, alinhe os tipos das colunas no momento da definição da tabela. Por exemplo, defina tanto t1.a quanto t2.a como timestamp without time zone.

Localizar assimetria de dados

A assimetria de dados ocorre quando as linhas são distribuídas de forma desigual entre os nós de computação. Uma tabela assimétrica faz com que um único nó realize a maior parte do trabalho enquanto os outros ficam ociosos, o que desacelera toda a consulta.

Verifique a distribuição de linhas entre os nós de computação:

postgres=# SELECT gp_segment_id, count(1) FROM t1 GROUP BY 1 ORDER BY 2 DESC;
 gp_segment_id | count
---------------+-------
             0 | 16415
             2 |    37
             1 |    32
(3 rows)

Esta saída mostra uma assimetria severa: o nó 0 detém 99% das linhas. Corrija isso reatribuindo a chave de distribuição:

-- Option 1: change the distribution key in place
ALTER TABLE t1 SET DISTRIBUTED BY (b);

-- Option 2: recreate the table with a better distribution key
-- Create the new table, bulk-load data, then swap it in place of the original

Escolha uma chave de distribuição com alta cardinalidade e sem valores dominantes para garantir uma distribuição uniforme.

Visualize instruções SQL em execução

Quando muitas consultas são executadas simultaneamente, a instância pode apresentar respostas lentas ou recursos insuficientes. Use a view pg_stat_activity para ver o que está em execução no momento.

postgres=# SELECT * FROM pg_stat_activity;

Campos principais:

Campo	Descrição
`procpid`	ID do processo mestre que executa a consulta
`usename`	Nome de usuário da sessão
`current_query`	Texto da consulta atual
`waiting`	Indica se a consulta está aguardando um bloqueio
`query_start`	Momento em que a consulta iniciou
`backend_start`	Momento em que o processo backend iniciou
`xact_start`	Momento em que a transação atual iniciou
`waiting_reason`	Motivo pelo qual a consulta está aguardando

Filtre apenas as consultas ativas:

SELECT * FROM pg_stat_activity WHERE current_query != '<IDLE>';

Encontre as cinco consultas mais longas:

SELECT current_timestamp - query_start AS runtime,
       datname,
       usename,
       current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>'
ORDER BY runtime DESC
LIMIT 5;

Verificar status de bloqueios

Se uma consulta mantiver um bloqueio por um período prolongado, outras consultas no mesmo objeto aguardarão indefinidamente. Execute a seguinte consulta para ver quais tabelas estão bloqueadas e quais sessões detêm os bloqueios:

SELECT pgl.locktype   AS locktype,
       pgl.database   AS database,
       pgc.relname    AS relname,
       pgl.relation   AS relation,
       pgl.transaction AS transaction,
       pgl.pid        AS pid,
       pgl.mode       AS mode,
       pgl.granted    AS granted,
       pgsa.current_query AS query
FROM pg_locks pgl
JOIN pg_class pgc ON pgl.relation = pgc.oid
JOIN pg_stat_activity pgsa ON pgl.pid = pgsa.procpid
ORDER BY pgc.relname;

Para desbloquear uma consulta em espera, cancele ou encerre a sessão que detém o bloqueio:

-- Cancel the query (does not work if the session is IDLE)
SELECT pg_cancel_backend(pid);

-- Terminate the session and roll back its uncommitted transactions
SELECT pg_terminate_backend(pid);

pg_cancel_backend não surte efeito quando pg_stat_activity.current_query exibe IDLE . Nesse caso, use pg_terminate_backend .

Ative junções de loop aninhado

As junções de loop aninhado (nested loop joins) vêm desativadas por padrão. Para consultas que retornam um pequeno número de linhas — devido a condições de filtro altamente seletivas e uma cláusula LIMIT — ativar junções de loop aninhado pode reduzir significativamente o tempo de consulta.

Quando ativar

Essa otimização é eficaz quando:

Uma condição de filtro em uma tabela retorna muito poucas linhas.
Uma cláusula LIMIT restringe ainda mais o conjunto de resultados.
Existem índices disponíveis nas colunas de junção de ambas as tabelas.