4. postgreSQL 9.1: Langsames SELECT in einer Triggerfunktion

postgreSQL 9.1: Langsames SELECT in einer Triggerfunktion


  • Hallo zusammen,

    ich habe eine Tabelle, die im Grunde Key/Value Paare enthält. In manchen Fällen brauche ich nur die eindeutigen Keys und eindeutigen Values, die ich über zwei separate Queries abhole (sowohl das Feld key als auch value sind indiziert):

    SELECT DISTINCT key FROM custom_data;
SELECT DISTINCT value FROM custom_data;

    Da die Tabelle aber groß werden kann dauert die Abfrage bei ca. 40M Datensätzen jeweils einige Sekunden, was in bestimmten Fällen zu lang ist.
    Also habe ich mir überlegt, jeweils eine Lookuptabelle für key und value zu pflegen, die über einen Trigger auf der custom_data Tabelle aktualisiert wird. Das funktioniert eigentlich auch ganz gut, aber eine SELECT Abfrage in der Triggerfunktion dauert ungewöhnlich lang. Wenn ich sie als einzelnes Statement ausführe kehrt sie in einigen ms zurück, in der Triggerfunktion braucht sie aber 5-6s.

    Hier der relevante Teil der Triggerfunktion:

    CREATE TRIGGER on_custom_data_changed AFTER INSERT OR UPDATE OR DELETE ON
   public.custom_data 
FOR EACH ROW  EXECUTE PROCEDURE public.on_change_custom_data();

CREATE OR REPLACE FUNCTION public.on_change_custom_data ()
RETURNS trigger AS
$body$
BEGIN
   IF TG_OP = 'UPDATE' THEN
      -- Bei einem UPDATE kann der Fall auftreten, dass die alten key/value Einträge nicht 
      -- mehr in custom_data vorhanden sind und dann aus der Lookup Tabelle entfernt müssen.
      RAISE NOTICE 'Check custom data key start  : %', timeofday();
      IF NOT EXISTS (SELECT 1 FROM custom_data WHERE key = OLD.key ) THEN
         RAISE NOTICE 'Delete lookup key start      : %', timeofday();
         DELETE FROM lookup_custom_data_keys WHERE key = OLD.key;
         RAISE NOTICE 'Delete lookup key end        : %', timeofday();
      END IF;
      RAISE NOTICE 'Check custom data key end    : %', timeofday();
    END IF;
    RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100;

    Und hier das Laufzeitprotokoll für einen Funktionsaufruf nach einem Update Statement auf die Tabelle custom_data:

    HINWEIS:  Check custom data key start  : Fri Oct 30 11:56:41.785000 2015 CET (Ausgabe Zeile 13)
HINWEIS:  Delete lookup key start      : Fri Oct 30 11:56:47.144000 2015 CET (Ausgabe Zeile 15)
HINWEIS:  Delete lookup key end        : Fri Oct 30 11:56:47.145000 2015 CET (Ausgabe Zeile 17)
HINWEIS:  Check custom data key end    : Fri Oct 30 11:56:47.145000 2015 CET (Ausgabe Zeile 19)

Query OK, 1 rows affected (5,367 sec)

    Man sieht, dass der SELECT in Zeile 7 ca. 5,3 Sekunden braucht, was eindeutig zu lang ist. Wenn ich das SELECT separat ausführe kehrt die Abfrage in wenigen ms zurück.

    SELECT 1 FROM custom_data WHERE key='key-1'
1 rows returned (16 ms)

    Hat jemand eine Idee, woran das liegen kann? Oder hat jemand eine schlauere Idee, wie ich an die eindeutigen Key bzw. Value Einträge komme?

    0

  • Ich habe einen Work-around gefunden, obwohl das Verhalten immer noch merkwürdig ist. Ich konnte die Problemstelle auf das IF NOT EXISTS (...) reduzieren und ersetzen:

    CREATE OR REPLACE FUNCTION public.fn_trigger_test() 
RETURNS trigger AS
$body$
BEGIN
  -- Methode 1
  PERFORM 1 FROM custom_data WHERE key = OLD.key;
  IF FOUND THEN
     RAISE NOTICE 'Key found';
  ELSE
     RAISE NOTICE 'Key not found';
  END IF;
  RAISE NOTICE 'Checking key existence (1): %ms', age( ct(), s );

  -- Methode 2
  IF NOT EXISTS (SELECT 1 FROM custom_data WHERE key = OLD.key) THEN
     RAISE NOTICE 'Key found';
  ELSE
     RAISE NOTICE 'Key not found';
  END IF;
  RAISE NOTICE 'Checking key existence (2): %ms', age( ct(), s );

  RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100;

    Methode 1 liefert sofort ein Ergebnis, Methode 2 braucht immer noch ca. 5-6 Sekunden. Muss das mal mit anderen postgreSQL Versionen vergleichen.

    0
  • T

    Wie sieht denn der execution plan aus? Vielleicht erkennst Du dann das Problem.

    Ich schreibe gerne mal

    SELECT MAX(1) FROM ...

    Wenn ich einfach nur die 1 selektiere, muss die Datenbank im prinzip so viele Einser liefern, wie Zeilen gefunden werden. Bei MAX(1) kann die Datenbank abbrechen, sobald sie mindestens eine Zeile gefunden hat.

    Aber eigentlich sollte so ein EXISTS auch das bewirken. Vielleicht hat da der Optimierer fron Postgresql 9.1 eine Schwäche. Und vielleicht ist er in späteren Postgresql-Versionen besser. Und vielleicht interessiert die Postgresql-Entwickler solch ein Fall, der nicht gut optimiert wird.

    0

  • Das Problem ist wohl doch tiefgreifender als befürchtet. Das Modul auto_explain hat gezeigt, dass der Query Planer eine falsche Strategie wählt und einen langsamen sequentiellen Scan benutzt, obwohl er einen Index Scan benutzen könnte. Der Query Planer ist wohl sehr empfindlich, was selbst kleinste Änderungen am Quellcode angeht. Ich habe die IF NOT EXISTS (...) in Einzelschritte zerlegt und jetzt wird auch der Index Scan benutzt (Änderungen von SELECT 1 zu SELECT MAX( 1 ) führen wieder zum benutzen des sequentiellen Scans und sind wieder langsam. Genauso wie SELECT ... LIMIT 1).

    Die schnelle Triggerunktion seiht jetzt so aus:

    CREATE OR REPLACE FUNCTION public.fn_trigger_test() 
RETURNS trigger AS
$body$
DECLARE
  result INTEGER;
BEGIN
  SELECT 1 FROM custom_data WHERE key = old.key) INTO result;
  IF result ISNULL THEN
     DELETE FROM custom_data_lookup_keys WHERE key = old.key;
  END IF;
  RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100;
    0
Anmelden zum Antworten