Aurora PostgreSQL 쿼리 계획 개선

계획 성과를 평가하고 계획을 수정하여 쿼리 계획 관리를 개선합니다. 쿼리 계획을 개선하는 방법에 대한 자세한 내용은 다음 주제를 참조하세요.

계획 성능 평가

최적화 프로그램이 계획을 미승인 계획으로 캡처한 후 apg_plan_mgmt.evolve_plan_baselines 함수를 사용하여 실제 성능에 따라 계획을 비교합니다. 성능 실험의 결과에 따라 승인되지 않음에서 승인됨 또는 거부됨으로 계획의 상태를 변경할 수 있습니다. 대신에 apg_plan_mgmt.evolve_plan_baselines 함수를 사용하여 요구 사항을 충족하지 않을 경우 계획을 일시적으로 비활성화하도록 결정할 수 있습니다.

더 나은 계획 승인

다음 예제에서는 apg_plan_mgmt.evolve_plan_baselines 함수를 사용하여 관리형 계획의 상태를 승인됨으로 변경하는 방법을 보여줍니다.

SELECT apg_plan_mgmt.evolve_plan_baselines (
   sql_hash, 
   plan_hash, 
   min_speedup_factor := 1.0, 
   action := 'approve'
) 
FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';

NOTICE:     rangequery (1,10000)
NOTICE:     Baseline   [ Planning time 0.761 ms, Execution time 13.261 ms]
NOTICE:     Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms]
NOTICE:     Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms
NOTICE:     Unapproved -> Approved
evolve_plan_baselines 
-----------------------
0
(1 row)     

출력에 파라미터 바인딩이 1과 10,000인 rangequery 문에 대한 성능 보고서가 표시됩니다. 새로운 미승인 계획(Baseline+1)이 이전에 승인된 계획 최상의 계획(Baseline)보다 더 낫습니다. 새 계획이 Approved 상태인지 확인하려면 apg_plan_mgmt.dba_plans 보기를 확인합니다.

SELECT sql_hash, plan_hash, status, enabled, stmt_name 
FROM apg_plan_mgmt.dba_plans;

sql_hash  | plan_hash |  status  | enabled | stmt_name  
------------+-----------+----------+---------+------------
1984047223 | 512153379 | Approved | t       | rangequery
1984047223 | 512284451 | Approved | t       | rangequery
(2 rows)

이제 관리형 계획에 설명문의 계획 기준인 승인된 계획 두 개가 포함됩니다. 또한 apg_plan_mgmt.set_plan_status 함수를 호출하여 계획의 상태 필드를 'Approved', 'Rejected', 'Unapproved' 또는 'Preferred'로 직접 설정할 수도 있습니다.

느린 계획 거부 또는 비활성화

계획을 거부하거나 비활성화하려면 'reject' 또는 'disable' 을 apg_plan_mgmt.evolve_plan_baselines 함수에 작업 파라미터로 전달합니다. 이 예제에서는 해당 문에 대한 최상의 Unapproved 계획보다 최소 10% 더 느린 캡처된 Approved 계획을 비활성화합니다.

SELECT apg_plan_mgmt.evolve_plan_baselines(
sql_hash,  -- The managed statement ID
plan_hash, -- The plan ID
1.1,       -- number of times faster the plan must be 
'disable'  -- The action to take. This sets the enabled field to false.
)
FROM apg_plan_mgmt.dba_plans
WHERE status = 'Unapproved' AND   -- plan is Unapproved
origin = 'Automatic';       -- plan was auto-captured

또한 계획을 거부됨 또는 비활성화됨으로 즉시 설정할 수도 있습니다. 계획의 활성화 필드를 true 또는 false로 즉시 설정하려면 apg_plan_mgmt.set_plan_enabled 함수를 호출합니다. 계획의 상태 필드를 'Approved', 'Rejected', 'Unapproved' 또는 'Preferred'로 즉시 설정하려면 apg_plan_mgmt.set_plan_status 함수를 호출합니다.

현재 유효하지 않고 앞으로도 유효하지 않을 것으로 예상되는 계획을 삭제하려면 apg_plan_mgmt.validate_plans 함수를 사용하세요. 이 함수를 사용하면 유효하지 않은 계획을 삭제하거나 비활성화할 수 있습니다. 자세한 내용은 계획 검증 단원을 참조하십시오.

pg_hint_plan을 사용하여 계획 수정

쿼리 최적화 프로그램은 모든 설명문에 대한 최적 계획을 찾도록 설계되었으며, 대부분의 경우 최적화 프로그램은 좋은 계획을 찾아냅니다. 하지만 경우에 따라 최적화 프로그램에서 생성된 것보다 훨씬 더 나은 계획이 존재한다는 것을 알게 될 수도 있습니다. 최적화 프로그램을 통해 원하는 계획을 생성하기 위한 두 가지 권장 방법은 PostgreSQL에서 pg_hint_plan 확장을 사용하거나 Grand Unified Configuration(GUC) 변수를 설정하는 것입니다.

이러한 기술 중 하나를 사용하여 쿼리 최적화 프로그램에서 계획을 사용하도록 지정할 경우 쿼리 계획 관리를 사용하여 새 계획을 캡처하고 사용하도록 설정할 수도 있습니다.

pg_hint_plan 확장을 사용하여 SQL 문의 조인 순서, 조인 방법 또는 액세스 경로를 변경할 수 있습니다. 특수 pg_hint_plan 구문과 함께 SQL 설명을 사용하여 최적화 프로그램에서 계획을 생성하는 방식을 수정할 수 있습니다. 예를 들어 문제의 SQL 문에 양방향 조인이 있다고 가정해 보겠습니다.

SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;            

또한 최적화 프로그램에서는 조인 순서(t1, t2)를 선택하지만 조인 순서(t2, t1)가 더 빠름을 알고 있다고 가정해 보겠습니다. 다음 힌트는 최적화 프로그램에서 더 빠른 조인 순서(t2, t1)를 사용하도록 지정합니다. 최적화 프로그램에서 SQL 문에 대한 계획을 생성하되 해당 문을 실행하지 않도록 EXPLAIN을 포함합니다. (출력이 표시되지 않음)

/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id; 

다음 단계에서는 pg_hint_plan을 사용하는 방법을 보여줍니다.