Esta página foi traduzida pela API Cloud Translation.

Obtenha e analise planos de explicação do AlloyDB

Pode identificar gargalos de desempenho e otimizar as operações da base de dados do AlloyDB for PostgreSQL obtendo e analisando planos de execução. Um plano de execução ou um plano EXPLAIN é uma representação detalhada de como o motor da base de dados do AlloyDB pretende executar uma consulta SQL. O plano de execução compreende uma árvore de nós que descreve a sequência de operações, como verificações de tabelas, junções, ordenação e agregações, que a base de dados do AlloyDB executa para obter os dados pedidos. Cada passo neste plano é denominado nó.

Pode obter um plano de execução através do comando EXPLAIN, que devolve o plano que o planeador de consultas do AlloyDB gera para uma determinada declaração SQL. Um planeador de consultas, também conhecido como otimizador, determina a forma mais eficiente de executar uma determinada consulta SQL.

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

Nós do plano: representam os diferentes passos na execução da consulta, como uma análise, uma junção ou uma operação de ordenação.
Tempo de execução: o plano EXPLAIN inclui o tempo de execução estimado ou real para cada passo, o que ajuda a identificar gargalos da base de dados.
Utilização da memória intermédia: mostra a quantidade de dados lidos do disco em comparação com a cache, o que ajuda a identificar problemas de leitura do disco.
Definições de parâmetros: o plano mostra as definições de parâmetros que são eficazes durante a execução da consulta.

O PostgreSQL e, por extensão, o AlloyDB, suportam planos de execução para as seguintes declarações:

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

Antes de começar

Tem de ter um cluster e uma instância do AlloyDB. Para mais informações, consulte o artigo Crie um cluster e a respetiva instância principal.

Gere um plano de execução

Gera um plano de execução a partir de uma aplicação cliente, como psql, pgAdmin ou DBeaver. O AlloyDB suporta a geração de planos de execução em formato de texto ou JSON.

Para gerar um plano de execução, siga estes passos:

Associe 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 para uma determinada consulta no 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 planeador de consultas.

Veja e analise os dados do plano EXPLAIN

Depois de obter um plano de execução, pode ver e analisar os resultados.

Por predefinição, a saída EXPLAIN mostra a atividade de consulta do lado do servidor. Para medir o tempo de resposta de ida e volta completo, use a opção /timing no psql e transfira os resultados para /dev/null.

Para ver o plano de execução que gerou, use o comando EXPLAIN antes da sua 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 os tempos de execução reais e as contagens de linhas.

EXPLAIN sem ANALYZE

Para mostrar os custos de consulta estimados do planeador de consultas, execute uma declaraçã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 de obtenção da primeira linha. O segundo número indica o custo de obtenção da última linha.
rows = 100000000: este é o número estimado de linhas devolvidas pela consulta.
width = 27: esta é a largura estimada da linha devolvida, o que ajuda a compreender os blocos acedidos.

Opção ANALYZE

Para apresentar estatísticas de execução reais, bem como estimativas de execução, 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: isto mostra o tempo real para devolver a primeira linha e o tempo total para devolver todas as linhas.
rows = 100000001: este é o número real de linhas devolvidas.
loops = 1: este valor é importante para nós de ciclo aninhados. Mostra o tempo médio por repetição se loops for superior a 1.
Tempo de planeamento: 0,025 ms: isto indica o tempo que o planeador demorou a determinar o caminho de execução.
Tempo de execução: 13674,794 ms: isto indica o tempo que a execução demorou após o planeador determinar o caminho.
Tempo de execução total: 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 seguinte, a utilização de VERBOSE adiciona qualificações de esquema aos nomes das tabelas e mostra um identificador de consulta interno que pode usar para correlacionar a sua consulta com outras ferramentas de monitorização.

(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: apresenta as colunas incluídas na consulta. Por vezes, o planeador inclui mais colunas do que o pedido se determinar que deixar essas colunas de fora é mais caro.
Identificador da consulta: o identificador do PostgreSQL que é mapeado para pg_stat_statements.
ID da consulta do AlloyDB: o identificador da consulta do AlloyDB que pode ser usado para correlacionar informações de estatísticas de consultas

Opção COLUMNAR ENGINE

Para apresentar informações para o motor colunar do AlloyDB, adicione a opção COLUMNAR_ENGINE.

Se a tabela não estiver presente no motor de colunas, consulte a coluna Verificação de colunas no seguinte plano de explicação para ver o estado.

(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 motor de colunas e o motor de colunas for usado, é indicado um exame personalizado, juntamente com estatísticas sobre a forma como o motor de colunas é 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 usar um.
Linhas removidas pelo filtro de colunas: indica o número de linhas que o filtro de colunas removeu.
Bytes obtidos da cache de armazenamento: mostra o número de bytes obtidos da cache de armazenamento.
Tempo de substituição: este é o tempo necessário para substituir dados da cache de transbordo colunar (SSD) se a relação não couber na memória.

Opção DEFINIÇÕES

Para mostrar quaisquer definições de sessão, base de dados ou globais não predefinidas que o planeador use, 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 apresentar informações sobre a origem de dados, use a palavra-chave BUFFERS. A contagem de BUFFERS acumula-se a partir de todos os passos, não apenas de um passo específico no 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:

shared hit: o número de blocos de dados de 8 kB encontrados na cache de buffer partilhada principal do PostgreSQL.
shared read: o número de blocos de dados de 8 kB lidos do sistema operativo. Isto indica frequentemente E/S de disco.
dirtied: o número de blocos não modificados anteriormente que a consulta alterou (alterações do mapa de visibilidade).
written: o número de blocos previamente alterados removidos da cache por este backend durante o processamento de consultas, normalmente devido a alterações de bits de sugestões ou do mapa de visibilidade e descarregados para o disco.
ultra fast cache hit: o número de blocos obtidos a partir da cache ultrarrápida.
Tempos de E/S: a duração de qualquer E/S de disco ou SSD em milissegundos.
Planeamento: atividade de buffer durante a fase de planeamento, como a leitura de metadados ou estatísticas de tabelas de catálogos.
Tempo gasto em I/O durante o planeamento: isto mostra o tempo de I/O se forem necessários metadados para leitura do disco.

Opção WAL

Para fornecer informações sobre a atividade de registo antecipado (WAL), 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:

Registos WAL: o número de registos WAL lidos para manter a consistência.
Bytes de WAL: o número de bytes lidos do WAL para manter a consistência.
Registos que foram modificados, mas ainda não foram marcados: isto indica registos que foram modificados, mas ainda não foram marcados.

O que se segue?

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

Obtenha e analise planos de explicação do AlloyDB Mantenha tudo organizado com as coleções Salve e categorize o conteúdo com base nas suas preferências.

Antes de começar

Gere um plano de execução

Veja e analise os dados do plano EXPLAIN

EXPLAIN sem ANALYZE

Opção ANALYZE

Opção VERBOSE

Opção COLUMNAR ENGINE

Opção DEFINIÇÕES

Opção BUFFERS

Opção WAL

O que se segue?

Obtenha e analise planos de explicação do AlloyDB