Funktion wird immer langsamer

Hallo,
ich habe eine Funktion welche laut EXPLAIN intern nur Indexe verwendet und theoretisch sehr schnell ablaufen sollte, aber während einigen Stunden immer langsamer wird: (von 15ms auf 500ms nach ca. 8 Stunden)
Umgebung: PG 8.3.7, Windows XP

Die Daten werden ein paar Sekunden bevor die Funktion ausgeführt wird, von einer anderen Funktion in die Haupttabelle eingefügt (Alle 1,5sec ca. 50 Reihen), sollten sich also im Speicher befinden, oder?
Die eigentliche Funktion ist dann ein relativ unspektakulärer SELECT. (Einzige Besonderheit: Er wird dynamisch zusammengesetzt und dann damit eine TEMP Tabelle gefüllt)

Das witzige ist, dass die Funktionsaufrufe wieder sehr schnell laufen wenn eine von folgenden Dingen gemacht wird:

  • PG Neustart
  • Client Neustart (???)
  • Einige Daten löschen und VACUUM ANALYZE aufrufen

Wenn die Funktion mit denselben Parametern (demselben Primary Key) ein zweites Mal aufgerufen wird, läuft sie wieder sehr schnell.

Im Verdacht hatte ich schon die Windows Speicherverwaltung, da man ja nie genau weiß, was für eine Art von Speicher (RAM oder Platte) man zugewiesen bekommt. Habe dann die Auslagerungsdatei auf 0 gesetzt. Hat aber auch nichts gebracht.
shared_buffers = 800 (runtersetzen auf 200 bringt auch nix)

Auf Deutsch: Bin mittlerweile zieeeemlich ratlos. Hat irgendjemand schon einmal einen solchen Effekt gehabt?

Im Speicher ganz sicher. Kommt nur drauf an, was Du darunter verstehst.

Wo wie Daten aber recht sicher NICHT sind: in den Statistiken, die der Planner verwendet. Setze also mal nach dem Insert ein Analayse ein.

Ansonsten müßte man ein paar Dinge mehr wissen:

  • passende Indexe
  • konkrete Abfrage

Die Abfrage wird dynamisch erzeugt und mit Execute ausgeführt?

Andreas

→ Hmmm, beim ersten Anfassen der Daten vermutlich im 1.Level Cache. Daher sollte eigentlich bei Auslagerungsdateigröße = 0 alles im RAM ablaufen.

Es laufen automatische VACUUM ANALYZE. Ausserdem sind die Art der Daten immer sehr ähnlich.

OK, dann doch etwas mehr Infos:
Folgender String wird intern zusammengebaut:[INDENT]CREATE TEMP TABLE tmp ON COMMIT DROP AS
SELECT attribid, xdatatype, value, unit, partpassageattrib.xmachineid, typeid, jobid, partid, time, xpartpnr
FROM partpassageattrib, partpassage, attrib
WHERE xpartpnr = partpnr
AND partpassage.xmachineid >= 1000
AND partpassageattrib.xmachineid BETWEEN 1000 AND 1099 AND attribid = xattribid
AND attrib.xmachineid = partpassageattrib.xmachineid
AND partpnr = 41243
ORDER BY partpnr DESC;
ALTER TABLE tmp ADD COLUMN attribnr integer DEFAULT 1;
[/INDENT]Dieser String wird nun mit “EXECUTE sqlstring;” ausgeführt.

Das Ergebnis wird nun sortiert zurückgegeben:[INDENT] – Returns the ‘tmp’ table, but only if there is any result
IF EXISTS(SELECT * FROM tmp) THEN[INDENT] IF “@order” IS NOT NULL THEN[INDENT] var_sql = var_sql || 'SELECT * FROM tmp ORDER BY ’
|| “@order” || ‘;’;
FOR var_row IN EXECUTE var_sql LOOP[INDENT] RETURN next var_row;
[/INDENT]END LOOP;
[/INDENT]ELSE[INDENT] RETURN query SELECT * FROM tmp;
[/INDENT]END IF;
[/INDENT]END IF;
[/INDENT]Tabelle partpassageattrib (Tabelle ca. 100MB, Index ca. 60MB)
PRIMARY KEY (xpartpnr, xattribid, xmachineid)

Tabelle partpassage: (Tabelle: 4MB, Index: 6MB)
PRIMARY KEY (partpnr, xmachineid)
zusätzlicher INDEX auf partid

Tabelle attrib: (Tabelle: 8k)
PRIMARY KEY (attribid, xmachineid)

Wird zwar nicht die Ursache sein, ist aber aus Performance-Sicht auch doof: Du erzeugst die Tabelle und fügst dann eine extra Spalte an, mit Default-Wert. Das erfordert ein Neuschreiben der ganzen Tabelle. Erzeuge doch die Spalte gleich mit, einfach im Select-teil noch , 1 as attribnr dazu.

Mehr fällt mir erst mal grad nicht ein …


Andreas

Hi,
habe dieses Problem leider immer noch. Bisher war es nicht so tragisch, da wie schon geschrieben das Vacuum die Ausführungsdauer wieder beschleunigt hat. Das VACUUM ANALYZE wird stündlich aufgerufen.
Dies scheint jetzt allerdings mit der Umstellung auf 8.4.3 nicht mehr immer der Fall zu sein. So steigt die Ausführungszeit von 0ms innerhalb von mehreren Stunden langsam auf über 1 Sekunde an.
Neu getestet: Mit Indizes hat das ganze nichts zu tun, auch mit der Defragmentierung des shared_buffers (habe ich testhalber von 800MB auf 10MB reduziert)

… bin komplett ratlos

Ähm, ja.

Du hast eine Tabelle. In einer Funktion erstellst Du eine temp. Tabelle als Abfrage auf diese Tabelle. Dann machst Du ein select * auf diese Tabelle, das ist teuer. Dann machst Du nochmals ein select *, um die Tabelle auszugeben. Wäre es nicht zielführender, das Resultat der ersten Abfrage gleich auszugeben?

Das schreiben der temp. Tabelle ist teuer, da muß ja auch was WAL geschrieben werden. Das select * ist teuer, weil es eines seq-scan auslöst. Das nochmalige Lesen für die Ausgabe ist dann auch nochmal teuer, das ORDER BY kann keinen Index nutzen, da keiner da. Du wirfst viel Geld auf - ja, auf was eigentlich?


Andreas

Bingo,
dein Tip war Gold wert. Ich habe die temporäre Tabelle entfernt (zugegeben ein Relikt von früher und völlig unnötig) und siehe da, die Ausführungszeiten der Abfrage laufen jetzt seit 12 Stunden stabil und schnell.

Vielen Dank

Geld reicht, wohin darf ich die Rechnung denn schicken?

Andreas