LIMIT, OFFSET과 페이지 매김 기초

LIMIT: 결과 제한하기

SELECT * FROM products
ORDER BY created_at DESC
LIMIT 10;

OFFSET: 처음 N개 건너뛰기

-- Rows 11–20 (the second page of 10):
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;

페이지 나누기 = LIMIT + OFFSET

-- Page p (1-based), page size s:
SELECT * FROM articles
ORDER BY published_at DESC
LIMIT s OFFSET (p - 1) * s;

OFFSET이 느려지는 이유

키셋 페이지 나누기("탐색 방식")

-- First page:
SELECT * FROM articles
ORDER BY published_at DESC, id DESC
LIMIT 20;

-- Next page — pass the last seen (published_at, id):
SELECT * FROM articles
WHERE (published_at, id) < ($1, $2)
ORDER BY published_at DESC, id DESC
LIMIT 20;

키셋 방식이 더 빠른 이유

ORDER BY 없는 LIMIT은 비결정적입니다

-- Wrong — results may vary between runs:
SELECT * FROM events LIMIT 10;

-- Right:
SELECT * FROM events ORDER BY id DESC LIMIT 10;

전체 개수와 페이지 나누기

SELECT COUNT(*) FROM articles WHERE author_id = $1;

-- Alternative for huge tables: estimate from pg_class.reltuples or use approximate counts.

FETCH FIRST(표준 SQL)

SELECT * FROM products
ORDER BY id
FETCH FIRST 10 ROWS ONLY;

-- With offset:
-- OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

대규모 결과 집합을 위한 커서

BEGIN;
DECLARE big_cursor CURSOR FOR
  SELECT * FROM events ORDER BY id;
FETCH 1000 FROM big_cursor;
-- ...
CLOSE big_cursor;
COMMIT;

OFFSET을 사용해도 괜찮은 경우

복습

빠른 확인