Best Practice / Explain Delete für massives Löschen

[dabadepdu]

Tabelle a mit x Millionen Datensätzen, x derzeit einstellig
Tabelle b x*10 Mio. Datensätzen
Eine N:M Relation zwischen a und b
Alle mit PK und FK und zugehörigen Indizes, keine Cascade Definitionen


Bei einem massiven Löschvorgang abhängig von einem Datum in A entferne ich zunächst aus B und dann aus der NM Relation alle zugehörigen Datensätze, zuletzt aus A. Dabei verwende ich eine Temp Tabelle mit ID der zu löschenden Records.
Das alles ist sehr langsam, Tendenz unendlich. Irritierend: ausgerechnet der Löschvorgang in A (kleine Tabelle) ist sehr langsam. Explain sagt, es wird ein simple Indexscan für das Datumskriterium verwendet, ziemlich übersichtlich.

Nach einigem Hinundher habe ich „festgestellt“, dass der Zeitfresser beim Löschen der kleinen Tabelle die Prüfung der FK Constraints auf der N:M Relation ist. Der PK Index wird dabei offenbar nicht verwendet.

Also bei jedem Delete aus A wird auf N:M geschaut, ob noch Verweise da sind und dabei greift offenbar der PK auf die Schlüsselfelder nicht. Das ist allerdings eine Vermutung, da Explain darüber nichts sagt.
Proof: Erzeugt man analog zu dem bereits existierenden PK Index einzelne Indizes für die beiden PK Spalten, hat man einen enormen Geschwindigkeitszuwachs.

Hat dazu jemand einen Hinweis, entweder Richtung Explain (zu den Constraint Operationen hinter dem Delete) oder zu einem anderen Vorgehen?
Ich habe bis jetzt bewusst kein Cascade Delete drin und verspreche mir auch nichts davon, eher das Gegenteil/ ist mir unlieb. Bzw: ich könnte mir vorstellen, dass die Cascade Delete Trigger * doch schneller sind, als der jetzige Zustand. Aber nicht so schnell wie ein Massendelete auf den Einzeltabellen ohne Cascade. Habs noch nicht getestet.


Es geht erst mal nicht um Änderungen des logischen DM, Partitioning oder so, nur Analyse und Verfahrensfragen. Wahrscheinlich wird die Persistierung sowieso verschlankt. X aus Tabelle A wird dennoch eher wachsen. Das Verhalten betrifft verschiedene Versionen, die neueste ist 13.5, Standardinstallationen in VM. Neuere werde ich noch testen.


* der genaue Mechnismus dafür ist mir nicht bekannt

 

[akretschmer]

Die Prozedur könntest Du so umschreiben, ohne (expliziete) temp. Tabelle:

edb=# create table a(id int primary key);
CREATE TABLE
edb=*# create table b(id int primary key, a_id int references a);
CREATE TABLE
edb=*# with deleted as (delete from b returning a_id) delete from a where id in (select a_id from deleted);
DELETE 0
edb=*#

Wenn da noch eine N:M - Tabelle involviert ist, passend anpassen. Das *könnte* etwas schneller sein.

 

[dabadepdu]

Ok, danke, werde ich probieren. Ich sehe da nicht unbedingt, dass die Prüfung der PK dann besser greift, aber wer weiß, was der Optimizer alles kann.
Hast Du dazu vielleicht noch einen Hinweis? Kann man irgendwo etwas zum Verhalten der RefConstraint Prüfung finden?

 

[akretschmer]

da hast Du keinen Einfluß drauf, soweit ich weiß.

 

[dabadepdu]

Nein, ich meinte nur, wie man sowas überhaupt bemerken kann, also eine Art extend explain oder sowas.

 

[castorp]

Du kannst explain (analyze) auch für einen DELETE Befehl verwenden.
Aber Achtung: das löscht die Daten wirklich. Also entweder in einer Transaktion machen und danach ein ROLLBACK, oder mit Testdaten

 

[dabadepdu]

Ah, ich habe das explain analyze bis jetzt immer abgebrochen und es jetzt mal mit einer gestellten Situation laufen lassen (wenig Daten).
Dann bekomme ich immerhin die Trigger Laufzeiten. Ist jetzt wegen der kleinen Datenmenge auch nicht aussagekräftig aber zumindest im Vergleich mit unterschiedlicher Indizierung ein guter Indikator. Dass, ob, welcher Index verwendet wird, muss man dennoch erst mal drauf kommen.
Man müsste also den Trigger Code nehmen und die Statements darin explainen.
Ok, ist damit erstmal erledigt, die Ursache ist klar, die Lösungsmöglichkeiten muss ich noch überlegen.

Was eine Sache, die ich bereits versucht hatte ist leider nicht möglich:
Den Originalindex löschen. Das halte ich für eine unnötige Einschränkung. Die ganze Automatik mit den Indices für Constraints ist mir insgesamt zu streng. M.E. sollten die Abfragen im Zweifel auch ohne Indizierung laufen (technisch). Wie man sie dann in der Praxis wirklich einsetzt, könnte man dem Anwender überlassen. (So kenne ich es zumindest grob von Oracle).
Das Löschen des Originalindex ist natürlich streng genommen nicht die Lösung, aber es spart redundante Indizierung. Wie sich das dann insgesamt auf die Anwendung / Laufzeitverhalten auswirkt, wäre die nächste Frage.

 

[castorp]

Die ganze Automatik mit den Indices für Constraints ist mir insgesamt zu streng. M.E. sollten die Abfragen im Zweifel auch ohne Indizierung laufen (technisch).

Die Abfragen läuft auch ohne Index, wird halt einfach nur langsam sein. Die Empfehlung FK Spalten zu indizieren ist im Übrigen nicht spezifisch für Postgres. Bei Oracle und SQL Server gibt es die gleichen Empfehlungen.

 

[dabadepdu]

Schon klar, man kann ihn halt nicht löschen. Ich habe noch nicht geschaut, ob man mit expliziten Alter Constraint Statements eigene Angaben mit expliziter Indexangabe machen kann.

 

[akretschmer]

Du kannst innerhalb einer Session mit set enable_* auch Indexscans (und anderes) abschalten, Liste der möglichen Parameter via:

select name, setting from pg_settings where name ~ 'enable';

Außerdem kann man indexe auch als invalid markieren - das verhindert auch dessen Nutzung. Aber vermutlich wirst Du das mit solchen Maßnahmen nicht schneller bekommen.

 

[castorp]

Schon klar, man kann ihn halt nicht löschen. Ich habe noch nicht geschaut, ob man mit expliziten Alter Constraint Statements eigene Angaben mit expliziter Indexangabe machen kann.

Versteh' ich nicht ganz.
PK Indizes können nicht gelöscht werden ohne den PK Constraint zu löschen.
FK Constraints haben keine automatischen Indizes, die kannst Du problemlos löschen.

Wenn Du einen PK auf (a,b) hast, dann gibt es einen Index auf (a,b). Wenn b ein FK auf eine andere Tabelle ist, dann braucht man noch einen Index auf (b) (wegen Performance).

Wenn a auch ein FK auf eine Tabelle ist, muss man dafür keinen extra Index anlegen, da für diese Prüfungen der bestehende Index (mit a als führende Spalte) problemlos verwenden werden kann.

 

[akretschmer]

ein Index auf (a), auch wenn einer auf (a,b) da ist, kann aber auch sinnvoll sein. Nur so als Anmerkung ;-)

 

[castorp]

ein Index auf (a), auch wenn einer auf (a,b) da ist, kann aber auch sinnvoll sein. Nur so als Anmerkung ;-)

Hmm, mir fällt nicht wirklich eine Situation ein, wo es ein Vorteil wäre.

 

[dabadepdu]

Versteh' ich nicht ganz.

Ja, war auch Blödsinn bzw. meine Darstellung entspricht nicht den Tatsachen.

Es gibt keinen PK auf beiden Spalten. Das ist wieder so ein JPA Mist.
Der Unique Constraint gilt nur für eine der beiden Spalten. Entsprechend auch der Index. Logisch ist das in dem Fall okay.

Dass der kombinierte PK Index auf 2 Spalten nicht beim Löschen des Master Records greift, war ein Trugschluss. Er kann nicht greifen, weil es gar keinen kombinierten Index für beide Spalten gibt. In der Remote Session habe ich das nicht bemerkt. Ich muss mal klären, welches Klassenkonstruct dafür sorgt, dass so ein Datenmodell da raus kommt.

ein Index auf (a), auch wenn einer auf (a,b) da ist, kann aber auch sinnvoll sein.

Das war meine Notlösung, weil ich dachte, der Optimizer schnallt das nicht. Es war wie gesagt falsch bzw. viel schlimmer. Wie gesagt, der Unique Index ging nur auf (b) und nicht auf (a,b).