Optimierung skalarer Subqueries für Oracle und SQL Server

Nenad Noveljic hat zuletzt in zwei Artikel das Verhalten von skalaren Subqueries im SQL Server und in Oracle untersucht und dabei darauf hingewisen, dass dies einer der (nicht allzu häufigen) Fälle ist, in denen der Optimizer des SQL Servers eine bessere Lösung bietet als Oracles Optimizer. Grundsätzlich bereiten skalare Subqueries Schwierigkeiten, können aber in vielen Fällen in einen Join umgewandelt werden. Der SQL Server schafft das intern - also ohne eine explizite Umformulierung durch den Entwickler - in einer deutlich höheren Zahl von Fällen als das Oracle Pendant:

• Correlated Subqueries in the SELECT Clause: erläutert das costing für skalare Subqueries und weist auf die Fälle hin, in denen skalare Subqueries unerfreulich werden. Das Muster, dass man im Ausführungsplan dazu findet ist a) zwei Children eines Selects ohne einen Join, b) ein Filter Prädikat für das erste child, c) hohe Kosten für das parent Select aufgrund der hohen cardinality des zweiten child. Gezeigt wird auch, wie man eine solche korrelierte skalare Subquery in einen Outer Join umwandelt, was die Kosten der Operation massiv reduzieren kann (was sowohl die Kosten des Optimizers als auch die reale Ressourcennutzung der Operation betrifft). Der SQL Server ist dazu in der Lage diese Umformulierung für den gegebenen Fall durch eine interne Transformation abzubilden.
• Correlated Subqueries in the SELECT Clause (2): zeigt, dass auch Oracle in manchen Fällen eine solche interne Transformation durchführen kann, was von der Struktur der skalaren Subquery abhängt.

Früher habe ich skalare Subqueries recht gerne benutzt, weil ich sie für recht gut lesbar hielt. Inzwischen neige ich aus Performance-Gründen dazu, sie grundsätzlich überall umzuformulieren, wo sie mir begegnen.

CTEs ohne Materialisierung in Postgres 12

Jonathan S. Katz weist in einem Artikel auf eine wichtige Verbesserung hin, die mit Postgres 12 verfügbar werden soll: CTEs werden dann nicht mehr automatisch materialisiert. In der commit message findet man dazu folgende Beschreibung:

By default, we will inline [CTEs] into the outer query (removing the optimization fence) if they are called just once. If they are called more than once, we will keep the old behavior by default, but the user can override this and force inlining by specifying NOT MATERIALIZED.

Damit  entspricht das Verhalten recht genau dem, das auch bei Oracle verwendet wird. Aus meiner Sicht ist das eine extrem wichtige Änderung, da ich CTEs eigentlich extrem gerne verwende, um SQL lesbarer zu machen - aber im Fall von Postgres immer darüber nachdenken musste, ob ich damit dem Planner ein Problem bereitete. Das sollte jetzt einfacher werden.

Erweiterte Analytische Funktionen in Postgres 11

Markus Winand zeigt, welche Verbesserungen Postgres 11 im Bereich der analytischen Funktionen bringt: insbesondere werden jetzt "Frame Units" in der OVER clause unterstützt - also Einschränkungen wie:

row between unbound preceeding and current row.

Wobei neben "row" auch "range" und "groups" als Einheit erscheinen können. Insbesondere "groups" ist dabei eine interessant Ergänzung, die nicht die Anzahl der Datensätze, sondern die der distinkten Werte berücksichtigt. Eine weitere wichtige Neuerung, die bisher nur Postgres anbietet, ist die "exclude" clause, mit der man Datensätze aus der Gruppierung in der Window-Funktion ausnehmen kann. Der Artikel spricht auch diverse andere Ergänzungen in Postgres 11 an, aber die führe ich hier nicht noch mal auf.