explain 만으로는 정확한 원인을 찾을 수 없습니다.

explain analyze로 해야 정확한 결과를 얻을 수 있습니다.

좌우간.. 제 시스템에서 해보니 이렇게 나오네요.

 Limit  (cost=100000089.52..100000089.52 rows=1 width=32) (actual time=1129.634..1129.637 rows=1 loops=1)
   ->  Sort  (cost=100000089.52..100000089.77 rows=101 width=32) (actual time=1129.627..1129.627 rows=1 loops=1)
         Sort Key: random()
         ->  Subquery Scan t  (cost=100000079.90..100000086.15 rows=101 width=32) (actual time=589.377..738.581 rows=9999 loops=1)
               ->  SetOp Except  (cost=100000079.90..100000084.90 rows=100 width=168) (actual time=589.328..672.754 rows=9999 loops=1)
                     ->  Sort  (cost=100000079.90..100000082.40 rows=1001 width=168) (actual time=589.307..622.303 rows=9999 loops=1)
                           Sort Key: user_id
                           ->  Append  (cost=1.00..100000030.01 rows=1001 width=168) (actual time=23.109..217.355 rows=9999 loops=1)
                                 ->  Subquery Scan "*SELECT* 1"  (cost=0.00..30.00 rows=1000 width=4) (actual time=23.107..206.493 rows=9999 loops=1)
                                       ->  Function Scan on generate_series r  (cost=0.00..20.00 rows=1000 width=4) (actual time=23.099..154.676 rows=9999 loops=1)
                                 ->  Subquery Scan "*SELECT* 2"  (cost=100000000.00..100000000.01 rows=1 width=168) (actual time=0.006..0.006 rows=0 loops=1)
                                       ->  Seq Scan on account  (cost=100000000.00..100000000.00 rows=1 width=168) (actual time=0.004..0.004 rows=0 loops=1)
 Total runtime: 1231.507 ms
(13 rows)

대체로 소트할 때에 처리 시간이 급증하는 것을 볼 수 있습니다. 그래서 postgresql.conf에서 work_mem을 늘려보기로 했습니다. 기본 설정인 1024로 되어 있던 것을 2048로 올려봤습니다.

pg_ctl reload 한다음에 다시 실행해봤습니다.

 Limit  (cost=100000089.52..100000089.52 rows=1 width=32) (actual time=605.568..605.571 rows=1 loops=1)
   ->  Sort  (cost=100000089.52..100000089.77 rows=101 width=32) (actual time=605.561..605.561 rows=1 loops=1)
         Sort Key: random()
         ->  Subquery Scan t  (cost=100000079.90..100000086.15 rows=101 width=32) (actual time=292.820..424.809 rows=9999 loops=1)
               ->  SetOp Except  (cost=100000079.90..100000084.90 rows=100 width=168) (actual time=292.759..348.799 rows=9999 loops=1)
                     ->  Sort  (cost=100000079.90..100000082.40 rows=1001 width=168) (actual time=292.734..300.536 rows=9999 loops=1)
                           Sort Key: user_id
                           ->  Append  (cost=0.00..100000030.01 rows=1001 width=168) (actual time=29.891..231.560 rows=9999 loops=1)
                                 ->  Subquery Scan "*SELECT* 1"  (cost=0.00..30.00 rows=1000 width=4) (actual time=29.887..220.895 rows=9999 loops=1)
                                       ->  Function Scan on generate_series r  (cost=0.00..20.00 rows=1000 width=4) (actual time=29.878..157.544 rows=9999 loops=1)
                                 ->  Subquery Scan "*SELECT* 2"  (cost=100000000.00..100000000.01 rows=1 width=168) (actual time=0.011..0.011 rows=0 loops=1)
                                       ->  Seq Scan on account  (cost=100000000.00..100000000.00 rows=1 width=168) (actual time=0.007..0.007 rows=0 loops=1)
 Total runtime: 619.185 ms
(13 rows)

약 2배 빨라졌네요.

쿼리문은 저 보다 훨씬 잘 만드시는 분 같으니 쿼리문 최적하는 Pass... ㅋㅋㅋ

걍 php나 c로 랜덤한 4자리 숫자와 앞에 id를 결합해서 생성하세요.

굳이 모든걸 디비로 한다는것 자체가 성능 저하의 원인이 아닐듯 싶습니다.

성철님/ 메모리를 늘이니 조금(850.406 ms --> 750.034 ms) 빨라네요..

"쿼리문은 더보다 훨씬 잘 만드시는 분 같으니" ... 헉.. 성철님께 그런말을 들으니 몸둘바를 모르겠습니다.. 가까운 쥐구멍에. .얼른..

종민님/ 프로그래밍으로 처리하면 간단한데.. 쿼리로 처리할 수 있을것 같은 예감에 오기로 한번 해봤습니다. DB의 능력을 믿으니깐요.. 프로그래밍으로 하면 편한데.. 젊어서 고생은 사서한다는 기분으로 해봤스빈다. 코멘트  감사합니다.

저도 위에 글 쓰면서 김종민님 같이 생각을 했고 저라도 그렇게 했을 것 같은데요. 또 한편 생각해보면 사용자가 많아져서 9000 명 정도 등록되어 있는 상태에서 비어있는 사용자의 id를 알아내는 것도 쉬운일은 아니겠더군요. 좌우간 최정대님의 쿼리도 예술 작품으로 보이더군요. ^^;;

비어있는 ID를 찾는 가장 바보같은 방법은 id를 난수로 만들어서 이미 있는 id인지 DB에 물어보고 없으면 다시 만들고 하는 것을 반복하는 방법이겠구요.

좀 나아 보이는 방법음 9000명의 user id를 다 읽어서 메모리에 배열로 가지고 있는 상태에서 난수로 id를 생성시키는 방법이 있겠네요.

그리고 9999까지로 고정되어 있다면 미리 account table에 9999명을 미리 등록시켜 놓고 flag를 하나 추가해서 활성화 되었는지 아니면 아직 사용 전인 계정인지 구별하게 하는 것도 방법 중에 하나일 것 같습니다. 속도는 가장 빠른 것 같더군요. 이 경우 임의의 id를 얻어야 하니 random 번호를 가지고 있는 컬럼을 하나 만들어 넣어도 좋겠네요.

그런데 의외인것은 위의 쿼리 plan에서 보듯이 서브쿼리 처리가 늦네요. 메모리에서 겨우 9999개의 row 정도를 가지고 있다가 처리하는 것인데....

그리고 ID가 헝그리찍세인데... 카메라가 취미세요? 저는 로커 (www.rokkorclub.net)에 자주 갑니다. 놀러오세요. ㅋㅋㅋ

헉.. 오타...'더보다...' 발 빠르게 수정했습니다. ㅋㅋㅋ

그리고 저 DB 잘 몰라요. 여기 김상기님, 신기배님, 정재익님등 하늘 같은 고수님들이 즐비하죠. 비록 개인적으로 만나 뵌 적은 없지만요.

저는 그저 문제점들이 올라오면 같이 고민하면서 하나씩 배우는 심정으로 자주 들어옵니다. 요즘은 타성에 졌어서 그런지 새로운 시도를 안해보게 되서요. 여기에 와서 다양한 상황을 접하면 배우는 것이 참 많습니다.

SELECT * FROM ( 
    SELECT user_id FROM (
        SELECT 'ID' || LPAD(r, 4, '0') AS user_id 
    FROM generate_series(1, 9999) as r) AS s 
    EXCEPT
    SELECT user_id FROM account ) AS t 
ORDER BY random() 
LIMIT 1

예술이잖아요? ㅋㅋㅋ

Generate them all into a table and just delete them as you use them.
It's only 10000 rows...

심플하긴한데.. 이것땜시 테이블하나 만들기도 거시기하고 그냥 느린것 참고 쓰렵니다.. 그래봤자 1초인데..

제가 보기에는 가장 빠른 것은 김종민님 이야기처럼 응용 프로그램에 일단 id를 만들고 그 id가 db에 있는가? 있으면 다른 id 만들고... 이게 가장 보편적이고 비용이 제일 적을 것 같네요.