Mag vielleicht trivial sein, aber ich bin gestern wieder über einen relativ häufigen Optimierungsfall gestolpert, der zumindest in der Entwicklungsphase nicht immer so einfach zu Tage tritt:

Das Query läuft ca. 1,3 Sekunden – nicht gut. Was passiert hier?

Hier ist der Ausführungsplan:

Die meiste Zeit wird im Index Scan Backward auf idx05_article der article-Tabelle verbracht (Zeile 3) – das ist der Index auf das published-Feld. Seltsamerweise ist das gleiche Query mit einem anderen Entitätstyp pfeilschnell:

Ausführungsplan:

Der Sinn des JOINs auf die jeweilige Entitätstabelle ist im Beispiel nicht offensichtlich, da hier ja nur Felder aus der article-Tabelle genutzt werden – es kann jedoch Fälle geben, in denen auch Daten aus der Entitätstabelle benötigt werden, insofern brauchen wir den JOIN hier.

Das Problem wird aber offensichtlich, wenn man Zeile 6 im ersten mit Zeile 5 im zweiten Ausführungsplan vergleicht – im ersten Fall werden nach dem Index-Scan 31.541 Tupel durch den Filter verworfen, im zweiten gerade mal 10.

Die Ursache findet man, wenn man die Datenlage kennt: Über viele Produktarten wird auf Videogameszone.de regelmäßig berichtet, so auch über PC-Spiele. Dementsprechend muss die Datenbank beim zweiten Query auch nicht lange über die Daten rennen, bis sie die gewünschten vier Datensätze zusammen hat. 10.167 von 72.210 Artikeln haben ein PC-Spiel als-Hauptthema.

Das erste Query ist aber für einen eher exotischen Fall – es gibt praktisch keine Artikel zu DVDs auf Videogameszone, exakt drei an der Zahl (in einer Gesamtmenge von wie gesagt 72.210). Das führt hier dazu, dass die Datenbank praktisch den gesamten Published-Index auf Treffer überprüfen muss – und am Ende feststellt, dass nicht einmal die gewünschten vier Zeilen zusammengekommen sind. Die Kardinalität für den DVD-Entitätstyp ist also sehr niedrig, was die Ausführungszeit der Abfrage in diesem Fall explodieren lässt.

Beim Entwickeln ist das offensichtlich nicht aufgefallen, da ausschließlich Spiele-Entitäten überprüft wurden. Nun enthält aber die article-Tabelle neben der entity_id der Haupt-Entität auch deren entity_type_id – und für die gibt es auch einen Index. Also fügen wir die Bedingung für die entity_type_id hinzu:

Der Query-Planer weiß aus den Tabellen-Statistiken, dass die Selektivität für die Entity-Type-ID 183 sehr hoch ist, daher wählt er einen Ausführungsplan, der den passenden Index auf dieses Feld nutzen kann:

Wir sind von 1322,966ms runter auf 0,168ms, das entspricht einem Faktor von siebentausendachthundertfünfundsiebzig (7.875). Ziel erreicht, würde ich sagen!

Das zweite Query (PC-Spiel) wird dadurch allerdings nicht schneller – der Query-Planer bevorzugt für die PC-Spiele-Entität den bisherigen Ausführungsplan; er kennt offensichtlich die Kardinalität des Wertes der entity_type_id für PC Game (viele, viele) und damit die schlechte Selektivität des entity_type_id-Index für diesen Fall und bevorzugt daher den alternativen Plan, der eine höhere Selektivität bringen könnte:

Das Hinzufügen der WHERE-Bedingung auf die entity_type_id schadet hier aber auch nicht. Somit haben wir durch eine sehr kleine Änderung eine konsistent massiv verbesserte Ausführungsgeschwindigkeit bekommen.

P.S.: Schöner wäre der JOIN natürlich mit echtem JOIN …

… dem Query-Planer ist das aber egal, der Ausführungsplan ist für beide Varianten der gleiche.

Kommentar verfassen