Gute Statistiken – gute Laufzeiten !!!

Ziel einer jeden SQL-Anweisung ist eine gute Laufzeit! Neben einem guten Datenbankdesign und entsprechend angelegten Indices sind die Statistiken auf den einzelnen Tabellen der Hauptfaktor, warum eine Abfrage performant durchgeführt werden kann.
In einer PostgreSQL-Datenbank übernimmt der Planner/Optimizer die Aufgabe, für eine Query einen guten Zugriffsplan auf die Daten zu erstellen. In der Regel gibt es mehrere Wege, innerhalb einer Abfrage an die entsprechenden Daten zu kommen. Dies können zum Beispiel verschiedene Arten des Joins sein (Nested Loop Join, Merge Join, Hash Join), die Verwendung bzw. das nicht Verwenden von Indices, etc. Damit der Planner/Optimizer hier eine gute Entscheidungsgrundlage hat, welches der optimale Zugriff auf die Daten ist, verwendet er Statistiken, in welchen Informationen gespeichert sind, um diese Entscheidungen treffen zu können. Dies sind z.B. die Anzahl Datensätze in der Tabelle oder die Anzahl der Seiten, welche die Tabelle bzw. der Index an Speicherplatz belegt.

Analyze

Doch wie werden nun diese Statistiken erstellt? Dies geschieht mit dem Kommando ANALYZE bzw. VACUUM ANALYZE, wenn gleichzeitig davor noch die Tabellen reorganisiert und aufgeräumt werden sollen.
Beim Ausführen des Kommandos werden die entsprechenden Statistiken gesammelt und in der Tabelle pg_statistic abgelegt. Der Administrator sollte beim Lesen der Statistiken jedoch auf die View pg_stats zugreifen, welche etwas lesbarer und anwendungsfreundlicher aufgebaut ist.
Gesammelt werden dabei Werte wie die Anzahl NULL-Werte (null_fraq), durchschnittliche Größe (avg_width), unterschiedliche Werte einer Spalte (n_distinct), die häufigsten Werte (most_common_vals), Selektivität der häufigsten Werte (most_common_freqs), Gruppierung der Spaltenwerte in gleich große Gruppen (histogram_bounds) und Verteilung der Daten auf der Platte (correlation).

Beispiel:

testdb=# select tablename, most_common_vals, most_common_freqs from pg_stats where tablename='kfz';
tablename | most_common_vals             | most_common_freqs                                                                                            
-----------+-----------------------------+-------------------------------------
 kfz       | {Seat,Honda,BMW,Toyota,etc.}| {0.124294,0.101695,0.0960452, etc.}
 kfz       | {Civic,Corsa,3er,Leon,etc.} | {0.0960452,0.0734463,0.0677966, etc.}
etc.
 

Standardmäßig werden die 100 häufigsten Werte (most_common_freqs) ermittelt. Dies ist in der Praxis in den allermeisten Fällen ausreichend. Der Wert kann jedoch durch Setzen des Konfigurationsparameters default_statistics_target geändert werden bzw. durch das ALTER TABLE ... ALTER COLUMN ... SET STATISTICS Kommando auch für jede einzelne Spalte individuell angepasst werden.

Erweiterte Statistiken

Unter bestimmten Umständen kann es sein, dass die Standard-Statistiken nicht ausreichen und sich der Planner beim Erstellen des Zugriffspfades verschätzt. Hier können ggf. die erweiterten Statistiken helfen, welche seit Version 10 zur Verfügung stehen.
Durch Anlegen dieser erweiterten Statistiken werden beim Kommando ANALYZE noch zusätzliche Werte gesammelt und in der Katalog-Tabelle pg_statistic_ext gespeichert.
Syntax:

	CREATE STATISTICS [ IF NOT EXISTS ] <Statistik-Name>
    	[ ( <Statistik-Art> [, ... ] ) ]
    	ON <spalte1>, <spalte2> [, ...]
    	FROM <tabelle>
 

Aktuell können zwei verschiedene Arten von erweiterten Statistiken erfasst werden:

1) NDISTINCT
Bei der NDISTINCT-Statistik werden neben der Anzahl unterschiedlicher Werte einer Spalte auch noch die Anzahl unterschiedlicher Werte in Kombination zu den anderen angegebenen Spalten erfasst. Damit lassen sich z.B. bei Abfragen mit einem GROUP-BY über mehrere Spalten bessere Schätzwerte zur Ermittlung des Zugriffsplans erreichen.

2) DEPENDENCIES
Hiermit können funktionale Abhängigkeiten zwischen mehreren Spalten einer Tabelle bekanntgegeben werden.
Beispiel: In der Tabelle KFZ ist sowohl die Spalte MARKE als auch MODEL vorhanden. Bei einer Abfrage mit der Einschränkung auf die Marke ‚VW' und das Model ‚Golf' macht die Datenbank einen Schätzfehler, der zu einem ungünstigen Zugriffspfad führen kann, da der Datenbank nicht bekannt ist, dass bei einem Golf automatisch die Marke VW ist.

Beispiel für den Zugriffspfad:

mit Standard-Statistik:

testdb=# analyse kfz;
ANALYZE
testdb=# explain (analyze, timing off) select kennzeichen, marke, model from kfz where marke='VW' and model='Golf';
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on kfz  (cost=0.00..7.53 rows=2 width=21) (actual rows=32 loops=1)
   Filter: (((marke)::text = 'VW'::text) AND ((model)::text = 'Golf'::text))
   Rows Removed by Filter: 878
 Planning Time: 0.165 ms
 Execution Time: 0.051 ms
(5 Zeilen)

testdb=#
 

  mit erweiterter DEPENDENCIES-Statistik:

testdb=# create statistics kfz_marke_model (dependencies) on marke, model from kfz;
CREATE STATISTICS
testdb=# analyse kfz;
ANALYZE
testdb=# explain (analyze, timing off) select kennzeichen, marke, model from kfz where marke='VW' and model='Golf';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on kfz  (cost=0.00..7.53 rows=32 width=21) (actual rows=32 loops=1)
   Filter: (((marke)::text = 'VW'::text) AND ((model)::text = 'Golf'::text))
   Rows Removed by Filter: 878
 Planning Time: 0.164 ms
 Execution Time: 0.056 ms
(5 Zeilen)

testdb=#
 

Man erkennt das mit der Standard-Statistik die Ergebnismenge auf 2 Zeilen geschätzt wird, es jedoch tatsächlich 32 sind. Mit der erweiterten Statistik fällt dieser Schätzfehler weg. In unserem Beispiel mit knapp 1.000 Zeilen in der Tabelle und einer relativ einfachen Where-Bedingung mag dieser Fehler kaum von Bedeutung sein, bei größeren Datenmengen mit komplexeren Queries können solche Schätzfehler jedoch enorme Auswirkungen auf die Laufzeit haben.
Wurde das Datenmodel der Datenbank vollständig normalisiert, so sollten solche Abhängigkeiten zwischen mehreren Spalten nicht auftauchen. Allerdings gibt es eine Reihe von Gründen, warum nicht immer alle Tabellen normalisiert werden, sodass ein solcher Anwendungsfall auch in der Praxis immer wieder vorkommt.

Fazit

Aktuelle Statistiken sind für eine gute Performance unausweichlich und sollten regelmäßig – am bestem in Kombination mit dem VACUUM-Prozess erstellt werden. In den meisten Fällen genügt es, die Standardwerte zu verwenden, jedoch bietet PostgreSQL mit dem CREATE STATISTICS-Kommando die Möglichkeit, auch bei speziellen Abfragen und Anwendungstypen genauere Statistiken für eine gute Laufzeit der Abfragen zu sammeln.