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);

Melhorar a performance de consultas do Aurora PostgreSQL usando a transformação de subconsultas

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.

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

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_mem e hash_mem_multiplier. Para saber mais, consulte Resource Consumption.

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_subquery_cache_check_interval, o benefício do cache de subconsultas é avaliado conferindo se a CHR é maior que apg_subquery_cache_hit_rate_threshold. 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.

É 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.

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: