Too slow query, do you have an idea to optimize?

From: "Choe, Cheng-Dae" <whitekid(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Too slow query, do you have an idea to optimize?
Date: 2005-09-09 06:32:21
Message-ID: 38c00bae050908233278ab6cc8@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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?
--
Choe, Cheng-Dae(최정대)
Blog: http://www.comdongin.com/

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2005-09-09 06:58:16 Re: please comment on cpu 32 bit or 64 bit
Previous Message wisan watcharinporn 2005-09-09 05:54:15 please comment on cpu 32 bit or 64 bit