optimalizace triggeru (Postgres, ...)

[Robert Vojta]

Zdravim,
  mam vytvorene tabulky:

  counters (line, from, to, stamp, stamp_zmeny)
  counters_hour (line, from, to, stamp, stamp_zmeny)
  counters_day (line, from, to, stamp, stamp_zmeny)
  counters_month (line, from, to, stamp, stamp_zmeny)

  Na te prvni tabulce mam nasazeny trigger, ktery pri zmene (insert,update)
dat v tabulce counters provede hodinovou, denni sumu a mesicni sumu
(vypis triggeru je na konci). Nemam zkusenosti s programovanim databazi
pomoci tech jejich jazyku a hledam jak to cele zoptimalizovat, aby to bylo
co nejrychlejsi. Tabulka counters bude co pet minut dostavat (insert) zhruba
1000 radku, tj. celkem rychle naroste a potom bude nejdele trva sumarizace
hour, day, month, resp. select a delete v nich.
  Nemate nekdo prosim odkaz na nejaky web, ktery by se zabyval optimalizaci
pri programovani triggeru, ...

CREATE FUNCTION counters_change () RETURNS OPAQUE AS '
  DECLARE
    hour_stamp       VARCHAR(20);
    day_stamp        VARCHAR(20);
    month_stamp      VARCHAR(20);
    sums             RECORD;
    lineid           INT8;
    counter_stamp    TIMESTAMP;
  BEGIN
    IF tg_op = ''INSERT'' OR tg_op = ''UPDATE'' THEN
      counter_stamp := NEW.counterstamp;
      lineid        := NEW.line;
    END IF;

    hour_stamp       := substr(counter_stamp,1,13);
    SELECT INTO sums
      SUM(counterfrom) as sum_from,SUM(counterto) as sum_to FROM counters
      WHERE line = lineid and counterstamp like
      hour_stamp || '':%'';
    IF sums.sum_from ISNULL OR sums.sum_to ISNULL THEN
      sums.sum_from := 0;
      sums.sum_to   := 0;
    END IF;
    DELETE FROM counters_hour
      WHERE line = lineid AND counterstamp like
      hour_stamp || '':00%'';
    INSERT INTO counters_hour VALUES
      (lineid,sums.sum_from,sums.sum_to,
      timestamp(hour_stamp || '':00''),''now'');

    day_stamp       := substr(counter_stamp,1,10);
    SELECT INTO sums
      SUM(counterfrom) as sum_from,SUM(counterto) as sum_to FROM counters_hour
      WHERE line = lineid and counterstamp like
      day_stamp || '' %'';
    IF sums.sum_from ISNULL OR sums.sum_to ISNULL THEN
      sums.sum_from := 0;
      sums.sum_to   := 0;
    END IF;
    DELETE FROM counters_day
      WHERE line = lineid AND counterstamp like
      day_stamp || '' 00:00%'';
    INSERT INTO counters_day VALUES
      (lineid,sums.sum_from,sums.sum_to,
      timestamp(day_stamp || '' 00:00''),''now'');

    month_stamp     := substr(counter_stamp,1,7);
    SELECT INTO sums
      SUM(counterfrom) as sum_from,SUM(counterto) as sum_to FROM counters_day
      WHERE line = lineid and counterstamp like
      month_stamp || ''%'';
    IF sums.sum_from ISNULL OR sums.sum_to ISNULL THEN
      sums.sum_from := 0;
      sums.sum_to   := 0;
    END IF;
    DELETE FROM counters_month
      WHERE line = lineid AND counterstamp like
      month_stamp || ''%'';
    INSERT INTO counters_month VALUES
      (lineid,sums.sum_from,sums.sum_to,
      timestamp(month_stamp || ''-01 00:00''),''now'');

    RETURN NULL;
  END;
'
LANGUAGE 'plpgsql';

  S pozdravem,
    Robert Vojta

--
   _
  |-|  __      Robert Vojta <vojta na ipex.cz>
  |=| [Ll]     IPEX, s.r.o.
  "^" ====`o