Antw: Re: Performance problem with query

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)