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@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Jason > Sent: Wednesday, August 24, 2005 11:38 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
