Generate them all into a table and just delete them as you use them. It's only 10000 rows...
Chris Choe, Cheng-Dae wrote: > I need to generate unused random id with format is ID[0-9]{4} > so i write below query but it seems to be too slow > > 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 > > and I execute explain analyze query. > ------------------------------------------------------------------------------------------------------------------------------------------------------------------ > Limit (cost=318.17..318.17 rows=1 width=32) (actual > time=731.703..731.707 rows=1 loops=1) > -> Sort (cost=318.17..318.95 rows=312 width=32) (actual > time=731.693..731.693 rows=1 loops=1) > Sort Key: random() > -> Subquery Scan t (cost=285.79..305.24 rows=312 width=32) > (actual time=424.299..659.193 rows=9999 loops=1) > -> SetOp Except (cost=285.79..301.35 rows=311 > width=16) (actual time=424.266..566.254 rows=9999 loops=1) > -> Sort (cost=285.79..293.57 rows=3112 > width=16) (actual time=424.139..470.529 rows=12111 loops=1) > Sort Key: user_id > -> Append (cost=0.00..105.24 rows=3112 > width=16) (actual time=5.572..276.485 rows=12111 loops=1) > -> Subquery Scan "*SELECT* 1" > (cost=0.00..30.00 rows=1000 width=4) (actual time=5.565..149.615 > rows=9999 loops=1) > -> Function Scan on > generate_series r (cost=0.00..20.00 rows=1000 width=4) (actual > time=5.553..63.224 rows=9999 loops=1) > -> Subquery Scan "*SELECT* 2" > (cost=0.00..75.24 rows=2112 width=16) (actual time=0.030..28.473 > rows=2112 loops=1) > -> Seq Scan on account > (cost=0.00..54.12 rows=2112 width=16) (actual time=0.019..10.155 > rows=2112 loops=1) > Total runtime: 738.809 ms > > > do you have any idea for optimize? ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq