목차

개발 노트

[Postgresql] 쿼리 플랜으로 의사 결정하기

천만일 2025. 3. 16. 22:55

서비스를 개발하다보면 쿼리가 점차 복잡해집니다.

 

쿼리가 복잡해지는 만큼, 부하를 줄 가능성이 크기 때문에 쿼리 튜닝이라는 용어들이 나오는 것 같습니다.

이름부터 근사한 쿼리 튜닝이라는 것을 하기 위해서는 쿼리 플랜을 볼 줄 알아야 합니다.

 

제가 개발한 간단한 기능을 통해 쿼리 플랜을 해석해보고, 간단하게 개선한 경험에 대해서 풀어보려고 합니다.

 

상황

대출 상품과 금리 정보 관계

 

구현하려고 했던 기능은 꽤나 간단합니다.

 

대출 상품을 다루는 loan_product 테이블과 대출 상품의 금리 정보를 다루는 daily_interest_rate 테이블이 존재합니다.

 

이름에서도 유추할 수 있지만, 금융사의 금리 정보는 매일 변경되기 때문에 매일 크론 잡이 돌면서 수집하고 있습니다.

 

금융사의 금리정보는 주로 영업일 기준으로 변경되기 때문에 변경되지 않은 날짜가 있을 수 있습니다.

이런 경우 금리정보를 저장하지 않습니다.

 

이런 상황에서 특정 날짜의 금리가 직전 금리와 얼마나 차이가 나는지 계산해야하는 요구사항이 발생했습니다.

 

일단 구현하자

저희 팀은 Django를 사용하고 있기 때문에, Django ORM으로 다음과 같이 구현했습니다.

가장 간단하고 한번의 쿼리로 해결할 수 있는 서브쿼리로 문제를 해결했습니다.

 

import datetime

from django.db.models import OuterRef, Subquery

def get_latest_interest_rate_change_by_date(
    loan_product_id: int,
    date: datetime.date,
):
    sub_query = Subquery(
        queryset=DailyInterestRate.objects.filter(
            loan_product=OuterRef("loan_product"),
            standard_date__lte=date,
        ).values(
            "loan_product"
        ).annotate(
            latest_standard_date=Max("standard_date")
        ).values("latest_standard_date")
    )

    queryset = DailyInterestRate.objects.filter(
        loan_product_id=loan_product_id,
        standard_date=sub_query
    )

    return list(queryset)

 

sub_query에서는 특정 금융 상품의 금리정보를 조회합니다.

기준이 되는 날짜 이전의 금리정보만 조회한 후, GROUP BY 문을 이용해 가장 큰 날짜를 반환합니다.

 

sub_query에서 반환하는 가장 최근의 날짜를 통해 금리정보를 다시 조회합니다.

 

위 코드의 쿼리 로그를 찍어보면 다음과 같습니다.

-- loan_product_id: 4, date: "2025-03-16"
select
	"daily_interest_rate"."id",
	"daily_interest_rate"."loan_product_id",
	"daily_interest_rate"."interest_rate",
	"daily_interest_rate"."standard_date"
from
	"daily_interest_rate"
where
	("daily_interest_rate"."loan_product_id" = 4
		and "daily_interest_rate"."standard_date" = (
		select
			MAX(U0."standard_date") as "latest_standard_date"
		from
			"daily_interest_rate" U0
		where
			(U0."loan_product_id" = ("daily_interest_rate"."loan_product_id")
				and U0."standard_date" <= '2025-03-16')
		group by
			U0."loan_product_id"))

 

 

서브쿼리를 꼭 써야할까?

분명 간단한 기능으로 보였는데,, 꽤나 복잡한 SQL 쿼리가 생성되었습니다.

 

서브쿼리의 장점은 네트워크 통신을 여러 번 하지 않고 한번의 쿼리로 원하는 결과를 얻을 수 있다는 것입니다.

과연 쿼리 수를 줄이는 것이 최선의 선택일까요?

 

쿼리를 두 번 나눠서 보내는 방법으로도 구현해보았습니다.

import datetime

def get_latest_interest_rate_change_by_date(
    loan_product_id: int,
    date: datetime.date,
):
    latest_standard_date = (
        DailyInterestRate.objects.filter(
            loan_product=loan_product_id,
            standard_date__lte=date,
        )
        .values("loan_product")
        .annotate(latest_standard_date=Max("standard_date"))
        .values_list("latest_standard_date", flat=True)[0]
    )

    queryset = DailyInterestRate.objects.filter(
        loan_product_id=loan_product_id,
        standard_date=latest_standard_date
    )

    return list(queryset)

 

서브 쿼리를 사용하기 위해 썼던 OuterRef가 사라진 것 말고는 큰 차이가 없습니다.

 

위 함수가 생성하는 SQL은 다음과 같습니다.

-- loan_product_id: 4, date: "2025-03-16"
// 1차 쿼리
select
	MAX("daily_interest_rate"."standard_date") as "latest_standard_date"
from
	"daily_interest_rate"
where
	("daily_interest_rate"."loan_product_id" = 4
		and "daily_interest_rate"."standard_date" <= '2025-03-16')
group by
	"daily_interest_rate"."loan_product_id"
	
// 2차 쿼리
select
	"daily_interest_rate"."id",
	"daily_interest_rate"."loan_product_id",
	"daily_interest_rate"."interest_rate",
	"daily_interest_rate"."standard_date"
from
	"daily_interest_rate"
where
	("daily_interest_rate"."loan_product_id" = 4
		and "daily_interest_rate"."standard_date" = '2025-03-16')

 

Python 코드도 코드지만, SQL이 참 간결해서 보기 좋습니다.

 

쿼리 플랜 활용

두 방식 중 어떤 방식을 적용할지 고민하던 차에, 쿼리 플랜을 확인하면 좋겠다는 생각이 들었습니다.

 

제가 사용하는 PostgreSQL에서 쿼리 플랜을 사용하는 법은 쿼리문 앞에 explain analyze를 추가하는 것입니다.

 

서브 쿼리를 사용하는 SQL의 쿼리 플랜입니다.

Index Scan using daily_interest_rate_loan_product_id_719fa548 on daily_interest_rate  (cost=0.29..458895.22 rows=6 width=44) (actual time=676.625..683.919 rows=6 loops=1)
  Index Cond: (loan_product_id = 4)
  Filter: (standard_date = (SubPlan 1))
  Rows Removed by Filter: 1119
  SubPlan 1
    ->  GroupAggregate  (cost=7.38..400.52 rows=1 width=12) (actual time=0.606..0.606 rows=1 loops=1125)
          ->  Bitmap Heap Scan on daily_interest_rate u0  (cost=7.38..399.51 rows=399 width=12) (actual time=0.086..0.512 rows=1113 loops=1125)
                Recheck Cond: (loan_product_id = daily_interest_rate.loan_product_id)
                Filter: (standard_date <= '2025-03-16'::date)
                Heap Blocks: exact=273375
                ->  Bitmap Index Scan on daily_interest_rate_loan_product_id_719fa548  (cost=0.00..7.28 rows=399 width=0) (actual time=0.049..0.049 rows=1168 loops=1125)
                      Index Cond: (loan_product_id = daily_interest_rate.loan_product_id)
Planning Time: 0.356 ms
Execution Time: 684.049 ms

 

위에서 부터 하나씩 살펴보겠습니다.

 

Index Scan using …

해당 라인의  daily_interest_rate_loan_product_id_719fa548 는 Foreign Key의 index입니다.

해당 Index를 통해 4번 상품을 걸렀습니다.

 

Filter: (standard_date = (SubPlan 1))

 loan_product_id=4 를 거른 후, Subplan 1의 결과에 대응되는 데이터를 걸렀습니다.

이 필터로 인해 걸러진 로우는 1119개 입니다.

 

SubPlan 1 - GroupAggregate

loops=1125는 loan_product_id = 4인 로우의 갯수로, SubPlan 1은 총 1125번 실행되었습니다.

GroupAggregate는 결과를 집계하여  Max(standard_date) 를 구했고, 1개의 결과를 냈습니다. (rows=1)

 

SubPlan 1 - Bitmap Heap Scan

Bitmap Index와 Heap을 함께 사용하여 스캔합니다.

전반적으로  loan_product_id=4 로 걸러진 모든 로우가 서브쿼리를 타는 상황이었습니다.

0.05ms 정도의 적은 비용의 쿼리라도 O(N)만큼 반복으로 돈다고 생각하면, 추후 점점 느려질 가능성이 있습니다.

 

 

다음은 두 번 나눠서 보낸 쿼리문의 쿼리 플랜입니다.

-- 1차 쿼리
GroupAggregate  (cost=17.14..407.13 rows=1 width=12) (actual time=0.615..0.616 rows=1 loops=1)
  ->  Bitmap Heap Scan on daily_interest_rate  (cost=17.14..404.27 rows=1142 width=12) (actual time=0.084..0.524 rows=1125 loops=1)
        Recheck Cond: (loan_product_id = 4)
        Filter: (standard_date <= '2025-03-16'::date)
        Heap Blocks: exact=243
        ->  Bitmap Index Scan on daily_interest_rate_loan_product_id_719fa548  (cost=0.00..16.85 rows=1142 width=0) (actual time=0.051..0.052 rows=1168 loops=1)
              Index Cond: (loan_product_id = 4)
Planning Time: 0.083 ms
Execution Time: 0.648 ms

-- 2차 쿼리
Bitmap Heap Scan on daily_interest_rate  (cost=16.85..403.98 rows=4 width=106) (actual time=0.492..0.495 rows=6 loops=1)
  Recheck Cond: (loan_product_id = 4)
  Filter: (standard_date = '2025-03-16'::date)
  Rows Removed by Filter: 1119
  Heap Blocks: exact=243
  ->  Bitmap Index Scan on daily_interest_rate_loan_product_id_719fa548  (cost=0.00..16.85 rows=1142 width=0) (actual time=0.051..0.051 rows=1168 loops=1)
        Index Cond: (loan_product_id = 4)
Planning Time: 0.098 ms
Execution Time: 0.529 ms

 

SubPlan 1과 동일되는 플랜이 단순히 구분된 것을 알 수 있습니다.

이로 인해 O(N)번씩 돌던 서브 플랜이 한번으로 줄어든 것을 확인할 수 있습니다.

 

쿼리 시간만 본다면 쿼리를 두 번 나눠서 보내는 것이

684.049 ms → 1.177ms (0.648 ms + 0.529 ms)로 압도적으로 빠름을 알 수 있습니다.

 

통상적으로 쿼리를 보내는 네트워크 레이턴시를 왕복 30ms로 잡는다고 합니다.

(데이터베이스가 같은 리전에 있거나 로컬에 존재하면 더 빠를 것이고, 타 대륙에 위치하면 더 오래 걸릴 것입니다.)

 

쿼리를 두 번 보내는 로직의 경우 서브 쿼리 로직보다 30ms 정도 더 걸린다고 감안하더라도, 두 번 보내는 것이 더욱 효율적이라는 결론을 내릴 수 있었습니다.

저작자표시 비영리 변경금지

'개발 노트' 카테고리의 다른 글

무중단 배포 적용기  (1) 2025.01.19

'개발 노트'의 다른글

  • 현재글[Postgresql] 쿼리 플랜으로 의사 결정하기

관련글

댓글

티스토리툴바