Antw: Re: Performance problem with query

now finally after a long time i have the query plan for the whole filled
table. I hope somebody can explain me why it takes so much longer...

   QUERY PLAN

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----
Result  (cost=0.09..254643.09 rows=8044000 width=39) (actual
time=0.043..0.043
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.02 rows=1 width=10) (never executed)
          ->  Seq Scan on temp_table  (cost=0.00..194313.00 rows=8044000
width=
10) (never executed)
    ->  Limit  (cost=0.00..0.02 rows=1 width=10) (actual
time=0.006..0.006 rows
=1 loops=1)
          ->  Seq Scan on temp_table  (cost=0.00..194313.00 rows=8044000
width=
10) (actual time=0.004..0.004 rows=1 loops=1)
    ->  Limit  (cost=0.00..0.02 rows=1 width=10) (never executed)
          ->  Seq Scan on temp_table  (cost=0.00..194313.00 rows=8044000
width=
10) (never executed)
  ->  Seq Scan on temp_table  (cost=0.00..194313.00 rows=8044000
width=39) (nev
er executed)
Total runtime: 0.238 ms

Result  (cost=0.06..254643.06 rows=8044000 width=39) (actual
time=0.056..655772
.273 rows=8044000 loops=1)
  One-Time Filter: ((split_part(($1)::text, '_'::text, 2))::smallint =
1)
  InitPlan
    ->  Limit  (cost=0.00..0.02 rows=1 width=10) (actual
time=0.003..0.003 rows
=1 loops=1)
          ->  Seq Scan on temp_table  (cost=0.00..194313.00 rows=8044000
width=
10) (actual time=0.002..0.002 rows=1 loops=1)
    ->  Limit  (cost=0.00..0.02 rows=1 width=10) (actual
time=0.006..0.007 rows
=1 loops=1)
          ->  Seq Scan on temp_table  (cost=0.00..194313.00 rows=8044000
width=
10) (actual time=0.004..0.004 rows=1 loops=1)
  ->  Seq Scan on temp_table  (cost=0.00..194313.00 rows=8044000
width=39) (act
ual time=0.002..191672.344 rows=8044000 loops=1)
Total runtime: 62259544.896 ms

Result  (cost=0.06..254643.06 rows=8044000 width=39) (actual
time=1.245..1.245
rows=0 loops=1)
  One-Time Filter: ((split_part(($1)::text, '_'::text, 2))::smallint =
22)
  InitPlan
    ->  Limit  (cost=0.00..0.02 rows=1 width=10) (never executed)
          ->  Seq Scan on temp_table  (cost=0.00..194313.00 rows=8044000
width=
10) (never executed)
    ->  Limit  (cost=0.00..0.02 rows=1 width=10) (actual
time=0.013..0.014 rows
=1 loops=1)
          ->  Seq Scan on temp_table  (cost=0.00..194313.00 rows=8044000
width=
10) (actual time=0.009..0.009 rows=1 loops=1)
  ->  Seq Scan on temp_table  (cost=0.00..194313.00 rows=8044000
width=39) (nev
er executed)
Total runtime: 22.270 ms
(31 Zeilen)



>>> "Merlin Moncure" <mmoncure@gmail.com> 13.07.06 15.20 Uhr >>>
On 7/13/06, Christian Rengstl
<Christian.Rengstl@klinik.uni-regensburg.de> wrote:
> Good morning list,
>
> the following query takes about 15 to 20 minutes for around 2 million
lines in the file myfile.txt, but with 8 million lines it takes around 5
hours and i just don't understand why there is such a huge discrepancy
in performance.


on the surface it doesn't make sense, can you post an explain analyze?

merlin

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

>>> "Merlin Moncure" <mmoncure@gmail.com> 13.07.06 15.20 Uhr >>>
On 7/13/06, Christian Rengstl
<Christian.Rengstl@klinik.uni-regensburg.de> wrote:
> Good morning list,
>
> the following query takes about 15 to 20 minutes for around 2 million
lines in the file myfile.txt, but with 8 million lines it takes around 5
hours and i just don't understand why there is such a huge discrepancy
in performance.


on the surface it doesn't make sense, can you post an explain analyze?

merlin

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster