안녕하세요! Justee에 오신 걸 환영합니다.
이번 글에서는 실무에서 만난 아주 흔한 SQL 한 줄, ORDER BY RANDOM() 이야기를 해보려고 합니다.
"조건에 맞는 데이터 중 랜덤으로 하나 뽑기" — 정말 자주 쓰는 패턴이죠. 그런데 이게 데이터가 커지면 어떻게 서비스를 느리게 만드는지, 그리고 어떻게 7초를 1ms로 줄였는지 하나씩 알아보겠습니다.
목차
- 문제 상황 — 랜덤 1건에 7초
- EXPLAIN으로 원인 찾기
- 첫 번째 시도: TABLESAMPLE (그리고 왜 실패했는지)
- 해결: 랜덤값을 미리 저장하는 rand_key 패턴
- 두 번째 함정: pivot을 CTE로 넘기면 39초
- 조건(필터)이 붙으면? — 플래너를 믿자
- 왜 이게 동시성에도 좋은가
- 정리
1. 문제 상황 — 랜덤 1건에 7초
먼저 상황부터 설명드릴게요.
서비스에 "조건에 맞는 데이터 중 랜덤으로 하나 보여주기" 기능이 있었습니다.
대상 테이블은 약 100만 행, 4.8GB. 쿼리는 아주 익숙한 모양이었어요.
SELECT * FROM my_table WHERE sex = '남자' ORDER BY random() LIMIT 1;이 한 줄이 무려 7초가 걸렸습니다. 랜덤으로 딱 한 건 뽑는 데 말이죠. 처음엔 "데이터가 좀 크긴 하지만 인덱스도 있는데 왜 이렇게 느리지?" 싶었습니다. 그래서 원인부터 정확히 찾아보기로 했습니다.
2. EXPLAIN으로 원인 찾기
PostgreSQL에서 쿼리가 느릴 때는 무조건 EXPLAIN (ANALYZE, BUFFERS) 부터 봅니다. 실제로 어떤 실행 계획으로 도는지 알려주거든요.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM my_table ORDER BY random() LIMIT 1;결과는 이렇게 나왔습니다.
Limit (actual time=7319.428..7319.429 rows=1)
Buffers: shared hit=50905 read=199098
-> Sort (actual time=7319.426..7319.427 rows=1)
Sort Key: (random())
Sort Method: top-N heapsort
-> Seq Scan on my_table (actual time=0.048..6362.131 rows=1000000)
Execution Time: 7319.479 ms
여기서 세 가지가 눈에 들어옵니다.
Seq Scan ... rows=1000000→ 테이블 100만 행을 전부 읽습니다.Sort Key: (random())→ 그 100만 행을 랜덤값 기준으로 정렬합니다.Buffers: hit=50905 read=199098→ 합쳐서 250,003 페이지, 8KB씩 곱하면 약 2GB를 읽습니다.
정리하면, 랜덤 한 건을 뽑으려고 테이블 전체에 난수를 매기고 → 전부 정렬하는 겁니다. 데이터가 커질수록, 요청이 몰릴수록 그대로 무너지는 구조죠. 원인을 알았으니 이제 해결 방법을 찾아봅시다.
3. 첫 번째 시도: TABLESAMPLE (그리고 왜 실패했는지)
처음 떠오른 건 TABLESAMPLE 이었습니다. "테이블 전체를 다 읽지 말고 일부만 샘플링하면 빠르지 않을까?" 하는 생각이었죠.
SELECT * FROM my_table TABLESAMPLE SYSTEM (0.1)
WHERE sex='남자' AND ... LIMIT 1;속도는 확실히 빨랐습니다. 그런데 결과가 없을 때가 너무 많았습니다. 이유는 공식 문서에 한 줄로 나와 있었어요.
This sampling precedes the application of any other filters such as WHERE clauses.
즉, TABLESAMPLE은 WHERE 조건보다 먼저 테이블 전체에서 페이지를 샘플링합니다. 그 다음에야 WHERE를 겁니다. 그래서 조건이 희소할수록(예: 매칭이 전체의 0.03%밖에 안 되는 경우) 샘플 안에 매칭이 들어오지 못해서 빈 결과가 나옵니다.
실제로 테스트해보니 희소한 조건에서는 8번 중 6번이 빈 결과였고, 극단적인 조합에서는 SYSTEM(10)으로 샘플 비율을 크게 올려도 62%가 빈 결과 + 3~13초가 걸렸습니다.
"그럼 조건마다 샘플 비율(%)을 조정하면 되지 않나?" 싶지만, 그러려면 그 조건 조합이 전체에서 얼마나 희소한지 미리 알아야 합니다. 필터 조합은 사실상 무한이라 현실적으로 불가능하죠. 범용 해법이 될 수 없었습니다.
4. 해결: 랜덤값을 미리 저장하는 rand_key 패턴
여기서 핵심 아이디어가 나옵니다.
랜덤값을 매 쿼리마다 계산하지 말고, 딱 한 번 계산해서 컬럼에 저장하고 인덱스를 걸자.
이 데이터는 거의 바뀌지 않는 데이터였기 때문에, 랜덤값을 한 번만 구워두면 됐습니다.
-- 딱 1회만 실행 (불변 데이터라 한 번이면 끝)
ALTER TABLE my_table ADD COLUMN rand_key double precision;
UPDATE my_table SET rand_key = random(); -- 각 행에 0~1 사이 고정 난수 부여
ALTER TABLE my_table ALTER COLUMN rand_key SET NOT NULL;
CREATE INDEX idx_rand_key ON my_table (rand_key); -- 정렬된 인덱스이제 모든 행은 rand_key라는 고정 난수(0~1)를 가지고 있고, 그 값으로 정렬된 인덱스가 있습니다.
랜덤으로 뽑을 때는 이렇게 합니다. 애플리케이션에서 랜덤 기준점 p(예: Math.random())를 하나 정하고, rand_key >= p인 첫 번째 행을 가져옵니다.
SELECT * FROM my_table WHERE rand_key >= 0.6273 ORDER BY rand_key LIMIT 1;정렬된 인덱스에서 "0.6273 위치로 바로 펴서 다음 한 행" — 사전에서 단어 찾듯 점프하는 겁니다. 스캔도, 정렬도 없습니다.
0~1 수직선 위에 점들(각 행의 rand_key)이 흩어져 있고, pivot p 화살표에서 오른쪽 첫 점으로 점프하는 그림. wrap-around(끝에서 처음으로 되돌아오는 화살표)도 함께 표현하면 이해가 확 됩니다.
4.1. wrap-around — 항상 1건을 보장하는 장치
만약 p가 너무 크면(예: 0.99) 그 위에 행이 없을 수도 있습니다. 그럴 땐 처음으로 돌아가서 잡습니다. 시계가 12를 넘으면 다시 1로 돌아가는 것과 똑같아요.
(SELECT * FROM my_table WHERE rand_key >= 0.6273 ORDER BY rand_key LIMIT 1)
UNION ALL
(SELECT * FROM my_table WHERE rand_key < 0.6273 ORDER BY rand_key LIMIT 1)
LIMIT 1;모든 행은 >= p 아니면 < p, 둘 중 하나에 반드시 속합니다. 그래서 매칭되는 행이 하나라도 있으면 무조건 1건이 나옵니다.
4.2. 결과
EXPLAIN → Index Scan using idx_rand_key
Execution Time: 1.3 ms
7,319ms → 1.3ms. 읽는 데이터도 약 2GB에서 4KB 수준으로 줄었습니다.
5. 두 번째 함정: pivot을 CTE로 넘기면 39초
여기서 제가 실제로 밟은 지뢰를 하나 공유할게요. 수동으로 테스트해보려고 pivot(기준점)을 SQL 안에서 만들었습니다.
-- ❌ 이렇게 하면 39초가 걸립니다
WITH r AS (SELECT random() AS v)
SELECT p.* FROM my_table p, r
WHERE p.sex='남자' AND ... AND p.rand_key >= r.v
ORDER BY p.rand_key LIMIT 1;분명히 인덱스를 만들었는데도 39초. 당황해서 EXPLAIN을 보니 이렇게 나왔습니다.
Index Cond: (sex = '남자') ← rand_key가 인덱스 조건에서 빠짐!
Join Filter: (p.rand_key >= r.v) ← Join Filter로 밀려남
Rows Removed by Filter: 285064
원인은 이거였습니다. rand_key >= X를 인덱스 범위의 경계로 쓰려면, X가 실행 계획을 세우는 시점에 이미 아는 값이어야 합니다. 그런데 CTE를 FROM p, r처럼 조인으로 넘기면 r.v는 조인 컬럼이 되어서 계획 시점엔 미지수입니다. 그래서 인덱스 점프를 못 하고 Join Filter로 밀려 결국 전체 스캔을 하게 된 거죠.
해결 방법은 두 가지입니다.
- 애플리케이션에서 값으로 넘기기 (파라미터/리터럴):
rand_key >= 0.6273→ Index Cond → 빠름. (실제 서비스는 이 방식을 씁니다.) - 수동 테스트라면 스칼라 서브쿼리:
rand_key >= (SELECT random())→ InitPlan으로 한 번만 평가 → Index Cond → 빠름.
교훈: "DB에서 랜덤값을 어떻게 만드느냐"에 따라 인덱스를 타느냐 마느냐가 갈립니다. 되도록 값으로 넘겨주세요.
6. 조건(필터)이 붙으면? — 플래너를 믿자
"조건을 여러 개 걸면 다시 필터링하느라 느려지지 않을까?" 걱정했는데, 필터 컬럼마다 인덱스가 있으면 PostgreSQL 플래너가 알아서 BitmapAnd로 조합해줍니다.
Index Cond: (sex='남자' AND rand_key >= 0.5)
Filter: (province='서울' AND age='31~34') ← recheck- 넓은 조건(예: 성별만) →
(sex, rand_key)복합 인덱스 seek - 선택적 조건 여러 개 → 각 인덱스를 BitmapAnd로 교집합 → 작은 후보 집합 → rand_key로 마무리 (약 1.9ms)
- 매칭이 0건인 조합 → BitmapAnd가 빈 결과 (약 3ms, 타임아웃 아님!)
조건 개수와 상관없이 전부 밀리초 단위로 끝났습니다. 컬럼마다 인덱스만 잘 갖춰져 있으면 플래너를 믿으면 됩니다.
7. 왜 이게 동시성에도 좋은가
마지막으로, 속도 말고 하나 더 중요한 이야기가 있습니다. 바로 커넥션 점유 시간입니다.
DB 커넥션은 쿼리가 도는 동안 계속 잡혀 있습니다. 그래서 쿼리 시간이 길면 그만큼 커넥션이 오래 묶이고, 동시에 처리할 수 있는 요청 수가 줄어듭니다.
| 방식 | 커넥션 점유 시간 | 커넥션 1개당 처리량 |
|---|---|---|
ORDER BY random() |
약 7.4초 | 약 0.13건/초 |
rand_key |
약 수십 ms | 약 수백 건/초 |
쿼리가 짧아지면 커넥션이 빨리 반환되고, 같은 커넥션 풀로 훨씬 많은 요청을 소화할 수 있습니다. "조회 시간 단축 → 커넥션 점유 단축 → 동시 처리량 증가" 로 자연스럽게 이어지는 거죠. 사양이 낮은 DB, 커넥션 수가 빠듯한 환경일수록 이 효과가 큽니다.
8. 정리
오늘 내용을 짧게 정리해볼게요.
ORDER BY random()은 매번 전체 행에 난수를 매기고 정렬합니다. 데이터가 크면 그 대가가 큽니다.- 데이터가 (거의) 변하지 않는다면, 랜덤값을 미리 컬럼에 저장하고 인덱스를 거세요. 뽑을 때는 인덱스 점프 + wrap-around.
- TABLESAMPLE은 WHERE보다 먼저 샘플링하기 때문에, 조건이 붙는 랜덤 조회에는 적합하지 않습니다.
- pivot은 값으로 넘기세요. CTE 조인으로 넘기면 인덱스를 못 탑니다.
- 필터가 붙어도 컬럼 인덱스 + BitmapAnd로 플래너가 알아서 처리합니다.
7초짜리 랜덤 조회를 1ms로 바꾼 건 사실 화려한 기술이 아니었습니다. "매번 할 일을 미리 딱 한 번 해두는" 아주 오래된 아이디어였죠.
읽어주셔서 감사합니다! 혹시 더 좋은 방법이나 궁금한 점이 있다면 댓글로 남겨주세요. 그럼 다음 글에서 또 뵙겠습니다!
측정 환경: PostgreSQL, 약 100만 행 / 4.8GB 테이블. 모든 수치는 실제 EXPLAIN (ANALYZE) 기준입니다.