SQL 레벨업 - 3장 SQL 조건 분기 (1/2)

2018-11-05

8강 UNION을 사용한 쓸데없이 긴 표현

UNION을 사용한 조건 분기는 큰 문제를 작은 문제로 나눌 수 있다는 점에서 생각하기 쉽다는 장점이 있다. 따라서 조건 분기에 관련된 문제에서 가장 먼저 생각할 수 있는 방법이다.

하지만 성능적인 측면에서 큰 단점을 가지고 있다. 외부적으로 하나의 SQL 구문을 실행하는 것처럼 보이지만 내부적으로 SELECT 구문을 실행하는 실행 계획으로 해석된다. 따라서 테이블에 접근하는 횟수가 많아져 I/O 비용이 크게 증가한다.

1. UNION을 사용한 조건 분기와 관련된 간단한 예제

TB_ITEM

item_id	year	item_name	price_tax_ex	price_tax_in
100	2000	머그컵	500	525
100	2002	머그컵	600	630
101	2000	티스푼	500	525
101	2003	티스푼	500	525

-- 구하고자 하는 결과
item_name | year | price
----------*------*------
머그컵     | 2000 | 500
머그컵     | 2002 | 630
티스푼     | 2000 | 500
티스푼     | 2003 | 525

2001년과 2002년 이후로 구분하여 값을 출력하면 된다.

-- UNION을 사용한 조건 분기
SELECT item_name, year, price_tax_ex AS price
FROM Items
WHERE year <= 2001
UNION ALL
SELECT item_name, year, price_tax_ex AS price
FROM Items
WHERE year >= 2002

조건이 배타적이기 때문에 중복된 레코드가 발생하지 않기 때문에 정렬 처리를 하지 않아도 되는 UNION ALL을 사용한다.

위 쿼리문의 첫 번째 문제는 쓸데없이 길다는 것이다. 거의 같은 쿼리를 두번이나 실행하게 된다. 그리고 두 번째는 성능이다.

UNION을 사용했을 때의 실행 계획 문제

UNION을 사용한 쿼리 성능 문제를 명확하기 위해 실행계획을 본다.

Append (cost-0.00..2.42 rows=12 width=47)
-> Seq Scan on items ... 생략
	Filter: (year <= 2001)
-> Seq Scan on items ... 생략
	Filter: (year >= 2002)

PostgreSQL의 실행 계획

UNION 쿼리는 Items 테이블에 2회 접근하고 TABLE ACCESS FULL 이 발생한다. 따라서 비용도 데이터에 따라서 선형적으로 증가하게 된다.

정확한 판단 없는 UNION 사용 회피

간단하게 사용하기에는 UNION을 편리하다. 하지만 SELECT구문 전체를 여러번 사용해서 코드를 길게 많드는 것은 쓸데없는 테이블 접근을 발생 시키며 성능을 떨어뜨린다. 또한 저장소I/O도 쓸데없이 소비하게 된다.

2. WHERE 구에 조건 분기를 하는 사람은 초보자

“조건 분기를 WHERE 구로 하는 사람들은 초보자다. 잘하는 사람은 SELECT 구문으로 조건 분기를 한다” 라는 말이 있다.

위 쿼리도 SELECT구문으로 최적화 할 수 있다.

SELECT item_name, year, 
	CASE WHEN year <= 2001 THEN price_tax_ex
		 WHEN year >= 2002 THEN price_tax_in AS price
FROM Items;

위 쿼리는 UNION을 사용한 쿼리문과 동일한 결과값을 가져온다.

Seq Scan on items (cost=0.00..1.18 ......)

PostgreSQL의 실행 계획

실행계획을 보면 Items테이블에 대한 접근이 1회로 줄어든것을 볼 수 있다. 이전 UNION을 사용한 쿼리문에 비하면 2배 좋아졌다고 볼 수 있다.

9강 집계와 조건 분기

TB_Population

prefecture	sex	pop
성남	1	60
성남	2	40
수원	1	90
수원	2	100

 -- 원하는 결과
prefecture | pop_men | pop_wom
-----------*---------*--------
수원        |      90 |     100
성남        |      60 |     40

1. 집계 대상으로 조건 분기

UNION을 사용한 방법

절차 지향적인 방법을 생각한다면 각각의 지역별 인구를 구한뒤 머지(merge) 하는 방법이 일반적이다.

SELECT prefecture, SUM(pop_men) AS pop_men, SUM(pop_wom) AS pop_wom
	FROM ( SELECT prefecture, pop AS pop_men, null AS pop_wom
          	FROM Population
          	WHERE sex = '1'
       	UNION
          SELECT prefecture, null AS pop_men, pop AS pop_wom
          	FROM Population
          	WHERE sex = '2') TMP
GROUP BY prefecture;

서브쿼리 TMP를 통해 각 인구가 별도의 레코드로 나온다. 그 뒤 GROUP BY를 통해 하나의 레코드로 집약한다.

UNION의 실행 계획

HashAggregate (cost ~~)
-> HashAggregate (cost ~~)
	-> Append
		-> Seq Scan on population
			Filter: (sex = '1'::bpchar)
		-> Seq Scan on population
			Filter: (sex = '2'::bpchar)

테이블에 두번의 풀스캔이 수행되는것을 확인할 수 있다.

집계 조건 분기도 CASE 식을 사용

CASE 식을 집약 함수 내부에 포함시켜서 ‘남성 인구’와 ‘여성 인구’ 필터를 만든다.

SELECT prefecture,
	SUM(CASE WHEN sex='1' THEN pop ELSE 0 END) AS pop_men,
	SUM(CASE WHEN sex='2' THEN pop ELSE 0 END) AS pop_wom
FROM Population
GROUP BY prefecture;

CASE 식의 실행 계획

HashAggreagate ( ~~)
	-> Seq Scan on population ( ~~ )

테이블 풀 스캔이 1회로 줄었다. 따라서 UNION의 경우에 비해 I/O 비용이 절반으로 감소했다는 의미다.

 SQL 레벨업 - 2장 SQL 기초 (2/2) SQL 레벨업 - 3장 SQL 조건 분기 (2/2) 