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 은 알아둘 만한 유용한 기능이며,

다양한 케이스를 커버하며 속도를 향상 시키거나,

혹은 코드를 더 쉽고 이해하기 좋게 만들 수 있도록 사용할 수 있습니다.

 

 

 

 

| Reference |

Postgresql Official - QUERIES LATERAL

Cybertec: Understanding-lateral-joins-in-postgresql/