쿼리 튜닝을 통해 WMS 레이어 응답 속도 80% 개선하기

0. SQL과 친해지기 위한 과정

실무를 시작하고 여러가지를 배울 수 있었지만, 그 중 가장 많이 도움이 된 것은 sql을 다룰 수 있게 된 것이라고 생각한다. 한창 취업을 준비하던 당시에는 JPA나 QueryDSL에 익숙해진 나머지, 간단한 join이 들어간 쿼리 정도만 이해하고 작성할 수 있었는데, 프로젝트에 투입되고 보니 MyBatis를 비롯한 생 sql을 다룰 일이 많았었다. 특히, mybatis 쿼리의 경우 100줄은 가볍게 넘기는 복잡한 쿼리들이 많아 익숙해지기 위한 시간이 필요했고 이에 적응하게 되었다.

 

또한, 실행 계획을 통해 성능이 떨어지는 쿼리를 분석하고 개선하는 경험도 할 수 있었다. 지나치게 느린 쿼리를 분석해보면 인덱스를 제대로 타지 않거나 정렬 과정에서 디스크에 접근하는 경우들이 있었다. 이런 경험들을 몇 번 겪어보니, 제대로 쿼리와 DBMS에 대해 공부해야겠다는 생각이 들었고 Real MySQL이라는 책을 완독하게 되는 계기가 되었다. 물론 이 책은 MySQL이라는 특정 DBMS에 대해 기술한 책이지만, 스토리지 엔진이나 옵티마이저의 동작 과정, 인덱스나 힌트를 통한 쿼리 성능 개선 등 범용적으로 도움이 되는 지식들을 쌓을 수 있었다.

 

결과적으로, 팀에서 성능이 떨어지는 쿼리들을 분석하고 튜닝할 수 있는 역할을 수행할 수 있을 정도로 이전보다 sql과 친해질 수 있었다. (물론, 아직 모르는게 더 많다. 무엇을 모르는지 이전보다 조금 더 알 뿐.)

 

---

 

1. 문제 상황 

현재 우리 팀은 GIS 관련 프로젝트를 진행하고 있다. 당연하게도 지도 기반의 기능들을 개발할 일이 많았고, 그 중 하나가 지도에 원하는 레이어를 띄우는 WMS(Web Map Service)를 다루는 것이었다. WMS는 GIS 데이터에 접근하기 위한 인터페이스로써 웹을 통해 지도 이미지를 제공하는 서비스인데, 데이터베이스와 연결하여 특정 좌표에 해당하는 데이터를 조회할 수 있다. 이때 공간 데이터를 기반으로 단순하게 db의 데이터를 테이블째로 읽어올 수 도 있지만, sql을 작성하여 보다 다양한 조건이 추가된 데이터들도 읽을 수가 있다.

 

문제는 이런 복잡한 쿼리를 통해 읽어오는 데이터는 레이어 응답 속도가 너무 느린 경우들이 있었는데, 아니나 다를까 클라이언트로부터 관련 기능의 속도 개선 요청 사항이 들어오게 되었다. (대략 10초 이상)

 

---

 

2. 해결 과정

WMS 레이어의 응답 속도가 느린 경우, 대부분은 해당 쿼리의 성능이 좋지 않은 상황이다. 따라서, 문제가 되는 쿼리들을 살펴보고 개선해나갔다. 작업 환경은 PostgreSQL 16 버전이며, 쿼리는 간소화하여 설명한다.

 

먼저, 사용되는 데이터와 쿼리에 대해 간단히 설명하자면 다음과 같다.

• beneficial_area(수혜면적) 테이블에서 수정날짜가 최신인 데이터를 조회한다.
• 이때, 해당 토지의 pnu(필지고유번호, 인덱스)를 가진 지번 정보를  cadastral_map(지적도) 테이블에서 가져와 함께 조회한다.

각 테이블의 레코드 수는 다음과 같았다.

• beneficial_area: 2,000,000
• cadastral_map: 40,000,000

 

2.1. 불필요한 필드 정리

처음 쿼리를 확인한 결과, 사용하지 않는 불필요한 필드들을 조회하고 있었다. 따라서, 가장 먼저 시도한 것은 필요한 필드들만을 남기고 정리하는 것이었다. WMS 최적화 가이드에도 불필요한 필드 조회는 지양하라고 권장하고 있다.

 

select
	base.pnu,
	base.geometry_data,
	(
		select parcel_number
		from cadastral_map cm
		where cm.pnu = base.pnu
	) as parcel_number
from 
	(
		select
			pnu,
			geometry_data,
			ROW_NUMBER() OVER(partition by pnu order by mod_date desc) as rn
		from
			beneficial_area
	) base
where
	rn = 1

 

실행 계획을 살펴보면, Window Function을 통해 최신 레코드를 찾고 스칼라 서브쿼리로 조인하고 있는 것을 확인할 수 있었다.

 

2.2. 서브쿼리와 JOIN 절 정리

다음으로 from 절의 서브쿼리를 CTE로 정리하고, parcel_number를 조회하는 서브쿼리를 left join으로 조인하도록 수정하였다.

with base as (
	select
		pnu,
		geometry_data,
		ROW_NUMBER() OVER(partition by pnu order by mod_date desc) as rn
	from
		beneficial_area
) 
select 
	base.pnu,
	base.geometry_data,
	cm.parcel_number
from
	base
left join
	cadastral_map cm on base.pnu = cm.pnu
where 
	rn = 1

 

실행 계획을 살펴보면, 기존의 스칼라 서브쿼리가 Nested Loop Join으로 동작하는 것으로 변경된 것을 확인할 수 있었는데, 이는 외부 쿼리의 행마다 독립적으로 호출되다가 옵티마이저에 의해서 가장 효율적인 조인 알고리즘으로 변경된 것을 의미한다. Nested Loop Join은 반복되는 중첩된 루프를 통해 두 테이블의 행을 하나씩 비교하고 결합하는 방식이다.

 

이때의 레이어 응답은 평균 3초 후반에서 4초 중반의 시간이 걸렸다.

 

2.3. DISTINCT ON 사용

마지막으로 기존의 윈도우 함수 대신 DISTINCT ON을 사용한 모습이다. DISTINCT ON의 경우, PostgreSQL에서 제공하는 기능으로 그룹 별로 한 개의 레코드를 가져오는데, 예시 쿼리처럼 가장 최신의 데이터만 활용하는 경우 윈도우 함수보다 더 효율적으로 사용할 수 있다. (정렬 필수)

with base as (
	select distinct on (pnu)
		pnu,
		geometry_data
	from
		beneficial_area
	order by pnu, mod_date DESC
)
select 
	base.pnu,
	base.geometry_data,
	cm.parcel_number
from
	base
left join
	cadastral_map cm on base.pnu = cm.pnu

 

실행 계획을 살펴보면, 기존의 쿼리가 모든 레코드에 대해 순위(rn)를 매기고 그 중 첫 번째 레코드만 사용하도록 필터링하는 과정을 거친 반면, DISTINCT ON은 정렬된 데이터가 준비되면 pnu가 바뀌는 시점까지만 레코드를 읽고 나머지를 버리는 Unique 연산을 수행하기 때문에 더 빠르게 쿼리를 처리하는 것을 확인할 수 있었다.

 

이때의 레이어 응답은 평균 2초 후반의 시간이 걸리는 것을 확인할 수 있었다.

 

---

 

3. 결과

결과적으로 처음 10초 가량 걸리던 쿼리를 2초 후반대까지 대략 80% 가량 개선할 수 있었다. 물론, 이전보다 많이 빨라졌다해도 사용자 경험은 1초 대의 응답 또한 느리다고 생각할 수 있기 때문에 추가적인 장치들을 찾게 되었다. 그 결과로 해당 WMS에서 사용할 수 있는 타일 캐시 기능이 있다는 것을 확인하였고, 이를 통해 아쉬운 점을 어느정도 해소할 수 있었다!