Re: Temp tables...

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@fuhr.org> wrote in message
news:20050713005700.GA87233@winnie.fuhr.org...
> 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
>