Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
"Choe, Cheng-Dae"
Date:
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/

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

From
Christopher Kings-Lynne
Date:
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?