Neue Funktionen im Query Planner von Postgres 16

• PostgreSQL 16 ergänzt den Query Planner/Optimizer um 10 Verbesserungen und erweitert damit die Auswahl möglicher Ausführungspläne für DISTINCT, Aggregationen, Joins, Window Functions und Abfragen auf Partitionstabellen
• Bei SELECT DISTINCT, Aggregationen mit ORDER BY/DISTINCT und der Verarbeitung nach Merge Join werden teilweise vorsortierte Eingaben aktiver genutzt, sodass Ergebnisse mit weniger Speicher als bei einer vollständigen Sortierung erzeugt werden können
• Die Unterstützung für Memoize innerhalb von UNION ALL, Right Anti Join sowie Parallel Hash Join für FULL/RIGHT Joins zielt darauf ab, Kosten für wiederholte Abfragen und den Aufbau großer Hash-Tabellen zu senken
• Window Functions reduzieren unnötige RANGE-Verarbeitung und WindowAgg-Ausführungen bis zum Ende; einige Funktionen können je nach Bedingung vorzeitig abbrechen
• Alle Verbesserungen sind standardmäßig aktiviert, daher lohnt sich ein Vergleich von EXPLAIN und Laufzeiten realer Workloads vor und nach dem Upgrade auf PostgreSQL 16

---

Umfang der Planner-Verbesserungen in PostgreSQL 16

• PostgreSQL 16 führt mehrere Verbesserungen am Query Planner ein, sodass viele SQL-Abfragen schneller ausgeführt werden können als in früheren PostgreSQL-Versionen
• Die in den Release Notes zu PG16 enthaltenen Planner-Verbesserungen werden hier ausführlicher erläutert, zusammen mit Vergleichen der EXPLAIN-Ausgaben von PG15 und PG16 sowie reproduzierbaren Testbeispielen
• Der Planner ist hier die Komponente, die in anderen relationalen Datenbanken oft als Optimizer bezeichnet wird

Optimierungen für Sortierung und DISTINCT

• Einsatz von Incremental Sort bei SELECT DISTINCT

  • Incremental Sort wurde erstmals in PostgreSQL 13 eingeführt und reduziert die Kosten, indem bei bereits nach führenden Spalten sortierten Ergebnissen nur noch die restlichen Spalten sortiert werden
  • Der Planner von PostgreSQL 16 berücksichtigt Incremental Sort nun auch bei SELECT DISTINCT-Abfragen
  • Wenn es zum Beispiel einen btree-Index auf Spalte a gibt und die Reihenfolge a, b benötigt wird, kann zunächst ein nach a sortiertes Ergebnis über den Index geliefert und dann bei jedem Wechsel des Werts von a nur b sortiert werden
  • In der Quicksort-Implementierung von PostgreSQL kann es effizienter sein, viele kleine Gruppen zu sortieren als eine einzige große Gruppe
  • In der Beispielabfrage verwendete PG15 HashAggregate und einen sequenziellen Scan, während PG16 den Index distinct_test_a_idx und Incremental Sort auswählte
  • Presorted Key: a in der PG16-Ausgabe bedeutet, dass eine bereits nach a sortierte Eingabe genutzt wurde
  • Der Hash-Ansatz in PG15 spillte etwa 30MB auf Platte, während der maximale Speicherverbrauch von Incremental Sort in PG16 bei 26KB lag
  • Die Laufzeit sank von 414.226ms in PG15 auf 263.167ms in PG16

• Optimierung von Aggregationen mit ORDER BY oder DISTINCT

  • Bis einschließlich PostgreSQL 15 sortierten Aggregatfunktionen mit ORDER BY- oder DISTINCT-Klausel immer innerhalb des Aggregate-Knotens
  • Der Planner von PostgreSQL 16 kann Ausführungspläne erzeugen, die dem Aggregate-Knoten Zeilen bereits in der richtigen Reihenfolge zuführen; der Executor lässt die interne Sortierung dann bei schon sortierter Eingabe aus
  • Im Beispiel mit COUNT(DISTINCT b) nutzen sowohl PG15 als auch PG16 GroupAggregate und Index Only Scan, aber in der PG15-Ausgabe erscheint temp read=4540 written=4560
  • Diese temporären Datei-I/O stammen daraus, dass die implizite Sortierung in PG15 auf Platte spillte
  • In der PG16-Ausgabe fehlt diese temporäre I/O, und die Laufzeit sinkt von 302.693ms in PG15 auf 115.534ms in PG16, also auf weniger als die Hälfte

Verbesserungen bei wiederholten Abfragen und Join-Plänen

• Memoize innerhalb von UNION ALL

  • Der Plan-Knoten Memoize wurde erstmals in PostgreSQL 14 eingeführt und arbeitet wie eine Cache-Schicht zwischen einem parametrisierten Nested Loop und dessen innerer Eingabe
  • Der Planner von PostgreSQL 16 berücksichtigt Memoize nun auch dann, wenn sich innerhalb der inneren Seite eines parametrisierten Nested Loop eine UNION ALL-Abfrage befindet
  • Im Beispiel führte PG15 Append eine Million Mal aus, während PG16 Memoize oberhalb von Append platzierte
  • Memoize in PG16 verzeichnete Hits: 999990, Misses: 10, Memory Usage: 2kB
  • Die Anzahl der Append-Ausführungen sank von 1.000.000 in PG15 auf 10 in PG16
  • Die Laufzeit ging von 1926.151ms in PG15 auf 282.120ms in PG16 zurück, also auf etwa ein Sechstel

• Unterstützung für Right Anti Join

  • Bei Hash Join für INNER JOIN ist es in der Regel vorteilhaft, die kleinere Tabelle als Hash-Tabelle aufzubauen
  • Eine kleinere Hash-Tabelle verursacht weniger Aufbauarbeit, ist CPU-Cache-freundlicher und reduziert die Wahrscheinlichkeit von CPU-Stalls beim Warten auf Daten aus dem Hauptspeicher
  • Vor PostgreSQL 16 platzierte Anti Join die in NOT EXISTS erwähnte Tabelle immer auf der inneren Seite des Joins, sodass unter Umständen eine Hash-Tabelle für die größere Tabelle erstellt werden musste
  • PostgreSQL 16 unterstützt Right Anti Join und kann dadurch von den beiden Tabellen die kleinere hashen
  • Im Beispiel hashte PG15 die Tabelle large mit 1.000.000 Zeilen und verbrauchte 6446KB Speicher, während PG16 die Tabelle small mit 100 Zeilen hashte und nur 12KB benötigte
  • Die Laufzeit sank von 139.023ms in PG15 auf 77.076ms in PG16, also fast auf die Hälfte

• Parallel Hash Join für FULL/RIGHT Join

  • PostgreSQL 11 führte Parallel Hash Join ein, bei dem mehrere parallele Worker gemeinsam eine einzelne Hash-Tabelle aufbauen
  • Parallel Hash Join in PostgreSQL 16 unterstützt nun die Join-Typen FULL und RIGHT
  • Auch Pläne für FULL OUTER JOIN und Right Join können damit parallel ausgeführt werden
  • Im FULL JOIN-Beispiel nutzte PG15 einen einzelnen Hash Full Join, während PG16 Parallel Hash Full Join und Gather verwendete
  • In der PG16-Ausgabe erscheinen Workers Planned: 1, Workers Launched: 1
  • Die Laufzeit sank deutlich von 220.677ms in PG15 auf 129.769ms in PG16

Optimierungen für Window Functions

• Unnötige RANGE-Verarbeitung überspringen

  • Bei Window Functions wie row_number(), rank(), dense_rank(), percent_rank(), cume_dist() und ntile() verwendet PostgreSQL standardmäßig die Option RANGE, wenn in der Window-Klausel keine ROWS-Option angegeben ist
  • Die Option RANGE muss frühere Zeilen prüfen, um peer rows mit demselben Sortierwert zu finden; bei vielen gleichen Werten nach ORDER BY kann das teuer werden
  • Diese Funktionen verhalten sich unabhängig davon, ob ROWS oder RANGE angegeben ist, gleich, aber vor PostgreSQL 16 konnte der Executor das nicht unterscheiden und musste in allen Fällen peer rows prüfen
  • Der Planner von PostgreSQL 16 weiß, welche Window Functions von ROWS/RANGE beeinflusst werden, und übergibt dem Executor Informationen, damit unnötige Verarbeitung übersprungen werden kann
  • Im Beispiel row_number() <= 10 las PG15 50.410 Zeilen aus dem Index, bevor abgebrochen wurde, während PG16 nur 11 Zeilen las
  • PG16 nutzt aus, dass nach Erreichen von row_number 11 keine weiteren Zeilen mehr die Bedingung <= 10 erfüllen können
  • Die Laufzeit sank von 29.775ms in PG15 auf 0.058ms in PG16, also um mehr als das 500-Fache

• Erweiterter vorzeitiger Abbruch bei monoton steigenden Window Functions

  • PostgreSQL 15 konnte die Ausführung von WindowAgg bereits früh beenden, wenn eine Bedingung in der WHERE-Klausel für bestimmte Window Functions einmal false wurde und danach nicht wieder true werden konnte
  • PostgreSQL 16 erweitert diese Optimierung auf ntile(), cume_dist() und percent_rank()
  • In PostgreSQL 15 galt dies nur für row_number(), rank(), dense_rank(), count() und count(*)
  • Im Beispiel percent_rank() <= 0.01 behandelte PG15 die Bedingung als Filter in der Unterabfrage, und WindowAgg verarbeitete alle 50.000 Zeilen
  • PG16 nutzt dieselbe Bedingung als Run Condition und beendet die WindowAgg-Ausführung vorzeitig
  • Die Laufzeit sank von 84.358ms in PG15 auf 19.454ms in PG16, also um mehr als das Vierfache

Partitionstabellen und triviale DISTINCT-Verarbeitung

• Entfernung von LEFT JOIN bei Partitionstabellen

  • PostgreSQL kann schon lange LEFT JOIN entfernen, wenn sie für die Abfrage nicht benötigt werden und keine Möglichkeit besteht, dass Zeilen dupliziert werden
  • Vor PostgreSQL 16 wurde die Entfernung von LEFT JOIN bei Partitionstabellen nicht unterstützt
  • Der Grund war, dass für Partitionstabellen der erforderliche Nachweis fehlte, dass innere Zeilen äußere Zeilen nicht duplizieren können
  • Der Planner von PostgreSQL 16 wendet die Optimierung zur Entfernung von LEFT JOIN nun auch auf Partitionstabellen an
  • Diese Optimierung kann besonders bei Views nützlich sein
    • Denn Views enthalten oft viele Spalten, obwohl reale Abfragen nicht immer alle davon benötigen
  • Im Beispiel enthält der Plan in PG15 noch den Join auf part_tab, während PG16 nur einen sequenziellen Scan von normal_table ausführt

• DISTINCT mit eindeutig feststehendem Ergebnis als Limit behandeln

  • Der PostgreSQL-Planner kann den Plan-Knoten zur Entfernung von Duplikaten weglassen, wenn er erkennen kann, dass alle Zeilen ohnehin denselben Wert haben
  • PostgreSQL 16 nutzt aus, dass das Ergebnis nur identische Werte enthalten kann, wenn alle Spalten im DISTINCT-Ziel durch Gleichheitsbedingungen in der WHERE-Klausel festgelegt sind, und behandelt dies als LIMIT 1
  • In der Beispielabfrage SELECT DISTINCT a,b,c FROM abc WHERE a = 5 AND b = 5 AND c = 5 sind alle DISTINCT-Spalten auf denselben Wert eingeschränkt
  • PG15 las das gesamte Ergebnis und reduzierte es mit dem Operator Unique auf eine Zeile
  • PG16 verwendete Limit und einen sequenziellen Scan, um nur eine Zeile zurückzugeben
  • Die Laufzeit sank von 30.381ms in PG15 auf 0.025ms in PG16, also um mehr als das 1200-Fache

Erweiterte Nutzung von Incremental Sort nach Merge Join

• Vor PostgreSQL 16 nutzte der Planner beim Betrachten von Merge Join die Sortierreihenfolge des Joins nur dann, wenn sie exakt zu den Anforderungen einer darüberliegenden DISTINCT-, GROUP BY- oder ORDER BY-Operation passte
• Diese Regel berücksichtigte nicht ausreichend, dass Incremental Sort in darüberliegenden Operationen teilweise vorsortierte Eingaben nutzen kann
• PostgreSQL 16 lockert die Regel für die Berücksichtigung der Reihenfolge bei Merge Join von „muss exakt übereinstimmen“ auf „mindestens eine führende Spalte muss korrekt sortiert sein“
• Dadurch kann der Planner das Ergebnis eines Merge Join häufiger mit Incremental Sort an die Anforderungen darüberliegender Operationen anpassen
  • Incremental Sort nutzt teilweise vorsortierte Eingaben, um in kleinen Batches zu sortieren, und reduziert so gegenüber einer vollständigen Sortierung Speicherbedarf und Vergleichsaufwand
• Im Beispiel verwendete PG15 nach Merge Join eine vollständige Sort-Operation, während PG16 Incremental Sort einsetzte
  • Der maximale Speicherverbrauch von Incremental Sort in PG16 lag bei 26KB
  • Die Laufzeit sank leicht von 1010.738ms in PG15 auf 915.589ms in PG16, während der für die Sortierung benötigte Speicher deutlich zurückging

Aktivierung und Prüfung in der Praxis

• Alle 10 Planner-Verbesserungen in PostgreSQL 16 sind standardmäßig aktiviert
• Jede Optimierung wird entweder in allen möglichen Fällen angewendet oder selektiv gewählt, wenn der Planner sie für sinnvoll hält
• Wer eine ältere PostgreSQL-Version verwendet, kann reale Workloads unter PostgreSQL 16 ausführen und prüfen, welche Abfragen schneller werden
• Praktisches Feedback kann über die Mailingliste [e-mail] geteilt werden

[GN⁺]

Meinungen auf Hacker News

• Es wäre wirklich großartig, wenn der PostgreSQL-Query Planner eine Abfrage mitten in der Ausführung neu planen könnte.
  Pathologisch langsame Abfragen entstehen oft, weil der Planner die für die Datenverteilung nötigen Informationen nicht kennt und die Kosten falsch schätzt; dabei kommt es leicht zu einem Faktor 1000, etwa wenn die Laufzeit statt 1 ms plötzlich 1 s beträgt.
  Tabellenstatistiken können nicht zu 100 % korrekt sein. Daher wäre es gut, nach dem Start einer Abfrage aktuelle Fortschrittsinformationen wie die Zahl der gescannten Pages und der passenden Tupel wieder in den Planner einzuspeisen, wenn der Fortschritt langsamer ist als erwartet, und daraus einen neuen Plan zu erstellen.
  Allerdings streamt PostgreSQL Ergebnisse, statt sie erst vollständig zu erzeugen und dann zu senden. Um mitten in der Ausführung den Plan zu ändern, müsste man also bereits an den Client gesendete Ergebnisse nachverfolgen, was größere Infrastrukturänderungen erfordert.
  Außerdem kann der Client mitten in einer Abfrage die Richtung umkehren und frühere Ergebnisse erneut in umgekehrter Reihenfolge anfordern, was die Komplexität weiter erhöht.

  • Als Autor des Blogposts und PostgreSQL-Committer fände ich es gut, wenn es diese Funktion gäbe. Das Senden von Tupeln an den Client ist allerdings noch schwieriger als oben beschrieben.
    Es gibt nämlich nicht einmal die Garantie, dass ein neuer Plan dieselben Tupel zurückliefert. Ohne ORDER BY, etwa bei SELECT * FROM table LIMIT 10, ist nicht deterministisch, welche Tupel erscheinen.
    Einfacher wäre es vielleicht, X Tupel in eine Queue zu legen und erst zu senden, wenn die Queue voll ist. Sobald die Queue voll ist, betrachtet man eine Neuplanung als zu spät und bleibt beim aktuellen Plan.
    Nutzer könnten X anpassen und damit mehr Speicherverbrauch sowie eine höhere Latenz bis zum ersten Tupel in Kauf nehmen, dafür aber den Zeitraum verlängern, in dem ein Planwechsel möglich ist.
  • Eine andere Sichtweise wäre, Abfragen zuzulassen, bei denen eine längere Planungsphase erlaubt ist. Man könnte dem System gestatten, 1 Sekunde oder einige Sekunden für die Wahl des optimalen Plans aufzuwenden und dabei zusätzliche Statistiken zu sammeln oder die Abfrage kurz testweise auszuführen.
  • Ich frage mich, wofür die Funktion nützlich ist, bei der der Client mitten in einer Abfrage die Richtung umkehrt und frühere Ergebnisse in umgekehrter Reihenfolge erneut erhält.
  • Ich frage mich, ob der Query Plan die Reihenfolge der Ergebnisse beeinflussen kann, wenn die Abfrage die Sortierreihenfolge nicht vollständig festlegt. Falls ja, könnte der vorgeschlagene Ansatz nahezu unmöglich sein.
    Die neue Abfrage könnte nicht einfach die ersten N Ergebnisse überspringen, sondern müsste jede bereits gesendete Zeile gegen ein Verzeichnis abgleichen.
  • Dieses Paper und die darin zitierten Arbeiten könnten interessant sein: https://arxiv.org/pdf/1902.08291

• Für die Visualisierung von Abfragen nutze ich dieses Tool: https://explain.dalibo.com/
  Es gibt auch https://www.pgexplain.dev/; früher war dessen Ausgabe weniger gut, aber inzwischen wirken beide ziemlich ähnlich.

  • Das Tool ist hervorragend und ich nutze es, aber ich verstehe die Pläne nicht tief genug, um aus den schlecht aussehenden Stellen abzuleiten, wie ich meinen Ansatz korrigieren sollte.
  • Laut Profil bist du CTO im Fintech-Bereich; ich frage mich, wie du mit dem Hinweis dieses Tools umgehst, dass empfohlen wird, „keine wichtigen oder sensiblen Informationen zu senden“.
    Ich frage mich, ob es für solche Situationen ein Tool zum Bereinigen von Ausführungsplänen gibt.

• Verbesserungen am Query Planner sind immer willkommen und in einer Datenbank ein sehr wichtiger Bereich. Natürlich fallen sie meist dann am stärksten auf, wenn er nicht das tut, was ich möchte.
  Was ich persönlich ziemlich frustrierend fand, ist JIT in aktuellen PostgreSQL-Versionen. Die Heuristiken dafür, wann es verwendet wird, wirken überhaupt nicht robust.
  Ich habe das bei einer typischen von einem ORM generierten Abfrage gesehen: Die Abfrage selbst ist einfach, zieht aber durch Joins viele Tabellen hinzu. Ohne JIT ist sie in wenigen Millisekunden fertig, mit JIT kommen zusätzlich 1 bis 1,5 Sekunden hinzu, wodurch sie selbst bei kleinen Datenmengen extrem langsam wird.
  Inzwischen weiß ich, dass man JIT einfach abschalten kann, aber für Nutzer, die noch nicht herausgefunden haben, warum es langsam ist, kann das den Eindruck von PostgreSQL massiv beschädigen. Ich mag PostgreSQL, aber JIT standardmäßig aktiviert zu lassen wirkt viel zu riskant.

  • Als Autor des Blogposts und PostgreSQL-Committer stimme ich stark zu, dass der Code zur Entscheidung, ob JIT verwendet wird, verbessert werden muss.
    In PG16 wird nur auf die geschätzten Gesamtkosten des Plans geschaut, nicht aber auf die Zahl der Ausdrücke, die kompiliert werden müssen.
    Ein paar Ausdrücke zu kompilieren ist schnell, aber wenn man eine partitionierte Tabelle mit Hunderten Partitionen abfragt und all diese Partitionen im Plan landen, bekommt der JIT-Compiler viel zu tun.
    Ein Kollege und ich haben Code, der das verbessert, aber derzeit ist nicht sicher, ob er in PG17 landet.
  • Was bei JIT ebenfalls seltsam wirkt, ist, dass der generierte Code nicht gecacht wird. Das ist oft der teuerste Teil der Abfrageausführung; ich verstehe nicht, warum er nicht gecacht wird.
    Auch in den Diskussionen zu JIT auf der PostgreSQL-Mailingliste habe ich keinen überzeugenden Grund gefunden.
    Bei OLTP-Workloads ist es richtig, JIT abzuschalten.
  • Ich halte JIT im Grunde für nahezu gescheitert. Die Absicht war gut, aber LLVM ist dafür nicht das passende Werkzeug. Ich habe es global deaktiviert.
    Da ich kein ORM verwende, liegt es auch nicht einfach nur an seltsamen Abfragemustern.
    Query-Parallelisierung dagegen kann tatsächlich nützlich sein und richtet vor allem nur selten Schaden an.
  • Ich bin kürzlich in Produktion auf einen merkwürdigen JIT-Bug gestoßen.
    Nachdem ich mit apt ein paar Pakete aktualisiert hatte, begann eine große Abfrage, die alle 5 Minuten läuft, plötzlich fehlzuschlagen. Genauer gesagt trennte PostgreSQL mitten in der Abfrageausführung stillschweigend die Verbindung, ohne etwas zu loggen.
    Beim manuellen Prüfen mit EXPLAIN zeigte sich, dass nur die Abfragevariante kaputtging, die JIT verwendete; die Variante ohne JIT war in Ordnung. Nach dem Abschalten von JIT lief wieder alles normal.
  • Ich frage mich, ob man nicht Prepared Statements verwenden könnte, um nur einmal zu kompilieren und das Kompilat bei jeder Ausführung dieser Abfrage wiederzuverwenden.

• Ich frage mich, wie oft solche Änderungen bei echten Queries tatsächlich Wirkung zeigen. Besonders die Änderung „wenn möglich für die Implementierung von DISTINCT Limit statt Unique verwenden“ fühlt sich so an, als würde sie nur auf extrem dumme Queries zutreffen
  Ich frage mich, ob die PostgreSQL-Entwickler Informationsquellen haben, um das einzuschätzen

  • Ich denke, das dürfte ziemlich oft etwas bringen. DISTINCT ist etwas, das unerfahrene Entwickler häufig dranhängen, um eine schlechte Query zu reparieren, und normalerweise ist der erste Schritt beim Optimieren der Performance, die Query so umzuschreiben, dass es nicht mehr nötig ist
    Wenn Verbesserungen an DISTINCT schlechte Queries robuster machen, ist damit viel gewonnen. Es wird nicht alle Probleme beheben, aber jede Verbesserung ist willkommen
  • Als Autor des Blogposts und der betreffenden Funktion: Dieser Fall kam tatsächlich auf der Mailingliste pgsql-hackers auf
    Ich stimme zu, dass sie vermutlich nicht oft greift, aber das Gute daran ist, dass die Erkennung der Anwendbarkeit so einfach war wie zu prüfen, ob ein Pointer NULL ist
    Die Erkennung ist sehr simpel, und meistens wird es nicht anwendbar sein, aber wenn es anwendbar ist, kann es eine deutliche Performance-Steigerung bringen
  • Das Problem ist, dass ORMs die Angewohnheit haben, sehr dumme Queries zu erzeugen, und Entwickler sich weigern, sie durch selbst geschriebenes SQL zu beheben, weil das irgendwie nicht puristisch sei
    Es ist vermutlich kein sehr häufiges Problem, aber es würde mich nicht überraschen, wenn es gelegentlich auftaucht
  • In einem früheren Job haben wir aus Legacy-Gründen doppelte E-Mail-Adressen in der User-Tabelle erlaubt, wollten aber keine neuen Duplikate einfügen, also haben wir vor dem Anlegen eines neuen Users die Query select distinct email from users where email = ? ausgeführt
    Ich glaube, es gab nicht mehr als 100 Zeilen mit derselben E-Mail-Adresse. Die meisten waren Test-User, die man hätte löschen können, aber ich schweife ab

• Es wäre gut, wenn PostgreSQL einen Strict Mode für App-Tests hätte: einen Modus, der nur die Query selbst betrachtet und unabhängig von Statistiken einen Fehler zurückgibt, wenn ein Index die Query asymptotisch verbessern würde, dieser Index aber fehlt
  Für App-Upgrades wäre auch ein Befehl CREATE INDICES FOR schön, der die entsprechenden Indizes anlegt, sowie ein Modus zur automatischen Index-Erzeugung für interaktive Nutzung und Entwicklung
  Insgesamt sollte das System so entworfen sein, dass asymptotisch nicht optimale Ausführungen niemals passieren

• Ich weiß nicht, warum sie keine Hints implementieren

  • Es gibt die Erweiterung pg_hint_plan. Die Gefahr von Hints besteht darin, dass sie zum Zeitpunkt des Schreibens zwar richtig sein können, aber bei veränderter Tabellengröße oder Datenverteilung eher schaden können
    Als ich früher Diskussionen über Hints gesehen habe, erinnere ich mich, dass es keinen grundsätzlichen Widerstand gab, sofern sie den Planner nicht zu stark festlegen und sich an Änderungen der zugrunde liegenden Daten anpassen können
    Zum Beispiel, indem man ihm mitteilt, dass zwischen zwei Spalten eine Korrelation besteht, statt festzulegen, dass ein bestimmtes Prädikat mit 10 Zeilen übereinstimmt
  • Verwandte Diskussion: Why PostgreSQL doesn't have query hints
    https://news.ycombinator.com/item?id=2179433 (60 Kommentare, 2011)
    Die offizielle Position im PostgreSQL-Wiki steht unter https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
    Die Haltung lautet: „Wir haben kein Interesse an Hints in genau der Form, wie sie in anderen Datenbanken üblicherweise implementiert sind“
    Als Probleme bestehender Hint-Systeme werden genannt: schlechtere Wartbarkeit von Anwendungscode, Behinderung von Upgrades, Förderung schlechter DBA-Gewohnheiten und mangelnde Eignung bei wachsender Datenmenge
    Ich möchte diese Haltung nicht kritisieren, aber es ist frustrierend, wenn PostgreSQL einen dummen Plan wählt und man es nicht dazu bringen kann, eine vernünftige Wahl zu treffen

• Ein Freund, der als Microsoft-DBA für mittelständische Unternehmen arbeitet, sagte, mit PostgreSQL könne man keine ernsthaften Dinge machen. Er meinte sogar, er sei schockiert gewesen, als er erfahren habe, dass PostgreSQL nicht einmal einen Query Planner habe.
  Spott kurz beiseite: Ich frage mich, ob an der größeren Behauptung etwas dran ist, dass MSSQL Größenordnungen bewältigen kann, für die PostgreSQL ungeeignet ist. Aus dem Bauch heraus fühlt sich das unsinnig an, aber ich bin absolut kein DBA.

  • In gewisser Weise stimmt das schon. Wenn man eine Datenbank braucht, die fast alles hinreichend gut erledigt, sind MSSQL und Oracle ziemlich wahrscheinlich dazu in der Lage.
    Sie haben solche Probleme gelöst, indem sie Geld und Hardware – also noch mehr Geld – daraufgeworfen haben, bis das Problem verschwand. Natürlich steckt darin auch clevere Technik, aber im Kern ist dort über lange Zeit sehr viel mehr Engineering hineingeflossen.
    Sie können horizontal stärker skalieren, als PostgreSQL es vernünftigerweise kann.
    Allerdings holt PostgreSQL auf, und man könnte sagen, dass MySQL/MariaDB in dieser Hinsicht schon immer ganz ordentlich dastanden. Die Optionen für horizontale Skalierung werden laufend besser.
    Heute ist es auch einfacher geworden, PostgreSQL-Cluster im Multi-Terabyte-Bereich auf wenigen Maschinen zu betreiben, großen Traffic zu verarbeiten und „Big Data“ in spezialisiertere Datenbanken auszulagern. Der alte Ansatz, alles in MSSQL/Oracle zu stopfen, kann etwas überholt sein.
  • Ich habe viel mit MSSQL entwickelt, und bei PostgreSQL fehlen ein paar Funktionen, die einen durchaus überraschen können.
    Was dein Freund meinte, könnte sein, dass PostgreSQL keine Möglichkeit hat, Query-Pläne zu cachen oder festzuschreiben. PostgreSQL plant jede Anweisung neu, sofern man nicht manuell Prepared Statements verwendet, und selbst das funktioniert nur pro Verbindung.
    MSSQL cached und wiederverwendet Pläne seit langer Zeit, sodass der Planner mehr Zeit für die Planerstellung aufwenden kann. Außerdem gibt es Hints, und man kann Pläne fixieren.
    PostgreSQL braucht wirklich Hints. Auch wenn der Optimizer hervorragend ist, weiß ich es manchmal besser und möchte ihn dazu bringen, auf mich zu hören.
    Außerdem hat PostgreSQL keinen echten Clustered Index, und alle Tabellen sind Heaps. In MSSQL verwendet man das meistens häufig und setzt in der Regel den Primary Key als Clustered Index, sodass die Tabelle selbst zum Index wird und es bei Key-Lookups keine Indirektion gibt.
    Interessanterweise ist es bei SQLite umgekehrt: Tabellen haben immer einen Clustered Index, ob man ihn nun anlegt oder nicht, während MSSQL die Wahl zwischen Heap und indexorganisierter Tabelle lässt.
  • PostgreSQL hat einen Query Planner. Der gesamte Artikel handelt von dessen Verbesserungen. Also gab es entweder ein Kommunikationsproblem, oder dein Freund kennt PostgreSQL überhaupt nicht.
    Es gibt auch Beispiele sehr großer PostgreSQL-Datenbanken, die gut funktionieren; PostgreSQL ist also definitiv skalierbar.
    Allerdings hat SQL Server auch Funktionen, die PostgreSQL nicht hat, und wenn diese wichtig sind, kann er für bestimmte Use Cases besser passen. Am Ende sind es unterschiedliche Datenbanken mit unterschiedlichen Stärken und Schwächen.
  • Ich habe beide sowohl für OLTP als auch für Data Warehousing verwendet, und beide sind in Ordnung.
    Zuerst wollte ich schreiben, dass ich meinem Unternehmen eine Migration zu PostgreSQL empfohlen hätte, wenn es nicht um vom Vendor gelieferte Anwendungen ginge, die SQL Server benötigen.
    Dann wurde mir aber klar, wie viel Arbeit es wäre, Dinge zu ersetzen, die Microsoft mitliefert: Reporting Services, Integration Services, Jobs, AD-Integration, Service Broker und so weiter. notify/listen hat keine Nachrichtentypen.
    Analysis Services nutzen wir nicht mehr, aber als wir es früher nutzten, wäre auch das schwer zu ersetzen gewesen.
    Solche Dinge halten einen fest. Ich habe keine Vorstellung, wie lange es dauern würde, all das zu ersetzen, und ein Jahr damit zu verbringen, bereits Vorhandenes zu ersetzen, ist kein besonders guter Return on Investment.
  • Aurora von AWS scheint das ziemlich gut zu bewältigen und zielt darauf ab, ein Drop-in-Ersatz für PostgreSQL und MySQL zu sein.

• Ich frage mich, warum das auf citusdata veröffentlicht wurde und nicht auf postgresql.org. Ich weiß nicht, ob es nur um kostenpflichtige Funktionen geht oder um Open-Source-Erweiterungen.

  • Weil der Autor bei Citus Data arbeitet und einige dieser Optimierungen auch selbst geschrieben hat.

• Wann wird man wohl Indizes nutzen können, um IS NOT DISTINCT FROM-Abfragen schneller zu machen ;)