PostgreSQL Abfrage optimieren

[jb_alvarado]

Hallo Allerseits,
ich habe hier eine Datenbankabfrage die mir zu lange dauert:

SELECT mf.*,
    MAX(pbe."start") AS "last_start",
    (ml.id, ml."name") AS "language",
    (mc.id, mc.name) AS "category",
    (case when ms1.id > 0 then (ms1.id, ms1."name") end) AS "series",
    (ms2.id, ms2."path") AS "source",
    (mt.id, mt."name") AS "type",
    array_agg((case when ms3.id > -1 then (ms3.id, ms3."name") end)) AS "speaker"
FROM media_files mf
LEFT JOIN program_block_entries pbe ON pbe.media_id = mf.id
INNER JOIN media_languages ml ON ml.id = mf.lang_id
INNER JOIN media_categories mc ON mc.id = mf.category_id
LEFT JOIN media_series ms1 ON ms1.id = mf.series_id
INNER JOIN media_sources ms2 ON ms2.id = mf.source_id
INNER JOIN media_types mt ON mt.id = mf.type_id
LEFT JOIN media_file_speakers mfs ON mfs.media_file_id = mf.id
LEFT JOIN media_speakers ms3 ON ms3.id = mfs.media_speaker_id
GROUP BY mf.id, mc.id, ml.id, ms1.id, ms2.id, mt.id, pbe.id
ORDER BY mf.name ASC
LIMIT 20 OFFSET 0

Die Abfrage dauert ca. 900ms, Grund dafür ist hauptsächlich diese Zeile: MAX(pbe."start") AS "last_start",. Wenn ich die Abfrage aufteile in zwei Abfragen, die Erste ohne last_start und den zugehörigen JOIN, die Zweite dann nur mit last_start gefiltert nach den `media_id`s dauert das ganze nur noch ca. 40-60ms.

Das Problem ist nur, dass ich die Abfrage dynamisch veränderbar machen muss, Z.B. mit verschiedenen ORDER BY Klauseln. Wenn ich nun nach last_start sortieren möchte, wäre es nötig, dass ich diese Zeile natürlich auch in der Abfrage drin habe.

Fallen euch noch Optimierungsmöglichkeiten ein, wie ich alles in einer Abfrage drin haben kann und die Rückgabe dennoch schneller geht?

 

[kartoffelpü]

Ein passender Index sollte das beschleunigen: https://www.cybertec-postgresql.com/en/speeding-up-min-and-max/

 

[jb_alvarado]

Dank @kartoffelpü, habe mich generell noch nicht lange mit Postgres beschäftigt und gerade erst angefangen in das Thema Indexes mich einzulesen.

Habe jetzt das mal versucht:

CREATE INDEX index_program_block_entries_start ON program_block_entries (start);
CREATE INDEX index_program_block_entries_media_id ON program_block_entries (media_id);
CREATE INDEX index_media_files_id ON media_files (id);
CREATE INDEX index_media_files_name ON media_files (name);
CREATE INDEX index_media_files_entry_nr ON media_files (entry_nr);

Bringt aber nichts. Braucht es hier einen anderen Index Typen?

 

[kartoffelpü]

Konkret kann ich nichts zu sagen, da ich kein DB-Entwickler bin und mich erst recht nicht mit Postgres im Speziellen auskenne.
Bin eher mit MSSQL unterwegs, dort kann man sich bei manuell ausgeführten Queries im SQL Management Studio Empfehlungen zur Indexerzeugung anzeigen lassen. Vielleicht gibt es für Postgres ja auch irgendein Tool, was sowas bietet...

Ach so, im Hinterkopf behalten: Jeder Index benötigt natürlich zusätzlichen Platz. Außerdem sollten sie regelmäßig auf Fragmentierung überwacht und bei Bedarf optimiert/neu erstellt werden.

 

[Raijin]

In welchem Kontext benutzt du diese Abfrage bzw. die Datenbank denn? Ich frage deswegen, weil es nicht immer klug ist, dem Datenbanksystem ein absolut 100% fertiges Ergebnis abzuverlangen. Eine Datenbank ist primär ein Datenspeicher mit strukturiertem Zugriff und begrenzten Skript-Möglichkeiten.

Wenn du beispielsweise die Max-Klausel als Ressourcenfresser identifiziert hast, sie einfach weglässt und stattdessen nur die reinen Daten als Tabelle ausgibst, kannst du die Max-Berechnung auch im Aufrufer abfrühstücken. Ob das schneller geht? Kommt drauf an.
Beispielsweise die komplette Ergebnistabelle in C# als List<media_file> oder so laden und anschließend das Max rausrechnen zB so: mylist.Max(mf => mf.value)

 

[jb_alvarado]

@Raijin, stimmt das hätte ich noch dazuschreiben können.

Einige API Endpunkte, die ich ursprünglich in Django/rest_framework programmiert hatte, schreibe ich in Rust neu. Im Produktivbetrieb läuft später Backend und DB in unterschiedlichen VMs.

Habe noch mal etwas getestet, im Nachhinein MAX errechnen lassen würde kaum was bringen, denn die DB Abfrage ist kaum schneller. Das Hauptproblem ist garnicht mal die MAX Funktion, habe ich jetzt herausgefunden, sondern der JOIN (LEFT JOIN program_block_entries pbe ON pbe.media_id = mf.id).

 

[floq0r]

Das Hauptproblem ist garnicht mal die MAX Funktion, habe ich jetzt herausgefunden

Das hätte mich eigentlich auch gewundert, hast du die Möglichkeit dir die execution plans anzeigen zu lassen? Unter Umständen brauchst du einen composite index.

 

[jb_alvarado]

@floq0r , meinst du das?:

Kann ich mir mit DBeaver ausgeben lassen.

 

[floq0r]

Ja, wobei ich von PostgreSQL im Speziellen keine Ahnung habe. Wenn ein Index für den JOIN genutztvwerden würde, wo wäre das vermerkt?

 

[jb_alvarado]

Ich blick das irgendwie nicht... Habe noch mal die INDEXES neu erstellt (die primary keys haben schon welche):

CREATE INDEX index_program_block_entries_block_id ON program_block_entries (block_id);
CREATE INDEX index_program_block_entries_start ON program_block_entries (start);
CREATE INDEX index_program_block_entries_media_id ON program_block_entries (media_id);
CREATE INDEX index_program_blocks_program_id ON program_blocks (program_id);
CREATE INDEX index_programs_start ON programs (start);
CREATE INDEX index_media_files_name ON media_files (name);
CREATE INDEX index_media_files_lang_id ON media_files (lang_id);
CREATE INDEX index_media_files_category_id ON media_files (category_id);
CREATE INDEX index_media_files_series_id ON media_files (series_id);
CREATE INDEX index_media_files_source_id ON media_files (source_id);
CREATE INDEX index_media_files_type_id ON media_files (type_id);
CREATE INDEX index_media_file_speakers_media_file_id ON media_file_speakers (media_file_id);
CREATE INDEX index_media_file_speakers_media_speaker_id ON media_file_speakers (media_speaker_id);

Wenn ich nun meine Abfrage z.B. mit:

where pbe.id  < 5000

filtere wird im index gescannt.

Das Ergebnis braucht hierbei sage und schreibe um die 20ms.

Ohne WHERE wird kein einziger Index angesprochen und wenn ich mit:

WHERE pbe."start"  < NOW()

filtern würde, wird der Index auch ignoriert und es dauert genauso lange.

Ergänzung (1. Dezember 2022)

Ich denke ich habe es. Es werden zwar immer noch nur sequenzielle Scans gemacht, aber mit dieser Abfrage, komme ich auf ~65ms.

SELECT mf.*,
    last_start,
    (ml.id, ml."name") AS "language",
    (mc.id, mc.name) AS "media_category",
    (case when ms1.id NOTNULL then (ms1.id, ms1."name") end) AS "media_series",
    (ms2.id, ms2."path") AS "media_source",
    (mt.id, mt."name") AS "media_type",
    array_agg((case when ms3.id NOTNULL then (ms3.id, ms3."name") end)) AS "media_speaker"
FROM media_files mf
LEFT JOIN (select media_id, MAX(start) AS "last_start" from program_block_entries group by media_id) pbe on pbe.media_id = mf.id
INNER JOIN media_languages ml ON ml.id = mf.lang_id
INNER JOIN media_categories mc ON mc.id = mf.category_id
LEFT JOIN media_series ms1 ON ms1.id = mf.series_id
INNER JOIN media_sources ms2 ON ms2.id = mf.source_id
INNER JOIN media_types mt ON mt.id = mf.type_id
LEFT JOIN media_file_speakers mfs ON mfs.media_file_id = mf.id
LEFT JOIN media_speakers ms3 ON ms3.id = mfs.media_speaker_id
GROUP BY mf.id, mc.id, ml.id, ms1.id, ms2.id, mt.id, pbe.last_start
order by mf."name"
LIMIT 100 OFFSET 0

Wenn ich das hier richtig verstehe, würde wohl ein Index-Scan auch nur Sinn machen, wenn gefiltert wird und dabei nur ein kleiner Teil zurückgegeben wird.

 

[floq0r]

Ohne where clause kommen alle Daten zurück, da macht ein index scan (in MSSQL heißt es index seek und ein full read ist ein index oder table scan, war zunächst verwirrt) keinen Sinn. Die lange exec time entsteht dann durch die Datenmenge selbst und derrn Rückgabe.

 

[Iqra]

1. Sternchen weg => spalten ausschreiben.
2. Execution plan und sonst gar nichts.
3. Indices helfen zwar beim Abfragen, machen aber Einfüge-Ops lahm. "So viel wie nötig, so wenig wie möglich." Einfach Indices spammen ist kontraproduktiv.

Man kann pgsql dazu überreden, für eine Abfrage einen Index zu verwenden. Details siehe Doku bei denen. Aber, ja, ohne WHERE ist ein Index nur Overhead. Es wird ja nicht gesucht.

Eine mögliche Option, aber eine die gut überlegt werden will, ist ein full cover Index, wo alle benötigten Spalten bereits im Index stehen.

Ich kenne jetzt auch die genaue Struktur der Tabellen und deren Beziehungen zueinander nicht, aber EVENTUELL!!! kann es helfen die Joins zu überarbeiten. Je mehr verarbeitet werden muß, desto schlecht. Also den join so strukturieren, daß nicht (viel) mehr Daten als tatsächlich benötigt angefaßt werden müssen.

Aber das ist stark situationsbedingt. Da kann man pauschal nicht viel Ratschlag geben.

900ms halte ich jetzt auch nicht für ganz doll schlecht, wobei da natürlich die Frage nach dem Gesamtvolumen sich stellt. 20 Mio Datensätze und 900ms, naja, okay. 20 Datensätze und 900ms, naja, nicht ganz so.

PS. Den Teil der "statisch" ist in einen View stecken. Dann kann pgsql noch weiter helfen. Dynamische Teile dann in die Abfragen gegen den View.

 

[floq0r]

@Iqra In Bezug auf Query hints: Wenn man glaubt es besser zu wissen als der Query Analyzer hat man meistens unrecht, soweit ich informiert bin
Wegen Views: Sind die in PG immer materialized?

 

[jb_alvarado]

Danke @Iqra für die guten Tipps!

3. Indices helfen zwar beim Abfragen, machen aber Einfüge-Ops lahm. "So viel wie nötig, so wenig wie möglich." Einfach Indices spammen ist kontraproduktiv.

Also hilft der Index für JOINS nicht wirklich? Würde sie dann nur erstellen, wenn ich über eine Spalte eine Suche laufen lasse.

Eine mögliche Option, aber eine die gut überlegt werden will, ist ein full cover Index, wo alle benötigten Spalten bereits im Index stehen.

Macht in meinem Fall nicht viel Sinn, so wie ich das einschätzte. Das Backend wird von einer Webseite genutzt, wo eh ein Nginx Cache davor sitzt und von einer webbasierten Management Software, wo ich hauptsächlich die Schreiboperationen beschleunigen will. Optimierungen wie die Obige sind natürlich auch schön und ich bin begeistert, was man noch rausholen kann, im Vergleich zu einem Django Rest Framework.

Ich kenne jetzt auch die genaue Struktur der Tabellen und deren Beziehungen zueinander nicht, aber EVENTUELL!!! kann es helfen die Joins zu überarbeiten. Je mehr verarbeitet werden muß, desto schlecht. Also den join so strukturieren, daß nicht (viel) mehr Daten als tatsächlich benötigt angefaßt werden müssen.

Das erklärt warum LEFT JOIN (select media_id, MAX(start) AS "last_start" from program_block_entries group by media_id) pbe on pbe.media_id = mf.id schneller ist, als zuerst joinen und im Nachhinein MAX() anwenden.

900ms halte ich jetzt auch nicht für ganz doll schlecht, wobei da natürlich die Frage nach dem Gesamtvolumen sich stellt. 20 Mio Datensätze und 900ms, naja, okay. 20 Datensätze und 900ms, naja, nicht ganz so.

Bin ja jetzt schon auf ~65ms und damit bin ich sehr zufrieden! Mit Django hatte ich 1.6 Sekunden. So riesig ist die DB aber auch noch nicht, die größte Tabelle hat knapp 200000 Zeilen, und täglich kommen 120-180 dazu.

PS. Den Teil der "statisch" ist in einen View stecken. Dann kann pgsql noch weiter helfen. Dynamische Teile dann in die Abfragen gegen den View.

Das werde ich mir als nächstes anschauen. Werden die Views denn mit exportiert, wenn ich einen Dump mache?

 

[Iqra]

Wegen Views: Sind die in PG immer materialized?

Nicht soweit ich weiß. Das geht aber dennoch mit in die Optimierung Ein. Müßte aber bei pgsql auch irgendwo dokumentiert sein, da habe ich auch meinen Ansatz her, lieber einen nicht ganz so gut aussehenden view rauszuziehen und insgesamt ein bisschen mehr overhead zu haben für dennoch simplere query plans.
Andererseits nützt mehr Optimierungsarbeit natürlich nur bei ausreichend viel Last. Wenn da fünf Mann 3x am Tag zugreifen, egal.

Ansonsten klar, ich mein es GIBT Situationen wo es sinnvoll sein kann den Index tatsächlich anzugeben. Meistens ist das nicht der Fall. Aber man sieht dafür was. ZB das es nun langsamer geht. Fürs debugging kann man da auch so mal machen, für die Produktion sollte es dann ohne gehen.

Das erklärt[..]

Auf einen Blick sieht das eher wie eine korrelierte Abfrage aus. Die gilts zu vermeiden, denn die sind lahm und können auch von keinem query plan optimiert werden. Hatte nix derartiges gesehen und daher nix gesagt, aber allererste Optimierungs Aufgabe ist, Korrelationen Rauswerfen. Auch wenn es dadurch ggfs komplexer wird.

Views sind einfach Definitionen, die tauchen als create view x as select … im dump auf.


Der Vollständigkeit halber: regelmäßige Index Pflege nicht vergessen. Insbesondere da wo eingefügt wird. Und für umfangreiche Imports diese erst deaktivieren und danach rebuild drüber laufen lassen.

 

[jb_alvarado]

Auf einen Blick sieht das eher wie eine korrelierte Abfrage aus.

Ok, aber wie kann ich die durch eine performante Abfrage ersetzten, ohne Korrelation?

 

[KingLz]

Also hilft der Index für JOINS nicht wirklich? Würde sie dann nur erstellen, wenn ich über eine Spalte eine Suche laufen lasse.

Prinzipiell gibt es immer einen Index für den Primärschlüssel. Da die meisten Joins über den Primärschlüssel laufen ist ein extra Index für diesen Fall gar nicht nötig. Wenn der Join nicht über den Primärschlüssel des Eintrags läuft, so solltest du einen Index erstellen. Vereinzelte Joins würdest du damit auf jeden Fall beschleunigen, bei großen Joins (z.B. fast die ganze Tabelle) kann es auch sein dass der Index überflüssig wird, wenn sowieso über die ganze Tabelle iteriert wird.

Indizes brauchst du im Prinzip immer wenn du etwas im Datensatz suchen willst und dein DBMS (Datenbankmanagementsystem aka Postgres) nicht alle Einträge durchgehen soll. Anstatt z.B. 100k Einträge durchzugehen brauchst du bei einem B-Tree Index nur Log(100k) Vergleiche.
Wie vorher schon erwähnt machen Indizes das Einfügen etwas langsamer und brauchen extra Speicherplatz. (weil jeder neue Eintrag zusätlich in den jeweiligen Index eingefügt und sortiert werden muss) Wenn dein Datensatz aber z.B. Milliarden von Einträgen hat, dann ist dieser ohne Index auf bestimmte Spalten überhaupt nicht mehr in vernünftiger Zeit auswertbar.

Postgres bietet dir für Indizes übrigens verschiedene Möglichkeiten. (B-tree, Hash, GiST and GIN)
Außerdem kannst du Indizes auch über mehrere Spalten erstellen.

Ich würde immer empfehlen einen Index niemals präventiv zu erstellen, sondern immer erst wenn er wirklich nötig ist. Und selbst nach dem Erstellen sollte man immer in Executive Plan schauen ob er überhaupt verwendet wird. DBMS sind im allgemeinen ziemlich schlau gebaut und wenn der Executive Plan einen anderen Weg geht als man vorhersieht, so ist man oft selbst der Dumme, weil man etwas anderes nicht bedacht hat.

Der richtige Umgang mit Indizes ist am Ende einer der wichtigsten Punkte für die Performance. Besonders fehlende Indexe können einen zur Verzweiflung bringen, warum die Auswertung anstatt 1s 30Min dauert.

 

[floq0r]

Verzweiflung

Die Verzweiflung kommt dann, wenn der query analyzer einen Index auf einmal nicht mehr verwendet weil in der where clause noch eine restriction dazukommt obwohl es keinen erkennbaren Sinn dahinter gibt, ihn dann doch wieder verwendet wenn man ein LEFT JOIN zu einem OUTER APPLY umbaut, allerdings nur so lange bis sich die where clause wieder ändert. Und wehe die statistics sind outdated...