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 비용이 절반으로 감소했다는 의미다.