문제

0번 페이지 100 ~ 150 ms

10000번 페이지 700 ~ 1000 ms

20000번 페이지 1400 ~ 1700 ms

select
    p1_0.product_id,
    p1_0.product_name,
    p1_0.thumbnail_url,
    p1_0.price,
    p1_0.vendor_id,
    v1_0.name,
    case
        when (c1_0.parent_id is not null)
            then c1_0.parent_id
        else p1_0.category_id
    end,
    case
        when (c1_0.parent_id is not null)
            then p2_0.name
        else c1_0.name
    end,
    p1_0.category_id,
    c1_0.name
from
    product p1_0
    left join
    vendor v1_0
        on p1_0.vendor_id=v1_0.id
    join
    category c1_0
        on c1_0.category_id=p1_0.category_id
    join
    category p2_0
        on p2_0.category_id=c1_0.parent_id
order by
    p1_0.product_id desc
offset
    1000000 rows
    fetch
    first 100 rows only

snowflake 기반 PK - 인덱스 OK

offset 으로 인해 skip 비용 발생 → 뒷 페이지로 갈수록 느림

1차 개선

서브쿼리에서 페이징 하도록 네이티브 쿼리 사용

    SELECT
        p1_0.product_id AS productId,
        p1_0.product_name AS name,
        p1_0.thumbnail_url AS thumbnailUrl,
        p1_0.price AS price,
        p1_0.vendor_id AS vendorId,
        v1_0.name AS vendorName,
        CASE         
            WHEN c1_0.parent_id IS NOT NULL 
                THEN c1_0.parent_id         
            ELSE p1_0.category_id     
    END AS mainCategoryId,
    CASE         
        WHEN c1_0.parent_id IS NOT NULL 
            THEN p2_0.name         
        ELSE c1_0.name     
END AS mainCategoryName,
p1_0.category_id AS subCategoryId,
c1_0.name AS subCategoryName 
FROM
    (     SELECT
        p.product_id     
    FROM
        product p     
    JOIN
        category c 
            ON p.category_id = c.category_id     
    WHERE
        (
            NULL IS NULL 
            OR c.category_id = NULL 
            OR c.parent_id = NULL
        )       
        AND (
            NULL IS NULL 
            OR p.product_name ILIKE CONCAT('%', NULL, '%')
        )     
    ORDER BY
        p.product_id DESC     
    OFFSET
        1000000 ROWS 
    FETCH
        FIRST 100 ROWS ONLY ) p 
LEFT JOIN
    product p1_0 
        ON p.product_id = p1_0.product_id 
LEFT JOIN
    vendor v1_0 
        ON p1_0.vendor_id = v1_0.id 
JOIN
    category c1_0 
        ON c1_0.category_id = p1_0.category_id 
JOIN
    category p2_0 
        ON p2_0.category_id = c1_0.parent_id

0번 페이지 200 ~ 400 ms

10000번 페이지 400 ~ 600 ms

20000번 페이지 600 ~ 1000 ms

→ 여전히 느림

→ count 쿼리는 200 ms, 페이지 데이터 조회는 10000번 페이지 조회 기준 240ms

커서 기반 페이징으로 변경

2차 개선