Vor einigen Wochen hat Franck Pachot via Twitter die erfreuliche Ankündigung gemacht, eine Artikelserie mit einem Vergleich der Optimizerstrategien in Postgres und Oracle beginnen zu wollen. Noch erfreulicher ist, dass es nicht bei der Ankündigung geblieben ist, sondern dass die Artikelserie zeitnah begonnen hat. Vor ein paar Jahren hatte ich einen Artikel geschrieben, der ein paar einführende Punkte zum gleichen Thema erfassen sollte, aber dabei war der Idealtyp Oracle nur in meinem Kopf als Vergleichsmaßstab im Spiel gewesen - insofern ist das Vorgehen von Monsieur Pachot das plausiblere:
- Postgres vs. Oracle access paths – intro: erklärt die Zielsetzung der Serie und die grundsätzliche Vorgehensweise. Insbesondere weist der Autor darauf hin, dass es ihm nicht um eine Bewertung, sondern um den Vergleich der ihm bekannten Strategien des Oracle Optimizers mit denen bei Postgres. Die verwendeten Versionen sind Oracle 12.2 und Postgres 9.6.2 und die jeweiligen Ausführungspläne werden mit dbms_xplan bzw. explain analyze erzeugt.
- Postgres vs. Oracle access paths I – Seq Scan: hier werden zunächst zwei Tabellen identischer Struktur in beiden Systemen angelegt, die jeweils einen unique index auf einer id-Spalte erhalten. Anschließend erfolgt die Berechnung der Kosten für den seq scan bei Postgres bzw. den Full Table Scan in Oracles Nomenklatur. Für Postgres basiert die Berechnung auf der Anzahl der pages in der Tabelle, der Anzahl der Ergebniszeilen des Scans, der Anzahl der ausgegebenen Zeilen und den Parametern seq_page_cost, cpu_tuple_cost und cpu_operator_cost. Auch für Oracle wird das costing vorgestellt, aber hauptsächlich auf die einschlägigen Ausführung der Herren Lewis und Antognini verwiesen. Erklärt werden auch die Buffer-Informationen in der analyze explain Ausgabe: "shared hit" bedeutet, dass die Buffer aus dem Cache gelesen wurden, "shared read" weist auf einen physikalischen Lesevorgang auf der Platte hin. Mit Hilfe von strace wird dann genauer untersucht, was auf OS-Seite abläuft: über lseek() werden die Start und Endpositionen des Lesevorgangs bestimmt und die zugehörigen pages dann über read() sequentiell gelesen.
- Postgres vs. Oracle access paths II – IndexOnlyScan: erläutert einen entscheidenden Unterschied zwischen den MVCC Implementierungen von Oracle und Postgres: bei Postgres werden nur die Tabellendaten versioniert, bei Oracle zusätzlich auch die Inhalte der Indizes. Daher musste für die sum(n)-Query des ersten Artikels bei Postgres ein seq scan erfolgen, während Oracle ohne Verwendung eines FULL-Hints einen Index-Zugriff gewählt hätte. Nur nach einem vorherigen vacuum und der Setzung eines internen visibility flags kann für deine entsprechende Query ein index-only scan erfolgen. In diesem Fall ergibt sich die Kostenrechnung bei Postgres aus den Faktoren ("Anzahl Index-Blocks" * random_page_cost) + ("Anzahl Index-Einträge" * cpu_index_tuple_cost) + ("Anzahl Ergebniszeilen" * cpu_tuple_cost).
- Postgres vs. Oracle access paths III – Partial Index: erläutert das Verhalten von Index-Only Scans hinsichtlich der Existenz von NULL-Werten: bei Oracle muss eine Spalte einen NOT NULL Constraint erhalten (oder ein NOT NULL in der zugehörigen Abfrage ergänzt werden), damit der Index-Only Scan möglich wird, weil Indizes bei Oracle NULL-Werte ausklammern (jedenfalls für einen single column index). Alternativ kann man den Index in Oracle auch um eine zweite Spalte erweitern, die eine Konstante enthält, was den Index allerdings vergrößert. Mit Hilfe von Function Based Indexes (FBI) kann man in Oracle die Indizierung auf bestimmte Werte einschränken, muss die entsprechende Bedingung aber dann auch so in der Query verwenden. Die Implementierung von Partial Indexes in Postgres ist flexibler: hier kann man im create index Kommando einfach eine where-Bedingung einfügen. Für die Verwendung des entsprechenden Index ist keine Anpassung der Query erforderlich.
- Postgres vs. Oracle access paths IV – Order By and Index: erläutert die Unterschiede von INDEX FULL SCAN und INDEX FAST FULL SCAN in Oracle und die sich daran anschließenden Effekte hinsichtlich der Sortierung (Variante 1 ist sortiert, Variante 2 nicht). Bei Postgres entspricht der Index Only Scan dem Index Full Scan bei Oracle - liefert also ein sortiertes Ergebnis (wobei jeweils veränderte collations eine zusätzliche Sortierung erforderlich machen können - für beide RDBMS ist es möglich, Indizes zu erzeugen, die collations unterstützen, die nicht der default der gegebenen Datenbank sind).
- Postgres vs. Oracle access paths V – FIRST ROWS and MIN/MAX: in Postgres liefert der Ausführungsplan zwei cost-Angaben: die startup-Kosten und die Gesamt-Kosten. Um in Oracle eine Kostenangabe für einen Teil der Ergebnismenge zu erhalten, muss man den first_rows_n Parameter verwenden. Erläutert wird auch das Vorgehen bei "fetch first rows" Operationen und beim MIN/MAX Zugriff. Für letzteren gibt es bei Oracle eine spezielle Operation im Plan (INDEX FULL SCAN (MIN/MAX)), während Postgres einen Plan verwendet, der stark dem der "fetch first rows" Operation entspricht.
- Postgres vs. Oracle access paths VI – Index Scan: untersucht das Verhalten unterschiedlicher Index-Scan-Operationen und insbesondere die zugehörigen costing-Angaben. In beiden RDBMS gibt es die Möglichkeit des index only scan ohne Tabellenzugriff. Im Fall eines Tabellenzugriffs erscheint bei Oracle der zusätzliche Step TABLE ACCESS BY INDEX ROWID, Postgres belässt es beim Index Scan using
. Die Einschränkung für den Index-Zugriff erscheint bei Oracle unterhalb des Plans als Access-Prädikat, bei Postgres wird sie als "Index Cond" angegeben. Im Artikel wird mit Hilfe von strace genauer untersucht, wie das Postgres costing für einen Range Scan ausfällt und dabei kommt heraus, dass der Planner im Beispiel den clustering factor berücksichtigt und die Kosten auf Basis von Sequential reads berechnet (für die gut sortierten Daten des Tests). Für Oracle ergeben sich random reads (db file sequential reads = single block reads). In beiden RDBMS sind die Abarbeitung und das Costing letztlich ähnlich, wobei sich allerdings die Einheit unterscheidet: Postgres rechnet mit sequential reads, Oracle mit random reads. Allerdings gilt das nur im gegebenen Test-Szenario mit einer hinsichtlich des Index gut geclusterten Tabelle. - Postgres vs. Oracle access paths VII – Bitmap Index Scan: ...
- Postgres vs. Oracle access paths VIII – Index Scan and Filter: ...
- Postgres vs. Oracle access paths IX – Tid Scan: ...
- Postgres vs. Oracle access paths X – Update: ...
- Postgres vs. Oracle access paths XI – Sample Scan
Ich gehe davon aus, dass die Serie noch weitere Artikel umfassen wird, die ich dann voraussichtlich auch noch hier nacherzählen werde.
