1. 서론
커머스 서비스에선 상품을 정렬하는 게 매우 중요하다.
가장 간단한 인기순은 판매량의 역순으로 상품을 정렬해서 구현할 수 있다.
그런데 중요한 것은 전체 기간의 판매량이 아니다.
최신 며칠 간의 경향성을 반영해야 한다.
베스트셀러와 스테디셀러는 다르지 않은가?
베스트셀러 SQL
create table product_sales
(
product_id integer not null
references products
on update cascade,
date timestamp(3) not null,
total_sale_price integer not null,
primary key (product_id, date)
);최신 인기는 product_sales 테이블을 통해 계산한다.
해당 일에 발생하는 해당 상품 id의 총 판매 금액을 1개의 레코드에 몰아넣는 방식이다.
WITH week_product_sales AS (
SELECT product_id,
SUM(total_sales) AS total_sales,
FROM product_sales
WHERE date > '2025-11-21 01:13:00'
AND date < '2025-11-28 01:13:00'
GROUP BY product_id)
SELECT p.*
FROM products p
JOIN week_product_sales wps ON p.id = wps.product_id
WHERE ...
ORDER BY wps.total_sale_count DESC
LIMIT 10
OFFSET 0;위 쿼리는 2025년 11월 28일 00시 00분 00초를 기준으로 (기본 조건을 만족하는)
최근 일주일 간 가장 인기가 많았던 상품 10개를 조회한다.
그런데...
갑자기 낮은 가격순 정렬 기준이 추가되었다고 가정하자.
SELECT *
FROM products p
WHERE ...
ORDER BY p.price;
이렇게 쓰면 되잖아?
그런데...
인기순을 서브 정렬에 추가해야 한다.
즉, 가격이 동일한 상품에 대해 인기순으로 다시 정렬해야 한다는 뜻이다.
SELECT p.id
FROM products p
LEFT JOIN (
SELECT ps.product_id,
SUM(total_sale_price) AS total_sale_price
FROM product_sales ps
WHERE date > '2025-11-21 01:13:00' AND
date < '2025-11-28 01:13:00'
GROUP BY ps.product_id
) ps ON p.id = ps.product_id
ORDER BY p.sale_price, ps.total_sale_price DESC
LIMIT 10
OFFSET 0;그러면 이렇게 작성할 수밖에 없다.
결과는...
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Limit (cost=12282.62..21991.45 rows=100 width=16) (actual time=55.016..93.409 rows=100 loops=1) |
| -> Incremental Sort (cost=12282.62..84475718.54 rows=869965 width=16) (actual time=55.014..93.399 rows=100 loops=1) |
| Sort Key: p.price, ps.total_sale_price DESC |
| Presorted Key: p.price |
| Full-sort Groups: 2 Sort Methods: top-N heapsort, quicksort Average Memory: 28kB Peak Memory: 28kB |
| Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 28kB Peak Memory: 28kB |
| -> Nested Loop Left Join (cost=769.15..84414838.81 rows=869965 width=16) (actual time=9.767..93.330 rows=135 loops=1) |
| Join Filter: (p.id = ps.product_id) |
| Rows Removed by Join Filter: 665349 |
| -> Index Scan using idx_tmp on products p (cost=0.42..427600.69 rows=869965 width=8) (actual time=0.059..0.304 rows=135 loops=1) |
| -> Materialize (cost=768.72..929.62 rows=6436 width=12) (actual time=0.053..0.303 rows=4929 loops=135) |
| -> Subquery Scan on ps (cost=768.72..897.44 rows=6436 width=12) (actual time=7.095..8.269 rows=4936 loops=1) |
| -> HashAggregate (cost=768.72..833.08 rows=6436 width=12) (actual time=7.094..7.807 rows=4936 loops=1) |
| Group Key: ps_1.product_id |
| Batches: 1 Memory Usage: 721kB |
| -> Index Only Scan using product_sales_covering_idx on product_sales ps_1 (cost=0.42..727.00 rows=8344 width=8) (actual time=0.027..4.583 rows=8381 loops=1) |
| Index Cond: ((date > '2025-11-21 01:13:00'::timestamp without time zone) AND (date < '2025-11-28 01:13:00'::timestamp without time zone)) |
| Heap Fetches: 8229 |
|Planning Time: 0.934 ms |
|Execution Time: 93.559 ms |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
쿼리 플랜을 통해 확인할 수 있다.
Materialize 단계에서 product_sales, 즉 인기순 테이블을 필터링한다.
총 4,929개 행을 조회한 것이 확인된다.
그보다 위의 Nested Loop Left Join 단계의 (actual ...)에서 135개 행이 보인다.
낮은 가격순으로 정렬해 같은 값의 상품들 포함해 100개 값을 맞추니 총 135개의 상품을 찾았다는 것을 뜻한다.
그 바로 밑에 p.id=ps.product_id로 join filter를 거치고 665,349개 행을 버렸다.
이 행들은 실제로는 사용되지 않았지만 left join 연산에 불가피하게 참여했다.
2. 본론
커머스 서비스에선 상품을 리스트로 보여주는 경우가 매우 많기 때문에
위와 같이 높은 비용의 쿼리를 다량으로 수행하는 것은 큰 부담이 된다.
따라서 돌파구를 찾아야만 했고,
그래서 찾은 게 LATERAL JOIN이었다.
LATERAL JOIN
LATERAL JOIN은 서브쿼리로 하여금
FROM 절의 테이블에 있는 컬럼에 접근할 수 있게 하는 조인 방식이다.
그래서 FROM 테이블 내 각각의 row에 맞춰 서브쿼리를 1번씩 실행한다고 생각하면 된다.
core example
SELECT *
FROM customers
JOIN LATERAL (
SELECT *
FROM orders o
WHERE o.customer_id = c.id
ORDER BY o.placed_at DESC
LIMIT 1
) o ON true;위와 같이 top-N records for each group 문제에 최적화되어 있다.
orders 테이블에 (customer_id, placed_at) 복합 인덱스가 걸려 있을 경우
각 고객(group)마다 최신 placed_at(top-N records)을 낮은 비용으로 조회할 수 있기 때문이다.
적용기
SELECT p.id, p.sale_price, ps.total_sale_price, p.total_sale_price
FROM public.products p
LEFT JOIN LATERAL (
SELECT SUM(total_sale_price) AS total_sale_price,
SUM(total_sale_count) AS total_sale_count
FROM daily_product_sale_price_logs ps
WHERE ps.product_id = p.id
AND date > '2025-11-21 01:13:00'
AND date < '2025-11-28 01:13:00'
) ps ON TRUE
ORDER BY p.sale_price, ps.total_sale_price DESC NULLS LAST, p.total_sale_price DESC
LIMIT 100
OFFSET 0;나의 경우엔 반대였다.
LATERAL 테이블이 아닌 FROM 테이블에 ORDER BY + LIMIT이 걸려 있다.
(참고로 첫번째 정렬 기준 products.price에는 인덱스가 적용된다)
그렇지만...
신기하게도 PostgreSQL 엔진은 나의 소원을 들어주었다...
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Limit (cost=1078.54..1981.82 rows=100 width=20) (actual time=0.239..0.628 rows=100 loops=1) |
| -> Incremental Sort (cost=1078.54..7859258.88 rows=869965 width=20) (actual time=0.238..0.618 rows=100 loops=1) |
| Sort Key: p.price DESC, (sum(ps.total_sale_price)) DESC |
| Presorted Key: p.price |
| Full-sort Groups: 4 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB |
| -> Nested Loop Left Join (cost=8.87..7798379.15 rows=869965 width=20) (actual time=0.049..0.569 rows=102 loops=1) |
| -> Index Scan using idx_tmp on products p (cost=0.42..427600.69 rows=869965 width=12) (actual time=0.031..0.230 rows=102 loops=1) |
| -> Aggregate (cost=8.44..8.45 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=102) |
| -> Index Scan using product_sales_pkey on product_sales ps (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=102)|
| Index Cond: ((product_id = p.id) AND (date > '2025-11-21 01:13:00'::timestamp without time zone) AND (date < '2025-11-28 01:13:00'::timestamp without time zone)) |
|Planning Time: 0.308 ms |
|Execution Time: 0.674 ms |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+위 쿼리 플랜을 보자.
Nested Loop Left Join으로 상품과 인기순 테이블을 조인하는 것은 동일하다.
그런데 그 밑에 p.sale_price에 대해 인덱스 스캔이 발생하면서 가격으로 정렬된 인덱스를 그대로 활용한다.
그래서 Aggregate, 즉 각 상품에 대한 인기순 테이블 집계 연산이 loop=102번만 발생하면서
이전 쿼리의 4,936회보다 획기적으로 줄었고,
join filter로 인해 버리는 행도 1개도 없게 되었다.
3. 결론
실무 외에도 꾸준히 관심을 갖게 된 주제 위주로 책이나 글을 꾸준히 학습하고는 있지만
언제나 가장 깊게 파서 확실히 습득하는 건 실무에서 오는 것 같다.
이번에도 우연히 발견한 LATERAL JOIN을 아무렇지 않게 적용했다가 쿼리 플랜 보고
이 상태 되었다가
어떻게 이 상황을 설명할 수 있는지 찾아보다 보니 확실하게 알게 된 것 같다.
앞으로도 top-N group을 뽑아내면서
순위를 결정하는 테이블을 LEFT JOIN 해야하는 상황에서 애용하려 한다..!
