Vielen Dank für die vielen Antworten! Hier meine:
Hast du mal die wesentlichen Informationen bzgl. der Speichernutzung geprüft, also shared_buffers, work_mem, effective_cache_size usw.?
Wurde die Konfiguration dieser Datenbank überhaupt schon mal an die Arbeitssituation angepasst?
Ich bin nicht der Administrator der Maschine. Ich habe meine eigene Instanz und habe da drin alle Rechte, aber ich habe die nicht aufgesetzt. Insofern werde ich mich nochmal mit dem Administrator in Verbindung setzen. Der ist sehr hilfsbereit (siehe autovacuum unten) und hat mir schon mehr Ressourcen gegeben. Außerdem hat er mir die maximalen gleichzeitigen Verbindungen auf 800 hochgesetzt, so dass ich jetzt auch mit mehr als 200 Rechnern gleichzeitig die Daten verarbeiten kann. Die Maschine wurde aber aufgesetzt, ohne dass ich der Administration genau gesagt hatte, wie ich die Instanz nutzen will.
Die genannten Variablen habe ich jetzt mal über “select * from pg_settings” abgefragt:
shared_buffers: 1048576 unit: 8kB
work_mem: 65536 unit: kB
effective_cache_size: 16384 unit: 8kB
Info zur Maschine noch: Die ist halbwegs dick (ich glaube 32 Gig RAM und 8 Kerne), aber meine Instanz ist nicht die einzige da drauf.
Per Default springt VACUUM erst bei 20% Änderungen an. Bei großen Tabellen ist das zu wenig und sollte manuell angepasst werden. Außerdem wird Autovacuum per Default ausgebremst, was bei großen Tabellen wiederum für lange Laufzeiten sorgt.
Auf meine Frage, warum mich der Autovacuum so ausbremst, hat der Administrator den autovacuum-Dienst erst mal abgeschaltet (so dass er nur noch im Notfall läuft), da die Datenbank, wenn einmal alle Daten verarbeitet sind, nur noch zum Lesen verwendet wird. D.h. er meinte dass es reicht, wenn ich den vacuum mache, wenn wirklich der wraparound bevorsteht. (Vorerst bin ich der einzige Benutzer der Datenbank und ich würde natürlich vor dem Produktivbetrieb darin nochmal gründlich aufräumen (vaccum, analyze, clustered index, …).)
So wie ich Dich jetzt aber verstehe, wäre die gegenteilige Lösung besser. Was für Werte würdest Du denn vorschlagen?
Hier stimmt was nicht:
Das ist aus Deinem ersten Posting. Dieser Scan liefert, mit der Bedingung status=1, 1086709 Zeilen. Nun schreibst Du aber:
Ja, das passt natürlich nicht ganz zusammen, weil zwischen dem ersten Posting und dem späteren eine Runde Aufräumen lag; daher stand im ersten Posting auch
zwischen 1000 und 1 Mio status = 1
Die Aufräumarbeit hat nur leider am Query Plan auch nix geändert.
Hier die aktuelle Version. Die Zahlen, die ich immer noch nicht ganz verstehe, sind aber trotzdem komisch:
QUERY PLAN
Aggregate (cost=20487.58..20487.59 rows=1 width=4) (actual time=2585.565..2585.566 rows=1 loops=1)
-> Bitmap Heap Scan on arbeitsplan (cost=20139.64..20487.36 rows=87 width=4) (actual time=2584.293..2585.401 rows=655 loops=1)
Recheck Cond: ((jobid = 2665390) AND (status = 1))
-> BitmapAnd (cost=20139.64..20139.64 rows=87 width=0) (actual time=2581.127..2581.127 rows=0 loops=1)
-> Bitmap Index Scan on jobidindex (cost=0.00..1351.52 rows=43273 width=0) (actual time=3.210..3.210 rows=7345 loops=1)
Index Cond: (jobid = 2665390)
-> Bitmap Index Scan on statusindex (cost=0.00..18787.82 rows=641874 width=0) (actual time=2577.438..2577.438 rows=28000 loops=1)
Index Cond: (status = 1)
Total runtime: 2585.783 ms
Wenn ich das jetzt richtig verstanden habe, hast Du aus dem Feld, wo jetzt rows=28000 steht, geschlossen, dass 28000 den Status 1 haben. Das stimmt aber nicht, denn das Ergebnis von “select count(id) from arbeitsplan where status = 1” ist im Moment 655. (D.h. es gibt keine anderen als die von Job 2665390, den ich nach dem Aufräumen zum Test angeworfen habe.) Deutet das vielleicht darauf hin, dass mit meinem Index etwas nicht stimmt?
beide Explains zeigen die Nutzung der Indexe. Nur unterschiedlich, je nachdem, wie selektiv die Indexe sind.
Aber der explain bei status = 2 nutzt ja schlauerweise den jobidindex, der ziemlich selektiv ist und filtert danach nur noch auf status. Diese Option stünde ja bei status = 1 auch zur Verfügung.
Ich habe jetzt jedenfalls verstanden, dass der statusindex nix bringt, weil er eine zu niedrige Kardinalität hat. Was ich nicht verstehe ist, warum der Query Planer im Fall von status = 1 und status = 2 unterschiedliche Pläne wählt und sich für den schlechteren Plan bei status = 1 entscheidet. Das würde ich aber natürlich gerne verstehen, damit ich nicht wieder auf so einen Unsinn reinfalle.
Bevor ich den Index jetzt wegwerfe: Wie viel langsamer wird ein “select count(id) from arbeitsplan where status = 2” bzw “where status = 0”?
Und noch zu meiner Frage, was ein Bitmap Heap Scan ist:
(Andere Tabelle!)
QUERY PLAN
Bitmap Heap Scan on words (cost=6.09..337.06 rows=84 width=288)
Recheck Cond: (((wordform)::text = 'the'::text) AND ((pos)::text = 'DT'::text))
-> Bitmap Index Scan on wordform_pos (cost=0.00..6.07 rows=84 width=0)
Index Cond: (((wordform)::text = 'the'::text) AND ((pos)::text = 'DT'::text))
Warum passiert da kein Index Scan? In diesem Fall gibt es einen unique Index auf wordform und pos gemeinsam. Wie genau nutzt ein Bitmap Heap Scan den Index?
Nochmal ganz herzlichen Dank für die Anregungen! Ich glaube, ich komme der Sache langsam näher.