# Trabalhar com os planos EXPLAIN do Aurora DSQL O Aurora DSQL usa uma estrutura de plano EXPLAIN semelhante à do PostgreSQL, mas com adições importantes que refletem a respectiva arquitetura distribuída e modelo de execução. Nesta documentação, forneceremos uma visão geral dos planos EXPLAIN do Aurora DSQL, destacando as semelhanças e diferenças em comparação ao PostgreSQL. Abordaremos os vários tipos de operações de verificação disponíveis no Aurora DSQL e ajudaremos você a entender o custo da realização de suas consultas. ## Planos EXPLAIN do PostgreSQL versus Aurora DSQL O Aurora SQL é desenvolvido com base no banco de dados PostgreSQL e compartilha a maioria das estruturas de planos com o PostgreSQL, mas tem diferenças arquitetônicas importantes que afetam a realização e a otimização de consultas: | Recurso | PostgreSQL | Aurora DSQL | | --- | --- | --- | | Armazenamento de dados | Armazenamento heap | Sem heap, todas as linhas são indexadas por um identificador exclusivo. | | Chave primária | O índice da chave primária é separado dos dados da tabela. | O índice da chave primária é a tabela com todas as colunas extras como colunas INCLUDE. | | Índices secundários | Índices secundários padrão. | Funciona da mesma forma que o PostgreSQL, com a capacidade de incluir colunas que não sejam chave. | | Recursos de filtragem | Condição de índice, filtro heap. | Condição de índice, filtro de armazenamento, filtro do processador de consultas. | | Tipos de varredura | Verificação sequencial, verificação de índice, Verificação somente de índice. | Verificação sequencial, verificação de índice, Verificação somente de índice. | | Realização de consultas | Local para o banco de dados. | Distribuído (computação e armazenamento são separados). | O Aurora DSQL armazena os dados da tabela diretamente na ordem da chave primária, em vez de em um heap separado. Cada linha é identificada por uma chave exclusiva, normalmente a chave primária, que permite que o banco de dados otimize as pesquisas com maior eficiência. A diferença arquitetônica explica por que o Aurora DSQL geralmente usa verificações somente de índice nos casos em que o PostgreSQL pode escolher a verificação sequencial. Outra distinção importante é que o Aurora DSQL separa a computação do armazenamento, permitindo que os filtros sejam aplicados mais cedo no caminho de execução a fim de reduzir a movimentação de dados e melhorar a performance. Para saber mais sobre o uso dos planos EXPLAIN com o PostgreSQL, consulte a [documentação sobre o EXPLAIN do PostgreSQL](https://www.postgresql.org/docs/current/using-explain.html). ## Elementos-chave nos planos EXPLAIN do Aurora DSQL Os planos EXPLAIN do Aurora DSQL fornecem informações detalhadas sobre como as consultas são realizadas, incluindo onde a filtragem ocorre e quais colunas são recuperadas do armazenamento. A compreensão dessa saída ajuda a otimizar a performance da consulta. Condição de índice Condições usadas para navegar no índice. A filtragem mais eficiente que reduz os dados verificados. No Aurora DSQL, as condições de índice podem ser aplicadas em várias camadas do plano de execução. Projeções Colunas recuperadas do armazenamento. Um menor número de projeções significa melhor performance. Filtros de armazenamento Condições aplicadas em nível de armazenamento. Mais eficiente do que os filtros do processador de consultas. Filtros do processador de consultas Condições aplicadas em nível de processador de consultas. Requer a transferência de todos os dados antes da filtragem, o que causa maior movimentação de dados e sobrecarga de processamento. ## Filtros no Aurora DSQL O Aurora DSQL separa a computação do armazenamento, o que significa que o ponto em que os filtros são aplicados durante a realização da consulta tem um impacto significativo na performance. Os filtros aplicados antes da transferência de grandes volumes de dados reduzem a latência e melhoram a eficiência. Quanto mais cedo um filtro for aplicado, menos dados precisarão ser processados, movidos e verificados, gerando consultas mais rápidas. O Aurora DSQL pode aplicar filtros em vários estágios no caminho da consulta. Compreender esses estágios é fundamental para interpretar os planos de consulta e otimizar a performance. | Nível | Tipo de filtro | Descrição | | --- | --- | --- | | 1 | Condição de índice | Aplicado durante a verificação do índice. Limita o volume de dados lidos do armazenamento e reduz os dados enviados à camada de computação. | | 2 | Filtros de armazenamento | Aplicado depois que os dados são lidos do armazenamento, mas antes de serem enviados à computação. Um exemplo aqui é um filtro em uma coluna de inclusão de um índice. Reduz a transferência de dados, mas não a quantidade lida. | | 3 | Filtros do processador de consultas | Aplicado após os dados chegarem à camada computacional. Todos os dados devem ser transferidos primeiro, o que aumenta a latência e o custo. Atualmente, o Aurora DSQL não pode realizar todas as operações de filtragem e projeção no armazenamento, portanto, algumas consultas podem ser forçadas a recorrer a esse tipo de filtragem. | # Ler os planos EXPLAIN do Aurora DSQL Entender como ler os planos EXPLAIN é fundamental para otimizar a performance das consultas. Nesta seção, vamos examinar exemplos reais de planos de consulta do Aurora DSQL, mostrar como os diferentes tipos de verificação se comportam, explicar onde os filtros são aplicados e destacar as oportunidades de otimização. ## Tabelas de amostra usadas nesses exemplos Os exemplos abaixo fazem referência a duas tabelas: `transaction` e `account`. A tabela `transaction` não tem uma chave primária, o que faz com que o Aurora DSQL realize varreduras completas da tabela ao consultá-la. A tabela `account` tem um índice no `customer_id`. Esse índice inclui `balance` e `status` como colunas de cobertura, o que permite que determinadas consultas sejam atendidas diretamente do índice sem serem lidas na tabela base. No entanto, o índice não inclui `created_at`, portanto, as consultas que fazem referência a essa coluna exigem acesso adicional à tabela. ``` CREATE TABLE transaction ( account_id uuid, transaction_date timestamp, description text ); CREATE TABLE account ( customer_id uuid, balance numeric, status varchar, created_at timestamp ); CREATE INDEX ASYNC idx1 ON account (customer_id) INCLUDE (balance, status); ``` ## Exemplo de verificação completa O Aurora DSQL tem tanto verificações sequenciais, que são funcionalmente idênticas ao PostgreSQL, quanto verificações completas. A única diferença entre elas é que as verificações completas podem utilizar filtragem extra no armazenamento. Por isso, quase sempre ela é selecionada em detrimento de verificações sequenciais. Devido à semelhança, abordaremos apenas exemplos das verificações completas mais interessantes. As verificações completas serão usadas principalmente em tabelas sem chave primária. Como as chaves primárias do Aurora DSQL são, por padrão, índices de cobertura completa, o Aurora DSQL provavelmente usará verificações somente de índice na chave primária em muitas situações em que o PostgreSQL usaria uma verificação sequencial. Como acontece com a maioria dos outros bancos de dados, uma tabela sem índices será mal escalada. ``` EXPLAIN SELECT account_id FROM transaction WHERE transaction_date > '2025-01-01' AND description LIKE '%external%'; ``` ``` QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Full Scan (btree-table) on transaction (cost=125100.05..177933.38 rows=33333 width=16) Filter: (description ~~ '%external%'::text) -> Storage Scan on transaction (cost=12510.05..17793.38 rows=66666 width=16) Projections: account_id, description Filters: (transaction_date > '2025-01-01 00:00:00'::timestamp without time zone) -> B-Tree Scan on transaction (cost=12510.05..17793.38 rows=100000 width=30) ``` Esse plano mostra dois filtros aplicados em diferentes estágios. A condição `transaction_date > '2025-01-01'` é aplicada na camada de armazenamento, reduzindo o volume de dados exibidos. A condição `description LIKE '%external%'` é aplicada posteriormente no processador de consultas, após a transferência dos dados, tornando-a menos eficiente. Colocar filtros mais seletivos nas camadas de armazenamento ou índice geralmente melhora a performance. ## Exemplo de verificação somente de índice As verificações somente de índice são os melhores tipos de verificação no Aurora DSQL, pois geram o menor número de idas e vindas até a camada de armazenamento e podem realizar a maior parte da filtragem. Mas só porque você vê “verificação somente de índice” não significa que tenha o melhor plano. Por conta de todos os diferentes níveis de filtragem que podem ocorrer, é essencial ainda prestar atenção aos diferentes locais em que a filtragem pode acontecer. ``` EXPLAIN SELECT balance FROM account WHERE customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb' AND balance > 100 AND status = 'pending'; ``` ``` QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using idx1 on account (cost=725.05..1025.08 rows=8 width=18) Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid) Filter: (balance > '100'::numeric) -> Storage Scan on idx1 (cost=12510.05..17793.38 rows=9 width=16) Projections: balance Filters: ((status)::text = 'pending'::text) -> B-Tree Scan on idx1 (cost=12510.05..17793.38 rows=10 width=30) Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid) ``` Nesse plano, a condição de índice, `customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'`, é avaliada primeiro durante a verificação do índice, que é o estágio mais eficiente porque limita o volume de dados lidos do armazenamento. O filtro de armazenamento, `status = 'pending'`, é aplicado após a leitura dos dados, mas antes de serem enviados para a camada computacional, reduzindo o volume de dados transferidos. Por fim, o filtro do processador de consultas, `balance > 100`, é executado por último, após a movimentação dos dados, tornando-o o menos eficiente. Dessas, a condição de índice oferece a melhor performance porque controla diretamente o volume de dados verificados. ## Exemplo de verificação de índice As verificações de índice são semelhantes às verificações somente de índice, exceto que elas têm a etapa extra de exigir o acesso à tabela base. Como o Aurora DSQL pode especificar filtros de armazenamento, ele pode fazer isso tanto na chamada de índice quanto na chamada de pesquisa. Para deixar isso claro, o Aurora DSQL apresenta o plano como dois nós. Dessa forma, é possível ver claramente o quanto a adição de uma coluna de inclusão ajudará em termos de linhas exibidas do armazenamento. ``` EXPLAIN SELECT balance FROM account WHERE customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb' AND balance > 100 AND status = 'pending' AND created_at > '2025-01-01'; ``` ``` QUERY PLAN ---------------------------------------------------------------------------------------------------------- Index Scan using idx1 on account (cost=728.18..1132.20 rows=3 width=18) Filter: (balance > '100'::numeric) Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid) -> Storage Scan on idx1 (cost=12510.05..17793.38 rows=8 width=16) Projections: balance Filters: ((status)::text = 'pending'::text) -> B-Tree Scan on account (cost=12510.05..17793.38 rows=10 width=30) Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid) -> Storage Lookup on account (cost=12510.05..17793.38 rows=4 width=16) Filters: (created_at > '2025-01-01 00:00:00'::timestamp without time zone) -> B-Tree Lookup on transaction (cost=12510.05..17793.38 rows=8 width=30) ``` Esse plano mostra como a filtragem ocorre em vários estágios: + A condição de índice em `customer_id ` filtra os dados com antecedência. + O filtro de armazenamento em `status` restringe ainda mais os resultados antes que eles sejam enviados para computação. + O filtro do processador de consultas em `balance` é aplicado posteriormente, após a transferência. + O filtro de pesquisa em `created_at` é avaliado ao buscar colunas adicionais da tabela base. Adicionar colunas usadas com frequência como campos `INCLUDE` geralmente pode eliminar essa pesquisa e melhorar a performance. ## Práticas recomendadas + **Alinhe os filtros com as colunas indexadas** para enviar a filtragem mais cedo. + **Use as colunas INCLUDE** para permitir verificações somente de índice e evitar pesquisas. + **Valide as estimativas de linha** ao investigar problemas de desempenho. O Aurora DSQL gerencia estatísticas automaticamente executando `ANALYZE` em segundo plano com base nas taxas de alteração de dados. Se as estimativas parecerem imprecisas, você pode executar `ANALYZE` manualmente para atualizar as estatísticas imediatamente. + **Evite consultas não indexadas** em tabelas grandes para evitar verificações completas dispendiosas. # Noções básicas sobre DPUs em EXPLAIN ANALYZE O Aurora DSQL fornece informações da Unidade de Processamento Distribuído (DPU) em **nível de instrução** na saída do plano `EXPLAIN ANALYZE VERBOSE`, oferecendo maior visibilidade do custo da consulta durante o desenvolvimento. Esta seção explica o que são DPUs e como interpretá-las na saída `EXPLAIN ANALYZE VERBOSE`. ## O que é DPU? Unidade de Processamento Distribuído (DPU) é a medida normalizada do trabalho realizado pelo Aurora DSQL. É composta por: + **ComputeDPU**: tempo gasto na execução de consultas SQL. + **ReadDPU**: recursos usados para ler dados do armazenamento. + **WriteDPU**: recursos usados para gravar dados no armazenamento. + **MultiRegionWriteDPU**: recursos usados para replicar gravações em clusters emparelhados em configurações multirregionais. ## Uso de DPU em EXPLAIN ANALYZE VERBOSE O Aurora DSQL estende `EXPLAIN ANALYZE VERBOSE` para incluir uma estimativa de uso da DPU em nível de instrução no final da saída. Isso oferece visibilidade imediata do custo da consulta, ajudando você a identificar os fatores de custo da workload, ajustar a performance da consulta e prever melhor o uso dos recursos. Os exemplos a seguir mostram como interpretar as estimativas de DPU em nível de instrução incluídas na saída de EXPLAIN ANALYZE VERBOSE. ### Exemplo 1: consulta SELECT ``` EXPLAIN ANALYZE VERBOSE SELECT * FROM test_table; ``` ``` QUERY PLAN ---------------------------------------------------- Index Only Scan using test_table_pkey on public.test_table (cost=125100.05..171100.05 rows=1000000 width=36) (actual time=2.973..4.482 rows=120 loops=1) Output: id, context -> Storage Scan on test_table_pkey (cost=125100.05..171100.05 rows=1000000 width=36) (actual rows=120 loops=1) Projections: id, context -> B-Tree Scan on test_table_pkey (cost=125100.05..171100.05 rows=1000000 width=36) (actual rows=120 loops=1) Query Identifier: qymgw1m77maoe Planning Time: 11.415 ms Execution Time: 4.528 ms Statement DPU Estimate: Compute: 0.01607 DPU Read: 0.04312 DPU Write: 0.00000 DPU Total: 0.05919 DPU ``` Neste exemplo, a instrução SELECT executa uma verificação somente de índice, então a maior parte do custo vem da DPU de leitura (0,04312), representando os dados recuperados do armazenamento e da DPU de computação (0,01607), o que reflete os recursos computacionais utilizados para processar e exibir os resultados. Não há DPU de gravação, pois a consulta não modifica os dados. A DPU total (0,05919) é a soma de computação \$1 leitura \$1 gravação. ### Exemplo 2: consulta INSERT ``` EXPLAIN ANALYZE VERBOSE INSERT INTO test_table VALUES (1, 'name1'), (2, 'name2'), (3, 'name3'); ``` ``` QUERY PLAN ---------------------------------------------------- Insert on public.test_table (cost=0.00..0.04 rows=0 width=0) (actual time=0.055..0.056 rows=0 loops=1) -> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=122) (actual time=0.003..0.008 rows=3 loops=1) Output: "*VALUES*".column1, "*VALUES*".column2 Query Identifier: jtkjkexhjotbo Planning Time: 0.068 ms Execution Time: 0.543 ms Statement DPU Estimate: Compute: 0.01550 DPU Read: 0.00307 DPU (Transaction minimum: 0.00375) Write: 0.01875 DPU (Transaction minimum: 0.05000) Total: 0.03732 DPU ``` Essa instrução executa principalmente gravações, portanto, a maior parte do custo está associada à DPU de gravação. A DPU de computação (0,01550) representa o trabalho realizado para processar e inserir os valores. A DPU de leitura (0,00307) reflete leituras secundárias do sistema (para pesquisas de catálogos ou verificações de índice). Observe os mínimos de transação mostrados ao lado de DPUs de leitura e gravação. Eles indicam os custos básicos por transação que se aplicam *somente quando a operação inclui leituras ou gravações*. Eles não significam que cada transação gera automaticamente em uma cobrança de 0,00375 de DPU de leitura ou 0,05 de DPU de gravação. Em vez disso, esses mínimos são aplicados em nível de transação durante a agregação de custos e somente se as leituras ou gravações ocorrerem dentro dessa transação. Devido a essa diferença no escopo, as estimativas em nível de instrução em `EXPLAIN ANALYZE VERBOSE` podem não corresponder exatamente às métricas em nível de transação relatadas no CloudWatch ou nos dados de faturamento. ## Usar informações de DPU para otimização As estimativas de DPU por instrução oferecem uma maneira poderosa de otimizar as consultas além do tempo de execução. Entre os casos de uso comuns estão: + **Consciência de custos:** entenda o quanto uma consulta é cara em relação a outras. + **Otimização do esquema:** compare o impacto dos índices ou das alterações do esquema na performance e na eficiência dos recursos. + **Planejamento de orçamento:** estime o custo da workload com base no uso observado da DPU. + **Comparação de consultas:** avalie abordagens de consulta alternativas de acordo com seu consumo relativo de DPU. ## Interpretar informações de DPU Lembre-se das seguintes práticas recomendadas ao usar dados de DPU de `EXPLAIN ANALYZE VERBOSE`: + **Use de forma direcionada:** trate a DPU relatada como uma forma de entender o custo *relativo* de uma consulta, em vez de uma correspondência exata com as métricas ou os dados de faturamento do CloudWatch. As diferenças são esperadas porque `EXPLAIN ANALYZE VERBOSE` indica o custo em nível de instrução, enquanto o CloudWatch agrega atividades em nível de transação. O CloudWatch também inclui operações em segundo plano (como ANALYZE ou compactações) e sobrecarga de transação (`BEGIN`/`COMMIT`) que `EXPLAIN ANALYZE VERBOSE` exclui intencionalmente. + A **variabilidade da DPU entre as execuções é normal** em sistemas distribuídos e não indica erros. Fatores, como armazenamento em cache, alterações no plano de execução, simultaneidade ou mudanças na distribuição de dados, podem fazer com que a mesma consulta consuma recursos diferentes de uma execução para outra. + **Operações pequenas em lotes:** se sua workload emitir muitas instruções pequenas, pense em agrupá-las em operações maiores (que não excedam 10 MB). Isso reduz a sobrecarga de arredondamento e produz estimativas de custo mais significativas. + **Use para ajuste, não para cobrança:** os dados em `EXPLAIN ANALYZE VERBOSE`foram projetados para reconhecimento de custos, ajuste de consultas e otimização. Não é uma métrica de faturamento. Sempre confie nas métricas do CloudWatch ou nos relatórios de faturamento mensais para acessar dados confiáveis de custo e uso.