Обсуждение: Antw: Re: Performance problem with query

Поиск
Список
Период
Сортировка

Antw: Re: Performance problem with query

От
"Christian Rengstl"
Дата:
Hi,

somehow my reply yesterday got lost, but nevertheless here comes the
explain analyze again. It's the explain of the operation that causes
this huge performance discrepancy. Unfortunately i had to perform the
explain analyze with an empty temp_table, because after the whole
operation is done, i delete the data again to save some space.


    QUE
RY PLAN

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----
 Result  (cost=0.12..16.95 rows=390 width=108) (actual time=0.025..0.025
rows=0
loops=1)
   One-Time Filter: ((((split_part(($1)::text, '_'::text, 2))::smallint
= 1) IS
NOT TRUE) AND (((split_part(($2)::text, '_'::text, 2))::smallint = 22)
IS NOT TR
UE))
   InitPlan
     ->  Limit  (cost=0.00..0.04 rows=1 width=28) (never executed)
           ->  Seq Scan on temp_table  (cost=0.00..13.90 rows=390
width=28) (nev
er executed)
     ->  Limit  (cost=0.00..0.04 rows=1 width=28) (actual
time=0.005..0.005 rows
=0 loops=1)
           ->  Seq Scan on temp_table  (cost=0.00..13.90 rows=390
width=28) (act
ual time=0.001..0.001 rows=0 loops=1)
     ->  Limit  (cost=0.00..0.04 rows=1 width=28) (actual
time=0.002..0.002 rows
=0 loops=1)
           ->  Seq Scan on temp_table  (cost=0.00..13.90 rows=390
width=28) (act
ual time=0.000..0.000 rows=0 loops=1)
   ->  Seq Scan on temp_table  (cost=0.00..13.90 rows=390 width=108)
(actual tim
e=0.000..0.000 rows=0 loops=1)
 Total runtime: 0.424 ms

 Result  (cost=0.08..16.90 rows=390 width=108) (actual time=0.005..0.005
rows=0
loops=1)
   One-Time Filter: ((split_part(($1)::text, '_'::text, 2))::smallint =
1)
   InitPlan
     ->  Limit  (cost=0.00..0.04 rows=1 width=28) (never executed)
           ->  Seq Scan on temp_table  (cost=0.00..13.90 rows=390
width=28) (nev
er executed)
     ->  Limit  (cost=0.00..0.04 rows=1 width=28) (actual
time=0.002..0.002 rows
=0 loops=1)
           ->  Seq Scan on temp_table  (cost=0.00..13.90 rows=390
width=28) (act
ual time=0.001..0.001 rows=0 loops=1)
   ->  Seq Scan on temp_table  (cost=0.00..13.90 rows=390 width=108)
(never exec
uted)
 Total runtime: 0.267 ms

 Result  (cost=0.08..16.90 rows=390 width=108) (actual time=0.005..0.005
rows=0
loops=1)
   One-Time Filter: ((split_part(($1)::text, '_'::text, 2))::smallint =
22)
   InitPlan
     ->  Limit  (cost=0.00..0.04 rows=1 width=28) (never executed)
           ->  Seq Scan on temp_table  (cost=0.00..13.90 rows=390
width=28) (nev
er executed)
     ->  Limit  (cost=0.00..0.04 rows=1 width=28) (actual
time=0.002..0.002 rows
=0 loops=1)
           ->  Seq Scan on temp_table  (cost=0.00..13.90 rows=390
width=28) (act
ual time=0.001..0.001 rows=0 loops=1)
   ->  Seq Scan on temp_table  (cost=0.00..13.90 rows=390 width=108)
(never exec
uted)
 Total runtime: 0.189 ms
(31 Zeilen)

Re: Performance problem with query

От
"Merlin Moncure"
Дата:
On 7/14/06, Christian Rengstl
<Christian.Rengstl@klinik.uni-regensburg.de> wrote:
> Hi,
>
> somehow my reply yesterday got lost, but nevertheless here comes the
> explain analyze again. It's the explain of the operation that causes
> this huge performance discrepancy. Unfortunately i had to perform the
> explain analyze with an empty temp_table, because after the whole
> operation is done, i delete the data again to save some space.

that's not much help.  remember that explain analyze actually performs
your query. so next time you run it, do explain analyze and post
results here. just quick tip: run analyze before you run your big
query.

merlin