[PostgreSQL] Parallel Query 튜닝 케이스 (Parallel Seq Scan PLAN 회피를 통한 퍼포먼스 향상)

1. 개요

모 고객사에서 '차세대 인프라 프로젝트'를 진행하며 있었던 Query 튜닝 케이스를 다루어보고자 합니다.
 

* 당시 상황
[AS-IS] 운영에서 [TO-BE] 개발DB로 테스트 이관 후 서비스를 검증하던 중,
회원가입 단계에서 문제가 발생했습니다.
(이용약관 / 개인정보제공 동의 항목을 불러오는데 무려 8초나 소요)
 

2. 실행계획 비교

[AS-IS]에서는 해당 Entity가 Bitmap Index Scan으로 풀리는 Plan을 가집니다.
Cost 10만, Elapsed time 1초 미만의 Query였습니다.
 
[TO-BE] 환경에서 실행계획을 추출한 다음, 분석해보았습니다.
Cost 8만, Elapsed time 8.0초 의 Query가 되었습니다.
 
Cost 측면에서 살펴보자면, 기존 운영에 비해 더 낮은 Cost의 plan을 가지는 Query가 되었습니다.
 
하지만, Elapsed time이 말도 안 되게 늘어난 관계로 이를 개선 할 필요가 있습니다.
 

3. 실행계획 추출 및 Tuning

Plan 추출 후 특정 Entity의 Gather Merge, Parallel Seq Scan 부분을 주요하게 살펴봤습니다.
 
[TO-BE]의 conf  Parameter 값에서 parallel 관련 내용이 모두 Default인 점과 연관지어 생각해보고자 했습니다.
 
그리고 Parallel Seq Scan을 하지 않도록 hint를 적용 했습니다.

/*+ NoSeqScan(Entity) */
SELECT
...

 

제가 생각했던 문제 지점이 적중한 듯 합니다.
 
약 8초 가량 소요되던 Query가 0.6초만에 출력되었습니다.
 

4. 원인 분석 및 근거자료

우선,  상기의 항목에서 Parallel 관련 conf Parameter가 모두 Default 인 점을 언급드렸습니다.

#max_worker_processes = 8
#max_parallel_workers_per_gather = 2
#max_parallel_maintenance_workers = 2
#max_parallel_workers = 8
#parallel_setup_cost = 1000.0
#parallel_tuple_cost = 0.1
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB

Parallel worker 및 process가 설정되지 않은 상태에서 Parallel 작업을 수행했으며,
이로 인해 Performance 저하가 발생한 것으로 판단했습니다.
 
해당 내용을 뒷받침 할 수 있는 근거를 찾던 도중,
PostgreSQL Document에서도 동일한 내용을 찾아볼 수 있었습니다.

 

아래의 내용은 PostgreSQL Document에서 발췌한 내용입니다.
(출처 : https://www.postgresql.org/docs/current/how-parallel-query-works.html)

15.1 How Parallel Query Works The number of background workers that the planner will consider using is limited to at most max_parallel_workers_per_gather.  The total number of background workers that can exist at any one time is  limited by both max_worker_processes and max_parallel_workers.  Therefore, it is possible for a parallel query to run with fewer workers than planned, or even with no workers at all.  The optimal plan may depend on the number of workers that are available, so this can result in poor query performance.  If this occurrence is frequent, consider increasing max_worker_processes and max_parallel_workers so that more workers can be run simultaneously or alternatively reducing max_parallel_workers_per_gather so that the planner requests fewer workers.

** 번역
Background Worker는 max_worker_process 및 max_parallel workers 값으로 제한됩니다.
따라서 worker 수가 적거나 거의 없는 경우에도 Parallel Query를 실행할 수 있습니다.
이런 경우, Query 성능이 저하될 수 있습니다.

 

5. 정리

Background Worker가 적거나 없는 경우에도 Query가 Parallel로 실행될 수 있으며,
Query Performance에 영향을 미칠 수 있습니다.
 
해당 케이스에서는 Parallel Seq Scan을 하지 않도록 Query를 튜닝하여 해결했습니다.
 
하지만, 근본적인 해결을 위해서는 Query Tuning이 아닌
postgresql.conf 상에서의 Parameter Tuning이 필요할 것으로 사료됩니다.