서브 쿼리의 성능에 대한 오해

개인적인 경험 상, 취업을 준비하는 개발자 지망생 분들이나 혹은 신입 내지 주니어 개발자 분들 사이에는 서브 쿼리를 사용하면 데이터의 검색 성능이 드라마틱하게 나빠질 것이라는 통념이 존재하는 것 같다. 

나 또한 그렇게 생각해왔었는데 이게 정말 맞는지, 아니면 오해인 것인지 확인해보려한다.

 

서브 쿼리란?

서브 쿼리란 메인 쿼리가 존재하는 상황에서 메인 쿼리 내에 포함된 또 다른 쿼리를 의미한다.

어떤 절에 포함된 쿼리인지에 따라 스칼라 서브 쿼리(SELECT 문), 인라인 뷰(FROM 절), 서브 쿼리(WHERE 절, 그냥 서브 쿼리라고 하는 듯...) 등의 명칭으로 불린다.

아래는 서브 쿼리의 예시이다.

-- 스칼라 서브 쿼리
SELECT 
  p.id AS id, 
  (SELECT email FROM account WHERE email = p.account) AS acc_email
FROM post p
WHERE created_at > '2024-01-20'::DATE - interval '1 MONTH';

-- 인라인 뷰
SELECT
  p.id AS post_id,
  a.email AS acc_email
FROM 
  account a,
  (SELECT * FROM post WHERE created_at > '2024-01-20'::DATE - interval '1 MONTH') p
WHERE p.account = a.email;

-- 서브 쿼리 (WHERE 절)
SELECT *
FROM account
WHERE 
  email IN (SELECT DISTINCT post.account FROM post WHERE created_at > '2024-01-20'::DATE - interval '1 MONTH');

 

서브 쿼리의 성능

위 예시의 서브 쿼리를 기준으로, 실행 계획과 성능이 어떤지 확인해보고 해당 쿼리의 대안으로 여겨지는 쿼리의 실행 계획과 성능을 비교해보는 식으로 글을 진행하겠다. 위 쿼리들은 모두 비슷한 작업을 수행하는 쿼리이기 때문에 가장 위에 자리하고 있는 스칼라 서브 쿼리에 대해서만 확인해보겠다. 이 때 테스트용 DB로는 PostgreSQL을 사용하였다.

테스트 사전 작업

해당 테스트를 위해 account 테이블과 post 테이블을 아래의 스키마로 생성해주었고, 각각 5만개와 25만개의 더미 데이터를 추가해주었다.

CREATE TABLE account (
  email VARCHAR(50) PRIMARY KEY,
  pwd VARCHAR(20) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE post (
  id SERIAL PRIMARY KEY,
  account VARCHAR(50) NOT NULL,
  title VARCHAR(30) NOT NULL,
  content VARCHAR NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  CONSTRAINT fk_post_account FOREIGN KEY (account) REFERENCES account(email)
);

성능 확인

위 예시의 쿼리는 아래와 같다.

SELECT 
  p.id AS id, 
  (SELECT email FROM account WHERE email = p.account) AS acc_email
FROM post p
WHERE created_at > '2024-01-20'::DATE - interval '1 MONTH';

해당 쿼리의 실행 계획은 아래와 같다.

실행 계획을 보면 post 테이블을 풀스캔하면서 조건절에 부합하는 6만여개의 데이터를 찾고, 이들에 대해 일일이 account 테이블의 인덱스를 확인하는 모습이다. 서브 쿼리 쪽의 6만건의 loop 값이 이를 말해준다.

그렇다면 이제 위와 동일한 데이터를 반환하는 조인 쿼리를 실행시켜보자. 재구성한 쿼리는 아래와 같다.

SELECT 
  p.id AS id, 
  a.email AS acc_email
FROM post p
LEFT JOIN account a
  ON a.email = p.account
WHERE p.created_at > '2024-01-20'::DATE - interval '1 MONTH';

해당 쿼리의 실행 계획은 아래와 같다.

실행 계획을 보면 post 테이블을 풀스캔하여 조건에 맞는 데이터들을 읽고, account 테이블을 따로 풀스캔하여 해시테이블을 생성하여 이를 이용해서 조인을 수행하고 있다. 조인은 세션 별 메모리에 생성한 해시테이블을 통해 수행하기 때문에 버퍼 캐시를 읽는 논리적 IO가 확연히 줄어든 것을 알 수 있다. (187253회 -> 5570회) 또한 실제 성능도 많이 개선되었다. (277ms ->101ms)

그렇다면 표본이 단 하나의 쿼리이긴 하나, 동일한 데이터를 출력하는 쿼리에 대해 조인 쿼리가 훨씬 좋은 성능이 보이기 때문에 대부분의 경우 조인 연산이 더 낫다고 일반화할 수 있을까?

이는 명백히 아니다. 다시 한번 위 서브 쿼리의 실행 계획을 살펴보면, 매 레코드마다 서브 쿼리가 실행되고 있는 것을 알 수 있는데 이는 사실 NL 조인과 동일하게 작동하고 있는 것이다. 위에서 살펴본 경우는 6만건이나 되는 데이터를 조회하기 때문에 옵티마이저가 해시 조인을 선택했고, 이로 인해 서브 쿼리의 비효율이 커졌지만 옵티마이저가 NL 조인을 선택하는 경우에는 두 쿼리가 유사한 성능을 보일 가능성이 크다.

이 경우를 확인하기 위해 위 쿼리를 아래처럼 수정한 뒤 다시 한번 실행 계획을 확인해보자.

SELECT 
  p.id AS id, 
  (SELECT email FROM account WHERE email = p.account) AS acc_email
FROM post p
WHERE created_at > '2024-01-20'::DATE - interval '6 HOUR';

SELECT 
  p.id AS id, 
  a.email AS acc_email
FROM post p
LEFT JOIN account a
  ON a.email = p.account
WHERE p.created_at > '2024-01-20'::DATE - interval '6 HOUR';

두 쿼리의 실행 계획은 각각 아래와 같다.

스칼라 서브 쿼리 실행 계획

조인 쿼리 실행 계획

두 쿼리의 실행 계획을 비교해보면, 계층 구조가 조금 다르게 보일 수도 있으나 결국은 거의 동일하게 작동하는 것을 볼 수 있다. 따라서 NL 조인으로 실행되는 조인 쿼리라면 두 쿼리의 성능을 비슷하다고 볼 수 있다. 

 

결론

결론만 놓고 보자면, 결국 동일한 쿼리라면 조인문으로 작성 or 유도되도록 하는 것이 성능 상 차선을 보장하는 방법이 아니냐고 할 수 있고, 이는 타당해보인다. 하지만 오해하지 말아야 할 점은 이 경우 서브 쿼리라서 느린 것이 아니라 NL 조인이여서 느린 것이다. NL 조인은 조회해야 할 데이터가 많은 경우 성능 상 불이익이 발생하는 구조이기 때문에 자연적으로 발생하는 성능 저하이고, 옵티마이저가 서브 쿼리를 unnest 처리하여 다른 조인 방식으로 풀지 않는 이상 NL 조인의 구조를 탈피하는 것은 어렵다.

결론이 좀 길어졌는데, 하고 싶은 이야기는 결국 왜 서브 쿼리가 성능이 더 안좋은 것인지 제대로 파악하지 않고 무조건 서브 쿼리라서 안좋다는 것은 그 사람의 착각이라는 것이다. 이건 결국 까놓고 보면 NL 조인은 해시 조인보다 항상 성능이 안좋다! 라고 말하는 것과 다르지 않다.

 

여담

오라클에는 스칼라 서브 쿼리 캐싱이라는 기능이 존재한다. PGA 영역에 스칼라 서브 쿼리에 대해 input -> output을 캐싱해두고 동일한 input이 들어오는 경우 버퍼 캐시를 조회하지 않고 곧바로 output을 도출하는 식으로 작동한다. 따라서 본문에서 언급한 NL 조인 예시의 경우, 오히려 서브 쿼리 예제가 성능 상 더 나을 수도 있다. 본문의 예시는 Postgresql을 사용하였기 때문에 이에도 유사한 기능이 있나 찾아봤는데, 자료가 많이 나오진 않았으나 아래 아티클을 보면 유사한 기능이 현재는 존재하는 것으로 보인다.

커뮤니티 > 나만의 강좌및 팁 > Scalar subquery caching and Memoization1 - All About PostgreSQL

 

커뮤니티 > 나만의 강좌및 팁 > Scalar subquery caching and Memoization2 - All About PostgreSQL