Insert Nested Set

Hallo,
trotz langem Suchens und Probierens habe ich bisher leider keine Lösung finden können für mein Problem.
Ich habe eine Nestedset-Tabelle mit erst 8000 Datensätzen. Nun wird über eine Funktion zu den meisten Datensätzen ein Unterpunkt(Ast) unterhalb hinzugefügt. D.h. es laufen pro neuem Datensatz zwei Updates und ein Inserts. Die Funktion liest die notwendigen Daten aus zwei Tabellen aus und ruft eine Funktion auf, die dann unterhalb einfügt.
So weit so gut, wenn ich das mit einem Datensatz mache geht das wunderbar schnell (ca. 10ms pro eingefügtem Datensatz). Wenn ich aber nun diese Funktion laufen lasse laufen die ersten Datensätze schnell(1 Datensatz pro Sekunde) und wird dann schnell langsamer bis zum fast Stillstand. Ich habe schon sämtliche Tuningtipps für den Postgresserver versucht, leider ohne Erfolg.

Vielleicht hat jemand von euch eine Idee.

Glaskugel ist grad defekt. Vielleicht kannst Du helfen und Deine Funktion und Tabelle mal zeigen?


Andreas

die Zieltabelle, enthält ca. 2500 Einzelbäume unter root, aber das ist ja eigentlich egal…

CREATE TABLE d_data.d_attribut
(
  roid bigserial NOT NULL, -- ID
  tree_id integer, -- Baum ID
  rank integer NOT NULL, -- Ebene
  lft bigint NOT NULL, -- links
  rgt bigint NOT NULL, -- rechts
  comments character varying, -- Bemerkung, Kommentar
  y_m_obj_roid bigint, -- roid aus y_meta_object
  fadd_user character varying DEFAULT "current_user"(), -- Benutzer des Eintragens - first add user
  fadd timestamp with time zone DEFAULT now(), -- Zeitstempel des Eintragens - first add
  k_objit_char_roid bigint,
  attribut character varying,
  CONSTRAINT pk_attribut_neu PRIMARY KEY (roid),
  CONSTRAINT k_objit_char_d_att FOREIGN KEY (k_objit_char_roid)
      REFERENCES y_metadata.k_objitem_char (roid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT y_m_obj_d_att FOREIGN KEY (y_m_obj_roid)
      REFERENCES y_metadata.y_meta_object (roid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX attindda
  ON d_data.d_attribut
  USING btree
  (attribut);


CREATE INDEX index_k_objit_char
  ON d_data.d_attribut
  USING btree
  (k_objit_char_roid);


CREATE INDEX index_rank
  ON d_data.d_attribut
  USING btree
  (rank);


CREATE INDEX index_tree_id
  ON d_data.d_attribut
  USING btree
  (tree_id);


CREATE INDEX index_y_m_obj
  ON d_data.d_attribut
  USING btree
  (y_m_obj_roid);


CREATE INDEX indroidda
  ON d_data.d_attribut
  USING btree
  (roid);

CREATE INDEX lftindexda
  ON d_data.d_attribut
  USING btree
  (lft);


CREATE INDEX rgtindda
  ON d_data.d_attribut
  USING btree
  (rgt);

Wie zu sehen ist, habe ich mittlerweile auf jede erdenkliche Spalte einen Index gelegt.

Die Quelltabelle kann varieren, es sind aber normale Tabellen mit bis zu 40 Spalten und mehreren tausend Datensätzen, sie enthalten alle nur eine Primärschlüssel.

Die Funktion die das alles erledigen soll:

CREATE OR REPLACE FUNCTION y_function.insert_d_attribut_b2til()
  RETURNS void AS
$BODY$
	DECLARE
	ersterloop text;
	zweiterloop text;
	insertme text;
	refcur record;
	roid_par integer;
	roid_koic integer;
	roid_obj integer;
	tabellenschema text;
	tabellenname text;
	methode  bigint;

	refcur_plot record;
	tabobj_roid integer;
	attributaustabelle character varying;


	
	BEGIN
	tabellenschema:='quellschema';
	tabellenname:='quelltabelle';
	methode := 233;--Vorklärung
	ersterloop:='select column_name from information_schema.columns where table_schema=' || quote_literal(tabellenschema) || ' and table_name=' || quote_literal(tabellenname);
	zweiterloop:='select attribut::integer from d_data.d_attribut where y_m_obj_roid=40 order by attribut::integer DESC';
	/*welche spalten hat die tabelle?*/
	for refcur in execute ersterloop  LOOP
	
	for refcur_plot in execute zweiterloop LOOP
	
	/* HIER WERDEN DIE PARAMETER GESUCHT */
	roid_par:=roid from y_metadata.y_meta_parameter where (select split_part(ref_l, ' ## ',1))=tabellenname and (select split_part(ref_l, ' ## ',2))=refcur.column_name;
		
	roid_koic:= koic.roid from y_metadata.k_objitem_char koic inner join y_metadata.k_object_item koi on koic.k_obj_item_roid=koi.roid inner join y_metadata.k_item_ref kir on koi.k_item_roid=kir.roid where kir.meth_roid=methode and para_roid=roid_par;
	
	roid_obj:=koi.m_ort_roid from y_metadata.k_object_item koi inner join y_metadata.k_item_ref kir on koi.k_item_roid=kir.roid where kir.meth_roid=methode and para_roid=roid_par;

	
	--for refcur_subplot in select * from d_data.d_attribut where lft>=(SELECT lft from d_data.d_attribut where attribut::integer=refcur_plot.attribut) and rgt<=(SELECT rgt from d_data.d_attribut where attribut::integer=refcur_plot.attribut) LOOP

	tabobj_roid:=roid from d_data.d_attribut where y_m_obj_roid=roid_obj and k_objit_char_roid is null and lft>=(SELECT lft from d_data.d_attribut where attribut='' || refcur_plot.attribut || ''  and k_objit_char_roid is null) and rgt<=(SELECT rgt from d_data.d_attribut where attribut='' || refcur_plot.attribut || ''  and k_objit_char_roid is null);
	
	/*diese Abfrage muss an jede Tabelle angepasst werden, um den richtigen Wert zum Plot zu finden */

	execute 'select ' || refcur.column_name || ' from ' || quote_ident(tabellenschema) || '.' ||quote_ident(tabellenname) || ' where num=' || refcur_plot.attribut || ';' into attributaustabelle;
	if attributaustabelle is null then
	
	raise notice 'attributtabelle is null, überspringe';
	else
	attributaustabelle:='''' || attributaustabelle || '''';

 	insertme:='select y_function.x_da_attribut_unterhalb_rein(''d_data'', ''d_attribut'',' || tabobj_roid || ',null, null,' || roid_obj || ', ' || roid_koic || ', ' || attributaustabelle || ');';

	raise notice 'bin bei spalte: % num: % wert: %',refcur.column_name,refcur_plot.attribut,attributaustabelle;
	--raise notice '%',insertme;
	execute insertme;

	end if;

	
	end loop; --refcur_plot

	end loop;--refcur


	END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Es ist sicherlich etwas schwer zu verstehen, da die Funktion, die nachher aufgerufen werden soll mit Parametern gefüttert werden muss, die aus verschiedenen Tabellen kommen. Aber hier noch die augerufene Nestedset-Funktion die unterhalb einfügt:

CREATE OR REPLACE FUNCTION y_function.x_da_attribut_unterhalb_rein(character varying, character varying, bigint, bigint, character varying, bigint, bigint, character varying)
  RETURNS void AS
$BODY$
	/*
	$1=zielschema
	$2=zieltabelle
	$3=zielroid unter der eingefügt werden soll
	$4=tree_id (baum_id);Standard: NULL --> wenn Tabelle keine Spalte baum_id dann nicht berücksichtigt; wenn Spalte baum_id vorhanden: bei NULL wird baum_id von zielroid($3) benutzt, bei 1 wird nächsthöhere baum_id erzeugt
	$5=comments
	$6=y_m_obj_roid
	$7=k_objit_char_roid
	$8=attribut
	*/
	DECLARE
	ziel_schema ALIAS FOR $1;
	ziel_tabelle alias for $2;
	ziel_roid alias for $3;
	tree_id_neu alias for $4;
	quelle_comments alias for $5;
	quelle_y_m_obj_roid alias for $6;
	quelle_k_objit_char_roid alias for $7;
	attribut alias for $8;

	baum_id_exists integer;
	suche_baum_id text;
	suche_baum_id_int integer;
	insert_ort text;
	runme1 text;
	runme2 text;
	runme3 text;
	runme4 text;
	runme5 text;
	lastroid integer;
	curatt refcursor;
	
	/*spalten mit möglichen null-werten*/
	rec_comments character varying;
	rec_k_objit_char_roid character varying;
rec_attribut character varying;
	
	d_attribut text;
	BEGIN
	
	/*NULL-Werte filtern*/
	
	if  quelle_comments is null then
	rec_comments := 'NULL';
	else
	rec_comments := '''' || quelle_comments || '''';
	end if;

	if  attribut is null then
	rec_attribut := 'NULL';
	else
	rec_attribut := '''' || attribut || '''';
	end if;
	if  quelle_k_objit_char_roid is null then
	rec_k_objit_char_roid:= 'NULL';
	else
	rec_k_objit_char_roid:=quelle_k_objit_char_roid;
	end if;

	/* erstelle abfragen */
	runme1:='UPDATE ' || ziel_schema || '.' || ziel_tabelle || ' SET lft = lft+2 WHERE lft > (SELECT rgt FROM ' || ziel_schema || '. ' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ');';
	
	runme2:='UPDATE ' || ziel_schema || '.' || ziel_tabelle || ' SET rgt = rgt + 2 WHERE  rgt >= (SELECT rgt FROM ' || ziel_schema || '. ' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ') ;';
	
	runme3:= 'INSERT INTO ' || ziel_schema || '.' || ziel_tabelle || ' (rank,lft,rgt,comments,y_m_obj_roid,k_objit_char_roid,attribut ';
	
	runme4 := ')VALUES
	(
	(SELECT rank FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')+1,--rank
	(SELECT rgt FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')-2,--lft
	(SELECT rgt FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')-1,--rgt
	' || rec_comments || ', --comments
	' || quelle_y_m_obj_roid || ',--y_meta_object roid
	' || rec_k_objit_char_roid ||  ',--k_objitem_char roid
	' || rec_attribut || '
	
	';--bestandteil von runme3
	
	runme5 := ');';--bestandteil von runme3
	
	if tree_id_neu is null then
		/*baum_id von zielroid wird übernommen */
		
		runme3 := runme3 || ',tree_id' || runme4;
		runme3 := runme3  || ',(SELECT tree_id FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')';
		runme3 := runme3 || runme5;

	elseif tree_id_neu=0 then
		/*baum_id von zielroid wird übernommen */
		
		runme3 := runme3 || ',tree_id' || runme4;
		runme3 := runme3  || ',(SELECT tree_id FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')';
		runme3 := runme3 || runme5;
	elseif tree_id_neu=1 then
		--raise notice 'baum_id wird erzeugt';
		runme3 := runme3 || ',tree_id' || runme4 || ',(SELECT max(tree_id) FROM ' || ziel_schema || '.' || ziel_tabelle || ' )+1' || runme5;
		else
		
	end if;
	--raise notice 'runme1 jetzt';

	execute runme1;
	--raise notice 'runme2 jetzt';
	execute runme2;
	--raise notice 'runme3 jetzt';
	execute runme3;
	--raise notice 'runme3: %', runme3;
	
      
	end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Das ganze ist so aufgebaut, dass ich nachher in der ersten Funktion die Tabelle+Schema angeben kann und dann automatisch der Inhalt aus einer normalen Tabelle in die Nestedsettabelle übertragen wird, in der der Inhalt der Spalte num die oberste Ebene ist, der alte Tabellenname zweite Ebene und der Inhalt der alten Tabelle dort unterhalb eingefügt wird.

Ich hoffe es wird halbwegs verständlich.

Ich habe es nach und nach versucht den Überltäter zu finden. Es scheint am längsten zu brauchen, bei den Updates in der Nestedset-Funktion. Das zusammenbauen zum Aufrufen der Funktion geht schnell.

Ich habe die zu übergebenen Parameter an die Nestedset-Funktion auch in eine Tabelle geschrieben, um sie dann von dort aus wieder aufzurufen, gleiches Problem.

Er wird halt nach ein paar Datensätzen langsamer, wobei ich glaube das es daran liegen kann, dass Postgres sich die geänderten Daten erst merkt (WAL?) und erst am Ende schreibt. Und da fast jeder vorhandener Datensatz mit jeden neuem Datensatz geändert wird kommen ziemlich viele Änderungen zusammen. Dafür habe ich aber eben schon fast jeden Tuningtrick probiert den ich finden konnte um dieses Problem zu beheben.

So, ich hoffe es ist verständlich. Danke schon mal im Voraus.

Da durchzusteigen hab ich wenig Lust, aber, was mir so aufgefallen ist:

  • warum so viele Indexe? Die zu aktualisieren ist teuer
  • warum so viele BIGINT, wenn es doch nur ein paar tausend Zeilen sind?
  • die verschachtelten EXECUTE - Anweisungen sorgen dafür, daß da jedesmal neu geplant wird, auch das kostet Zeit

Fakt ist, bei so einer Update-lastigen Aktion entstehen massig tote Tupel, und es ist viel WAL zu schreiben. Ads kann da vielleicht was zum Tuning schreiben, was ich mich frage: wären rekursive Abfragen (PG 8.4+) nicht sinnvoller?

Andreas

Ack.


Ads kann da vielleicht was zum Tuning schreiben, was ich mich frage: wären rekursive Abfragen (PG 8.4+) nicht sinnvoller?

Dafür müsste man sich erst mal konkret die Last der Maschine anschauen - hier pauschale Aussagen zu treffen ist wahrscheinlich kontraproduktiv.

Die Bigint deshalb, weil ich bei dem Nestedset nicht absehen kann, wieviele Datensätze es werden.
Aber was meinst du mit rekursiven Abfragen ab 8.4?

Die Maschinenauslastung denke ich ist erstmal noch nicht das Problem, die Ursache liegt sicher in der Funktion.

PostgreSQL kann mittels “recursive common table expressions” hierarchische Datenstrukturen relativ einfach abhandeln.

Wenn Du eine Tabelle der Form:

CREATE TABLE category 
(
   id                  integer primary key,
   category_name       varchar(100) not null,
   parent_category_id  integer, 
   foreign key (parent_category_id) references category (id)
);

hast, kann man die relativ einfach abfragen

Siehe die Beispiel im Handbuch:
PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: WITH Queries

Oder einfach nach “recursive common table expressions” (oder “recursive CTE”) suchen

Also ich befürchte das Problem liegt an den vielen Updates von lft/rgt des NestedSet. bei vorhandenen knapp 8000 Zeilen und einem Insert von Zeile 1, dann Zeile 2,…
bearbeitet Postgres erst 7999 Zeilen mit lft/rgt +2, dann 7998 Zeilen lft/rgt + 2,…

Das scheint auch der Grund zu sein, weshalb die Abfrage schnell anfängt und dann langsamer wird. Beginne ich den Insert bei Zeile 8000 und dann abwärts geht es anfangs länger schnell, wird aber auch dann langsamer bis ein Insert meherer Minuten dauert.
Ich habe spaßenshalber mal eine parent-Spalte eingefügt und siehe da, insert sind in weniger als 1 Minute durch.

Aber falls noch jemand eine Idee hat, ich wäre froh, das ganze auch mal erfolgreich mit NestedSet zu probieren.

Danke