LATERAL JOIN, 어렵지 않게 사용하기
본 포스팅은 LATERAL JOIN을 이해하고 실습해보기 위한 포스팅입니다.
LATERAL JOIN 은 PostgreSQL, Oracle, DB2, MS SQL 등에서 사용될 수 있지만, 잘 알려지지는 않은 기능입니다.
최근 쿼리를 작성하면서 LATERAL JOIN 기능을 사용할 일이 있었는데요.
LATERAL JOIN 으로 어떤 것들을 할 수 있을지 잘 파악한다면,
꽤 유용한 기능이라고 생각이 들어 글을 작성해보고자 합니다.
내용을 살펴보기 전에, SQL의 기본적인 SELECT 와 FROM 구문을 생각해봅시다.
SELECT <something...> FROM <table>;
기본적으로, 위 코드는 루프를 돌며 데이터를 가져오는 것을 알 수 있는데요.
마치 아래와 같은 pseudo 코드와 같이 생각할 수 있습니다.
for x in tab
loop
"do something"
end loop
테이블 내 각각의 엔트리마다, SELECT 구문에 작성된 내용들을 확인하고 가져옵니다.
주로 간단한 쿼리로도 결과를 반환하며, 개발자들이 원하는 요구사항을 충족하곤 합니다.
하지만 만약 "중첩" 루프가 필요하다면 어떻게 될까요?
이 때, 바로 LATERAL 이 좋은 옵션이 됩니다.
LATERAL JOIN
Presentation
데이터베이스에 많은 상품들이 저장되어 있고, 또 소비자들의 위시리스트를 갖고 있다고 가정해봅시다.
이 때, 목표를 각 위시리스트 마다 각 3개씩의 최상위 상품들을 찾아낸다고 설정해봅니다.
SQL은 간단히 아래와 같이 준비해보겠습니다.
postgres=# CREATE TABLE product AS
SELECT id AS product_id,
id * 10 * random() AS price,
'product ' || id AS product
FROM generate_series(1, 1000) AS id;
postgres=# CREATE TABLE wishlist
(
wishlist_id int,
username text,
desired_price numeric
);
postgres=# INSERT INTO wishlist VALUES
(1, 'sun.ny', '450'),
(2, 'kiana', '60'),
(3, 'gngsn', '1500')
;
상품 테이블은 1,000개의 상품들로 채워져있습니다.
postgres=# SELECT * FROM product LIMIT 10;
product_id | price | product
------------+--------------------+------------
1 | 2.4007917948567448 | product 1
2 | 15.46599584566692 | product 2
3 | 12.327515522601548 | product 3
4 | 27.733379812329897 | product 4
5 | 24.29865467116169 | product 5
6 | 5.258883593537411 | product 6
7 | 47.37658728440519 | product 7
8 | 35.28311989012357 | product 8
9 | 51.37916660750454 | product 9
10 | 0.5820259854988574 | product 10
(10 rows)
다음으로, 아래와 같은 두 가지의 위시리스트를 가집니다.
postgres=# SELECT * FROM wishlist;
wishlist_id | username | desired_price
-------------+----------+---------------
1 | sun.ny | 450
2 | kiana | 60
3 | gngsn | 1500
(3 rows)
Running
위와 같은 데이터들을 생성한 후,
이제 실제 상황에서 발생할 수 있는 상황을 알아보도록 하겠습니다.
모든 위시리스트에 대해 최상위 세 개의 상품을 얻고 싶을 때,
다음과 같은 pseudo-code를 작성해볼 수 있습니다.
for x in wishlist
loop
for y in products order by price desc
loop
found++
if found <= 3
then
return row
else
jump to next wish
end
end loop
end loop
여기서 핵심은, 중첩 루프가 필요로 한다는 것입니다.
이를 위해서는 먼저, 모든 위시리스트를 순회하고 난 후,
정렬된 상품 목록에서 상위 3개를 고른 후,
다음 위시리스트로 넘어갑니다.
자, 이제 pseudo-code 를 LATERAL-JOIN 을 사용해서 표현해보도록 하겠습니다.
SELECT *
FROM wishlist AS w,
LATERAL (
SELECT *
FROM product AS p
WHERE p.price < w.desired_price
ORDER BY p.price DESC
LIMIT 3
) AS x
ORDER BY wishlist_id, price DESC;
가장 먼저 FROM 구문에 위시리스트 테이블을 확인할 수 있는데요.
LATERAL이 무엇을 동작하냐면, 상위 쿼리인 위시리스트의 엔트리에 접근할 수 있게 합니다.
그래서 위시 리스트에 있는 각각의 엔트리를 위해 우리는 세 개의 상품들을 출력합니다.
우리가 필요한 상품이 어떤 것인지 알아내기 위해서는,
위시리스트 테이블에 있는 desired_price 라는 값을 알 수 있도록 만들어야 하는데요.
일반적인 JOIN으로는 외부쿼리의 엔트리에 접근할 수 없죠.
하지만, LATERAL 를 사용한다면, 이를 가능하게 해줍니다.
위 pseudo-code에서 확인했던 FROM 구문은 outer loop 코드로,
그리고 LATERAL 은 inner loop로 이해해도 좋습니다.
이에 대한 결과 값을 확인해보면 아래와 같이 출력됩니다.
wishlist_id | username | desired_price | product_id | price | product
-------------+----------+---------------+------------+--------------------+-------------
1 | sun.ny | 450 | 662 | 449.89768179672245 | product 662
1 | sun.ny | 450 | 54 | 433.56115443539176 | product 54
1 | sun.ny | 450 | 736 | 432.9276145431032 | product 736
2 | kiana | 60 | 535 | 59.783453007038275 | product 535
2 | kiana | 60 | 714 | 58.56072565492091 | product 714
2 | kiana | 60 | 42 | 55.71988686789034 | product 42
3 | gngsn | 1500 | 608 | 1494.5220473756249 | product 608
3 | gngsn | 1500 | 173 | 1494.2119790676245 | product 173
3 | gngsn | 1500 | 944 | 1491.6807231391247 | product 944
(9 rows)
PostgreSQL은 각 위시리스트마다 세 개의 엔트리를 반환하는데,
이는 정확히 우리가 원하는 대로 출력된다는 것을 알 수 있습니다.
여기서 중요한 부분은 LIMIT 구문이 LATERAL에 공급되는 SELECT 내부에 있다는 것입니다.
따라서 전체 행 수가 아니라 위시리스트당 행 수를 제한합니다.
PostgreSQL은 LATAL 조인을 최적화하는 작업을 꽤 잘 하고 있습니다.
이 경우 실행 계획execution plan은 매우 간단해 보입니다.
postgres=# EXPLAIN SELECT *
FROM wishlist AS w,
LATERAL (SELECT *
FROM product AS p
WHERE p.price < w.desired_price
ORDER BY p.price DESC
LIMIT 3
) AS x
ORDER BY wishlist_id, price DESC;
QUERY PLAN
-------------------------------------------------------------------------------------
Sort (cost=23403.03..23409.40 rows=2550 width=91)
Sort Key: w.wishlist_id, p.price DESC
-> Nested Loop (cost=27.30..23258.74 rows=2550 width=91)
-> Seq Scan on wishlist w (cost=0.00..18.50 rows=850 width=68)
-> Limit (cost=27.30..27.31 rows=3 width=23)
-> Sort (cost=27.30..28.14 rows=333 width=23)
Sort Key: p.price DESC
-> Seq Scan on product p (cost=0.00..23.00 rows=333 width=23)
Filter: (price < (w.desired_price)::double precision)
(9 rows)
LATERAL JOIN 은 알아둘 만한 유용한 기능이며,
다양한 케이스를 커버하며 속도를 향상 시키거나,
혹은 코드를 더 쉽고 이해하기 좋게 만들 수 있도록 사용할 수 있습니다.