# Otimizar a performance de consultas no Aurora PostgreSQL Otimizar a performance das consultas é crucial porque torna a execução dos bancos de dados mais rápida e eficiente, usando menos recursos, o que melhora a experiência do usuário e reduz os custos operacionais. O Amazon Aurora PostgreSQL oferece vários recursos que ajudam a otimizar a performance das consultas para workloads do PostgreSQL. **Topics** + [ # Melhorar a performance das consultas do Aurora PostgreSQL com o Aurora Optimized Reads ](AuroraPostgreSQL.optimized.reads.md) + [ # Otimizar subconsultas correlacionadas no Aurora PostgreSQL ](apg-correlated-subquery.md) + [ # Melhorar o desempenho de consultas usando junção adaptável ](user-apg-adaptive-join.md) + [ # Usar o cache de plano compartilhado ](apg-shared-plan-cache.md) # Melhorar a performance das consultas do Aurora PostgreSQL com o Aurora Optimized Reads É possível acelerar o processamento de consultas do Aurora PostgreSQL com o Aurora Optimized Reads. Uma instância do banco de dados Aurora PostgreSQL que utiliza Leituras Otimizadas do Aurora proporciona até 8 vezes menos latência nas consultas e até 30% de economia para aplicações com grandes volumes de dados que excedem a capacidade de memória da instância do banco de dados. **Topics** + [ ## Visão geral do Aurora Optimized Reads no PostgreSQL ](#AuroraPostgreSQL.optimized.reads.overview) + [ ## Utilizar o Aurora Optimized Reads ](#AuroraPostgreSQL.optimized.reads.using) + [ ## Casos de uso do Aurora Optimized Reads ](#AuroraPostgreSQL.optimized.reads.usecases) + [ ## Monitorar instâncias de banco de dados que utilizam o Aurora Optimized Reads ](#AuroraPostgreSQL.optimized.reads.monitoring) + [ ## Práticas recomendadas para o Aurora Optimized Reads ](#AuroraPostgreSQL.optimized.reads.bestpractices) ## Visão geral do Aurora Optimized Reads no PostgreSQL O Aurora Optimized Reads está disponível por padrão quando você cria um cluster de banco de dados que use instâncias R6gd e R8gd baseadas em Graviton e R6id baseadas em Intel com armazenamento de memória expressa não volátil (NVMe). Ele está disponível nas seguintes versões do PostgreSQL: + 14.12 e versões posteriores, 15.7 e versões posteriores, 16.3 e versões posteriores, 17.4 e versões posteriores das instâncias R8gd. + 14.9 e versões posteriores, 15.4 e versões posteriores, 16.1 e todas as versões posteriores das instâncias R6gd e R6id. O Aurora Optimized Reads oferece suporte a dois recursos: cache em camadas e objetos temporários. **Cache em camadas habilitado para o Aurora Optimized Reads**: usando o cache em camadas, você pode estender a capacidade de cache da instância de banco de dados em até cinco vezes a memória da instância. Isso mantém automaticamente o cache para conter os dados mais recentes e transacionalmente consistentes, liberando as aplicações da sobrecarga de gerenciar a moeda de dados de soluções externas de armazenamento em cache baseadas em conjuntos de resultados. Ele oferece latência até 8 vezes melhor para consultas que anteriormente buscavam dados do armazenamento Aurora. No Aurora, o valor para `shared_buffers` no grupo de parâmetros padrão geralmente é definido como cerca de 75% da memória disponível. No entanto, para os tipos de instância r8gd, r6gd e r6id, o Aurora reduzirá o espaço de `shared_buffers` em 4,5% para hospedar os metadados do cache do Optimized Reads. **Objetos temporários habilitados para o Optimized Reads**: usando objetos temporários, você pode acelerar o processamento de consultas colocando os arquivos temporários gerados pelo PostgreSQL no armazenamento NVMe local. Isso reduz o tráfego para o Elastic Block Storage (EBS) pela rede. Ele oferece latência e taxa de throughput até duas vezes melhores para consultas avançadas que classificam, unem ou mesclam grandes volumes de dados que não se ajustam à capacidade de memória disponível em uma instância de banco de dados. Em um cluster otimizado para E/S do Aurora, o Optimized Reads usa tanto o cache em camadas quanto os objetos temporários no armazenamento NVMe. Com o recurso de cache hierárquico habilitado para o Optimized Reads, o Aurora aloca o dobro da memória da instância para objetos temporários, aproximadamente 10% do armazenamento para operações internas e o armazenamento restante como cache em camadas. Em um cluster Aurora Standard, o Optimized Reads usa somente objetos temporários. Os clusters do Aurora otimizados para E/S permitem redimensionar o espaço alocado a objetos temporários habilitados para leituras otimizadas usando o parâmetro dinâmico `aurora_temp_space_size` no nível da instância. O recurso de redimensionamento está disponível nas seguintes versões do PostgreSQL: + 16.8 e todas as versões posteriores + 15.12 e versões 15 posteriores + 14.17 e versões 14 posteriores Com esse parâmetro, você pode redimensionar a capacidade de memória da instância de duas até seis vezes sem precisar reinicializar o mecanismo de banco de dados. Ao expandir o espaço de objetos temporários, a alteração entra em vigor de imediato, independentemente das workloads simultâneas. Entretanto, ao reduzir o espaço, o ajuste é concluído somente depois que há espaço não utilizado suficiente em objetos temporários para atender à nova solicitação de tamanho. Depois de redimensionar objetos temporários habilitados para leituras otimizadas, o cache em camadas se ajusta automaticamente para usar qualquer espaço disponível. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.optimized.reads.html) **nota** Uma alternância entre clusters otimizados para E/S e Standard em uma classe de instância de banco de dados baseada em NVMe causa uma reinicialização imediata do mecanismo de banco de dados. No Aurora PostgreSQL, use o parâmetro `temp_tablespaces` para configurar o espaço da tabela onde os objetos temporários são armazenados. Para verificar se os objetos temporários estão configurados, use o seguinte comando: ``` postgres=> show temp_tablespaces; temp_tablespaces --------------------- aurora_temp_tablespace (1 row) ``` O `aurora_temp_tablespace` é um espaço de tabela configurado pelo Aurora que aponta para o armazenamento NVMe local. Não é possível modificar esse parâmetro nem voltar para o armazenamento no Amazon EBS. Para verificar se o cache de leitura otimizado está ativado, use o seguinte comando: ``` postgres=> show shared_preload_libraries; shared_preload_libraries -------------------------------------------------------- rdsutils,pg_stat_statements,aurora_optimized_reads_cache ``` ## Utilizar o Aurora Optimized Reads Quando você provisiona uma instância de banco de dados do Aurora PostgreSQL com uma instância de banco de dados baseada em NVMe, a instância de banco de dados utiliza automaticamente o Aurora Optimized Reads. Para ativar o Aurora Optimized Reads, execute um destes procedimentos: + Crie um cluster de banco de dados do Aurora PostgreSQL utilizando uma das classes de instância de banco de dados NVMe. Para obter mais informações, consulte [Criar um cluster de bancos de dados do Amazon Aurora](Aurora.CreateInstance.md). + Modifique um cluster de banco de dados do Aurora PostgreSQL para utilizar uma das classes de instância de banco de dados baseada em NVMe. Para obter mais informações, consulte [Modificar um cluster de bancos de dados Amazon Aurora](Aurora.Modifying.md). O Aurora Optimized Reads está disponível em todas as Regiões da AWS onde há suporte para uma ou mais dessas classes de instância de banco de dados com SSD NVMe local. Para obter mais informações, consulte [Classes de instâncias de banco de dados do Amazon Aurora](Concepts.DBInstanceClass.md). Para voltar para uma instância do Aurora de leituras não otimizadas, modifique a classe de instância de banco de dados da instância do Aurora para a classe de instância semelhante sem o armazenamento efêmero do NVMe para as workloads de banco de dados. Por exemplo, se a classe de instância de banco de dados atual for db.r6gd.4xlarge, selecione db.r6g.4xlarge para voltar. Para ter mais informações, consulte [Modificar uma instância de banco de dados do Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.DBInstance.Modifying.html). ## Casos de uso do Aurora Optimized Reads **Cache em camadas habilitado para o Optimized Reads** Veja a seguir alguns casos de uso que podem se beneficiar do Optimized Reads com cache em camadas: + Aplicações de escala na Internet, como processamento de pagamentos, cobrança e comércio eletrônico com SLAs altamente rigorosos. + Painéis de relatórios em tempo real que executam centenas de consultas pontuais para coleta de métricas/dados. + Aplicações de IA generativa com a extensão pgvector para pesquisar vizinhos exatos ou mais próximos em milhões de incorporações vetoriais. **Objetos temporários habilitados para o Optimized Reads** Veja a seguir alguns casos de uso que podem se beneficiar do Optimized Reads com objetos temporários: + Consultas analíticas que incluem expressões de tabela comuns (CTEs), tabelas derivadas e operações de agrupamento. + Réplicas de leitura que lidam com as consultas não otimizadas de uma aplicação. + Consultas de relatórios dinâmicos ou sob demanda com operações complexas, como GROUP BY e ORDER BY, que nem sempre podem usar índices apropriados. + Operações `CREATE INDEX` ou `REINDEX` de classificação. + Outras workloads que usam tabelas temporárias internas. ## Monitorar instâncias de banco de dados que utilizam o Aurora Optimized Reads Você pode monitorar as consultas que usam o cache em camadas habilitado para o Optimized Reads com o comando EXPLAIN, conforme mostrado no seguinte exemplo: ``` Postgres=> EXPLAIN (ANALYZE, BUFFERS) SELECT c FROM sbtest15 WHERE id=100000000 QUERY PLAN -------------------------------------------------------------------------------------- Index Scan using sbtest15_pkey on sbtest15 (cost=0.57..8.59 rows=1 width=121) (actual time=0.287..0.288 rows=1 loops=1) Index Cond: (id = 100000000) Buffers: shared hit=3 read=2 aurora_orcache_hit=2 I/O Timings: shared/local read=0.264 Planning: Buffers: shared hit=33 read=6 aurora_orcache_hit=6 I/O Timings: shared/local read=0.607 Planning Time: 0.929 ms Execution Time: 0.303 ms (9 rows) Time: 2.028 ms ``` **nota** Os campos `aurora_orcache_hit` e `aurora_storage_read` na seção `Buffers` do plano de explicação são mostrados somente quando o Optimized Reads está ativado e seus valores são maiores que zero. O campo de leitura é o total dos campos `aurora_orcache_hit` e `aurora_storage_read`. Você pode monitorar instâncias de banco de dados que usam o Aurora Optimized Reads com as seguintes métricas do CloudWatch: + `AuroraOptimizedReadsCacheHitRatio` + `FreeEphemeralStorage` + `ReadIOPSEphemeralStorage` + `ReadLatencyEphemeralStorage` + `ReadThroughputEphemeralStorage` + `WriteIOPSEphemeralStorage` + `WriteLatencyEphemeralStorage` + `WriteThroughputEphemeralStorage` Essas métricas fornecem dados sobre armazenamento de instâncias, IOPS e throughput. Para ter mais informações sobre essas métricas, consulte [Métricas no nível da instância do Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md#Aurora.AuroraMySQL.Monitoring.Metrics.instances). Também é possível usar a extensão `pg_proctab` para monitorar o armazenamento NVMe. ``` postgres=>select * from pg_diskusage(); major | minor | devname | reads_completed | reads_merged | sectors_read | readtime | writes_completed | writes_merged | sectors_written | writetime | current_io | iotime | totaliotime ------+-------+---------------------+-----------------+--------------+--------------+----------+------------------+---------------+-----------------+-----------+------------+---------+------------- | | rdstemp | 23264 | 0 | 191450 | 11670 | 1750892 | 0 | 24540576 | 819350 | 0 | 3847580 | 831020 | | rdsephemeralstorage | 23271 | 0 | 193098 | 2620 | 114961 | 0 | 13845120 | 130770 | 0 | 215010 | 133410 (2 rows) ``` ## Práticas recomendadas para o Aurora Optimized Reads Use as práticas recomendadas a seguir para o Aurora Optimized Reads: + Monitore o espaço de armazenamento disponível no armazenamento de instâncias com a métrica do CloudWatch `FreeEphemeralStorage`. Se o armazenamento de instância estiver atingindo seu limite devido à workload na instância de banco de dados, ajuste a simultaneidade e as consultas que fazem uso intenso de objetos temporários ou modifique para usar uma classe de instância de banco de dados maior. + Monitore a métrica do CloudWatch para a taxa de acertos de cache do Optimized Reads. Operações como VACUUM modificam um grande número de blocos muito rapidamente. Isso pode causar uma queda temporária na taxa de acerto. A extensão `pg_prewarm` pode ser usada para carregar dados no cache de buffer, permitindo que o Aurora grave proativamente alguns desses blocos no cache do Optimized Reads. + Você pode ativar o gerenciamento de cache de cluster (CCM) para aquecer o cache de buffer e o cache em camadas em um leitor de nível 0, que será usado como destino de failover. Quando o CCM está ativado, o cache do buffer é escaneado periodicamente para gravar páginas elegíveis para remoção em cache em camadas. Para obter mais informações sobre CCM, consulte [Recuperação rápida após failover com o gerenciamento de cache do cluster para o Aurora PostgreSQL](AuroraPostgreSQL.cluster-cache-mgmt.md). # Otimizar subconsultas correlacionadas no Aurora PostgreSQL Uma subconsulta correlacionada faz referência às colunas da tabela da consulta externa. Ela é avaliada uma vez para cada linha exibida pela consulta externa. No exemplo a seguir, a subconsulta faz referência a uma coluna da tabela ot. Essa tabela não está incluída na cláusula FROM da subconsulta, mas é referenciada na cláusula FROM da consulta externa. Se a tabela ot tiver 1 milhão de linhas, a subconsulta precisará ser avaliada 1 milhão de vezes. ``` SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); ``` **nota** A transformação e o cache de subconsultas estão disponíveis no Aurora PostgreSQL a partir da versão 16.8, enquanto o Babelfish para Aurora PostgreSQL é compatível com esses recursos a partir da versão 4.2.0. A partir das versões 4.6.0 e 5.2.0 do Babelfish para Aurora PostgreSQL, os seguintes parâmetros controlam esses recursos: babelfishpg\$1tsql.apg\$1enable\$1correlated\$1scalar\$1transform babelfishpg\$1tsql.apg\$1enable\$1subquery\$1cache Por padrão, ambos os parâmetros estão ativados. ## Melhorar a performance de consultas do Aurora PostgreSQL usando a transformação de subconsultas O Aurora PostgreSQL pode acelerar subconsultas correlacionadas transformando-as em junções externas equivalentes. Essa otimização se aplica a estes dois tipos de subconsultas correlacionadas: + Subconsultas que exibem um único valor agregado e aparecem na lista SELECT. ``` SELECT ot.a, ot.b, (SELECT AVG(it.b) FROM it WHERE it.a = ot.a) FROM ot; ``` + Subconsultas que exibem um único valor agregado e aparecem em uma cláusula WHERE. ``` SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); ``` ### Ativar a transformação na subconsulta Para permitir a transformação de subconsultas correlacionadas em junções externas equivalentes, defina o parâmetro `apg_enable_correlated_scalar_transform` como `ON`. O valor padrão desse parâmetro é `OFF`. É possível modificar o grupo de parâmetros do cluster ou da instância para definir os parâmetros. Para saber mais, consulte [Grupos de parâmetros para Amazon Aurora](USER_WorkingWithParamGroups.md). Como alternativa, é possível definir a configuração apenas para a sessão atual chamando o seguinte comando: ``` SET apg_enable_correlated_scalar_transform TO ON; ``` ### Verificar a transformação Use o comando EXPLAIN para verificar se a subconsulta correlacionada foi transformada em uma junção externa no plano de consulta. Quando a transformação estiver habilitada, a parte da subconsulta correlacionada aplicável será transformada em junção externa. Por exemplo: ``` postgres=> CREATE TABLE ot (a INT, b INT); CREATE TABLE postgres=> CREATE TABLE it (a INT, b INT); CREATE TABLE postgres=> SET apg_enable_correlated_scalar_transform TO ON; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); QUERY PLAN -------------------------------------------------------------- Hash Join Hash Cond: (ot.a = apg_scalar_subquery.scalar_output) Join Filter: ((ot.b)::numeric < apg_scalar_subquery.avg) -> Seq Scan on ot -> Hash -> Subquery Scan on apg_scalar_subquery -> HashAggregate Group Key: it.a -> Seq Scan on it ``` A mesma consulta não é transformada quando o parâmetro GUC é `OFF`. O plano não terá junção externa, mas, sim, um subplano. ``` postgres=> SET apg_enable_correlated_scalar_transform TO OFF; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); QUERY PLAN ---------------------------------------- Seq Scan on ot Filter: ((b)::numeric < (SubPlan 1)) SubPlan 1 -> Aggregate -> Seq Scan on it Filter: (a = ot.a) ``` ### Limitações + A subconsulta deve estar na lista SELECT ou em uma das condições na cláusula where. Caso contrário, ela não será transformada. + A subconsulta deve exibir uma função agregada. Não é possível usar funções agregadas definidas pelo usuário para transformação. + Uma subconsulta cuja expressão de retorno não seja uma função agregada simples não será transformada. + A condição correlacionada nas cláusulas WHERE da subconsulta deve ser uma referência de coluna simples. Caso contrário, ela não será transformada. + A condição correlacionada na subconsulta em que as cláusulas devem ser um predicado de igualdade simples. + A subconsulta não pode conter uma cláusula HAVING nem GROUP BY. + A cláusula WHERE na subconsulta pode conter um ou mais predicados combinados com AND. **nota** O impacto da transformação no desempenho varia de acordo com seu esquema, dados e workload. A execução de subconsulta correlacionada com transformação pode melhorar significativamente o desempenho à medida que o número de linhas produzidas pela consulta externa aumenta. Recomendamos que você teste esse recurso em um ambiente de não produção com seu esquema, dados e workload reais antes de habilitá-lo em um ambiente de produção. ## Usar o cache de subconsultas para melhorar a performance das consultas do Aurora PostgreSQL O Aurora PostgreSQL permite o uso de cache de subconsultas para armazenar os resultados de subconsultas correlacionadas. Esse recurso ignora execuções repetidas de subconsultas correlacionadas quando os resultados da subconsulta já estão no cache. ### Noções básicas sobre cache de subconsultas O nó Memoize do PostgreSQL é a parte principal do cache de subconsultas. Ele mantém uma tabela de hash no cache local para associar os valores dos parâmetros de entrada às linhas de resultados da consulta. O limite de memória para a tabela de hash é o produto de work\$1mem e hash\$1mem\$1multiplier. Para saber mais, consulte [Resource Consumption](https://www.postgresql.org/docs/16/runtime-config-resource.html). Durante a execução da consulta, o cache de subconsultas usa a taxa de acertos de cache (CHR) para estimar se o cache está melhorando a performance das consultas e para decidir, no tempo de execução da consulta, se deve continuar usando o cache. CHR é a razão entre o número de acertos de cache e o número total de solicitações. Por exemplo, se uma subconsulta correlacionada precisar ser executada 100 vezes e 70 desses resultados de execução puderem ser recuperados do cache, a CHR será 0,7. Para cada número de falhas de cache de apg\$1subquery\$1cache\$1check\$1interval, o benefício do cache de subconsultas é avaliado conferindo se a CHR é maior que apg\$1subquery\$1cache\$1hit\$1rate\$1threshold. Caso contrário, o cache será excluído da memória e a execução da consulta retornará à reexecução da subconsulta original sem cache. ### Parâmetros que controlam o comportamento do cache de subconsultas A tabela a seguir lista os parâmetros que controlam o comportamento do cache de subconsultas. | Parameter | Descrição | Padrão | Permitido | | --- | --- | --- | --- | | apg\$1enable\$1subquery\$1cache | Permite o uso de cache para subconsultas escalares correlacionadas. | DESL. | ON, OFF | | apg\$1subquery\$1cache\$1check\$1interval | Define a frequência, em número de falhas de cache, para avaliar a taxa de acertos de cache da subconsulta. | 500 | 0–2147483647 | | apg\$1subquery\$1cache\$1hit\$1rate\$1threshold | Define o limite para a taxa de acertos de cache da subconsulta. | 0.3 | 0, 0 a 1,0 | **nota** Valores maiores de `apg_subquery_cache_check_interval` podem melhorar a precisão da estimativa dos benefícios do cache baseada em CHR, mas aumentarão a sobrecarga no cache, pois a CHR só será avaliada quando a tabela de cache tiver linhas `apg_subquery_cache_check_interval`. Valores maiores de `apg_subquery_cache_hit_rate_threshold` têm a tendência de abandonar o cache de subconsultas e retornar à reexecução da subconsulta original sem cache. É possível modificar o grupo de parâmetros do cluster ou da instância para definir os parâmetros. Para saber mais, consulte [Grupos de parâmetros para Amazon Aurora](USER_WorkingWithParamGroups.md). Como alternativa, é possível definir a configuração apenas para a sessão atual chamando o seguinte comando: ``` SET apg_enable_subquery_cache TO ON; ``` ### Ativar o cache de subconsultas no Aurora PostgreSQL Quando o cache de subconsultas está habilitado, o Aurora PostgreSQL o aplica para salvar os resultados das subconsultas. O plano de consulta terá então um nó Memoize em SubPlan. Por exemplo, a sequência de comandos a seguir mostra o plano de execução de consulta estimado de uma subconsulta correlacionada simples sem cache de subconsultas. ``` postgres=> SET apg_enable_subquery_cache TO OFF; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a); QUERY PLAN ------------------------------------ Seq Scan on ot Filter: (b < (SubPlan 1)) SubPlan 1 -> Seq Scan on it Filter: (a = ot.a) ``` Após a ativação de `apg_enable_subquery_cache`, o plano de consulta conterá um nó Memoize sob o nó SubPlan, indicando que a subconsulta planeja usar o cache. ``` postgres=> SET apg_enable_subquery_cache TO ON; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a); QUERY PLAN ------------------------------------ Seq Scan on ot Filter: (b < (SubPlan 1)) SubPlan 1 -> Memoize Cache Key: ot.a Cache Mode: binary -> Seq Scan on it Filter: (a = ot.a) ``` O plano real de execução da consulta contém mais detalhes do cache de subconsultas, incluindo acertos de cache e ausências no cache. A saída a seguir mostra o plano real de execução da consulta do exemplo acima após a inserção de alguns valores nas tabelas. ``` postgres=> EXPLAIN (COSTS FALSE, TIMING FALSE, ANALYZE TRUE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a); QUERY PLAN ----------------------------------------------------------------------------- Seq Scan on ot (actual rows=2 loops=1) Filter: (b < (SubPlan 1)) Rows Removed by Filter: 8 SubPlan 1 -> Memoize (actual rows=0 loops=10) Cache Key: ot.a Cache Mode: binary Hits: 4 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Seq Scan on it (actual rows=0 loops=6) Filter: (a = ot.a) Rows Removed by Filter: 4 ``` O número total de acertos de cache é quatro e o número total de ausências no cache é seis. Se o número total de acertos e ausências for menor que o número de loops no nó Memoize, isso significa que a avaliação da CHR não foi aprovada e o cache foi limpo e abandonado em algum momento. A execução da subconsulta então retornou à reexecução original sem cache. ### Limitações O cache de subconsultas não comporta determinados padrões de subconsultas correlacionadas. Esses tipos de consulta serão executados sem cache, mesmo que o cache de subconsultas esteja ativado: + Consultas relacionadas IN/EXISTS/ANY/ALL + Subconsultas correlacionadas contendo funções não determinísticas. + Subconsultas correlacionadas que fazem referência a colunas de tabelas externas com tipos de dados que não comportam operações de hash ou igualdade. # Melhorar o desempenho de consultas usando junção adaptável ## Visão geral A junção adaptável é um recurso de versão prévia no Aurora PostgreSQL 17.4 que ajuda a melhorar o desempenho das consultas. Esse recurso está desabilitado por padrão, mas você pode habilitá-lo usando os parâmetros de configuração global do usuário (GUC). Como esse é um recurso de versão prévia, os valores padrão dos parâmetros podem mudar. Quando habilitada, a junção adaptável ajuda a otimizar o desempenho da consulta ao alternar dinamicamente de uma junção de loop aninhado para uma junção de hash em tempo de execução. Essa troca ocorre quando o otimizador do PostgreSQL escolhe incorretamente uma junção de loop aninhado devido a estimativas de cardinalidade imprecisas. ## Configurar junção adaptável Você pode controlar a junção adaptável usando estes três parâmetros de GUC: **Parâmetros de configuração de junção adaptável** | Parâmetro de GUC | Descrição | Opções padrão e de configuração | | --- | --- | --- | | apg\$1adaptive\$1join\$1crossover\$1multiplier | Esse multiplicador funciona com o ponto de cruzamento de linhas para determinar quando mudar de um loop aninhado para uma junção de hash. O ponto de cruzamento de linhas é onde o otimizador SQL estima que as operações de loop aninhado e junção de hash tenham o mesmo custo. Um valor multiplicador mais alto reduz a probabilidade de a junção adaptável mudar para uma junção de hash. | Controla se a junção adaptável está habilitada [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) | | apg\$1adaptive\$1join\$1cost\$1threshold | Esse parâmetro define um limite mínimo de custo de consulta. A junção adaptável se desabilita automaticamente para consultas abaixo desse limite. Isso evita a sobrecarga de desempenho em consultas simples, nas quais o custo de planejar uma junção adaptável pode superar os benefícios de mudar de loop aninhado para junção de hash. | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) | | apg\$1enable\$1parameterized\$1adaptive\$1join | Quando habilitado, esse parâmetro estende a funcionalidade de junção adaptável às junções de loop aninhado parametrizadas. Por padrão, a junção adaptável funciona somente com junções de loop aninhado não parametrizadas, pois é mais provável que elas se beneficiem da mudança para a junção de hash. As junções de loop aninhado parametrizadas geralmente têm melhor desempenho, tornando a mudança para a junção de hash menos essencial. | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) Requer que `apg_adaptive_join_crossover_multiplier` seja habilitado primeiro. | # Usar o cache de plano compartilhado ## Visão geral O Aurora PostgreSQL usa um modelo de processo por usuário em que cada conexão de cliente cria um processo de backend dedicado. Cada processo de backend mantém seu próprio cache de plano local para instruções preparadas. Como esses caches não podem ser compartilhados entre processos, as aplicações que usam muitas instruções preparadas podem criar caches duplicados em diferentes processos de backend, o que aumenta o uso da memória. As versões 17.6 e posteriores e 16.10 e posteriores do Aurora PostgreSQL introduzem a funcionalidade de cache de plano compartilhado. Quando você habilita esse recurso, os processos de backend podem compartilhar planos genéricos, o que reduz o uso de memória e melhora o desempenho ao eliminar a geração de planos duplicados. O cache de plano compartilhado usa os seguintes componentes como chave de cache: + string de consulta (incluindo comentários); + parâmetros de GUC relacionados ao planejador (inclusive `search_path`); + ID de usuário + ID do banco de dados. As reinicializações da instância redefinem o cache compartilhado. ## Parâmetros A seguinte tabela descreve os parâmetros de um que controlam o recurso de cache de plano compartilhado: | Parameter | Descrição | Padrão | Permitido | | --- | --- | --- | --- | | apg\$1shared\$1plan\$1cache.enable | Habilita ou desabilita o cache de plano compartilhado. | 0 (DESABILITADO) | 0, 1 | | apg\$1shared\$1plan\$1cache.max | O número máximo de entradas de cache. | 200-1.000 (depende do tamanho da instância) | 100-50.000 | | apg\$1shared\$1plan\$1cache.min\$1size\$1per\$1entry | O tamanho mínimo do plano para armazenar no cache compartilhado. Planos menores usam cache local para otimizar o desempenho de OLTP. | 16 KB | 0-32.768 (KB) | | apg\$1shared\$1plan\$1cache.max\$1size\$1per\$1entry | O tamanho máximo do plano para cache compartilhado. Planos maiores armazenam somente informações de custo. | 256 KB-4 MB (depende do tamanho da instância) | 0-32.768 (KB) | | apg\$1shared\$1plan\$1cache.idle\$1generic\$1plan\$1release\$1timeout | O tempo após o qual as sessões ociosas lançam planos genéricos locais. Valores mais baixos economizam memória; valores mais altos podem melhorar o desempenho. | 10 segundos | 0-2.147.483.647 (ms) | **nota** É possível modificar todos os parâmetros sem reiniciar. ## Monitorar visualizações e funções + `apg_shared_plan_cache()`: mostra informações detalhadas de entradas do cache (acertos, validade e carimbos de data/hora). + `apg_shared_plan_cache_stat()`: exibe estatísticas em nível de instância (remoções e invalidações). + `apg_shared_plan_cache_reset()`: remove todas as entradas em `apg_shared_plan_cache()` e `apg_shared_plan_cache_stat()`. + `apg_shared_plan_cache_remove(cache_key)`: remove uma entrada de `apg_shared_plan_cache()` quando a entrada corresponde à `cache_key`. ## Limitações + Funciona somente com instruções preparadas e não armazena em cache instruções PL/pgSQL. + Não armazena em cache consultas que contenham tabelas temporárias ou tabelas de catálogo. + Não armazena em cache consultas que dependem de segurança em nível de linha (RLS). + Cada réplica mantém seu próprio cache (sem compartilhamento entre réplicas).