LATERAL JOIN은 오픈소스 DBMS에서 인라인 뷰로 인한 SQL 성능 문제의 돌파구다.
이번 글에서는 동일한 SQL에 대해, 각 DBMS가 어떻게 처리하는지 살펴보고, 오픈소스 DBMS에서 LATERAL 조인을 통해 성능 개선을 하는 과정을 설명한다.
테스트에 사용하는 DBMS별 버전은 다음과 같다.
DBMS별로 ms_shop과 tr_ord_big이라는 동일한 구조와 동일한 데이터를 가진 테이블을 생성해 테스트를 진행한다. (ms_shop과 tr_ord는 SQL STARTER에서 사용하는 실습용 테이블이다. tr_ord_big은 tr_ord를 성능 테스트에 맞게 데이터양을 증가시킨 테이블이다.) 테스트를 위해 각 DBMS의 tr_ord_big에는 아래와 같은 tx01 인덱스를 생성한다.
CREATE INDEX startdbora.tr_ord_big_tx01 ON startdbora.tr_ord_big(shop_id, ord_dtm); -- For ORACLE
CREATE INDEX tr_ord_big_tx01 ON startdbpg.tr_ord_big(shop_id, ord_dtm); -- For PostgreSQL
CREATE INDEX tr_ord_big_tx01 ON startdbmy.tr_ord_big(shop_id, ord_dtm); -- For MySQL
아래의 [SQL-1]은 ms_shop과 tr_ord_big을 조인하는 SQL이다. tr_ord_big은 인라인 뷰로 처리되어 있다. [SQL-1]은 어느 DBMS든 성능상 이슈가 없다.
[SQL-1] ORACLE
SELECT t1.shop_id ,MAX(t1.shop_nm) shop_nm ,SUM(t2.ord_amt) ord_amt_sum
FROM startdbora.ms_shop t1
INNER JOIN (
SELECT a.shop_id ,a.ord_amt
FROM startdbora.tr_ord_big a
WHERE a.ord_dtm >= TO_DATE('20250101','YYYYMMDD')
AND a.ord_dtm < TO_DATE('20250102','YYYYMMDD')
) t2 ON (t1.shop_id = t2.shop_id)
WHERE t1.shop_nm LIKE 'New York%'
AND t1.shop_size >= 150
GROUP BY t1.shop_id;
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 265 | | | |
| 1 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 265 | 812K| 812K| 767K (0)|
| 2 | NESTED LOOPS | | 1 | 16 | 252 |00:00:00.01 | 265 | | | |
| 3 | NESTED LOOPS | | 1 | 41 | 252 |00:00:00.01 | 13 | | | |
|* 4 | TABLE ACCESS FULL | MS_SHOP | 1 | 1 | 2 |00:00:00.01 | 6 | | | |
|* 5 | INDEX RANGE SCAN | TR_ORD_BIG_TX01 | 2 | 41 | 252 |00:00:00.01 | 7 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| TR_ORD_BIG | 252 | 41 | 252 |00:00:00.01 | 252 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("T1"."SHOP_SIZE">=150 AND "T1"."SHOP_NM" LIKE 'New York%'))
5 - access("T1"."SHOP_ID"="A"."SHOP_ID" AND "A"."ORD_DTM">=TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A"."ORD_DTM"<TO_DATE(' 2025-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
[SQL-1] PostgreSQL
EXPLAIN (ANALYZE,COSTS OFF)
SELECT t1.shop_id ,MAX(t1.shop_nm) shop_nm ,SUM(t2.ord_amt) ord_amt_sum
FROM startdbpg.ms_shop t1
INNER JOIN (
SELECT a.shop_id ,a.ord_amt
FROM startdbpg.tr_ord_big a
WHERE a.ord_dtm >= TO_DATE('20250101','YYYYMMDD')
AND a.ord_dtm < TO_DATE('20250102','YYYYMMDD')
) t2 ON (t1.shop_id = t2.shop_id)
WHERE t1.shop_nm LIKE 'New York%'
AND t1.shop_size >= 150
GROUP BY t1.shop_id;
GroupAggregate (actual time=0.473..0.474 rows=1 loops=1)
Group Key: t1.shop_id
-> Nested Loop (actual time=0.086..0.399 rows=252 loops=1)
-> Index Scan using ms_shop_pk on ms_shop t1 (actual time=0.031..0.108 rows=2 loops=1)
Filter: (((shop_nm)::text ~~ 'New York%'::text) AND (shop_size >= 150))
Rows Removed by Filter: 298
-> Index Scan using tr_ord_big_tx01 on tr_ord_big a (actual time=0.032..0.108 rows=126 loops=2)
Index Cond: (((shop_id)::text = (t1.shop_id)::text) AND (ord_dtm >= to_date('20250101'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20250102'::text, 'YYYYMMDD'::text)))
Planning Time: 0.440 ms
Execution Time: 0.525 ms
[SQL-1] MySQL
EXPLAIN ANALYZE
SELECT t1.shop_id ,t1.shop_nm ,SUM(t2.ord_amt) ord_amt_sum
FROM startdbmy.ms_shop t1
INNER JOIN (
SELECT a.shop_id ,a.ord_amt
FROM startdbmy.tr_ord_big a
WHERE a.ord_dtm >= STR_TO_DATE('20250101','%Y%m%d')
AND a.ord_dtm < STR_TO_DATE('20250102','%Y%m%d')
) t2 ON (t1.shop_id = t2.shop_id)
WHERE t1.shop_nm LIKE 'New York%'
AND t1.shop_size >= 150
GROUP BY t1.shop_id;
-> Group aggregate: sum(startdbmy.a.ord_amt) (cost=18681 rows=300) (actual time=148..148 rows=1 loops=1)
-> Nested loop inner join (cost=17732 rows=9495) (actual time=98.1..148 rows=252 loops=1)
-> Filter: ((startdbmy.t1.shop_nm like 'New York%') and (startdbmy.t1.shop_size >= 150)) (cost=32 rows=11.1) (actual time=18.5..18.7 rows=2 loops=1)
-> Index scan on t1 using PRIMARY (cost=32 rows=300) (actual time=18.4..18.7 rows=300 loops=1)
-> Index lookup on a using tr_ord_big_tx01 (shop_id=startdbmy.t1.shop_id), with index condition: ((startdbmy.a.ord_dtm >= <cache>(str_to_date('20250101','%Y%m%d'))) and (startdbmy.a.ord_dtm < <cache>(str_to_date('20250102','%Y%m%d')))) (cost=825 rows=855) (actual time=61.8..64.4 rows=126 loops=2)
DBMS별 실행시간과 처리 과정을 정리하면 다음과 같다.
| [SQL-1] | 실행시간 | 인라인 뷰(t2) 처리 방식 |
|---|---|---|
| ORACLE | 0.01 초 | 인라인 뷰를 내부적으로 제거하고 t1과 조인 처리 |
| PostgreSQL | 0.0005 초 | 인라인 뷰를 내부적으로 제거하고 t1과 조인 처리 |
| MySQL | 0.14 초 | 인라인 뷰를 내부적으로 제거하고 t1과 조인 처리 |
<aside> 💡
환경에 따른 성능 차이
위 결과로 PostgreSQL이 가장 좋다고 단정하면 안된다. 세 DBMS는 하나의 노트북에 모두 설치된 상태다. 동일한 스키마와 데이터로 구성되어 있지만, DBMS마다 설치 방식(Docker, 윈도우 네이티브)이 다르며 사용하는 자원도 다르다. 그러므로 1초 미만의 실행 시간 차이는, 환경에 따른 차이로 크게 의미가 없다.
</aside>
[SQL-1]의 인라인 뷰(t2)는 tr_ord_big을 단순 조회하고 있다. GROUP BY나 분석함수, 집계함수 등이 사용되지 않고 있다. 이처럼 인라인 뷰에서 SQL이 단순 조회인 경우를 Simple View라고 한다. 단순 뷰는 DBMS가 알아서, 인라인 뷰를 제거하고 조인 SQL로 변경해 SQL을 처리한다.