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@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)---------------------------