Plano de consulta - Amazon Redshift
Operadores EXPLAINJunções em EXPLAIN

Plano de consulta

É possível usar o plano de consulta para obter informações sobre operações individuais necessárias para executar uma consulta. Antes de trabalhar com um plano de consulta, recomendamos que você primeiro entenda como o Amazon Redshift lida com o processamento de consultas e a criação de planos de consulta. Para ter mais informações, consulte Planejamento de consulta e fluxo de trabalho de execução.

Para criar um plano de consulta, execute o comando EXPLAIN seguido pelo texto real da consulta. O plano de consulta oferece as seguintes informações:

  • Quais operações o mecanismo de execução executa, lendo os resultados de baixo para cima.

  • Que tipo de etapa cada operação executa.

  • Quais tabelas e colunas são usadas em cada operação.

  • Quantos dados são processados em cada operação, em termos de número de linhas e largura de dados em bytes.

  • O custo relativo da operação. Custo é uma medida que compara os tempos relativos de execução das etapas em um plano. O custo não fornece qualquer informação precisa sobre o real tempo de execução ou consumo de memória, nem fornece uma comparação significativa entre planos de execução. Ele fornece uma indicação de quais operações em uma consulta estão consumindo a maioria dos recursos.

O comando EXPLAIN não executa a consulta de fato. Ele mostra apenas o plano que o Amazon Redshift executa se a consulta for executada nas condições operacionais atuais. Se você alterar o esquema ou os dados de uma tabela e executar ANALYZE novamente para atualizar os metadados estatísticos, o plano de consulta poderá ser diferente.

A saída do plano de consulta pelo comando EXPLAIN é uma exibição de alto nível simplificada da execução da consulta. Ela não ilustra os detalhes do processamento paralelo da consulta. Para visualizar informações detalhadas, execute a própria consultas e obtenha as informações de resumo na visualização SVL_QUERY_SUMMARY ou SVL_QUERY_REPORT. Para obter mais informações sobre como usar essas visualizações, consulte Analisar o resumo da consulta.

O seguinte exemplo mostra a saída de EXPLAIN para uma consulta GROUP BY simples na tabela EVENT:

explain select eventname, count(*) from event group by eventname;

                            QUERY PLAN
-------------------------------------------------------------------
XN HashAggregate  (cost=131.97..133.41 rows=576 width=17)
  ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=17)

EXPLAIN retorna as seguintes métricas para cada operação:

Custo

Um valor relativo que é útil para comparar operações em um plano. Custo consiste em dois valores decimais separados por dois pontos, por exemplo cost=131.97..133.41. O primeiro valor, nesse caso 131,97, fornece o custo relativo de retornar a primeira linha para essa operação. O segundo valor, nesse caso 133,41, fornece o custo relativo de concluir a operação. Os custos em plano de consulta são cumulativos à medida que você lê o plano, portanto o custo HashAggregate neste exemplo (131,97..133,41) inclui o custo da Seq Scan abaixo dele (0.00..87.98).

Linhas

O número previsto de linhas a retornar. Neste exemplo, a previsão de retorno da varredura é 8.798 linhas. O operador HashAggregate deve, sozinho, retornar 576 linhas (depois que os nomes de evento duplicados são descartados do conjunto de resultados).

nota

A estimativa de linhas baseia-se nas estatísticas disponíveis geradas pelo comando ANALYZE. Se ANALYZE não tiver sido executado recentemente, a estimativa será menos confiável.

Largura

A largura estimada da linha média, em bytes. Neste exemplo, a previsão de largura da linha média é de 17 bytes.

Operadores EXPLAIN

Esta seção descreve resumidamente os operadores que você verá com mais frequência na saída de EXPLAIN. Para uma lista completa dos operadores, consulte EXPLAIN na seção de comandos SQL.

Operador de varredura sequencial

O operador de varredura sequencial (Seq Scan) indica uma varredura de tabela. A Seq Scan faz a varredura de cada coluna na tabela sequencialmente do começo ao fim e avalia as restrições da consulta (na cláusula WHERE) para cada linha.

Operadores de junção

O Amazon Redshift seleciona operadores de junção com base no design físico das tabelas que estão sendo juntadas, a localização dos dados necessários para a junção e os requisitos específicos da própria consulta.

  • Loop aninhado

    Um loop aninhado, a junção menos ideal, é usado sobretudo para junções cruzadas (produtos cartesianos) e algumas junções de desigualdade.

  • Hash e junção hash

    Normalmente mais rápida que uma junção de loop aninhado, hash e junção hash são usadas para junções internas e junções externas da esquerda e direita. Esses operadores são usados para juntar tabelas onde as colunas de junção não são chaves de distribuição e chaves de classificação. O operador hash cria a tabela hash para a junção da tabela interna; o operador de junção hash lê a tabela externa, hash a coluna de junção e localiza correspondências na tabela hash interna.

  • Merge Join

    Normalmente a junção mais rápida, uma junção de mesclagem é usada para junções internas e externas. A junção de mesclagem não é usada para junções completas. Este operador é usado para juntar tabelas onde as colunas de junção são chaves de distribuição e chaves de classificação e quando menos que 20 por cento das tabelas para junção não estão classificadas. Ele lê duas tabelas classificadas na ordem e localiza as linhas correspondentes. Para ver a porcentagem de linhas não classificadas, consulte a tabela de sistema SVV_TABLE_INFO.

  • Junção espacial

    Normalmente, uma junção rápida com base na proximidade de dados espaciais, usados em tipos de dados GEOMETRY e GEOGRAPHY.

Operadores de agregação

O plano de consulta usa os seguintes operadores em consultas que envolvem funções agregadas e operações GROUP BY.

  • Aggregate

    Operador para funções agregadas escalares, tais como AVG e SUM.

  • HashAggregate

    Operador para funções agregadas agrupadas não classificadas.

  • GroupAggregate

    Operador para funções agregadas agrupadas classificadas.

Operadores de classificação

O plano de consulta usa os seguintes operadores quando as consultas precisam classificar ou mesclar conjuntos de resultados.

  • Sort

    Avalia a cláusula ORDER BY e outras operações de classificação, tais como classificações exigidas por consultas e junções UNION, consultas SELECT DISTINCT e funções de janela.

  • Merge

    Produz resultados finais classificados de acordo com os resultados classificados intermediários derivados de operações paralelas.

Operadores UNION, INTERSECT e EXCEPT

O plano de consulta usa os seguintes operadores para consultas que envolvem operações de conjunto com UNION, INTERSECT e EXCEPT.

  • Subconsulta

    Usada para executar consultas UNION.

  • Hash Intersect Distinct

    Usado para executar consultas INTERSECT .

  • SetOp Except

    Usada para executar consultas EXCEPT (ou MINUS).

Outros operadores

Os operadores a seguir também aparecem frequentemente na saída de EXPLAIN para consultas de rotina.

  • Unique

    Remove duplicidades para consultas SELECT DISTINCT e UNION.

  • Limite

    Processa a cláusula LIMIT.

  • Window

    Executa funções de janela.

  • Resultado

    Executa funções escalares que não envolvem qualquer acesso à tabela.

  • Subplan

    Usado para determinadas subconsultas.

  • Rede

    Envia resultados intermediários ao nó de liderança para processamento adicional.

  • Materialize

    Salva linhas para entrada em junções de loop aninhado e algumas junções de mesclagem.

Junções em EXPLAIN

O otimizador de consulta usa diferentes tipos de junção para recuperar dados da tabela, dependendo da estrutura da consulta e das tabelas subjacentes. A saída de EXPLAIN menciona o tipo de junção, as tabelas usadas e a forma que os dados da tabela estão distribuídos pelo cluster para descrever como a consulta é processada.

Exemplos de tipos de junção

Os seguintes exemplos mostram os diferentes tipos de junção que o otimizador de consulta pode utilizar. O tipo de junção usado no plano de consulta depende do design físico das tabelas envolvidas.

Exemplo: junção hash de duas tabelas

A seguinte consulta faz a junção de EVENT e CATEGORY na coluna CATID. CATID é a chave de distribuição e classificação para CATEGORY, mas não para EVENT. Uma junção hash é realizada com EVENT como a tabela externa e CATEGORY como a tabela interna. Como CATEGORY é a tabela menor, o planejador transmite uma cópia dela para os nós de computação durante o processamento da consulta usando DS_BCAST_INNER. O custo da junção neste exemplo corresponde à maioria do custo cumulativo do plano.

explain select * from category, event where category.catid=event.catid;

                               QUERY PLAN
-------------------------------------------------------------------------
 XN Hash Join DS_BCAST_INNER  (cost=0.14..6600286.07 rows=8798 width=84)
   Hash Cond: ("outer".catid = "inner".catid)
   ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=35)
   ->  XN Hash  (cost=0.11..0.11 rows=11 width=49)
         ->  XN Seq Scan on category  (cost=0.00..0.11 rows=11 width=49)
nota

Os recuos alinhados para operadores na saída de EXPLAIN às vezes indicam que aquelas operações não dependem uma das outras e podem começar em paralelo. No exemplo anterior, embora a varredura na tabela EVENT e a operação de hash estejam alinhadas, a varredura de EVENT deve esperar até que a operação de hash seja totalmente concluída.

Exemplo: junção de mesclagem de duas tabelas

A seguinte consulta também usa SELECT *, mas faz a junção de SALES e LISTING na coluna LISTID, onde LISTID foi definida tanto como a chave de distribuição quanto a chave de classificação para ambas as tabelas. Um junção de mesclagem é escolhida e nenhuma redistribuição de dados é necessária para a junção (DS_DIST_NONE).

explain select * from sales, listing where sales.listid = listing.listid;
QUERY PLAN
-----------------------------------------------------------------------------
XN Merge Join DS_DIST_NONE  (cost=0.00..6285.93 rows=172456 width=97)
  Merge Cond: ("outer".listid = "inner".listid)
  ->  XN Seq Scan on listing  (cost=0.00..1924.97 rows=192497 width=44)
  ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=53)

O seguinte exemplo demonstra os diferentes tipos de junção dentro da mesma consulta. Como no exemplo anterior, a junção de mesclagem de SALES e LISTING ocorre, mas a junção da terceira tabela, EVENT, deve ser uma junção hash com os resultados da junção de mesclagem. Outra vez, a junção hash incorre em custos de transmissão.

explain select * from sales, listing, event
where sales.listid = listing.listid and sales.eventid = event.eventid;
                                  QUERY PLAN
----------------------------------------------------------------------------
XN Hash Join DS_BCAST_INNER  (cost=109.98..3871130276.17 rows=172456 width=132)
  Hash Cond: ("outer".eventid = "inner".eventid)
  ->  XN Merge Join DS_DIST_NONE  (cost=0.00..6285.93 rows=172456 width=97)
        Merge Cond: ("outer".listid = "inner".listid)
        ->  XN Seq Scan on listing  (cost=0.00..1924.97 rows=192497 width=44)
        ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=53)
  ->  XN Hash  (cost=87.98..87.98 rows=8798 width=35)
        ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=35)

Exemplo: junção, agregação e classificação

A seguinte consulta executa a junção hash das tabelas SALES e EVENT, seguidas por operações de agregação e classificação para contabilizar a função SUM agrupada e a cláusula ORDER BY. O operador de classificação inicial executa em paralelo nos nós de computação. Então, o operador de rede envia os resultados ao nó de liderança, onde o operador de mesclagem produz os resultados classificados finais.

explain select eventname, sum(pricepaid) from sales, event 
where sales.eventid=event.eventid group by eventname
order by 2 desc;
                                           QUERY PLAN
---------------------------------------------------------------------------------
 XN Merge  (cost=1002815366604.92..1002815366606.36 rows=576 width=27)
  Merge Key: sum(sales.pricepaid)
  ->  XN Network  (cost=1002815366604.92..1002815366606.36 rows=576 width=27)
        Send to leader
        ->  XN Sort  (cost=1002815366604.92..1002815366606.36 rows=576 width=27)
              Sort Key: sum(sales.pricepaid)
              ->  XN HashAggregate  (cost=2815366577.07..2815366578.51 rows=576 width=27)
                    ->  XN Hash Join DS_BCAST_INNER  (cost=109.98..2815365714.80 rows=172456 width=27)
                          Hash Cond: ("outer".eventid = "inner".eventid)
                          ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=14)
                          ->  XN Hash  (cost=87.98..87.98 rows=8798 width=21)
                                ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=21)

Redistribuição de dados

A saída EXPLAIN para junções também especifica um método de como os dados serão movidos em torno de um cluster para facilitar a junção. Esta movimentação de dados pode ser uma transmissão ou uma redistribuição. Em uma transmissão, os valores dos dados de um lado de uma junção são copiados de cada nó de computação para todos os outros nós de computação, de forma que todos os nós de computação tenham uma cópia completa dos dados. Em uma redistribuição, os valores dos dados participantes são enviados de sua fatia atual para uma nova fatia (possivelmente em um nó diferente). Os dados são tipicamente redistribuídos para corresponder à chave de distribuição de outra tabela que participa da junção se aquela chave de distribuição for uma das colunas de junção. Se nenhuma das tabelas possui chaves de distribuição em uma das colunas de junção, ambas as tabelas são distribuídas ou a tabela interna é transmitida para cada nó.

A saída EXPLAIN também faz referência à tabelas internas e externas. A varredura da tabela interna é realizada primeiro e aparece mais próximo da parte inferior do plano de consulta. A tabela interna é a tabela que é examinada quanto a correspondências. Ela é geralmente mantida na memória e normalmente é a tabela de origem para hashing e, se possível, é a menor das duas tabelas da junção. A tabela externa é a origem das linhas para correspondência na tabela interna. Ela é geralmente lida a partir do disco. O otimizador de consulta escolhe a tabela interna e externa com base em estatísticas do banco de dados e do comando ANALYZE executado mais recentemente. A ordem das tabelas na cláusula FROM de uma consulta não determina qual tabela é interna e qual é externa.

Use the following attributes in query plans to identify how data is moved to facilitate a query:

  • DS_BCAST_INNER

    Uma cópia de toda a tabela interna é transmitida a todos os nós de computação.

  • DS_DIST_ALL_NONE

    Nenhuma redistribuição é necessária, pois a tabela interna já foi distribuída a todos os nós usando DISTSTYLE ALL.

  • DS_DIST_NONE

    Nenhuma tabela é redistribuída. As junções colocadas são possíveis pois as fatias correspondentes são juntadas sem movimentação de dados entre nós.

  • DS_DIST_INNER

    A tabela interna é redistribuída.

  • DS_DIST_OUTER

    A tabela externa é redistribuída.

  • DS_DIST_ALL_INNER

    Toda a tabela interna é redistribuída a uma única fatia, pois a tabela externa usa DISTSTYLE ALL.

  • DS_DIST_BOTH

    Ambas as tabelas são redistribuídas.