Statistiken, Statistiken, Statistiken!

Wir hatten eine moderat komplexe Query, die ca. 270000 Zeilen umfasste, allerdings über eine Stunde lief. Nachdem Statistiken zu der darunterliegenden Tabelle aktualisiert wurden, lief die Query in nur 4 Sekunden durch.

Die Query sah etwa wie folgt aus:

WITH input_rows as ( 
 SELECT [270000 rows] FROM source WHERE [filter]
), subset_of_input_rows as (
 SELECT [13000 rows] FROM input_rows
), join_both_ctes as (
SELECT [270000 input_rows]
LEFT JOIN [13000 subset_of_input_rows]
)

Wennn wir die genaue Anwendung außen vorlassen und nur den Aufruf betrachten, können zwei Schlüsselkomponenten erkannt werden:

• eine initiale Teilmenge wird aus der Quelltabelle entnommen, mit der fortan weitergearbeitet wird
• ein JOIN Operator wird auf der Teilmenge durchgeführt

Dies ist natürlich keine allzu komplexe Query, die die Ausführungszeit von einer Stunde nicht rechtfertigen kann.

Wenn wir in den Ausführungsplan reinschauen, konnten wir zwei Probleme erkennen:

1. Der Query Optimizer geht davon aus, dass die Teilmenge nur ein ERgebnis zurückliefert
2. Darauf aufbauend, optimiert er die Abfrage, indem er ein NESTED LOOP JOIN durchführt

nested loop join: The right relation is scanned once for every row found in the left relation. This strategy is easy to implement but can be very time consuming (postgres doc)

Weil der Optimizer von einer falschen Grundmenge ausgeht und die Query mit einem NESTED LOOP JOIN optimiert, wird jede Zeile aus der kleineren Tabelle einmal mit jeder Zeile aus der größeren Tabelle abgeglichen. Dies bedeutet also, dass er 13000 Zeilen der subset_of_input_rows x 270000 Zeilen der input_rows vergleicht, was zu einer enormen Arbeitsbelastung führt.

“Most queries retrieve only a fraction of the rows in a table, due to WHERE clauses that restrict the rows to be examined. The planner thus needs to make an estimate of the selectivity of WHERE clauses, that is, the fraction of rows that match each condition in the WHERE clause. The information used for this task is stored in the pg_statistic system catalog” (postgres doc)

Um dieses Problem zu lösen, muss es dem Optimizer gelingen, die Teilmenge besser abschätzen zu können. Dies konnte mit ANALYZE (postgres doc) bewerkstelligt werden, welche die dahinterliegenden Statistiken der Tabelle aktualisiert. Dadurch kann der Optimizer nun von realitätsnahen Ergebnismengen ausgehen, entscheidet sich entsprechend für die effektivere Join Strategie und die Ausführungszeit konnte um ein vielfaches reduziert werden.