# Otimizar subconsultas correlacionadas no Aurora PostgreSQL
<a name="apg-correlated-subquery"></a>

 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
<a name="apg-corsubquery-transformation"></a>

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
<a name="apg-corsub-transform"></a>

 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
<a name="apg-corsub-transform-confirm"></a>

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 name="apg-corsub-transform-limitations"></a>
+ 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
<a name="apg-subquery-cache"></a>

 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
<a name="apg-subquery-cache-understand"></a>

 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 name="apg-subquery-cache-parameters"></a>

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
<a name="apg-subquery-cache-turningon"></a>

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
<a name="apg-subquery-cache-limitations"></a>

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.