Cloud-First-Strategien sind inzwischen die Regel und nicht mehr die Ausnahme und Workloads verlagern sich damit in die Cloud – auch Datenbanken. Dort geht es ... (mehr)

Common Table Expressions per Inlining

Common Table Expressions (CTEs), besser bekannt als WITH-Anweisung, kann der Optimizer in PostgreSQL 12 nun per Inlining optimieren. Das beeinflusst die Ausführungsgeschwindigkeit von entsprechenden Abfragen unter Umständen erheblich. Allerdings hat dies auch Auswirkungen auf Benutzer, die CTEs als sogenannte Optimizer Fence benutzt haben, um den Optimizer gerade von solchen Optimierungen abzuhalten. Daher besteht die Möglichkeit, mit der neuen Option "[NOT] MATERIALIZED" das Inlining zu verhindern oder zu erzwingen.

"CTE Inlining" ist das Standardverhalten. Der Optimizer plant und materialisiert den mit WITH angegebenen Ausdruck nicht mehr separat, sondern versucht, diesen in die eigentliche Abfrage zu integrieren und damit als deren Bestandteil zu planen. Bild 1 zeigt beispielhaft einen entsprechend optimierten Plan in PostgreSQL 12. Zum Einsatz kommen die zwei Tabellen "lookup" und "times". Die Schlüssel für die Zeitstempel in der Tabelle "times" werden entsprechend gegen die Tabelle "lookup" evaluiert. In diesem Fall benutzt die Abfrage eine CTE. Der Optimizer erstellt in PostgreSQL 12 nun einen optimierten Plan, der den CTEAusdruck als Hash-Join zwischen den beiden Tabellen ausführt.

Bild 2 zeigt dieselbe Abfrage mit dem bis PostgreSQL 11 üblichen Verhalten, erzwungen mit MATERIALIZED. An den Gesamtkosten des Planes (35882.39) im Vergleich zu dem optimierten Inlining der CTE (9964.42) lässt sich schon vermuten, dass ersterer trotz parallelisiertem Sequential Scan der Tabelle "times" effizienter ist. Das Inlining von CTEs funktioniert nur, wenn entsprechende Ausdrücke exakt einmal innerhalb einer Abfrage referenziert werden. Ferner dürfen CTE-Ausdrücke, um sich optimieren zu lassen, keine Seiteneffekte enthalten wie beispielsweise "INSERT RETURNING" oder Ähnliche.

Bild 2: Konservativer Ausführungsplan einer CTE ohne Inlining.

Plancaching anpassbar

Mit PostgreSQL 12 kann der Administrator auch das Verhalten vom Plancache der Datenbank beeinflussen. Die Mehrzahl an Clients verwenden heutzutage "Prepared Statements". PostgreSQL benutzt für Prepared Statements einen Session- lokalen Cache, der nur für die spezifische Datenbankverbindung gilt. Beim Vorbereiten (PREPARE) eines Prepared Statements werden die Abfragen geparsed und analysiert sowie an den Rewriter für weitere Anpassungen übergeben. Beim Ausführen der Abfrage (EXECUTE) entsteht die ersten fünf Mal ein sogenannter "Custom Plan". Beim fünften Mal erzeugt PostgreSQL einen "Generic Plan". Der Unterschied zwischen den beiden ist, dass der Custom Plan auf Abfrageparameter angepasst wird (beispielsweise WHERE-Bedingungen), während der Generic Plan allgemeingültig behandelt wird. Sind die Ausführungskosten des Generic Plan deutlich günstiger, bevorzugt PostgreSQL diesen für jede weitere Ausführung, andernfalls den Custom Plan. Die Standardeinstellung "auto" des Parameters "plan_cache_mode" behält dieses Verhalten bei.

Möchte der Anwender auf jeden Fall bevorzugt auf den "Custom Plan" Modus zurückgreifen, ermöglicht dies die Einstellung "plan_cache_mode=force_custom_plan". Diese bewirkt folglich die Bevorzugung von generischen Abfrageplänen. Der Parameter lässt sich jederzeit während einer Datenbanksitzung anpassen.

Ähnliche Artikel

comments powered by Disqus
Einmal pro Woche aktuelle News, kostenlose Artikel und nützliche ADMIN-Tipps.
Ich habe die Datenschutzerklärung gelesen und bin einverstanden.

Konfigurationsmanagement

Ich konfiguriere meine Server

  • von Hand
  • mit eigenen Skripts
  • mit Puppet
  • mit Ansible
  • mit Saltstack
  • mit Chef
  • mit CFengine
  • mit dem Nix-System
  • mit Containern
  • mit anderer Konfigurationsmanagement-Software

Ausgabe /2023