PostgreSQL Discussion - My Slow query.

Author

My Slow query.

Jason

2005-08-24, 8:24 pm

Hi:

I have a query that uses one table with about 1 million rows. So far it
has been running for about 12h on a P4 3Ghz. The query/function does
this:

Esentially flattens some data. It looks through the values in a row,
does an if/else to categorize the value and update another table based
on the if/else. I put it into a function. The function is at the end
of this msg.

So I have a talble that looks like:

SPECIES_ORDER speciecCode speciesPercent
1 PL 10
1 P 30
2 Sp 11

And I turn it into

speciesCode1 speciesPercent1 speciesCode2 speciesPercent2
PL 10 Sp 11
P Sp NULL NULL

So the 1,2.. in the colum names comes from the ORDER_NUMBER.

I might be able to use the contributed crosstab function. Any ideas or
comments?

Thanks,
Jason.

DECLARE
r RECORD;

BEGIN

FOR r IN SELECT * FROM RDD010 LOOP
IF r."SPECIES_ORDER" = 1 THEN
UPDATE public.RES_layers
Set "speciesCode1" = r."speciesCode", "speciesPercent1" =
r."speciesPercent"
where public.RES_layers." FOREST_COVER_LAYER_I
D" =
r." FOREST_COVER_LAYER_I
D";
ELSIF r."SPECIES_ORDER" = 2 THEN
UPDATE public.RES_layers
Set "speciesCode2" = r."speciesCode", "speciesPercent2" =
r."speciesPercent",
"averageAge_spp2" = r."averageAge", "averageHeight_spp2" =
r."averageHeight"
where public.RES_layers." FOREST_COVER_LAYER_I
D" =
r." FOREST_COVER_LAYER_I
D";
ELSIF r."SPECIES_ORDER" = 3 THEN
UPDATE public.RES_layers
Set "speciesCode3" = r."speciesCode", "speciesPercent3" =
r."speciesPercent"
where public.RES_layers." FOREST_COVER_LAYER_I
D" =
r." FOREST_COVER_LAYER_I
D";
ELSIF r."SPECIES_ORDER" = 4 THEN
UPDATE public.RES_layers
Set "speciesCode4" = r."speciesCode", "speciesPercent4" =
r."speciesPercent"
where public.RES_layers." FOREST_COVER_LAYER_I
D" =
r." FOREST_COVER_LAYER_I
D";
ELSIF r."SPECIES_ORDER" = 5 THEN
UPDATE public.RES_layers
Set "speciesCode5" = r."speciesCode", "speciesPercent5" =
r."speciesPercent"
where public.RES_layers." FOREST_COVER_LAYER_I
D" =
r." FOREST_COVER_LAYER_I
D";
END IF;
END LOOP;

RETURN 0;

end;

Dann Corbit

2005-08-24, 8:24 pm

Can we see the schema for the tables RDD010 and RES_layers (including
keys)?

12 H for a million rows really sounds brutal (23 rows/sec).

I am guessing it can be done a lot faster using a join but I would like
to see more information about the tables involved in the query.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Jason
> Sent: Wednesday, August 24, 2005 11:38 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] My Slow query.
>
> Hi:
>
> I have a query that uses one table with about 1 million rows. So far
it
> has been running for about 12h on a P4 3Ghz. The query/function does
> this:
>
> Esentially flattens some data. It looks through the values in a row,
> does an if/else to categorize the value and update another table based
> on the if/else. I put it into a function. The function is at the end
> of this msg.
>
> So I have a talble that looks like:
>
> SPECIES_ORDER speciecCode speciesPercent
> 1 PL 10
> 1 P 30
> 2 Sp 11
>
> And I turn it into
>
> speciesCode1 speciesPercent1 speciesCode2 speciesPercent2
> PL 10 Sp 11
> P Sp NULL NULL
>
> So the 1,2.. in the colum names comes from the ORDER_NUMBER.
>
> I might be able to use the contributed crosstab function. Any ideas or
> comments?
>
> Thanks,
> Jason.
>
> DECLARE
> r RECORD;
>
> BEGIN
>
> FOR r IN SELECT * FROM RDD010 LOOP
> IF r."SPECIES_ORDER" = 1 THEN
> UPDATE public.RES_layers
> Set "speciesCode1" = r."speciesCode", "speciesPercent1" =
> r."speciesPercent"
> where public.RES_layers." FOREST_COVER_LAYER_I
D" =
> r." FOREST_COVER_LAYER_I
D";
> ELSIF r."SPECIES_ORDER" = 2 THEN
> UPDATE public.RES_layers
> Set "speciesCode2" = r."speciesCode", "speciesPercent2" =
> r."speciesPercent",
> "averageAge_spp2" = r."averageAge", "averageHeight_spp2" =
> r."averageHeight"
> where public.RES_layers." FOREST_COVER_LAYER_I
D" =
> r." FOREST_COVER_LAYER_I
D";
> ELSIF r."SPECIES_ORDER" = 3 THEN
> UPDATE public.RES_layers
> Set "speciesCode3" = r."speciesCode", "speciesPercent3" =
> r."speciesPercent"
> where public.RES_layers." FOREST_COVER_LAYER_I
D" =
> r." FOREST_COVER_LAYER_I
D";
> ELSIF r."SPECIES_ORDER" = 4 THEN
> UPDATE public.RES_layers
> Set "speciesCode4" = r."speciesCode", "speciesPercent4" =
> r."speciesPercent"
> where public.RES_layers." FOREST_COVER_LAYER_I
D" =
> r." FOREST_COVER_LAYER_I
D";
> ELSIF r."SPECIES_ORDER" = 5 THEN
> UPDATE public.RES_layers
> Set "speciesCode5" = r."speciesCode", "speciesPercent5" =
> r."speciesPercent"
> where public.RES_layers." FOREST_COVER_LAYER_I
D" =
> r." FOREST_COVER_LAYER_I
D";
> END IF;
> END LOOP;
>
> RETURN 0;
>
> end;
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

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

Sponsored Links