Re: My Slow query. - Mailing list pgsql-general

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_ID" =
> r."FOREST_COVER_LAYER_ID";
>   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_ID" =
> r."FOREST_COVER_LAYER_ID";
>   ELSIF r."SPECIES_ORDER" = 3 THEN
>     UPDATE public.RES_layers
>     Set "speciesCode3" = r."speciesCode", "speciesPercent3" =
> r."speciesPercent"
>     where public.RES_layers."FOREST_COVER_LAYER_ID" =
> r."FOREST_COVER_LAYER_ID";
>   ELSIF r."SPECIES_ORDER" = 4 THEN
>     UPDATE public.RES_layers
>     Set "speciesCode4" = r."speciesCode", "speciesPercent4" =
> r."speciesPercent"
>     where public.RES_layers."FOREST_COVER_LAYER_ID" =
> r."FOREST_COVER_LAYER_ID";
>   ELSIF r."SPECIES_ORDER" = 5 THEN
>     UPDATE public.RES_layers
>     Set "speciesCode5" = r."speciesCode", "speciesPercent5" =
> r."speciesPercent"
>     where public.RES_layers."FOREST_COVER_LAYER_ID" =
> r."FOREST_COVER_LAYER_ID";
>   END IF;
> END LOOP;
>
> RETURN 0;
>
> end;
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org