새 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등 되면 저를 잊지는 않으시겠지요? :)
|