Esta página foi traduzida pela API Cloud Translation.

Receber e analisar planos de explicação do AlloyDB

É possível identificar gargalos de desempenho e otimizar as operações do banco de dados AlloyDB para PostgreSQL ao receber e analisar planos de execução. Um plano de execução ou EXPLAIN é uma representação detalhada de como o mecanismo de banco de dados do AlloyDB pretende executar uma consulta SQL. O plano de execução consiste em uma árvore de nós que descreve a sequência de operações, como verificações de tabela, junções, classificação e agregações, que o banco de dados do AlloyDB realiza para recuperar os dados solicitados. Cada etapa desse plano é chamada de nó.

Um plano de execução é obtido usando o comando EXPLAIN, que retorna o plano gerado pelo planejador de consultas do AlloyDB para uma determinada instrução SQL. Um planejador de consultas, também conhecido como otimizador, determina a maneira mais eficiente de executar uma determinada consulta SQL.

Os planos de execução incluem os seguintes componentes:

Nós de plano: representam as diferentes etapas na execução da consulta, como uma verificação, uma junção ou uma operação de classificação.
Tempo de execução: o plano EXPLAIN inclui o tempo de execução estimado ou real de cada etapa, o que ajuda a identificar gargalos no banco de dados.
Uso do buffer: mostra quantos dados são lidos do disco em comparação com o cache, o que ajuda a identificar problemas de leitura do disco.
Configurações de parâmetros: o plano mostra as configurações de parâmetros que são válidas durante a execução da consulta.

O PostgreSQL e, por extensão, o AlloyDB, oferecem suporte a planos de execução para as seguintes instruções:

SELECT
INSERT
UPDATE
DECLARE CURSOR
CREATE AS
CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
EXECUTE

Antes de começar

Você precisa ter um cluster e uma instância do AlloyDB. Para mais informações, consulte Criar um cluster e a instância principal dele.

Gerar um plano de execução

Você gera um plano de execução de um aplicativo cliente, como psql, pgAdmin ou DBeaver. O AlloyDB é compatível com a geração de planos de execução em formato de texto ou JSON.

Para gerar um plano de execução, siga estas etapas:

Conecte um cliente psql a uma instância.

Para gerar um plano de execução em formato de texto, execute o seguinte comando:

SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format text)

Para gerar um plano de execução em formato JSON, execute o seguinte comando:
```
SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format json)
```
O comando EXPLAIN inclui todas as opções disponíveis (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing e summary) para gerar um plano de execução detalhado de uma determinada consulta em formato de texto ou JSON. A opção analyze significa que a consulta é executada para fornecer estatísticas de tempo de execução reais, bem como as estimativas do planejador de consultas.

Ver e analisar dados do plano EXPLAIN

Depois de receber um plano de execução, você pode conferir e analisar os resultados.

Por padrão, a saída EXPLAIN mostra a atividade de consulta do lado do servidor. Para medir o tempo de ida e volta de ponta a ponta, use a opção /timing no psql e despeje os resultados em /dev/null.

Para conferir o plano de execução gerado, use o comando EXPLAIN antes da consulta SQL.

EXPLAIN SELECT...: mostra o plano que o otimizador escolheria sem executar a consulta.
EXPLAIN ANALYZE SELECT...: executa a consulta e mostra o plano previsto e as estatísticas de execução reais, incluindo tempos de execução e contagens de linhas verdadeiros.

EXPLAIN sem ANALYZE

Para mostrar os custos estimados do planejador de consultas, execute uma instrução EXPLAIN sem a opção ANALYZE .

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27)
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(3 rows)

A saída do plano inclui os seguintes dados:

cost = 0.00..1735481.00: o primeiro número indica o custo para recuperar a primeira linha. O segundo número indica o custo para recuperar a última linha.
rows = 100000000: é o número estimado de linhas que a consulta retorna.
width = 27: é a largura estimada da linha retornada, o que ajuda a entender os blocos acessados.

Opção ANALYZE

Para mostrar estatísticas de execução reais e estimativas, adicione a opção ANALYZE.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.165..9342.424 rows=100000001 loops=1)
 Planning Time: 0.025 ms
 Execution Time: 13674.794 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(5 rows)

A saída do plano inclui os seguintes dados:

tempo real (em ms) = 0,165..9342,424: mostra o tempo real para retornar a primeira linha e o tempo total para retornar todas as linhas.
rows = 100000001: este é o número real de linhas retornadas.
loops = 1: esse valor é importante para nós de loop aninhado. Ele mostra o tempo médio por loop se loops for maior que 1.
Tempo de planejamento: 0,025 ms: indica o tempo que o planejador levou para determinar o caminho de execução.
Tempo de execução: 13674,794 ms: indica o tempo que a execução levou depois que o planejador determinou o caminho.
Tempo total de execução: a soma de Planning Time e Execution Time. (0,025 + 13674,794 = 13674,819)

Opção VERBOSE

Para adicionar mais informações ao plano de execução, use a opção VERBOSE. No exemplo a seguir, o uso de VERBOSE adiciona qualificações de esquema aos nomes de tabelas e mostra um identificador de consulta interna que pode ser usado para correlacionar sua consulta com outras ferramentas de monitoramento.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.164..6568.938 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10875.894 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

A saída do plano inclui os seguintes dados:

Nó de saída: lista as colunas incluídas na consulta. Às vezes, o planejador inclui mais colunas do que o solicitado se determinar que deixá-las de lado é mais caro.
Identificador da consulta: o identificador do PostgreSQL que mapeia para pg_stat_statements.
ID da consulta do AlloyDB: o identificador de consulta do AlloyDB que pode ser usado para correlacionar informações de insights de consultas

Opção COLUMNAR ENGINE

Para mostrar informações sobre o mecanismo de colunas do AlloyDB, adicione a opção COLUMNAR_ENGINE.

Se a tabela não estiver presente no mecanismo colunar, consulte a coluna Verificação colunar no plano de explicação a seguir para conferir o status.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.009..6328.154 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10673.310 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

Se a tabela estiver presente no mecanismo colunar e for usada, uma verificação personalizada será indicada junto com estatísticas sobre como o mecanismo colunar é usado.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test where product_id = 1;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Append  (cost=20.00..27438.78 rows=1166668 width=27) (actual time=0.066..377.029 rows=1000290 loops=1)
   ->  Custom Scan (columnar scan) on public.index_advisor_test  (cost=20.00..27437.66 rows=1166667 width=27) (actual time=0.065..296.904 rows=1000290 loops=1)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
         Rows Removed by Columnar Filter: 98999711
         Bytes fetched from storage cache: 774835915
         Columnar cache search mode: native
         Swap-in Time: 92.708 ms
   ->  Seq Scan on public.index_advisor_test  (cost=0.00..1.11 rows=1 width=27) (never executed)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
 Query Identifier: -4660018746142248761
 Planning Time: 0.217 ms
 Execution Time: 421.114 ms
 AlloyDB query id: 13855683355620344431
 AlloyDB plan id: 2126918133221480510

A saída do plano inclui os seguintes dados:

Filtro de consulta (predicado): mostra o filtro aplicado, se você usar um.
Linhas removidas pelo filtro colunar: indica o número de linhas que o filtro colunar removeu.
Bytes buscados do cache de armazenamento: mostra o número de bytes recuperados do cache de armazenamento.
Tempo de troca: é o tempo necessário para trocar dados do cache de transbordamento colunar (SSD) se a relação não couber na memória.

Opção CONFIGURAÇÕES

Para mostrar qualquer configuração de sessão, banco de dados ou global não padrão que o planejador usa, adicione a opção SETTINGS.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS) select * from public.index_advisor_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.007..6366.249 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10727.068 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

Opção BUFFERS

Para mostrar informações sobre a fonte de dados, use a palavra-chave BUFFERS. A contagem de BUFFERS é acumulada de todas as etapas, não apenas de uma etapa específica do plano.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.111..10007.193 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2588.597
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning:
   Buffers: shared hit=58 read=2, ultra fast cache hit=2
   I/O Timings: shared read=0.215
 Planning Time: 0.410 ms
 Execution Time: 14825.271 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

As informações do buffer incluem o seguinte:

acerto compartilhado: o número de blocos de dados de 8 kB encontrados no cache de buffer compartilhado principal do PostgreSQL.
leitura compartilhada: o número de blocos de dados de 8 kB lidos do sistema operacional. Isso geralmente indica E/S de disco.
dirtied: o número de blocos não modificados anteriormente que a consulta mudou (mudanças no mapa de visibilidade).
written: o número de blocos sujos anteriormente removidos do cache por esse back-end durante o processamento da consulta, geralmente devido a alterações no bit de dica ou no mapa de visibilidade e liberados para o disco.
Ocorrência em cache ultrarrápida: o número de blocos recuperados do cache ultrarrápido.
Tempos de E/S: a duração de qualquer E/S de disco ou SSD em milissegundos.
Planejamento: atividade de buffer durante a fase de planejamento, como leitura de metadados ou estatísticas de tabelas de catálogo.
Tempo gasto em E/S durante o planejamento: mostra o tempo de E/S se alguma leitura de metadados necessária do disco.

Opção WAL

Para fornecer informações sobre a atividade de registro prévio de gravação (WAL, na sigla em inglês), use a opção WAL .

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS, WAL) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.010..10147.314 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2590.410
   WAL: records=18 bytes=5178
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning Time: 0.030 ms
 Execution Time: 15033.004 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

As informações do WAL incluem o seguinte:

Registros WAL: o número de registros WAL lidos para manter a consistência.
Bytes de WAL: o número de bytes lidos do WAL para manter a consistência.
Registros modificados, mas ainda não verificados: indica registros modificados, mas ainda não verificados.

A seguir

Saiba mais sobre os tipos de nós do plano de execução.