새 DB 서버 혹사시키기 작업의 일환으로 lotto 번호 뽑기를 PostgreSQL 에서 해 보았습니다.

여기서 재미난 사실이 하나가 있어 기록을 남겨둡니다.

자료구조는

mydb=> \d lotto
           Table "public.lotto"
 Column |  Type   |     Modifiers
--------+---------+-----------------
 num    | integer | serial not null 
 a      | integer |
 b      | integer |
 c      | integer |
 d      | integer |
 e      | integer |
 f      | integer |
Indexes:
    "lotto_pkey" primary key, btree (num)

이렇고, 이 테이블 안에는 약 500만개의 로또번호가 차례대로 있습니다.

여기서 num 칼럼이 인덱스가 있으니,

mydb=> select * from lotto where num = 1000000;
   num   | a | b  | c  | d  | e  | f
---------+---+----+----+----+----+----
 1000000 | 1 | 25 | 30 | 31 | 39 | 43
(1 row)

Time: 0.976 ms

처럼 아주 깔끔한 접근이 가능하겠지요.

여기서 더 발전해서, 저 num 값을 random()으로 만들어서 하나를 뽑아기로 해서

mydb=> select * from lotto
mydb-> where num = (select int8(random() * 10000000) % 5000000);
   num   | a | b | c  | d  | e  | f
---------+---+---+----+----+----+----
 3566505 | 5 | 7 | 14 | 16 | 20 | 35
(1 row)

Time: 16126.252 ms

이런 결과가 나왔습니다. 뭔가 잘못되었지요.

그래서 실행계획을 살펴보니,

mydb=> explain select * from lotto
mydb-> where num = (select int8(random() * 10000000) % 5000000);
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on lotto  (cost=0.02..22.52 rows=1 width=28)
   Filter: (num = $0)
   InitPlan
     ->  Result  (cost=0.00..0.02 rows=1 width=0)
(4 rows)

Time: 1.953 ms

즉, subquery의 결과와 num의 조건 검색을 full scan 으로 검색하게 되었습니다. 원인이 어디에 있을까 가만히 살펴보니, (아시는 분은 이미 눈치 채셨겠지만) 윗 subquery 의 int8 이 문제였습니다.

num의 자료형은 serial 형으로 int4 형이고, subquery의 결과는 int8이 되어서 자료형이 일치하지 않아서 primary key 인덱스를 사용할 수 없었던 것이였습니다.

그래서, 다시 쿼리를 조정.

mydb=> select * from lotto
mydb-> where num = (select int4(random() * 10000000) % 5000000);
   num   | a | b | c  | d  | e  | f
---------+---+---+----+----+----+----
 1432547 | 2 | 7 | 23 | 25 | 31 | 41
(1 row)

Time: 12.696 ms

원하는 결과가 나왔습니다.

여기서 참한 로또 번호가 나온 것이 중요한 것이 아니고,

인덱스를 사용하려면, 그 비교대상의 자료형이 일치해야한다는 것입니다.

이렇게 해서 누가 1등 되면 저를 잊지는 않으시겠지요? :)