Otimizar subconsultas correlacionadas no Babelfish
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 t1. 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 t1 tiver um milhão de linhas, a subconsulta precisará ser avaliada um milhão de vezes.
SELECT col1, col2 FROM t1 WHERE col1 < (SELECT sum(col1) FROM t2 WHERE t1.col2 = t2.col2);
Melhorar a performance de consultas do Babelfish usando transformação de subconsultas
O Babelfish 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. Para ter mais informações, consulte SELECT clause
na documentação do Microsoft Transact-SQL. SELECT ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) FROM outer_sb_t1; -
Subconsultas que exibem um único valor agregado e aparecem em uma cláusula WHERE.
SELECT * FROM outer_sb_t1 WHERE ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) > 1.0;
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. Esse parâmetro está disponível no Babelfish 4.2.0 e versões posteriores. 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 a função set_config. Por exemplo, execute o comando a seguir para ativar o cache de subconsultas no Babelfish. Para saber mais, consulte Configuration Settings Functions
1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO
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. Para ter mais informações, consulte Usar o plano de explicação para melhorar a performance das consultas do Babelfish.
Quando a transformação estiver habilitada, a parte da subconsulta correlacionada aplicável será transformada em junção externa. Por exemplo:
1>select set_config('apg_enable_correlated_scalar_transform', 'true', false);2>GO1>set BABELFISH_STATISTICS PROFILE on2>GO1>select customer_name, ( select max(o.cost) from correlated_orders o2>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount3>from correlated_customers c order by customer_name ;4>GOQUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Text: select customer_name, ( select max(o.cost) from correlated_orders o where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10 ) AS max_order_amount from correlated_customers c order by customer_name Sort (cost=88.23..90.18 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Hash Left Join (cost=30.90..50.76 rows=780 width=40) Hash Cond: (c.customer_id = o.customer_id) -> Seq Scan on correlated_customers c (cost=0.00..17.80 rows=780 width=36) -> Hash (cost=28.73..28.73 rows=174 width=12) -> HashAggregate (cost=26.99..28.73 rows=174 width=12) Group Key: o.customer_id -> Seq Scan on correlated_orders o (cost=0.00..25.30 rows=338 width=12) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10)) ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A mesma consulta não é transformada quando o parâmetro GUC é OFF. O plano não terá junção externa, mas, sim, um subplano.
1>select set_config('apg_enable_correlated_scalar_transform', 'false', false);2>GO1>select customer_name, ( select max(o.cost)2>from correlated_orders o3>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount4>from correlated_customers c order by customer_name ;5>GOQUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Sort (cost=21789.97..21791.92 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Seq Scan on correlated_customers c (cost=0.00..21752.50 rows=780 width=40) SubPlan 1 -> Aggregate (cost=27.86..27.87 rows=1 width=8) -> Seq Scan on correlated_orders o (cost=0.00..27.85 rows=2 width=8) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10) AND (customer_id = c.customer_id))
Limitações
-
A subconsulta deve estar na select_list 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.
Uma subconsulta correlacionada contendo uma cláusula TOP não pode ser transformada.
-
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. Se a cláusula WHERE contiver uma cláusula OR, ela não poderá ser transformada.
Usar o cache de subconsultas para melhorar a performance das consultas do Babelfish
A partir da versão 4.2.0, o Babelfish comporta 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.
|
Parâmetro |
Descrição |
Padrão |
Permitido |
|---|---|---|---|
apg_enable_subquery_cache |
Permite o uso de cache para subconsultas escalares correlacionadas. |
DESL. |
ON, OFF |
apg_subquery_cache_check_interval |
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_subquery_cache_hit_rate_threshold |
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_intervalpodem 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 linhasapg_subquery_cache_check_interval.Valores maiores de
apg_subquery_cache_hit_rate_thresholdtê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 Trabalhar com grupos de parâmetros.
Como alternativa, é possível definir a configuração apenas para a sessão atual chamando a função set_config. Por exemplo, execute o comando a seguir para ativar o cache de subconsultas no Babelfish. Para saber mais, consulte Configuration Settings Functions
1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO
Ativar o cache de subconsultas no Babelfish
Atualmente, o cache de subconsultas é OFF por padrão. Conforme mencionado acima, é possível ativá-lo modificando o grupo de parâmetros. Quando apg_enable_subquery_cache está ON, o Babelfish aplica o cache de subconsultas para salvar os respectivos resultados. 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. Para saber mais, consulte Usar o plano de explicação para melhorar a performance das consultas do Babelfish.
1>CREATE TABLE outer_table (outer_col1 INT, outer_col2 INT)2>CREATE TABLE inner_table (inner_col1 INT, inner_col2 INT)3>GO1>EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'off'2>GO1>SET BABELFISH_SHOWPLAN_ALL ON2>GO1>SELECT outer_col1, (2>SELECT inner_col13>FROM inner_table4>WHERE inner_col2 = outer_col25>) FROM outer_table6>GOQUERY PLAN ------------------------------------------------------------ Query Text: SELECT outer_col1, ( SELECT inner_col1 FROM inner_table WHERE inner_col2 = outer_col2 ) FROM outer_table Seq Scan on outer_table SubPlan 1 -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)
1>SET BABELFISH_SHOWPLAN_ALL OFF2>GO1>EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'on'2>GO
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.
Seq Scan on outer_table SubPlan 1 -> Memoize Cache Key: outer_table.outer_col2 Cache Mode: logical -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)
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.
Seq Scan on outer_table (actual rows=10 loops=1) SubPlan 1 -> Memoize (actual rows=1 loops=10) Cache Key: outer_table.outer_col2 Cache Mode: logical Hits: 4 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Seq Scan on inner_table (actual rows=1 loops=6) Filter: (inner_col2 = outer_table.outer_col2) 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 uma coluna de tabela externa do tipo de dados BIT, VARBINARY ou BINARY.
