[Postgresql] 쿼리 속도개선 방법 정리

🚀  들어가며...

• 제가 실무에서 쿼리의 속도개선을 진행하면서 고민하고 시도해보았던 내용들을 정리해보는 시간을 가져보겠습니다. 다만, 예시들은 보안때문에 올릴수 없는점 양해부탁드립니다.

 

📑 내용

1. querystrings.py 적절한 TempTable 사용 (JOIN절에 과다한 서브쿼리 사용)

1. JOIN절에 과다한 서브쿼리를 사용하는 것 보다는 때로는 적절한 TempTable 사용 (실제 2,300여건 결과 TimeOut → 3초)
2. UPDATE - FROM 절 사용시에도 FROM 절의 서브쿼리를 temp table 로 변경했을 때 속도개선 효과가 클 수 있음

 

2. [querystrings.py] 여러 항목('...,...,...') 형태 등의 데이터를 다룰 때

1. '...,...,...' 형태의 데이터 양이 많을 수록 'REGEXP_SPLIT_TO_TABLE(...' 함수보다는 'UNNEST(STRING_TO_ARRAY(...' 형태의 함수가 더 효율적 (실제 13,000여건 결과 30초 → 0.7초 소요)
2. WHERE 조건의 IN 절에는 데이터가 많을 수록 불리하므로, INNER JOIN 절을 고려해보는 것도 좋은 방법 (실제 13,000여건 결과 TImeOut → 4.5초 소요)

 

 3. [Tip] 소요시간 측정 및 파악 하는 방법(time 라이브러리 사용)

1. API 호출로 View 단 진입부터 return 되기 전까지 어떤 부분에서 시간이 소요되는지 정확하게 파악하는게 중요
2. time 라이브러리를 사용하여 특정 구간의 소요시간을 측정 하여 OrderedDict 개체에 담아 이를 반환하도록 합니다.
3. 특히 대량데이터 조회건의 경우 postman 에서 호출시 결과데이터 display 에 더 많은 시간이 소요되거나 앱이 먹통되기 때문에 실제 데이터를 반환하는 로직은 잠시 주석처리 해두도록 해야 합니다.

 

3.1. 측정용 코드들 추가

# -*- coding: utf-8 -*-

@api_view(['GET', 'POST'])
def select_data(request):
    if request.method == "GET":
        dic = request.query_params.copy()
    else:
        dic = request.data.copy()

    ###### 속도 측정용 로직
    import collections, time
    result = collections.OrderedDict()  # 측정 결과를 순서대로 볼수 있도록 OrderedDict 에 결과를 담는다.
    bef_time = time.time()

    with get_service_connection(request) as cursor:
        cash = GetCashRemain(cursor, dic)

        ###### 속도 측정용 로직
        result['cash'] = time.time() - bef_time
        bef_time = time.time()

        cursor.execute(q_test_query1())
        dic.update(name_to_json(cursor)[0])

        ###### 속도 측정용 로직
        result['env'] = time.time() - bef_time
        bef_time = time.time()

        cursor.execute(q_test_query2(params=dic), dic)

        ###### 속도 측정용 로직
        result['execute main query'] = time.time() - bef_time
        bef_time = time.time()

        rows = name_to_json(cursor)

        ###### 속도 측정용 로직
        result['fetch data'] = time.time() - bef_time
        bef_time = time.time()

        crypt = CryptHelper(request)
        for row in rows:
            if row['private_number']:
                row['private_number'] = crypt.decrypt_social(data=row['private_number'])
                row['private_number'] = row['private_number'][:6] + '-' + row['private_number'][6:13]
            if row['no_security']:
                row['no_security'] = crypt.decrypt_string(data=row['no_security'])

        ###### 속도 측정용 로직
        result['decrypt'] = time.time() - bef_time
        bef_time = time.time()

    if 'test_column' not in dic and 'test_column2s' not in dic:
        rows = add_bungae_color(data=rows)

    ###### 속도 측정용 로직
    result['add color'] = time.time() - bef_time
    bef_time = time.time()

    return Response(data=result)
    # return Response(data=rows)  # 기존 반환문

 

 

3.2. 측정결과

{
    "cash":0.11293292045593262,
    "env":0.0039179325103759766,
    "execute main query":3.3925628662109375,
    "fetch data":16.065179109573364,   # 개선 포인트 확인
    "decrypt":0.27009010314941406,
    "add color":0.04897713661193848
}

 

3.3. 로직 수정

 

.......
#rows = name_to_json(cursor)
rows = name_to_json_large(cursor)
....

 

3.4. 개선결과 확인

{
    "cash":0.10492300987243652,
    "env":0.004446983337402344,
    "execute main query":3.836505889892578,
    "fetch data":0.7863199710845947,   # 개선 확인
    "decrypt":0.27866601943969727,
    "add color":0.04845118522644043
}

 

 

4. [querystrings.py] Select 절에서 case 문의 depth 가 깊을 때(case by case)

1. case 문의 depth 가 깊으면 조회 속도가 현저히 느려집니다.
2. temp table 에 쉬운조건(빠른 실행속도) 먼저 체크 후 해당 결과는 제외한 후 복잡한조건(느린 실행속도) 를 체크하도록 합니다.
3. coalesce(컬럼명, '') != '' 로직은 컬럼명 > '' 로 갈음하여 불필요한 함수 실행을 하지 않도록 합니다.
4. case 문을 대체하는 방법은 경우에 따라 다양하므로 본인의 case 에 맞추어 심층적인 고민이 필요합니다.

 

 5. [querystrings.py] 대량데이터가 존재하는 table 반복 사용시

1. 대량데이터가 존재하는 table 을 반복하여 사용하는 경우 temp table 에 데이터를 가져와서 사용합니다.
2. temp table 사용시 db 서버의 메모리를 사용하게 되므로 가능한 적은 컬럼과 집계 함수를 이용하여 최소한의 데이터를 담도록 합니다.

 

 6. [querystrings.py] 파라미터에 함수를 사용하는 경우

1. where 조건절에 함수를 사용한 파라미터를 사용할 경우 이론상(sql optimizer 에 따라 다름) row 수만큼 해당 행위를 반복하기때문에 view 단에서 1회 작업후 새로운 파라미터로 매핑합니다.
2. 특히 해당 함수와 조건절에 걸려있는 컬럼이 index 컬럼인 경우 index 를 사용할 수 없으므로 속도저하가 심하게 올 수 있습니다.

 

🙋🏻‍♂️ 후기

아직 경험이 많이 부족하지만, 앞으로 더 많은 경험과 더 많은 양의 데이터를 다뤄봄으로써 더 좋은 방법들을 고안해나가며 최적화를 시킬수 있는 다양한 방법들을 연구해 보아야겠습니다.