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. Mit der großen Ausnahme von PostgreSQL folgen die gängigen Datenbanken diesem Prinzip.

Die folgenden Abschnitte skizzieren die wichtigsten Optimierungen, die mit der with-Klausel zusammenspielen. Abschliessend 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 deteted 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!

Hinweis in eigener Sache

Wenn Dir dieser Artikel gefällt, könnte mein Buch SQL Performance Explained oder mein Training auch etwas für Dich sein.

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

Außer PostgreSQL optimieren alle getesteten Datenbanken with-Klauseln so gut (oder schlecht), wie sie Views und Unterabfragen optimieren.

Die PostgreSQL-Datenbank optimiert die einzelnen with-Abfragen und die Hauptabfrage unabhängig voneinander. Dadurch wird die with-Klausel zu einer Optimierungsgrenze (Stand 9.63): Metadataen, wie die Reihenfolge, in der eine with-Abfrage die Zeilen liefert, steht wärend der Optimierung der äußeren Abfrage nicht zur Verfügung.

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

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Sein Buch bei Amazon kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

Fußnoten

0

SQL:2016, Part 2, §10.9, General Rule 5: Das Ergebnis von count(*) ist als die Zeilenzahl definiert – unabhänig etwaiger null-Werte.

1

Z. B. wenn es eine fetch first-Klausel gibt; wenn das order by in einer over oder within group-Klausel steht und der gleichen.

2

Die gängige Art Window-Funktionen auszuführen ist, zuerst nach dem kombinierten Schlüssel aus partition by und order by Ausdrücken zu sortieren. Die Auflösung der Window-Funktion findet dann auf Basis der sortierten Daten statt.

3

Siehe “Allow the CTE (Common Table Expression) optimization fence to be optionally disabled” in der PostgreSQL-Todo-Liste.

„modern SQL“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz