Re: My Slow query.

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(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Jason
> Sent: Wednesday, August 24, 2005 11:38 AM
> To: pgsql-general(at)postgresql(dot)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