PostgreSQL SQL튜닝 – OMOP CDM 실험 결과

2020.12.21

최근 PGDay.Seoul.2020가 열렸습니다. 평소 관심있게 보던 SQL튜닝에 대한 세션이 있었습니다. SQL을 튜닝하기 전과 후를 비교하며 개선된 성능을 제시하였는데, 이 방법이 라인웍스가 사용하는 SQL에도 성능 상 이득을 가져다줄지 궁금했습니다. 그래서 230만명의 가상 데이터로 구성된 OMOP CDM 데이터에 실험하였고, 그 후기를 공유하고자 합니다. OMOP CDM을 다루는 엔지니어들에게 좋은 벤치마킹 사례가 되길 바랍니다.

세 줄 요약

이번 실험에서 배운 것을 세 가지로 요약해보겠습니다. 

  • 튜닝에 정답은 없고, 데이터에 따라 다를 수 있습니다.
  • 동일한 결과를 내는 여러 쿼리를 시도합시다.
  • explain으로 실행시간, cost, 조회 행수 등을 비교합시다.

SQL튜닝에 대한 세션은 20가지 이상의 튜닝 사례를 소개했는데, 본 글은 OMOP CDM에서 성능 향상을 보였던 사례만 간추려 소개합니다. 전체 사례는 라인웍스의 깃허브를 참고하세요.

테스트 환경

본 실험은 가상의 전자의무기록을 생성하는 Synthea 프로젝트로 만든 데이터를 사용했습니다. 230만명분의 가상데이터를 생성하였는데, 이는 2020년 11월 서울 인구수 기준으로 23%에 해당합니다. 의료 데이터 표준 모델 중 하나인 OMOP CDM 구조로 변환했습니다. Synthea 에서 OMOP CDM으로 변환할 때는  ETL-Synthea 를 사용하였습니다. 그림 1과 같이 실험에 사용된 테이블은 총 4개로, 방문 정보(visit_occurrence), 진단 정보(condition_occurrence), 약물 처방 정보(drug_exposure), 컨셉 정보(concept) 입니다.

그림 1. 실험에 사용된 테이블

실험에 사용된 서버 사양입니다. 

  • CPU: Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz * 8
  • RAM: 64GB  
  • DISK: Samsung SSD 850 EVO 1TB
  • OS: Ubuntu 16.04
  • DBMS: PostgreSQL 11.6

성능이 향상되었던 튜닝 사례 소개

불필요한 반복 제거

불필요한 반복이 있을 수 있는 사례를 보여드리겠습니다. condition_occurrece 테이블은 환자의 진단을 기록하는 테이블입니다. OMOP CDM에서 진단을 비롯한 다양한 의료 용어를 코드마스터 역할을 하는 concept 테이블을 통해 고유하게 식별하고, 각 테이블에서 concept 테이블의 정수 타입 concept id 를 통해 사용하는데, 이 때문에 각 테이블에서 concept 이 어떤 의미인지 확인하기 위해서는 concept 테이블을 빈번히 사용합니다. (표 1 참조)

concept_idconcept_namedomain_id
260139Acute bronchitisCondition
표 1. concept 테이블의 예시

아래 예시는 10만건의 질병 컨셉의 정보를 순차적으로 조회하며, cid(260139, 급성 기관지염)와 일치하는 질병 컨셉의 아이디를 l_concept_name 테이블에 저장하는 프로시저 입니다.


-- 1.4ms
drop procedure C_NAME(l_concept_name varchar, cid int);
create or replace
procedure C_NAME(l_concept_name varchar, cid int) language plpgsql as $$ 

begin
 -- condition_occurrence 테이블 내용으로 100000번 반복 수행
 for cid in (
select
	co.condition_concept_id
from
	synthea_cdm.condition_occurrence co
limit 100000) loop begin
 -- 컨셉명 확인 위해 condition_concept_id 로 반복 조회
 select
	c.concept_name
into
	l_concept_name
from
	synthea_cdm.concept c
where
	c.concept_id = cid;
end;

raise notice '컨셉명:% ',
l_concept_name;
end loop;
end;

$$;

call C_NAME('public.procedure_test_1',
260139);

아래 예시는 개선된 쿼리인데, 불필요한 반복을 제거하여 실행 시간을 단축시켰습니다. 반복문 안의 SQL을 스칼라 서브쿼리로 변경하여, 건별 concept 테이블 조회를 하지 않았습니다. 스칼라 서브쿼리란 SELECT문에 있는 서브 쿼리를 말합니다. SQL튜닝에 대한 세션에는 1,000번 반복을 테스트했으나 실제로 10만번 이상 반복 시 성능 차이가 드러났습니다.


-- 0.7ms
drop procedure C_NAME2(l_concept_name varchar, cname varchar);
create or replace
procedure C_NAME2(l_concept_name varchar, cname varchar) language plpgsql as $$ 

begin
 -- Loop 안의 sql을 스칼라 서브쿼리로 변경
 for cname in (
select
	(
	select
		c.concept_name
	from
		synthea_cdm.concept c
	where
		c.concept_id = co.condition_concept_id) as concept_name
from
	synthea_cdm.condition_occurrence co
limit 100000) 

loop
 -- 컨셉명 확인 위해 condition_concept_id로 반복 조회
 raise notice '컨셉명:% ',
cname;
end loop;
end;

$$;

call C_NAME2('public.procedure_test_1', '');

페이징 쿼리 최적화 – 부분 페이징 처리 방식

웹 기반의 페이지네이션 처럼, 특정 조건의 데이터를 한번에 일정량씩만 봐야할 때가 있습니다. 이런 경우에 최적화된 페이징 쿼리를 쓰면 실행 시간을 줄일 수 있습니다. 아래 예시는 2009년 11월 15일 이전에 방문한 환자의 약물처방 기록을 최근 기록부터 10건씩 조회하는 쿼리입니다. 환자 방문 정보는 visit_occurrence 테이블에 저장되고, 약물 처방 정보는 drug_exposure에 저장되는데, 앞서 언급한 예시를 수행하기 위해서 환자 방문 정보 133,016,048 건과 약물 처방 정보 148,406,080건을 조인하고 정렬합니다.


-- 1m 56s
select
	*
from
	synthea_cdm.visit_occurrence vo,
	synthea_cdm.drug_exposure de
where
	vo.visit_occurrence_id = de.visit_occurrence_id
	and vo.visit_start_date < '2009-11-15'
order by
	vo.visit_start_date desc,
	de.days_supply offset 10 rows fetch next 10 rows only;

K 인라인 뷰에서 20행의 처방 날짜(visit_start_date)를 추출하고 중복값을 제거하기 위해 GROUP BY를 수행합니다.(1건 추출) 해당 처방 날짜 값으로 visit_occurrence 테이블을 조회합니다. K 인라인 뷰로부터 20 개 이내의 처방 날짜를 제공 받으므로 건수가 매우 적습니다. drug_exposure과 조인하여 최종 데이터를 추출하며 최근 처방 날짜 순과 처방량이 적은 순서로 정렬하여 10건의 데이터를 제공합니다. 이렇게 개선된 쿼리로 기존의 약 2분간의 실행시간에서 약 30초를 단축시켰습니다.


-- 1m 28s
select
	*
from
	(
	select
		visit_start_date
	from
		(
		select
			visit_start_date
		from
			synthea_cdm.visit_occurrence vo,
			synthea_cdm.drug_exposure de
		where
			vo.visit_occurrence_id = de.visit_occurrence_id
			and vo.visit_start_date < '2009-11-15'
		order by
			vo.visit_start_date 
                         desc offset 0 rows fetch next 20 rows only ) K
	group by visit_start_date ) V,
 synthea_cdm.visit_occurrence vo,
	synthea_cdm.drug_exposure de
where
	V.visit_start_date = vo.visit_start_date
	and vo.visit_occurrence_id = de.visit_occurrence_id
	and vo.visit_start_date < '2009-11-15'
order by
	vo.visit_start_date desc,
	de.days_supply offset 10 rows fetch next 10 rows only;

페이징 쿼리 최적화 - union all 을 이용한 페이징 처리

이번 예시는 두개의 쿼리를 UNION ALL로 합한 후 정렬하여 10건을 추출하는 것입니다. 위쪽 집합에서 133,011,332 행을, 아래쪽 집합에서 148,399,662 행을 추출하는데, 결국 10행씩 페이징하기 때문에 비효율적입니다.


-- 49ms
select
	*
from
	(
	select
		visit_occurrence_id,
		visit_concept_id as concept_id
	from
		synthea_cdm.visit_occurrence
	where
		visit_occurrence_id > 5000
union all
	select
		visit_occurrence_id,
		drug_concept_id as concept_id
	from
		synthea_cdm.drug_exposure
	where
		visit_occurrence_id > 5000 ) A
order by
	visit_occurrence_id desc offset 10 rows fetch next 10 rows only;

아래 예시는 위쪽 집합에서 20행, 아래쪽 집합에서 20행을 추출하여 합친 후 다시 정렬하고 10행을 추출함으로써 실행 시간을 절반 이상 단축시켰습니다.


-- 20ms
select
	visit_occurrence_id,
	concept_id
from
	(
	select
		*
	from
		(
		select
			visit_occurrence_id,
			visit_concept_id as concept_id
		from
			synthea_cdm.visit_occurrence
		where
			visit_occurrence_id > 5000
		order by
			visit_occurrence_id desc 
                         offset 0 rows fetch next 20 rows only ) A
union all
	select
		*
	from
		(
		select
			visit_occurrence_id,
			drug_concept_id as concept_id
		from
			synthea_cdm.drug_exposure
		where
			visit_occurrence_id > 5000
		order by
			visit_occurrence_id desc 
                        offset 0 rows fetch next 20 rows only ) B
              ) C
order by
	visit_occurrence_id desc 
offset 10 rows fetch next 10 rows only;

페이징 쿼리 최적화 - OUTER 조인을 이용한 페이징 처리

이번에는 약물 처방량이 많았던 순서대로 나열하면서, 해당 처방을 위해 언제 병원에 방문했는지 보는 예시입니다. 방문 날짜(visit_start_date)를 출력하기 위하여 drug_exposure 전체와 visit_occurrence 를 LEFT OUTER 조인을 합니다.


-- 5.7s
select
	A.drug_type_concept_id,
	A.drug_exposure_id,
	A.drug_concept_id,
	B.visit_start_date
from
	synthea_cdm.drug_exposure A
left join visit_occurrence B on
	A.visit_occurrence_id = B.visit_occurrence_id
order by
	A.days_supply desc offset 40000 rows fetch next 10 rows only;

최종 출력할 대상은 처방기록 중에 40,000번째부터 10개의 행입니다. 따라서 이 조건에 해당하는 처방 데이터를 추출하고 10행에 한하여 방문 일자를 붙이는 조인을 합니다. 이로써 1/100로 실행 시간을 단축시켰습니다.


-- 57ms
select
	V.drug_exposure_id,
	V.visit_occurrence_id,
	V.drug_concept_id,
	B.visit_start_date
from
	(
	select
		A.visit_occurrence_id,
		A.drug_exposure_id,
		A.drug_concept_id,
		A.days_supply
	from
		synthea_cdm.drug_exposure A
	order by
		A.days_supply desc offset 40000 rows fetch next 10 rows only ) V
left join visit_occurrence B on
	V.visit_occurrence_id = B.visit_occurrence_id
order by
	V.days_supply desc;

페이징 쿼리 최적화 - 웹화면 페이징 쿼리

이번에는 페이지네이션 구현에 자주 쓰이는 예시입니다. 조건에 맞는 전체 환자수나 처방건수를 보여주고 세부 내역을 한 페이지당 10건씩 보여주는 상황을 가정해보겠습니다. 

아래 예시는 전체 건수와 첫 페이지의 데이터를 함께 구하기 위하여 window 함수 2개를 사용하였습니다. RNUM between 에 조회할 페이지 번호에 맞춰 값을 바꿔주면 페이지네이션을 할 수 있습니다.


-- 654ms
select
	*
from
	(
	select
		count(*) over () as CNT,
		row_number () over (
			order by D.visit_occurrence_id,
			D.drug_concept_id) as RNUM,
		D.drug_exposure_id,
		D.visit_occurrence_id,
		D.drug_concept_id
	from
		synthea_cdm.drug_exposure D,
		synthea_cdm.visit_occurrence O
	where
		D.visit_occurrence_id = O.visit_occurrence_id
		and D.visit_occurrence_id between 1000 and 100000 ) A
where
	RNUM between 21 and 30;

아래는 전체 건수와 첫 페이지의 데이터를 구하는 쿼리입니다. window 함수 1개만 사용하면서 실행 시간이 절반으로 줄었습니다.


-- 312ms
select
	*
from
	(
	select
		count(*) over () as CNT,
		D.drug_exposure_id,
		D.visit_occurrence_id,
		D.drug_concept_id
	from
		synthea_cdm.drug_exposure D,
		synthea_cdm.visit_occurrence O
	where
		D.visit_occurrence_id = O.visit_occurrence_id
		and D.visit_occurrence_id between 1000 and 100000
	order by
		D.visit_occurrence_id,
		D.drug_concept_id ) A 
offset 20 rows fetch next 10 rows only;

사전에 Group by 수행 - HashAggregate 유도

이 예시는 처방일자(drug_exposure_start_date)별로 처방량(days_supply)이 가장 많은 환자를 추출하는 쿼리입니다. 


-- 1m 3s
select
	drug_exposure_start_date,
	days_supply,
	person_id
from
	(
	select
		row_number() over (partition by drug_exposure_start_date
	order by
		days_supply desc) as RN ,
		days_supply,
		drug_exposure_start_date,
		person_id
	from
		synthea_cdm.drug_exposure de ) A
where
	RN = 1;

처방일자를 기준으로 HashAggregate 하여 작업대상 건수를 줄인 후 정렬합니다. 이 방법으로 1분 3초 걸렸던 것을 약 20초를 단축시켰습니다.


-- 43.3s
select
	B.drug_exposure_start_date,
	B.days_supply,
	B.person_id
from
	(
	select
		drug_exposure_start_date,
		(max(array[days_supply, drug_exposure_id ]))[2] as drug_exposure_id
	from
		synthea_cdm.drug_exposure
	group by
		drug_exposure_start_date) A
join synthea_cdm.drug_exposure B on
	B.drug_exposure_id = A.drug_exposure_id;

나가며

SQL 튜닝으로 성능이 향상되었던 사례를 소개해드렸습니다. 다른 사례는 오히려 성능이 개선되리라 기대했던 것과 반대로 느린 결과가 나와서 소개하지 않았습니다. 여러분이 가지고 있는 데이터의 양이나 구조에 따라 성능이 달라질 수 있으니, 꼭 직접 실험하여 어떤 SQL이 적절한지 알아보시길 권합니다. 본 포스팅이 OMOP CDM을 다루는 엔지니어들에게 좋은 벤치마킹 사례가 되길 바랍니다.

라인웍스는 대량의 의료데이터를 효율적으로 처리하기 위하여 다양한 시도를 하고 있습니다. 저희와 함께 의료 데이터를 처리할 데이터 엔지니어를 모시고 있습니다. 자세한 내용은 채용 공고를 확인해주세요.

Cinyoung Hur

Lead Data Engineer, Software Developer

Cinyoung Hur