WITH-Klausel: Performance Auswirkungen

Die nicht-rekursive With-Klausel, Views, und Unterabfragen in der from-Klausel („derived tables“) bieten sehr ähnliche Funktionalität. Der größte Unterschied ist die Sichtbarkeit. Daher legt das Prinzip der geringsten Überraschung nahe, das sich eine Änderung der Form – z. B. von einer Unterabfrage in eine With-Abfrage – keine Nebenwirkungen auf die Performance haben sollte.

Die folgenden Abschnitte skizzieren die wichtigsten Optimierungen, die mit der With-Klausel zusammenspielen. Abschließend zeigt eine Übersicht, welche Datenbanken diese Optimierungen auf With-Klauseln, Views, und Unterabfragen anwenden.

Predicate Pushdown – Filter runterreichen

Predicate Pushdown ist eine Optimierung, um Filterbedingungen (Prädikate) so früh wie möglich anzuwenden. Im Idealfall werden nicht benötigte Zeilen erst garnicht geladen.

Das folgende Beispiel zeigt Predicate Pushdown bei einer Unterabfrage in der from-Klausel:

SELECT c1, c2, ...
  FROM (SELECT c1, c2, ...
          FROM base_table
       ) derived_table
 WHERE c1 = ?

Wenn die Unterabfrage isoliert ausgeführt wird, liefert sie alle Zeilen der Tabelle base_table. Selbst wenn es einen Index auf c1 gibt, kann er nicht benutzt werden, weil der Filter auf c1 für die Unterabfrage so nicht sichtbar ist.

Durch Predicate Pushdown wird der Filter and die Unterabfrage runtergereicht, sodass die Abfrage de-facto folgendermaßen aussieht:

SELECT c1, c2, ...
  FROM (SELECT c1, c2, ...
          FROM base_table
         WHERE c1 = ?
       ) derived_table

Bei diesem Beispiel kann man natürlich die Meinung vertreten, dass die where-Klausel ohnehin in die Unterabfrage gehört. Dieses Argument kann jedoch nicht geltend gemacht werden, wenn anstatt einer Unterabfrage auf einen View zugegriffen wird.

Predicate pushdown unterliegt einigen logischen Einschränkungen. So ist es zum Beispiel nicht möglich, Join-Prädikate weiter als bis zum ersten Join den sie betreffen runterzureichen. Andererseits können Prädikate auch durch group by und Window-Funktionen gereicht werden, wenn sie nur den Gruppen-, oder Partitionierungsschlüssel betreffen.

Predicate pushdown ist besonders wichtig, um bessere Indizierung zu ermöglichen.

Projection Pushdown – Spalten runterreichen

Projection Pushdown – das runterreichen von Spalten – verwirft ungenuzte Spalten so früh wie möglich. Im Idealfall werden sie erst garnicht geladen.

Das folgende Beispiel zeigt Projection Pushdown beim Zugriff auf einen View:

CREATE VIEW view_name AS
SELECT *
  FROM base_table
 WHERE deleted = 0

SELECT count(*)
  FROM view_name

Beachte, dass count(*) keine Tabellenspalten benötigt.0 Wenn die Datenbank die Information, welche Spalten benötigt werden, runterreicht, kann sie es vermeiden nicht benötigte Spalten zu laden.

Im oberen Beispiel ist das Projection Pushdown besonders wichtig, wenn es einen Index auf der Spalte deleted gibt. In diesem Fall kann die Abfrage alleine durch einen Zugriff auf den Index ausgeführt werden (der sogenannte Index-Only-Scan). Der Zugriff auf die Tabelle selbst kann entfallen. Dadurch kann die Abfrage um Faktoren schneller werden. Mehr über den Index-Only-Scan gibt es auf Use The Index, Luke!

Sortierungen eliminieren

Durch die Eliminierung von Sortierungen werden redundante Sortierungen vermieden. Redudante Soriterungen können zum Beispiel durch mehrere order by-Klauseln entstehen:

WITH cte
  AS (SELECT id
        FROM feature_with
       ORDER BY id
     )
SELECT id
  FROM cte
 ORDER BY id

Natürlich ist es unnötig, das Ergebnis zweimal zu sortieren. Selbst wenn die beiden order by-Klauseln unterschiedlich wären, legt nur die äußerste order by-Klausel die Reihenfolge im Endergebnis fest. Abgesehen von einigen Ausnahmen1 können alle ausser der äußersten order by-Klausel eliminiert werden.

Redundante Sortierungen sind weniger offensichtlich, wenn sie durch die Kombination von order by, group by (manchmal Sortier-basiert), over (partition by … order by…) und Join-Operationen (Sort-Merge) enstehen. Dazu kommt, dass Indizes die Zeilen in einer nützlichen Reihenfolge liefern könnten und eine Sortierung hinfällig wird.

Im folgenden Beispiel könnte eine Datenbank die Zeilen entsprechend der partition by und order by-Klauseln sortieren – also nach (c1, c2) –, um die Window-Funktion row_number() aufzulösen.2 In diesem Fall liefert die Abfrage in der With-Klausel die Zeilen bereits entsprechend der äußeren order by-Klausel. Die äußere Sortierung kann also eliminiert werden.

WITH cte
  AS (SELECT c1
           , c2
           , ROW_NUMBER()
             OVER(PARTITION BY c1 ORDER BY c2) rn
        FROM table_name
     )
SELECT c1, rn
  FROM cte
 ORDER BY c1, c2

Beachte, dass die äußere order by-Klausel dennoch notwenig ist. Die over-Klausel legt lediglich die Reihenfolge zur Berechnung der Window-Funktion fest. Wenn die Datenbank die Window-Funktion anders Berechnet – z. B. die Partitionen auf paralelle Prozesse aufteilt – kann die äußere Sortierung nicht eliminiert werden. Lässt man die äußere order by-Klausel weg, kommt das Ergebnis in diesem Fall in undefinierter Reihefolge.

Produktvergleich

Alle getesteten Datenbanken optimieren With-Klauseln so gut (oder schlecht), wie sie Views und Unterabfragen optimieren.

PostgreSQL hat sich vor Version 12 anders verhalten und die With-Abfragen getrennt von der Hauptabfrage optimiert.

Vor Version 5.7 hat MySQL Unterabfragen nicht so gut optimiert, wie Views.3

1. Wenn man in der Unterabfrage top verwendet
2. Wenn man eine Unterabfrage im View verwendet, damit man dort order by verwenden kann