Funktion ist zu langsam

Hallo an Alle,

ich bin neu in Benutzung von PostgreSQL und muss jetzt ein Fehler in einer fertigen Funktion suchen, den ich nicht verstehe.
An eine Funktion werden mehrere Parameter übergeben, die in der SQL-Abfrage benutzt wird, um eine Datenauswertung für einen Zeitraum als Tabelle zurüclzugeben.
Wenn ich die Funktion ausführe und den Zeitraum von 6 Monaten angebe, dauert die Abfrage ca. 4 Sekunden. Sobald ich an einer Stelle (fett markiert im Code) die Variablen durch feste Werte (z.B.

'20100404': :D ATE und '20101004': :D ATE

) ersetze, geht es nur in ca. 200 ms.
Was könnte das sein und wieso funktioniert es mit Variablen langsamer als mit den festen Werten, wo ich an der Stelle eigentlich kein Unterschied sehe? :confused:

-- Function: trainingsergebnis_geraet_view_function(nmandant_id integer, nkunden_id integer, nclass_id integer, ntype integer, nmachine_id integer, cerstellt_am_datumvon bpchar, cerstellt_am_datumbis bpchar)
-- DROP FUNCTION trainingsergebnis_geraet_view_function(nmandant_id integer, nkunden_id integer, nclass_id integer, ntype integer, nmachine_id integer, cerstellt_am_datumvon bpchar, cerstellt_am_datumbis bpchar);
CREATE OR REPLACE FUNCTION trainingsergebnis_geraet_view_function(nmandant_id integer, nkunden_id integer, nclass_id integer, ntype integer, nmachine_id integer, cerstellt_am_datumvon bpchar, cerstellt_am_datumbis bpchar)
  RETURNS SETOF record AS
$BODY$
 DECLARE
        oReturn record;
        nMd_Id integer;
        nKd_id integer;
        nTp integer;
        nMn_id integer;
        dErstDatVon DATE;
        dErstDatBis DATE;
 BEGIN
        nMd_Id := $1;
        nKd_id := $2;
        nTp := $4;
        nMn_id := $5;
        dErstDatVon := $6: :D ATE; 
        dErstDatBis := $7: :D ATE;
        FOR oReturn IN
  Select erstellt_am_datum, 
     machine_id, 
     class_id, 
     mandant_id, 
     kunden_id, 
     bereichs_id, 
     bereichs_name, 
     class_name, 
     geraete_name, 
     nutzung, 
     "template", 
     template_name, 
     CASE 
       when typ = 1 then 
      CASE
        -- // (kJ * 1000) = J  / (min * 60) = sek. ... (J / sek.) = Watt
        when class_id = 0 or class_id = 1 or class_id = 2 or class_id = 5 or class_id = 6 or class_id = 8 then summe
        -- // (m / 1000) = km  / (min / 60) = h  ... (km / h) = km/h
        when class_id = 3 then summe
      END
       when typ = 2 then summe
     END AS summe, 
     typ,
     "template2", 
     template_name2, 
     CASE 
       when typ = 1 then 
      CASE
 
        when class_id = 0 or class_id = 1 or class_id = 2 or class_id = 5 or class_id = 6 or class_id = 8 then summe2::integer
        when class_id = 3 then summe2::integer
      END
       when typ = 2 then summe2
     END AS summe2, 
     CASE 
       when typ = 1 then 
      CASE
        when class_id = 0 or class_id = 1 or class_id = 2 or class_id = 5 or class_id = 6 or class_id = 8 then (((summe2/5)*1000) / (summe*60))::numeric(8,2)
        when class_id = 3 then ((summe3/1000) / (summe/60))::numeric(8,2)
      END
       when typ = 2 then (summe2 / summe)::numeric(8,2)
     END AS durchschnitt,
     "template3", 
     template_name3,
     summe3 AS summe3
     From (
      SELECT tpe.erstellt_am_datum,
       tpe.machine_id,
       tpe.class_id,
       tpe.mandant_id,
       tpe.kunden_id,
       tpe.bereichs_id,
       name_bereich(tpe.bereichs_id) AS bereichs_name,
       name_class(tpe.class_id) AS class_name,
       name_geraet(tpe.machine_id) AS geraete_name,
       Max(tpe.nutzung) AS nutzung,
       tpe.template_name AS "template",
       name_template(tpe.template_name) AS template_name,
       sum(get_geraete_zaehler(tpe.class_id, tpe.machine_id, tpe.template_name, tpe.summe))::numeric AS summe,
       mc."type" AS typ
         FROM trainingsergebnis tpe
         LEFT JOIN machineclasses mc ON mc.class_id = tpe.class_id
      where
     tpe.mandant_id = nMd_Id and --1 and
     tpe.kunden_id = nKd_id and --5 and
     ((mc.type = 1 and tpe.template_name = 'ZZ') or (mc.type = 2 and tpe.template_name = 'HZ'))
     and (nMn_id = 0 and type = nTp and tpe.class_id <> 3 or tpe.machine_id = nMn_id)
     and erstellt_am_datum between [B][size=134]dErstDatVon[/size][/B] and [B][size=134]dErstDatBis[/size][/B]
        GROUP BY tpe.erstellt_am_datum,
       tpe.machine_id,
       tpe.template_name,
       tpe.class_id,
       tpe.mandant_id,
       tpe.kunden_id,
       tpe.bereichs_id,
       mc."type"
      ) AS foo
     Join
      (SELECT tpe2.erstellt_am_datum AS erstellt_am_datum2,
       tpe2.template_name AS "template2",
       tpe2.machine_id AS machine_id2,
       tpe2.class_id AS class_id2,
       name_template(tpe2.template_name) AS template_name2,
       sum(get_geraete_zaehler(tpe2.class_id, tpe2.machine_id, tpe2.template_name, tpe2.summe))::numeric AS summe2
         FROM trainingsergebnis tpe2
         LEFT JOIN machineclasses mc ON mc.class_id = tpe2.class_id
   where
     tpe2.mandant_id = nMd_Id and --1 and
     tpe2.kunden_id = nKd_id and --5 and
     ((mc.type = 1 and tpe2.template_name = 'AZ') or (mc.type = 2 and tpe2.template_name = 'GZ'))
     and (nMn_id = 0 and type = nTp and tpe2.class_id <> 3 or tpe2.machine_id = nMn_id) and erstellt_am_datum between dErstDatVon and dErstDatBis
        GROUP BY tpe2.erstellt_am_datum,
       tpe2.machine_id,
       tpe2.template_name,
       tpe2.class_id,
       mc."type"
      ) AS foo2
     on (foo.erstellt_am_datum = foo2.erstellt_am_datum2 and foo.machine_id = foo2.machine_id2 and foo.class_id = foo2.class_id2)
     left Join
      (SELECT tpe3.erstellt_am_datum AS erstellt_am_datum3,
       tpe3.template_name AS "template3",
       tpe3.machine_id AS machine_id3,
       tpe3.class_id AS class_id3,
       name_template(tpe3.template_name) AS template_name3,
       sum(get_geraete_zaehler(tpe3.class_id, tpe3.machine_id, tpe3.template_name, tpe3.summe))::numeric AS summe3
         FROM trainingsergebnis tpe3
         LEFT JOIN machineclasses mc ON mc.class_id = tpe3.class_id
      where
     tpe3.mandant_id = nMd_Id and
     tpe3.kunden_id = nKd_id and
     ((mc.type = 1 and tpe3.template_name = 'SZ') or (mc.type = 2 and tpe3.template_name = 'WZ'))
     and (nMn_id = 0 and type = nTp and tpe3.class_id <> 3 or tpe3.machine_id = nMn_id) and erstellt_am_datum between dErstDatVon and dErstDatBis
        GROUP BY tpe3.erstellt_am_datum,
       tpe3.machine_id,
       tpe3.template_name,
       tpe3.class_id,
       tpe3.mandant_id,
       tpe3.kunden_id,
       mc."type"
      ) AS foo3
     on (foo.erstellt_am_datum = foo3.erstellt_am_datum3 and foo.machine_id = foo3.machine_id3 and foo.class_id = foo3.class_id3)
     order by machine_id
          LOOP
          RETURN NEXT oReturn;
          END LOOP;
       RETURN ;
      END
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION trainingsergebnis_geraet_view_function(nmandant_id integer, nkunden_id integer, nclass_id integer, ntype integer, nmachine_id integer, cerstellt_am_datumvon bpchar, cerstellt_am_datumbis bpchar) OWNER TO "xxxxx";

Im Voraus vielen Dank,

Andreas

Der Planer hat keine Ahnung, welchen konkreten Wert Dein Parameter haben wird, daher wählt er einen offensichtlich ungünstigen Plan. Du kannst den Planner dazu bringen, den Plan neu zu wählen, indem Du das SQL dynamisch ausführst, EXECUTE ist hier Dein Freund.


Andreas

Ich habe versucht, die Funktion mit EXECUTE zu realisieren, bekomme aber leere Ergebnisse. Was mache ich hier falsch:

CREATE OR REPLACE FUNCTION trainingsergebnis_geraet_view_function_test(nmandant_id integer, nkunden_id integer, nclass_id integer, ntype integer, nmachine_id integer, cerstellt_am_datumvon bpchar, cerstellt_am_datumbis bpchar)
  RETURNS SETOF record AS
$BODY$
 DECLARE
        oReturn record;
        nMd_Id ALIAS FOR $1;
        nKd_id ALIAS FOR $2;
        nTp ALIAS FOR $4;
        nMn_id ALIAS FOR $5;
        dErstDatVon ALIAS FOR $6; 
        dErstDatBis ALIAS FOR $7;
 BEGIN
 FOR oReturn IN EXECUTE
  'Select erstellt_am_datum, 
     machine_id, 
     class_id, 
     mandant_id, 
     kunden_id, 
     bereichs_id, 
     bereichs_name, 
     class_name, 
     geraete_name, 
     nutzung, 
     "template", 
     template_name, 
     CASE 
       when typ = 1 then 
      CASE
        -- // (kJ * 1000) = J  / (min * 60) = sek. ... (J / sek.) = Watt
        when class_id = 0 or class_id = 1 or class_id = 2 or class_id = 5 or class_id = 6 or class_id = 8 then summe
        -- // (m / 1000) = km  / (min / 60) = h  ... (km / h) = km/h
        when class_id = 3 then summe
      END
       when typ = 2 then summe
     END AS summe, 
     typ,
     "template2", 
     template_name2, 
     CASE 
       when typ = 1 then 
      CASE
        when class_id = 0 or class_id = 1 or class_id = 2 or class_id = 5 or class_id = 6 or class_id = 8 then summe2::integer
        when class_id = 3 then summe2::integer
      END
       when typ = 2 then summe2
     END AS summe2, 
     CASE 
       when typ = 1 then 
      CASE
        when class_id = 0 or class_id = 1 or class_id = 2 or class_id = 5 or class_id = 6 or class_id = 8 then (((summe2/5)*1000) / (summe*60))::numeric(8,2)
        when class_id = 3 then ((summe3/1000) / (summe/60))::numeric(8,2)
      END
       when typ = 2 then (summe2 / summe)::numeric(8,2)
     END AS durchschnitt,
     "template3", 
     template_name3,
     summe3 AS summe3
     From (
      SELECT tpe.erstellt_am_datum,
       tpe.machine_id,
       tpe.class_id,
       tpe.mandant_id,
       tpe.kunden_id,
       tpe.bereichs_id,
       name_bereich(tpe.bereichs_id) AS bereichs_name,
       name_class(tpe.class_id) AS class_name,
       name_geraet(tpe.machine_id) AS geraete_name,
       Max(tpe.nutzung) AS nutzung,
       tpe.template_name AS "template",
       name_template(tpe.template_name) AS template_name,
       sum(get_geraete_zaehler(tpe.class_id, tpe.machine_id, tpe.template_name, tpe.summe))::numeric AS summe,
       mc."type" AS typ
         FROM trainingsergebnis tpe
         LEFT JOIN machineclasses mc ON mc.class_id = tpe.class_id
      where
     tpe.mandant_id = ' || nMd_Id || ' and --1 and
     tpe.kunden_id = ' || nKd_id || ' and --5 and
     ((mc.type = 1 and tpe.template_name = ''ZZ'') or (mc.type = 2 and tpe.template_name = ''HZ''))
     and (' || nMn_id || ' = 0 and type = ' || nTp || ' and tpe.class_id <> 3 or tpe.machine_id = ' || nMn_id || ') 
    and erstellt_am_datum between ' || dErstDatVon || ' and ' || dErstDatBis
        || 'GROUP BY tpe.erstellt_am_datum,
       tpe.machine_id,
       tpe.template_name,
       tpe.class_id,
       tpe.mandant_id,
       tpe.kunden_id,
       tpe.bereichs_id,
       mc."type"
      ) AS foo
  Join
      (SELECT tpe2.erstellt_am_datum AS erstellt_am_datum2,
       tpe2.template_name AS "template2",
       tpe2.machine_id AS machine_id2,
       tpe2.class_id AS class_id2,
       name_template(tpe2.template_name) AS template_name2,
       sum(get_geraete_zaehler(tpe2.class_id, tpe2.machine_id, tpe2.template_name, tpe2.summe))::numeric AS summe2
         FROM trainingsergebnis tpe2
         LEFT JOIN machineclasses mc ON mc.class_id = tpe2.class_id
   where
     tpe2.mandant_id = ' || nMd_Id || ' and --1 and
     tpe2.kunden_id =  ' || nKd_id || ' and --5 and
     ((mc.type = 1 and tpe2.template_name = ''AZ'') or (mc.type = 2 and tpe2.template_name = ''GZ''))
     and (' || nMn_id || ' = 0 and type = ' || nTp || ' and tpe2.class_id <> 3 or tpe2.machine_id = ' || nMn_id || ')
    and erstellt_am_datum between ' || dErstDatVon || ' and ' || dErstDatBis
        || 'GROUP BY tpe2.erstellt_am_datum,
       tpe2.machine_id,
       tpe2.template_name,
       tpe2.class_id,
       mc."type"
      ) AS foo2
     on (foo.erstellt_am_datum = foo2.erstellt_am_datum2 and foo.machine_id = foo2.machine_id2 and foo.class_id = foo2.class_id2)
     left Join
      (SELECT tpe3.erstellt_am_datum AS erstellt_am_datum3,
       tpe3.template_name AS "template3",
       tpe3.machine_id AS machine_id3,
       tpe3.class_id AS class_id3,
       name_template(tpe3.template_name) AS template_name3,
       sum(get_geraete_zaehler(tpe3.class_id, tpe3.machine_id, tpe3.template_name, tpe3.summe))::numeric AS summe3
         FROM trainingsergebnis tpe3
         LEFT JOIN machineclasses mc ON mc.class_id = tpe3.class_id
      where
     tpe3.mandant_id = ' || nMd_Id || ' and
     tpe3.kunden_id = ' || nKd_id || ' and
     ((mc.type = 1 and tpe3.template_name = ''SZ'') or (mc.type = 2 and tpe3.template_name = ''WZ''))
     and (' || nMd_Id || ' = 0 and type = ' || nTp || ' and tpe3.class_id <> 3 or tpe3.machine_id = ' || nMd_Id || ') 
    and erstellt_am_datum between ' || dErstDatVon || ' and ' || dErstDatBis
        || 'GROUP BY tpe3.erstellt_am_datum,
       tpe3.machine_id,
       tpe3.template_name,
       tpe3.class_id,
       tpe3.mandant_id,
       tpe3.kunden_id,
       mc."type"
      ) AS foo3
     on (foo.erstellt_am_datum = foo3.erstellt_am_datum3 and foo.machine_id = foo3.machine_id3 and foo.class_id = foo3.class_id3)
     order by machine_id'
 LOOP
          RETURN NEXT oReturn;
        END LOOP;
       RETURN ;
      END
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION trainingsergebnis_geraet_view_function_test(nmandant_id integer, nkunden_id integer, nclass_id integer, ntype integer, nmachine_id integer, cerstellt_am_datumvon bpchar, cerstellt_am_datumbis bpchar) OWNER TO "xxxxxxx";

Ich habe das Problem bei der EXECUTE-Umstellung gefunden: die Datum-Werte mussten noch konvertiert werden.

Danke für die Hilfe!

Wie sieht die Laufzeit jetzt aus?

Du kannst dir auch mal mittels:

RAISE NOTICE 'query: %', query_str;

Die Anfrage ausgeben lassen. Als Variable “query_str” müsstest du halt die Anfrage dort einsetzen.
Danach kannst du mittels EXPLAIN den Anfrageplan ausgeben lassen.

Auf einen grösseren Zeitraum:

  • alte Abfrage: 13765 ms,
  • neue Abfrage: 312 ms.

Danke für den Tip, es hat funktioniert. Bei der Verwendung von PostgreSQL muss ich alles neu lernen! Bis vor kurzem habe ich nur MS SQL benutzt.

Nun ja, dieses Verhalten von Funktionen (und auch prepared statements) ist eine von wenigen lästigen Eigenheiten, über die schon andere vor Dir gestolpert sind …


Andreas