I am "TOP POSTING" intentionally -- Thanks Mike... Very informative -- I never realized that child (INHERITED) tables do NOT inherit the indexes from their parent... that might be part of the solution.... I duplicated the parents index on the child table -- the function still takes 4672 ms to complete... Based on your evaluation -- I now believe that the issue is in the UPDATE clause -- -- THIS IS AN EXAMPLE OF THE ACTUAL UPDATE... WHICH GETS CALLED 50 TIMES... EXPLAIN ANALYZE UPDATE ONLY l_store_hours SET amount = amount * 1.00 WHERE l_store_hours.id = 14511; -- OUTPUT... "Nested Loop (cost=0.00..6.05 rows=1 width=52) (actual time=67.487..67.493 rows=1 loops=1)" " -> Index Scan using l_store_hours_pkey on l_store_hours lh (cost=0.00..3.01 rows=1 width=52) (actual time=54.674..54.675 rows=1 loops=1)" " Index Cond: (14511 = id)" " -> Index Scan using l_store_hours_pkey on l_store_hours (cost=0.00..3.01 rows=1 width=4) (actual time=0.006..0.009 rows=1 loops=1)" " Index Cond: (id = 14511)" "Total runtime: 87.803 ms" "" "Index Scan using l_store_hours_pkey on l_store_hours (cost=0.00..3.02 rows=1 width=58) (actual time=0.032..0.034 rows=1 loops=1)" " Index Cond: (id = 14511)" "Total runtime: 0.500 ms" -- THE TABLES, CHILD TABLE, VIEW, AND FUNCTION... CREATE TABLE l_store_hours ( id serial PRIMARY KEY, l_store_id int4 NOT NULL DEFAULT 0, l_activity_type int4 NOT NULL REFERENCES l_activity_type(id), week_code int4 NOT NULL DEFAULT 0, year_code int4 NOT NULL DEFAULT 0, amount numeric(10,2) DEFAULT 0.00, create_dt timestamp NOT NULL DEFAULT now(), change_dt timestamp NOT NULL DEFAULT now(), change_id int4 DEFAULT 0, active_flag bool DEFAULT true ) WITH OIDS; CREATE INDEX idx_store_hours ON l_store_hours USING btree (id, l_store_id, year_code, week_code); ALTER TABLE l_store_hours CLUSTER ON idx_store_hours; -- THE CHILD (INHERITED) TABLE... CREATE TABLE l_store_hours_history ( hist_id serial PRIMARY KEY, hist_dt timestamp NOT NULL DEFAULT now() ) INHERITS (l_store_hours) WITH OIDS; CREATE INDEX idx_store_hours_history ON l_store_hours_history USING btree (id, l_store_id, year_code, week_code); ALTER TABLE l_store_hours_history CLUSTER ON idx_store_hours_history; -- THE UPDATE RULE ON THE PARENT TABLE... CREATE OR REPLACE RULE l_store_hours_history_upd AS ON UPDATE TO l_store_hours DO INSERT INTO l_store_hours_history (SELECT * FROM ONLY l_store_hours LH WHERE LH.id = old.id); -- THE VIEW... CREATE OR REPLACE VIEW v_storehours AS SELECT ls.id, ls.l_activity_type, ls.l_store_id, ls.week_code, ls.year_code, ls.amount, ls.create_dt, ls.change_dt, ls.change_id, ls.active_flag, COALESCE(lsh.amount, ls.amount) AS previous, COALESCE(lsh.hist_id, 0) AS history, lsh.hist_dt FROM ONLY l_store_hours ls FULL JOIN l_store_hours_history lsh ON ls.id = lsh.id ORDER BY ls.year_code, ls.week_code; /* This function updates the block of expected employee hours (l_store_hours) based on the current projected sales figures */ CREATE OR REPLACE FUNCTION l_updatehoursonsales(int4, int4, int4) RETURNS bool AS $BODY$ DECLARE ROW v_storesales%ROWTYPE; DECLARE F1 real; DECLARE CUR t_updhours%ROWTYPE; DECLARE STORE ALIAS FOR $1; DECLARE WEEK ALIAS FOR $2; DECLARE YEAR ALIAS FOR $3; DECLARE C RECORD; BEGIN RAISE LOG 'STARTING: l_updatehoursonsales for store: %', $1; RAISE LOG 'STARTING: l_updatehoursonsales for week: %', $2; RAISE LOG 'STARTING: l_updatehoursonsales for year: %', $3; -- RAISE LOG 'CREATING TEMP TABLE AS SELECT...'; CREATE TEMPORARY TABLE tmphours AS SELECT LT.type_desc, 0 AS hist_id, LSH.* FROM ONLY l_store_hours LSH FULL OUTER JOIN l_activity_type LT ON LSH.l_activity_type = LT.id WHERE LSH.l_store_id = $1 AND LSH.week_code = $2 AND LSH.year_code = $3 ORDER BY l_activity_type; -- UPDATE THE TEMP TABLE WITH THE LEAST HISTORY ID FROM THE HISTORY TABLE... -- RAISE LOG 'SETTING HISTORY IDs...'; UPDATE tmphours SET hist_id = ( SELECT A.hist_id FROM ( SELECT MIN(hist_id) AS hist_id, id FROM ONLY l_store_hours_history LSH WHERE tmphours.l_store_id = LSH.l_store_id AND tmphours.year_code = LSH.year_code AND tmphours.week_code = LSH.week_code AND tmphours.l_activity_type = LSH.l_activity_type AND tmphours.id = LSH.id GROUP BY 2 ) AS A); -- UPDATE THE AMOUNTS (HOURS) WITH THE PRESERVED VALUES... -- RAISE LOG 'UPDATING tmphours --> Setting amount...'; UPDATE tmphours SET amount = LSH.amount FROM ONLY l_store_hours_history LSH WHERE tmphours.hist_id = LSH.hist_id; -- GET THE CURRENT SALES FIGURES FROM THE VIEW... FOR ROW IN SELECT VSS.* FROM v_storesales VSS WHERE VSS.l_store_id = $1 AND VSS.week_code = $2 AND VSS.year_code = $3 ORDER BY history LIMIT 1 LOOP -- RAISE LOG 'LOOPING FOR UPDATE... %', ROW.id; IF (ROW.amount > ROW.previous) THEN SELECT (1 + (((ROW.amount::float / ROW.previous::float)::float - 1) * 0.8))::NUMERIC(10, 2) INTO F1; RAISE LOG 'USING FORMULA (a > b): %', F1; END IF; IF (ROW.amount < ROW.previous) THEN SELECT (ROW.amount::float / ROW.previous::float)::NUMERIC(10, 2) INTO F1; RAISE LOG 'USING FORMULA: (a < b) %', F1; END IF; IF (ROW.amount = ROW.previous) THEN SELECT 1.00::NUMERIC(10, 2) INTO F1; RAISE LOG 'USING FORMULA: (a = b)%', F1; END IF; -- LOOP THROUGH THE TEMP TABLE AND UPDATE l_store_hours... FOR C IN SELECT * FROM tmphours LOOP UPDATE ONLY l_store_hours SET amount = C.amount * F1 WHERE l_store_hours.id = C.id; END LOOP; END LOOP; -- CLEAN UP AFTER YOURSELF... RAISE LOG 'LOOP COMPLETE! Dropping TEMPORARY TABLE tmphours...'; DROP TABLE tmphours; -- AND RETURN... RETURN TRUE; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Regards, Greg Patnude - Manager, Dynamic Applications Group Data Mosaics, Inc. 2406 South Dishman-Mica Road / Suite # 6 Spokane Valley, WA 99206-6429 VOICE: (866) 904-DMSF FAX: (509) 928-4236 "Michael Fuhr" <mike@xxxxxxxx> wrote in message news:20050713005700.GA87233@xxxxxxxxxxxxxxxxxx > On Tue, Jul 12, 2005 at 10:52:24AM -0700, Greg Patnude wrote: >> >> Performing an update to an inherited table system from inside of a stored >> procedure (PLPGSQL) seems to be unusually sluggish... > > Is the update slower when done inside a function than when doing > it directly (e.g., from psql)? That is, is the use of a function > relevant, or is the update equally slow in any case? Could you > post the EXPLAIN ANALYZE output for the update? > > The message subject is "Temp tables." Are you using temporary > tables, and if so, are you seeing different behavior with temporary > tables than with "real" tables? Again, is that relevant to the > problem? > >> Does anyone have a faster solution ? I am updating 50 records and it >> takes approximately 4.375 seconds + or -.... >> >> The inherited table has an ON INSERT DO INSTEAD and there are >> approximately >> 2 million rows in the inherited table structure... > > Could you post the table definitions, including all indexes, rules, > etc.? Do all the child tables have indexes on the column(s) used > to restrict the update? As the documentation states, indexes aren't > inherited, so you might need to create additional indexes on the > children, indexes that you'd think would be redundant. Example: > > CREATE TABLE parent (id serial PRIMARY KEY); > CREATE TABLE child (x integer) INHERITS (parent); > > INSERT INTO child (x) SELECT * FROM generate_series(1, 100000); > > ANALYZE parent; > ANALYZE child; > > EXPLAIN ANALYZE UPDATE child SET id = id WHERE id BETWEEN 1 AND 50; > QUERY PLAN > ------------------------------------------------------------------------------------------------------- > Seq Scan on child (cost=0.00..1991.00 rows=41 width=14) (actual > time=0.059..307.234 rows=50 loops=1) > Filter: ((id >= 1) AND (id <= 50)) > Total runtime: 309.350 ms > (3 rows) > > EXPLAIN ANALYZE UPDATE parent SET id = id WHERE id BETWEEN 1 AND 50; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------- > Append (cost=0.00..2006.37 rows=52 width=14) (actual > time=304.838..306.252 rows=50 loops=1) > -> Index Scan using parent_pkey on parent (cost=0.00..15.37 rows=11 > width=10) (actual time=0.110..0.110 rows=0 loops=1) > Index Cond: ((id >= 1) AND (id <= 50)) > -> Seq Scan on child parent (cost=0.00..1991.00 rows=41 width=14) > (actual time=304.705..305.619 rows=50 loops=1) > Filter: ((id >= 1) AND (id <= 50)) > Total runtime: 307.935 ms > (6 rows) > > Notice the sequential scans on child, even though we have an index > on parent.id, a column that child inherits. We need to create an > index on child.id as well: > > CREATE INDEX child_id_idx ON child (id); > > EXPLAIN ANALYZE UPDATE child SET id = id WHERE id BETWEEN 1 AND 50; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------- > Index Scan using child_id_idx on child (cost=0.00..3.65 rows=41 width=14) > (actual time=0.369..1.371 rows=50 loops=1) > Index Cond: ((id >= 1) AND (id <= 50)) > Total runtime: 6.100 ms > (3 rows) > > EXPLAIN ANALYZE UPDATE parent SET id = id WHERE id BETWEEN 1 AND 50; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------ > Append (cost=0.00..19.02 rows=52 width=14) (actual time=0.119..1.895 > rows=50 loops=1) > -> Index Scan using parent_pkey on parent (cost=0.00..15.37 rows=11 > width=10) (actual time=0.037..0.037 rows=0 loops=1) > Index Cond: ((id >= 1) AND (id <= 50)) > -> Index Scan using child_id_idx on child parent (cost=0.00..3.65 > rows=41 width=14) (actual time=0.066..1.320 rows=50 loops=1) > Index Cond: ((id >= 1) AND (id <= 50)) > Total runtime: 7.820 ms > (6 rows) > > If that's not the problem, then do other tables have foreign key > references to the table(s) you're updating? If so, then you might > need indexes on the foreign key columns in the referring tables. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
