Babelfish에서 상관관계가 있는 하위 쿼리 최적화 - Amazon Aurora
하위 쿼리 변환을 사용하여 Babelfish 쿼리 성능 개선하위 쿼리 캐시를 사용하여 Babelfish 쿼리 성능 향상

Babelfish에서 상관관계가 있는 하위 쿼리 최적화

상관관계가 있는 하위 쿼리는 외부 쿼리의 표 열을 참조합니다. 외부 쿼리에서 반환된 모든 행에 대해 한 번씩 평가됩니다. 다음 예에서는 하위 쿼리가 표 t1의 열을 참조합니다. 이 표는 하위 쿼리의 FROM 절에는 포함되지 않지만, 외부 쿼리의 FROM 절에서는 참조됩니다. 표 t1의 행이 100만 개이면 하위 쿼리를 100만 번 평가해야 합니다.


SELECT col1, col2 FROM t1 WHERE col1 < (SELECT sum(col1) FROM t2 WHERE t1.col2 = t2.col2);

하위 쿼리 변환을 사용하여 Babelfish 쿼리 성능 개선

Babelfish는 상관관계가 있는 하위 쿼리를 동일한 외부 조인으로 변환하여 해당 하위 쿼리를 가속화할 수 있습니다. 이 최적화는 다음과 같은 두 유형의 상관관계가 있는 하위 쿼리에 적용됩니다.

  • 단일 집계 값을 반환하고 SELECT 목록에 나타나는 하위 쿼리입니다. 자세한 내용은 Microsoft Transact-SQL 설명서의 SELECT 절을 참조하세요.

    
SELECT ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) FROM outer_sb_t1;

  • 단일 집계 값을 반환하고 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;

하위 쿼리에서 변환 활성화

상관관계가 있는 하위 쿼리를 동등한 외부 조인으로 변환할 수 있도록 하려면 apg_enable_correlated_scalar_transform 파라미터를 ON으로 설정합니다. 이 파라미터는 Babelfish 4.2.0 이상 버전에서 사용할 수 있습니다. 이 파라미터의 기본값은 OFF입니다.

클러스터 또는 인스턴스 파라미터 그룹을 수정하여 파라미터를 설정할 수 있습니다. 자세한 내용은 Amazon Aurora의 파라미터 그룹을 참조하십시오.

또는 set_config 함수를 직접 호출하여 현재 세션에 대한 설정만 구성할 수도 있습니다. 예를 들어, 다음 명령을 실행하여 Babelfish에서 하위 쿼리 캐시를 활성화합니다. 자세한 내용은 구성 설정 함수를 참조하세요.


1> SELECT set_config('apg_enable_subquery_cache', 'on', false)
2> GO

변환 확인

EXPLAIN 명령을 사용하여 상관관계가 있는 하위 쿼리가 쿼리 계획에서 외부 조인으로 변환되었는지 확인합니다. 자세한 내용은 설명 계획을 사용하여 Babelfish 쿼리 성능 향상 단원을 참조하십시오.

변환이 활성화되면 상관관계가 있는 관련 하위 쿼리 부분이 외부 조인으로 변환됩니다. 예시: 


1> select set_config('apg_enable_correlated_scalar_transform', 'true', false);
2> GO
1> set BABELFISH_STATISTICS PROFILE on
2> GO
1> select customer_name, ( select max(o.cost) from correlated_orders o 
2> where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount
3> from correlated_customers c order by customer_name ;
4> GO

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

GUC 파라미터를 OFF로 설정해도 동일한 쿼리는 변환되지 않습니다. 이 계획에는 외부 조인이 없고, 대신 하위 계획이 있습니다.


1> select set_config('apg_enable_correlated_scalar_transform', 'false', false);
2> GO
1> select customer_name, ( select max(o.cost) 
2> from correlated_orders o 
3> where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount
4> from correlated_customers c order by customer_name ;
5> GO
QUERY 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))

제한 사항

  • 하위 쿼리는 select_list 또는 where 절의 조건 중 하나에 있어야 합니다. 그렇지 않으면 변환되지 않습니다.

  • 하위 쿼리는 집계 함수를 반환해야 합니다. 사용자 정의 집계 함수는 변환에 지원되지 않습니다.

  • 반환 표현식이 단순 집계 함수가 아닌 하위 쿼리는 변환되지 않습니다.

  • 하위 쿼리 WHERE 절의 상관관계가 있는 조건은 단순한 열 참조여야 합니다. 그렇지 않으면 변환되지 않습니다.

  • 절이 일반 등식 조건자여야 하는 하위 쿼리의 상관관계가 있는 조건입니다.

  • TOP 절이 포함된 상관관계가 있는 하위 쿼리는 변환할 수 없습니다.

  • 하위 쿼리에는 HAVING 또는 GROUP BY 절을 포함할 수 없습니다.

  • 하위 쿼리의 where 절에는 AND와 결합된 하나 이상의 조건자가 포함될 수 있습니다.

참고

변환의 성능 영향은 스키마, 데이터 및 워크로드에 따라 달라집니다. 변환과 관련된 하위 쿼리 실행은 외부 쿼리에서 생성된 행 수가 증가함에 따라 성능을 크게 개선할 수 있습니다. 프로덕션 환경에서 활성화하기 전에 실제 스키마, 데이터 및 워크로드를 사용하여 비프로덕션 환경에서 이 기능을 테스트하는 것이 좋습니다.

하위 쿼리 캐시를 사용하여 Babelfish 쿼리 성능 향상

버전 4.2.0부터 Babelfish는 상관관계가 있는 하위 쿼리의 결과를 저장하는 하위 쿼리 캐시를 지원합니다. 이 기능은 하위 쿼리 결과가 이미 캐시에 있는 경우 상관관계가 있는 반복 하위 쿼리 실행을 건너뜁니다.

하위 쿼리 캐시 이해

PostgreSQL의 Memoize 노드는 하위 쿼리 캐시의 핵심 부분입니다. Memoize 노드는 로컬 캐시에 해시 표를 유지하여 입력 파라미터 값을 쿼리 결과 행으로 매핑합니다. 해시 표의 메모리 제한은 work_mem과 hash_mem_multiplier의 곱입니다. 자세한 내용은 리소스 소비를 참조하세요.

쿼리 실행 중에 하위 쿼리 캐시는 캐시 적중률(CHR)을 사용하여 캐시가 쿼리 성능을 개선하는지를 추정하고 쿼리 런타임 시 캐시를 계속 사용할지를 결정합니다. CHR은 총 요청 수에 대한 캐시 적중 횟수의 비율입니다. 예를 들어, 상관관계가 있는 하위 쿼리를 100번 실행해야 하고 해당 실행 결과 중 70개를 캐시에서 검색할 수 있는 경우 CHR은 0.7입니다.

apg_subquery_cache_check_interval 캐시 누락 횟수가 발생할 때마다 CHR이 apg_subquery_cache_hit_rate_threshold보다 큰지 확인하여 하위 쿼리 캐시의 이점을 평가합니다. 이보다 작으면 캐시가 메모리에서 삭제되고 쿼리 실행은 캐시되지 않은 원래 하위 쿼리 재실행 상태로 돌아갑니다.

하위 쿼리 캐시 동작을 제어하는 파라미터

다음 표에는 하위 쿼리 캐시 동작을 제어하는 파라미터가 나열되어 있습니다.

파라미터

설명

Default

허용

apg_enable_subquery_cache

상관관계가 있는 스칼라 하위 쿼리에 캐시를 사용할 수 있도록 지원합니다.

OFF

 ON, OFF

apg_subquery_cache_check_interval

하위 쿼리 캐시 적중률을 평가할 빈도(캐시 누락 횟수)를 설정합니다.

500

 0–2147483647

apg_subquery_cache_hit_rate_threshold

하위 쿼리 캐시 적중률의 임계값을 설정합니다.

0.3

 0.0~1.0
참고

  • apg_subquery_cache_check_interval 값이 클수록 CHR 기반 캐시 이점 추정의 정확도는 향상될 수 있지만, 캐시 표에 apg_subquery_cache_check_interval 행이 있을 때까지 CHR이 평가되지 않으므로 캐시 오버헤드가 증가합니다.

  • apg_subquery_cache_hit_rate_threshold 값이 클수록 하위 쿼리 캐시를 포기하고 캐시되지 않은 원래 하위 쿼리 재실행으로 돌아가는 경향이 있습니다.

클러스터 또는 인스턴스 파라미터 그룹을 수정하여 파라미터를 설정할 수 있습니다. 자세한 내용은 Amazon Aurora의 파라미터 그룹을 참조하십시오.

또는 set_config 함수를 직접 호출하여 현재 세션에 대한 설정만 구성할 수도 있습니다. 예를 들어, 다음 명령을 실행하여 Babelfish에서 하위 쿼리 캐시를 활성화합니다. 자세한 내용은 구성 설정 함수를 참조하세요.


1> SELECT set_config('apg_enable_subquery_cache', 'on', false)
2> GO

Babelfish에서 하위 쿼리 캐시 켜기

현재는 하위 쿼리 캐시가 기본적으로 OFF 상태입니다. 위에서 언급한 것처럼, 파라미터 그룹을 수정하여 활성화할 수 있습니다. apg_enable_subquery_cache가 ON인 경우 Babelfish는 하위 쿼리 캐시를 적용하여 하위 쿼리 결과를 저장합니다. 그러면 쿼리 계획의 SubPlan 아래에 Memoize 노드가 생깁니다.

예를 들어, 다음 명령 시퀀스는 하위 쿼리 캐시 없이 상관관계가 있는 단순 하위 쿼리의 예상 쿼리 실행 계획을 보여줍니다. 자세한 내용은 설명 계획을 사용하여 Babelfish 쿼리 성능 향상을 참조하십시오.


1> CREATE TABLE outer_table (outer_col1 INT, outer_col2 INT)
2> CREATE TABLE inner_table (inner_col1 INT, inner_col2 INT)
3> GO
1> EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'off'
2> GO
1> SET BABELFISH_SHOWPLAN_ALL ON
2> GO
1> SELECT outer_col1, (
2> SELECT inner_col1
3> FROM inner_table
4> WHERE inner_col2 = outer_col2
5> ) FROM outer_table
6> GO

QUERY 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 OFF
2> GO
1> EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'on'
2> GO

apg_enable_subquery_cache를 활성화한 후 쿼리 계획의 SubPlan 노드 아래에 Memoize 노드가 포함되며, 이는 하위 쿼리가 캐시를 사용할 계획임을 나타냅니다.


            
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)

실제 쿼리 실행 계획에는 캐시 적중 및 캐시 누락을 포함하여 하위 쿼리 캐시에 대한 자세한 정보가 포함되어 있습니다. 다음 출력은 표에 일부 값을 삽입한 후의 위 예제 쿼리의 실제 쿼리 실행 계획을 보여줍니다.


            
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

총 캐시 적중 수는 4이고 총 캐시 누락 수는 6입니다. 총 적중 및 누락 수가 Memoize 노드의 루프 수보다 적으면 CHR 평가를 통과하지 못했고 캐시가 어느 시점에서 정리되고 폐기되었음을 의미합니다. 이에 따라 하위 쿼리 실행이 캐시되지 않은 원래 재실행으로 되돌아갔습니다.

제한 사항

하위 쿼리 캐시는 특정 패턴의 상관관계가 있는 하위 쿼리를 지원하지 않습니다. 이러한 유형의 쿼리는 하위 쿼리 캐시가 설정되어 있더라도 캐시 없이 실행됩니다.

  • IN/EXISTS/ANY/ALL 상관관계가 있는 하위 쿼리

  • 비결정적 함수를 포함하는 상관관계가 있는 하위 쿼리.

  • BIT, VARBINARY 또는 BINARY 데이터 유형의 외부 표 열을 참조하는 상관관계가 있는 하위 쿼리.