Freitag, März 28, 2014

Log-Dateien parsen mit Postgres

Gerade ist mir klar geworden, wie handlich das Parsen von Log-Dateien in der postgres-Datenbank ist. Der gesamte Vorgang beschränkt sich auf folgende Schritte:

-- Anlage einer Tabelle mit einer einzigen Spalte (vom Typ TEXT)
create table my_log(log_line text);

-- Einlesen via copy-Kommando
copy my_log(log_line)
from 'pfad_der_log_dateien/log_datei.txt';

-- Parsen der Eingabe-Strings mit Hilfe der Funktion split_part in beliebig viele Abschnitte
-- „Split string on delimiter and return the given field (counting from one)“
select split_part(log_line, ' ', 1) ip
     , split_part(log_line, ' ', 2) p2
     , split_part(log_line, ' ', 3) p3
     , split_part(log_line, ' ', 4) p4
     , split_part(log_line, ' ', 5) p5
     , split_part(log_line, ' ', 6) p6
     , split_part(log_line, ' ', 7) p7
     , split_part(log_line, ' ', 8) p8
     , split_part(log_line, ' ', 9) p9
     , split_part(log_line, ' ', 10) p10
     , split_part(log_line, ' ', 11) p11
  from my_log

Neben der extrem simplen Syntax für die copy-Operation ist die Funktion split_part entscheidend: ursprünglich hatte ich eine INSTR-Funktion gesucht (aber nur POSITION gefunden), aber SPLIT_PART entspricht meinen Erfordernissen deutlich besser. Voraussetzung ist natürlich eine klar definierte Struktur der einzulesenden Datei mit eindeutigen Delimitern, aber wenn die Voraussetzungen stimmen, ist das Verfahren äußerst effizient.

Donnerstag, März 27, 2014

Langwierige Statistikerstellung mit auto_sample_size

Nur ein knapper Hinweis: Jonathan Lewis zeigt, dass die Verwendung der auto_sample_size seit 11g in Kombination mit der Ermittlung des approximate NDV Mechanismus zu unerwartet langen Ausführungszeiten bei der Statistikerfassung führen kann. Betroffen sind:
  • Auf LOBs basierende virtual columns mit zugehörigen Indizes. Dabei wird das Ergebnis des Funktionsaufrufs im Index gehalten, aber in der Tabelle erscheint nur die Funktionsdefinition, so dass Oracle bei der Statistikerstellung den Funktionsaufruf für jede Zeile ausführen muss - und wenn das approximate NDX Feature verwendet wird, umfasst das herangezogene Sample 100%.
  • IOTs mit overflow segment: auch in diesem Fall führt der approximate NDV Mechanismus unter Umständen zu extremen Effekten, da auf den Index Fast Full Scan zum Zugriff auf das Index Segment (single bloch I/O) fetch by rowid Zugriffe folgen, um die Inhalte des overflow segments zu lesen (was - abhängig vom ClusteringFactor - recht kostspielig sein kann).
Die Darstellung im Scratchpad ist mal wieder recht detailliert, so dass ich die Nacherzählung an dieser Stelle abbreche und diesen Eintrag (ebenfalls mal wieder) nur als Pointer verstehe.

Dienstag, März 25, 2014

SQL Server 2014

Wie man im Official Microsoft Blog erfährt, läuft der SQL Server 2014 zum 01.April 2014 feierlich vom Stapel, was immerhin früh genug ist, um den Namen nicht zur Farce werden zu lassen.

Samstag, März 22, 2014

Optimizing Oracle Performance von Cary Millsap

Amazon hat mich gebeten, mein kürzlich erworbenes Exemplar des Buches zu rezensieren, und bei solchen Anfragen neige ich zu Brechts: "Die etwas fragen/ Die verdienen Antwort". Und wenn ich es dort veröffentliche, dann kann ich es auch hier unterbringen:

Nachdem mir vor kurzem aufgefallen war, dass ich kein Exemplar von Cary Millsaps Standardwerk mehr besaß, habe ich mir noch mal eines gekauft und es auch noch einmal gelesen - und es gibt nicht viele Oracle-Bücher, die ich mir mehr als zehn Jahre nach ihrer Erstveröffentlichung erneut kaufen würde. Natürlich sind viele technische Details inzwischen überholt, aber erstaunlich viele Punkte sind immer noch aktuell - insbesondere die grundsätzliche Erkenntnis, dass Datenbank-Tuning (und das betrifft nicht nur Oracle) nicht von globalen statistischen Werten, sondern von der Wahrnehmung der Anwender und von den Erfordernissen der Arbeit mit der Software getrieben werden sollte (und damit von wirtschaftlichen Fragestellungen). Auch wenn die großen Tage der Buffer-Cache-Hit-Ratio vorüber sind, erfolgt Performance-Optimierung auch heute noch häufig auf der Basis von Vermutungen und Annahmen (Trial and Error) - und nicht auf der Verwendung (simpler empirischer) wissenschaftlicher Verfahrensweisen, wie sie bei Millsap beschrieben werden. Neben den eher allgemeinen Aussagen zur Verfahrensweise enthält der Text auch eine ganze Reihe wichtiger Informationen, die noch immer zutreffend sind: etwa die umfassende Beschreibung von SQL Trace, die auch für aktuelle Releases nahezu uneingeschränkt gültig ist (und die ich sonst nirgendwo in ähnlicher Klarheit gefunden habe). Daher halte ich das Buch noch immer für lesenswert und würde es mir bei erneutem Verlust wahrscheinlich auch noch einmal kaufen.

Donnerstag, März 20, 2014

Randolf Geist über Parallel Execution Skew

Für AllThingsOracle hat Randolf Geist eine kleine Serie zum Thema Parallel Execution Skew gestartet - wobei der Begriff Skew in diesem Kontext die ungleiche Verteilung der zu leistenden Arbeit auf die in der parallelen Verarbeitung verfügbaren Ressourcen bezeichnet.
  • Parallel Execution Skew – Introduction: nach der einleitenden Definition folgt der Hinweis, das eine ungleiche Arbeitsverteilung zwischen den parallelen Ressourcen im ungünstigsten Fall zu einer Verlängerung der Laufzeit gegenüber der seriellen Verarbeitung führen kann, da die Parallelisierung einen signifikanten Overhead hervorruft. Im Anschluss wird ein Beispiel vorgeführt, in dem die Parallelisierung eines Joins (Hash Join mit Hash Distribution) sehr effektiv erfolgt und die Ausführung um den Faktor der Parallelisierung beschleunigt. Ohne den folgenden Artikeln vorgreifen zu wollen, lässt sich aus dem Versuchsszenario bereits ableiten, dass die effektive Parallelisierung mit der Datenverteilung für die Join-Spalte zusammenhängt - eine Spalte mit einem prädominanten Wert würde (bzw. wird in den folgenden Artikeln) zu ungünstigeren Ergebnissen führen. Interessant ist auch noch der Hinweis auf das seit 11.2 verfügbare Feature der in-memory Parallel Execution, das die Verwendung des Buffer Caches bei parallelisierten Operationen ermöglicht - in älteren Releases verwendeten parallele Operationen immer direct path reads.
    In Ergänzung zum Artikel hat Randolf Geist in seinem eigenen Blog noch einen weiteren Artikel Parallel Execution Overhead veröffentlicht, der anhand eines Joins mit drei Tabellen zeigt, dass der Parallelisierungseffekt bei komplexeren Operationen aufgrund des Parallelisierungs-Overheads unter Umständen deutlich schwächer ausfällt, auch wenn keine Skew-Effekte im Spiel sind.
    Eine weitere Ergänzung ist das Analysing Parallel Execution Skew - Video Tutorial, das ursprünglich als Teil der XPLAN_ASH-Serie (die ich hier auch mal verlinkt haben dürfte, aber den Link gerade nicht finde) vorgesehen war.
  • Parallel Execution Skew – Demonstrating Skew: liefert die Demonstration des in der Einführung erläuterten Verhaltens anhand eines Beispiels mit einem "skewed foreign key" also einer massiven Ungleichverteilung der Werte des Fremdschlüssels mit einem prädominanten Eintrag. Zur Analyse der Aktivität kann die View v$pg_tqstat verwendet werden, die allerdings nur im privaten Speicherbereich der Query Coordinator session gefüllt wird und somit erst nach der erfolgreichen Verarbeitungen und nicht von anderen Sessions aus abgefragt werden kann. Im Ausführungsplan ist die Verknüpfung der Operationen von allem über die Table Queues möglich (zu denen Jonathan Lewis recht umfassende Erklärungen geliefert hat - insbesondere in Teil 3 seiner Artikelserie zum Thema).
    Zeitgleich hat der Herr Geist im eigenen Blog die Teile zwei und drei seines Video Tutorials Analyzing Parallel Execution Skew veröffentlicht, die sicherlich deutlich umfassendere Ausführungen enthalten (Laufzeit jeweils ca. 1h) - aber die Betrachtung verschiebe ich auf einen geeigneteren Zeitpunkt.
  • Parallel Execution Skew – 12c Hybrid Hash Distribution With Skew Detection: weist darauf hin, dass es in 12c eine automatische Erkennung und Behandlung von Skew-Effekten gibt, die allerdings nur in bestimmten Fällen verwendbar ist. Voraussetzung ist die Existenz von Histogrammen für die Join-Spalten: wenn popular values existieren, wird eine zusätzliche rekursive Query zur Bestimmung der populären Werte eingesetzt und die ermittelten Werte werden bei der Verteilung an die Slaves speziell behandelt (d.h. per broadcast an alle Worker übermittelt, während die seltenen Werte per hash distribution verteilt werden). Im Plan erscheint ein Step: PX SEND HYBRID HASH (SKEW). Aktuell sind die Einschränkungen für die Verwendung des Features noch relativ groß (nur bei Hash Joins, nicht bei Merge Joins verwendbar; nur für inner joins; nur bei Verwendung eines einzelnen Join-Prädikats einsetzbar etc. - im CBO-Trace findet sich ein Eintrag "Skew handling disabled since conditions not satisfied", wenn die Voraussetzungen nicht erfüllt sind). Weitere technische Details zum Verfahren (und seinen Grenzen) liefern zwei Artikel in Randolf Geists Blog, deren Aussagen ich zum Teil in meine Zusammenfassung des AllThingsOracle-Artikels integriert habe (allerdings längst nicht vollständig, da der Herr Geist die Details mal wieder recht umfassend ausführt: sollte ich diese Details gelegentlich benötigen, weiß ich jedenfalls, wo ich nachschlagen kann).
  • Parallel Execution Skew – Addressing Skew Using Manual Rewrites: liefert - was angesichts des Titels nicht allzu sehr überrascht - einige Vorschläge zur manuellen Optimierung paralleler Zugriffe. Erläutert werden dabei folgende Optionen:
    • Nachbildung der in 12c eingeführten skew aware Features: dabei wird die Query in zwei Teile gesplittet: einen Zugriff für die non-popular values und einen zweiten per UNION ALL verknüpften Teil für die populären Werte. Das Ergebnis liefert eine gute Performance, ist aber relativ unhandlich und erfordert einen zweifachen Zugriff auf die Daten.
    • Verteilung der popular values auf weniger ungleich verteilte Werte mit Hilfe einer Mapping-Tabelle: diese automatische Verteilung kann mit Hilfe einer View dauerhaft hinterlegt werden - das Verfahren sieht interessant aus, ist aber womöglich noch erläuterungsbedürftiger als die erste Variante.
Zum Abschluss der Serie hat der Autor dann noch einen Summary-Artikel mit Links auf die AllThingsOracle-Artikel und die Videos ergänzt.

Dienstag, März 18, 2014

Temporäre Tablespaces und Sortierungen

Jonathan Lewis hat sich zuletzt in mehreren Artikeln mit der Verwendung temporärer Tablespaces, insbesondere im Zusammenhang mit Global Temporary Tables (GTT) auseinandergesetzt:
  • 12c Temporary: zeigt, dass in 12c eine Session potentiell drei verschiedene temporäre Tablespaces verwenden kann. Über den Parameter temp_undo_enabled wird gesteuert, dass die Undo-Informationen einer GTT in den default temporary tablespace der Datenbank geschrieben werden sollen. Seit 11g kann man im Create für eine GTT den temporary tablespace explizit angeben, was zur feingranularen Steuerung von IO-Last genutzt werden kann. Außerdem gibt es natürlich noch den default temporary tablespace des Users, in dem GTTs per default landen.
  • Temporary Segments: erläutert die Möglichkeit über die v$sort_usage zugrunde liegende Struktur x$ktsso die Objektnummer einer GTT ausfindig machen zu können, die auch in v$sql_plan erscheint. Der Artikel enthält weiterhin einen Link auf einen Artikel von Stefan Koehler, der sich mit dem Phänomen falscher SQL_ID-Angaben in v$sort_usage beschäftigt.

Donnerstag, März 13, 2014

Deadlock bei Shrink Space

Jonathan Lewis beschreibt in seinem Blog ein ziemlich erstaunliches Phänomen: wenn man zwei Indizes der gleichen Tabelle in unterschiedlichen Sessions über ALTER INDEX ... SHRINK SPACE verkleinern möchte, dann versuchen beide Sessions ein exklusives (TM-) Lock auf die Tabelle zu bekommen - und blockieren sich dadurch gegenseitig. In der Alert.log finden sich auch Hinweise auf das deadlock, aber es erfolgt keine automatische Auflösung - beide Sessions warten und warten und warten...

Das Verhalten lässt sich in 11.2 mit einem einfachen Test problemlos reproduzieren (Anlage einer Tabelle mit zwei Indizes; paralleler Aufruf von Shrink Space für beide Indizes in unterschiedlichen Sessions).

Donnerstag, März 06, 2014

Direct Path Inserts und unusable indexes

Erst mal meine Entschuldigung für den Titel - eine brauchbare Übersetzung für "unusable indexes" ist mir nicht eingefallen (und über "direct path inserts" mache ich mir nicht einmal mehr Gedanken). Dann zum Thema: im OTN Forum wurde heute die Frage diskutiert, in welchen Fällen man Indizes vor einem Massenladevorgang UNUSABLE setzen und nach der Ladeoperation wieder neu aufbauen lassen kann, worauf die Antwort lautet, dass das Verfahren mit bitmap und non-unique B*Tree-Indizes problemlos funktioniert, mit einem unique B*Tree-Index aber zu Fehlern führt ("ORA-26026: unique index ... initially in unusable state" für direct path inserts und "ORA-01502: index ... or partition of such index is in unusable state" für conventional path inserts). Dieses Verhalten kann man unter Umständen als Argument dafür sehen, Primary Key Constraints durch non-unique Indizes zu unterstützen. Im Thread hat Randolf Geist allerdings auf einen ziemlich hässlichen Fehler hingewiesen, den er vor mehreren Jahren in seinem Blog beschrieben hatte: in eine Tabelle mit aktivem PK und einem diesen Constraint unterstützenden und auf UNUSABLE gesetzten non-unique Index kann man via direct path Daten einfügen, die dem Constraint widersprechen, und anschließend den Index neu aufbauen - und das ohne irgendeine Fehlermeldung: der Constraint ist VALIDATED und ENABLED, aber die Daten entsprechen ihm nicht. Bei Verwendung von conventional path loads trat der Fehler nicht auf. Dieser - recht dramatische - Bug existierte mindestens seit 11.1.0.6 und wurde offenbar in 12.1 behoben - und diese Behebung gelangte als Backport auch nach 11.2.0.4. Seltsam ist bei der Behebung übrigens noch, dass das direct path insert auch in diesem Fall "ORA-26026: unique index ... initially in unusable state" liefert, obwohl der Index offensichtlich nicht UNIQUE ist - aber das eigentliche Problem ist beseitigt. Code-Beispiele zum Verhalten spare ich mir diesmal, da sie im Thread zu finden sind.

Mittwoch, März 05, 2014

Analyse für Index Rebuild

Die Zeiten, in denen das Thema automatisierter und regelmäßiger Index Rebuilds intensiv diskutiert wurde, sind wahrscheinlich vorbei, obwohl in den einschlägigen Oracle-Foren noch immer zugehörige Fragen auftauchen. Ein Grund dafür, dass das Thema nicht deutlich früher ad acta gelegt wurde, war möglicherweise die Tatsache, dass der Oracle Support selbst ein paar Skripte zur Bestimmung von Rebuild-Kandidaten bereitstellte. Richard Foote weist nun in seinem Blog darauf hin, dass eines der abwegigeren Exemplare dieser Gattung inzwischen von den MOS-Seiten entfernt wurde, während ein paar weniger problematische Dokumente weiterhin verfügbar sind.

Der Vollständigkeit halber noch mal der Hinweis darauf, dass Index-Rebuilds in der Regel zweckfrei sind und nur für bestimmte Muster des Index-Wachstums eine gewisse Relevanz besitzen (ein solches Muster wären monoton ansteigende neue Werte, während die alten Werte zum größten Teil gelöscht werden, weil dadurch viele Leaf-Blocks mit sehr wenigen Einträgen erhalten bleiben können). So ziemlich alles, was es zu diesem Thema zu sagen gibt, findet man in der entsprechenden Kategorie in Richard Footes Blog. Mein Favorit unter den Beiträgen ist ein Kommentar von David Aldridge, der schreibt:
Interesting quote from Page 728 of that book [i.e. D.K. Burleson: Oracle Tuning: The Definite Reference]: “Even if index rebuilding were to be proven as [sic] a useless activity, the Placebo effect on the end users is enough to justify the task.”. It opens up a whole new class of performance tuning … perhaps it could be called “Faith-based Tuning”.
Jonathan Lewis schlug darauf hin vor: "If the only effect is a placebo effect couldn’t you just tell the users that you had rebuilt the indexes rather than actually doing it ?"

Montag, März 03, 2014

Kostenbasierte Optimierung mit Postgres

Mit den Details der kostenbasierten Optimierung von SQL-Queries in Oracle beschäftige ich mich, seit ich Jonathan Lewis' Standardwerk Cost Based Oracle zum ersten Mal gelesen habe - was ziemlich bald nach seiner Veröffentlichung im Jahr 2005 geschehen sein dürfte. Nachdem ich inzwischen etwa ein halbes Jahr lang mehr oder minder intensiv mit Postgres gearbeitet habe, bin ich nun endlich dazu gekommen, einen etwas genaueren Blick auf die kostenbasierte Optimierung in diesem RDBMS zu werfen, und dabei unter anderem zu folgenden Erkenntnissen gekommen:
  • Die beeindruckend klar strukturierte online-Dokumentation für postgres enthält eine Menge von Detailinformationen zum Ablauf der Erzeugung des Ausführungsplans für eine Query und auch einige Erläuterungen zum Costing.
  • Darüber hinaus kann man sich auch noch den Code der Costing-Komponente anschauen, insbesondere die Implementierung von costsize.c, die letztlich alle Fragen zum Costing beantworten dürfte. Wenn man aus dem Oracle-Kosmos kommt, in dem die Entscheidungen des Optimizers nur durch Induktion - sprich: Beobachtung und Interpretation des Verhaltens - zu erklären sind, ist diese Basis natürlich ein erstaunlicher Luxus. Wobei ich an dieser Stelle noch ergänzen möchte, dass der Oracle-Optimizer in dieser Hinsicht meiner Einschätzung nach sehr viel intensiver untersucht wurde als die entsprechenden Komponenten anderer RDBMS, die ihren Quellcode nicht offen legen.
  • Da mir die Analyse des recht umfangreichen Codes in costsize.c zu aufwändig ist (was möglicherweise mehr über meine Code-Analyse-Fähigkeiten aussagt als über den Code), beschränke ich mich doch wieder auf die angesprochenen induktiven Strategien (und die allgemein bekannten Formeln).
  • Die kostenbasierte Optimierung verwendet in ihren Kalkulationen diverse statistische Informationen zu den verwendeten Objekten (Tabellen, Indizes) und einige fest definierte Parameterwerte. Die statistischen Informationen können explizit über das ANALYZE-Kommando ermittelt werden, aber in der Regel übernimmt der autovacuum deamon diesen Job, der für diverse Maintenance-Aufgaben zuständig ist (vor allem auch für die automatische Reorganisation von Objekten zur Freigabe von Speicherplatz durch Ausführung des VACUUM-Kommandos).
  • Zu den statistischen Informationen, die über ANALYZE gesammelt werden, gehören:
    • die Tabellenstatistiken in pg_class:
      • relpages: Anzahl der pages des Objekts auf der Festplatte (wobei eine page dem Block der Oracle-Nomenklatur entspricht).
      • pg_class.reltuples: Anzahl der Datensätze.
    • die Spaltenstatistiken in pg_stats:
      • null_frac: Anteil von NULL-Werten.
      • avg_width: durchschnittliche Spaltenbreite in Byte.
      • n_distinct: Anzahl distinkter Werte in der Spalte.
      • most_common_vals: eine Liste der populärsten (= am häufigsten auftretenden) Werte in der Spalte. Für Oracle entspricht diese Angabe den Informationen eines Histogramms - tatsächlich eines top-n-frequency Histogramms, das für Oracle erst in 12c eingeführt wurde, also eines Histogramms, das sich auf Informationen zur Häufigkeit der Top-n-Werte mit den meisten Wiederholungen beschränkt.
      • most_common_freqs: die zu den Angaben in most_common_vals gehörigen Angaben zur Wiederholungshäufigkeit der Werte.
      • correlation: die statistische Korrelation der logischen Sortierung der Spaltenwerte und ihrer physikalischen Sortierung auf der Platte. Diese Angabe besitzt also eine relativ große Nähe zum Clustering-Factor für Oracle-Indizes.
  • Zusätzlich zu den statistischen Informationen werden im Costing unter anderem folgende Parameter berücksichtigt:
    • seq_page_cost (default: 1): die Kosten eines Page-Zugriffs im Rahmen eines sequenziellen Scans (also eines Full Table Scans).
    • random_page_cost (default: 4): Kosten für einen nicht-sequenziellen Page-Scan (also z.B. einen Index-Zugriff). Tatsächlich ist der nicht-sequenziellen Page-Scan im Verhältnis zum sequenziellen Scan deutlich teurer als der default-Faktor 4 andeutet, aber bei der Wahl der Defaults liegt die (durchaus plausible) Annahme zugrunde, dass die Index-Blocks relativ häufig im Cache zu finden sind.
    • cpu_tuple_cost (default: 0.01): Kosten für das Abrufen einer Ergebniszeile in einem Select.
    • cpu_index_tuple_cost (default: 0.005): Kosten für den Zugriff auf einen Index-Eintrag.
    • cpu_operator_cost (default: 0.0025): Kosten für die Verwendung eines Operators oder einer Funktion.
    • effective_cache_size (default: 128MB): Informationen zur Größe des Caches. Ein höherer Wert macht Index-Zugriffe wahrscheinlicher.
    Dazu noch ein kleines Beispiel mit einigen harmlosen Costing-Berechnungen:

    -- 9.3
    -- Anlage einer Testtabelle mit 10000 Datensätzen
    drop table t;
    
    create table t
    as
    with
    basedata as (
    select * from generate_series(1, 10000) id
    )
    select id
         , mod(id, 10) col1
         , '**********************************'::text col2
      from basedata
    ;
    
    -- manuelle Statistikerfassung
    analyze t;
    
    -- Ausgabe der erstellten Statistiken
    select * from pg_class where relname = 't';
    -[ RECORD 1 ]--+-------
    relname        | t
    relnamespace   | 2200
    reltype        | 112466
    reloftype      | 0
    relowner       | 10
    relam          | 0
    relfilenode    | 112464
    reltablespace  | 0
    relpages       | 94
    reltuples      | 10000
    relallvisible  | 0
    reltoastrelid  | 112467
    reltoastidxid  | 0
    relhasindex    | f
    relisshared    | f
    relpersistence | p
    relkind        | r
    relnatts       | 3
    relchecks      | 0
    relhasoids     | f
    relhaspkey     | f
    relhasrules    | f
    relhastriggers | f
    relhassubclass | f
    relfrozenxid   | 23527
    relacl         |
    reloptions     |
    
    select * from pg_stats where tablename = 't';
    -[ RECORD 1 ]----------+-------------------------------------------------
    schemaname             | public
    tablename              | t
    attname                | id
    inherited              | f
    null_frac              | 0
    avg_width              | 4
    n_distinct             | -1
    most_common_vals       |
    most_common_freqs      |
    histogram_bounds       | {1,100,200,300,400,500,600,700,800,900,1000,1100
    correlation            | 1
    most_common_elems      |
    most_common_elem_freqs |
    elem_count_histogram   |
    -[ RECORD 2 ]----------+-------------------------------------------------
    schemaname             | public
    tablename              | t
    attname                | col1
    inherited              | f
    null_frac              | 0
    avg_width              | 4
    n_distinct             | 10
    most_common_vals       | {0,1,2,3,4,5,6,7,8,9}
    most_common_freqs      | {0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1}
    histogram_bounds       |
    correlation            | 0.10045
    most_common_elems      |
    most_common_elem_freqs |
    elem_count_histogram   |
    -[ RECORD 3 ]----------+-------------------------------------------------
    schemaname             | public
    tablename              | t
    attname                | col2
    inherited              | f
    null_frac              | 0
    avg_width              | 35
    n_distinct             | 1
    most_common_vals       | {**********************************}
    most_common_freqs      | {1}
    histogram_bounds       |
    correlation            | 1
    most_common_elems      |
    most_common_elem_freqs |
    elem_count_histogram   |
    

    Erwähnenswert ist an dieser Stelle vielleicht noch, dass das Erzeugen von Datensätzen mit Hilfe von generate_series nicht nur komfortabel, sondern auch sehr schnell ist - was im Fall von 10000 Datensätzen natürlich noch keine Rolle spielt - und dass der über \x aktivierte erweiterte Ausgabemodus für psql eine nette Sache ist, da er eine automatische Transformation der Ausgabe von Spalten zu Zeilen hervorruft.


    Costing für Full Table Scan

    Das Costing für den Full Table Scan, der im postgres-Kontext als sequenzieller Scan bezeichnet wird, wird in der Dokumentation explizit erläutert und beruht auf der Kombination der Kosten für das Lesen der Tabellen-Pages mit den Kosten für den Zugriff auf die einzelnen Datensätze, also:

    (disk pages read * seq_page_cost) 
    + (rows scanned * cpu_tuple_cost)
    
    -- im Beispiel also:
    (94 * 1.0) + (10000 * 0.01) = 194.00
    
    -- explain liefert dazu:
    explain select * from t;
    
                           QUERY PLAN
    --------------------------------------------------------
     Seq Scan on t  (cost=0.00..194.00 rows=10000 width=43)
    

    Die Kosten unterteilen sich dabei in start-up cost (0.00 für den sequentiellen Scan) und total cost, wobei letztere exakt der Formel entsprechen. Auch die width ergibt sich exakt aus den Angaben zu den Spalten (4 + 4 + 35 = 43).
    Zusätzliche Filter-Bedingungen erhöhen im Fall des sequentiellen Scans die Kosten, da sie eine zusätzliche Prüfung erforderlich machen, die erst nach der Ermittlung aller Datensätze erfolgen kann - im gegebenen Fall steigen die Kosten um 25 (= rows scanned * cpu_operator_cost):

    dbadmin=# explain select * from t where id = 1;
                         QUERY PLAN
    ----------------------------------------------------
     Seq Scan on t  (cost=0.00..219.00 rows=1 width=43)
       Filter: (id = 1)
    

    Costing für Index-Zugriffe

    Im Fall von Index-Zugriffen ist die Kostenberechnung deutlich schwieriger. Zunächst sind ein paar Punkte erwähnenswert, in denen sich postgres anders verhält als Oracle:
    • Ein Index-Zugriff, der entweder sehr selektiv ist, oder über einen Index erfolgt, dessen Sortierung sehr stark der physikalischen Sortierung der Tabelle entspricht (also eine starke correlation der fraglichen Spalte in pg_stats besitzt; in Oracle wäre das ein günstiger Clustering Factor), wird über einen "Index Scan" durchgeführt.
    • Für Zugriffe, bei denen eine relativ große Abweichung der Sortierung von Index und Tabelle zu erwarten ist, steigt postgres auf einen "Bitmap Index Scan" mit folgendem "Bitmap Heap Scan" um. Das bedeutet: postgres liest die Index-Einträge, sortiert dann die enthaltenen row locations und greift dann mit der sortierten Ergebnisliste auf die Tabellen-Pages zu. Der Sinn der Übung ist die Vermeidung von Mehrfachzugriffen auf die Tabellen-Pages die sich ergeben würden, wenn die row locations ungeordnet besucht werden würden. Das wäre jetzt ein guter Moment für den Einsatz einer illustrierenden Grafik, aber die habe ich gerade nicht zur Hand. Das Vorgehen gibt es seit Version 11 auch in Oracle und tritt in 12c als TABLE ACCESS BY INDEX ROWID BATCHED in Zugriffsplänen auf.
    • Postgres neigt zur Verknüpfung mehrerer Indizes über BitmapOr- und BitmapAnd-Verknüpfungen. Oracle kann das grundsätzlich auch (mit Hilfe einer BITMAP CONVERSION TO ROWID), neigt aber fast immer zur Verwendung des am besten geeigneten Index und zur anschließenden Filterung der zurückgelieferten Ergebnisse (für bitmap Indizes verwendet Oracle natürlich auch die entsprechenden Kombinationsverfahren, aber ich betrachte hier zunächst nur normale B*Tree-Indizes).
    Das Costing für den "Index Scan" basiert, so weit ich sehen kann, auf folgender Formel:

    (index pages read * random_page_cost * 2) 
    + (cpu_tuple_cost * rows scanned) 
    + (cpu_index_tuple_cost * scanned rows) 
    + (cpu_operator_cost * x)
    
    -- im Beispiel für eine Query mit Einzelsatzzugriff:
    (1 * 4 * 2) + (0.01 * 1) + (0.005 * 1) + (0.0025 * 100)
    = 8 + 0.01 + 0.005 + 0.25
    
    -- also:
    create index t_idx0 on t(id);
    create index t_idx1 on t(col1);
    
    explain select * from t where id = 1;
    
                               QUERY PLAN
    -----------------------------------------------------------------
     Index Scan using t_idx0 on t  (cost=0.00..8.27 rows=1 width=43)
       Index Cond: (id = 1)
    

    So weit ist das ganz plausibel und vermutlich auch mehr oder minder zutreffend, aber die Frage ist: wie komme ich auf die 100 als Multiplikator für cpu_operator_cost? Die Antwort lautet: durch Einsetzen. Wenn ich für den Parameter cpu_operator_cost unterschiedliche Werte einsetze, dann liefert explain das passende Ergebnis zu meiner Formel. Für cpu_operator_cost = 0 ergibt sich ein Cost-Wert von 8.02. Allerdings bekomme ich für veränderte Einschränkungen auch einen veränderten Multiplikator:

    set cpu_operator_cost = 0;
    
    explain select * from t where id <= 100;
    
                                QUERY PLAN
    -------------------------------------------------------------------
     Index Scan using t_idx0 on t  (cost=0.00..9.50 rows=100 width=43)
       Index Cond: (id <= 100)
    
    --> (1 * 4 * 2) + (0.01 * 100) + (0.005 * 100) = 9.5
    
    set cpu_operator_cost = 0.1;
    
    explain select * from t where id <= 100;
    
                                 QUERY PLAN
    --------------------------------------------------------------------
     Index Scan using t_idx0 on t  (cost=0.00..29.50 rows=100 width=43)
       Index Cond: (id <= 100)
    
    --> (1 * 4 * 2) + (0.01 * 100) + (0.005 * 100) + (0.1 * (100 + 100))
    
    explain select * from t where id <= 200;
    
                                 QUERY PLAN
    --------------------------------------------------------------------
     Index Scan using t_idx0 on t  (cost=0.00..42.00 rows=200 width=43)
       Index Cond: (id <= 200)
    --> (1 * 4 * 2) + (0.01 * 200) + (0.005 * 200) + (0.1 * (200 + 110))
    

    Demnach scheint cpu_operator_cost immer mit (scanned rows + 100 + x) multipliziert zu werden, wobei das x vermutlich in einem bestimmten Verhältnis zu scanned rows steht. Vermutlich ändern sich die Details auch, wenn mehr als ein Index-Block gelesen werden muss, was bei 30 Index-Blocks und 10000 Datensätzen etwa nach 333 Sätzen erfolgen sollte (= 10000/30).

    An dieser Stelle soll mir dieses Zwischenergebnis bis auf weiteres genügen. Klar ist, dass das Costing für postgres - wie im Fall von Oracle - relativ klaren und relativ einfachen Regeln unterliegt; wobei das "relativ einfach" vermutlich nur so lange gilt, wie man überschaubare Fälle untersucht. Mit der Kenntnis dieser Regeln sollte es dann auch möglich sein, die Schwächen des Modells in entsprechenden Fällen zu erkennen und auszugleichen - und muss sich nicht auf die eigene Kreativität im Umformulieren von SQL verlassen (wobei man dann so lange umbaut, bis sich etwas ergibt, was dem Optimizer gerade gefällt - was durchaus zu Ergebnissen führen kann, aber nicht unbedingt effektiv ist).