PostgreSQL® Archiv - Seite 8 von 8 - credativ®

Die PostgreSQL® Community hat heute die Veröffentlichung der stabilen Version 9.0.0 bekanntgegeben.

Mit der Version 9.0 verfügt PostgreSQL® erstmals über eine eingebaute Replikationslösung (Streaming Replication) und die Möglichkeit, Standbyknoten im reinen Lesemodus zu betreiben (Hot Standby). Streaming Replication ermöglicht die transparente Replikation auf einen oder mehrere Standbyknoten mit geringer Latenz. Des Weiteren gibt es viele Änderungen im Bereich Skalierbarkeit, Geschwindigkeit und Wartung:

  • JOIN Removal
  • Unterstützung für 64 Bit Windows
  • Trigger mit Bedingungen
  • Spaltenbasierte Trigger
  • Anonyme Prozedurale Codeblöcke mit DO
  • Verbessertes Nachrichtensystem mit LISTEN/NOTIFY

Weitergehende Informationen können direkt über die Release Notes der PostgreSQL® Global Development Group eingesehen werden.

In dieser Folge stellen wir im Rahmen der „Optimizer Bits“ das Modul auto_explain vor, das seit PostgreSQL® Version 8.4 Bestandteil des contrib-Zweiges ist. Das Modul ermöglicht das Protokollieren von Abfrageplänen im PostgreSQL®-Log und so eine bessere Analyse von Abfrageproblemen während der Laufzeit.

Problemstellung

Für viele Datenbank-Entwickler und -Administratoren stellt sich täglich das Problem, problematische Abfragen zu finden, zu analysieren und effizienter zu gestalten. Hauptproblem dieser Aufgabe ist das Identifizieren solcher Abfragen. Erstes Mittel ist der Logparameter

log_min_duration_statement = '30s'

 

In diesem Falle werden alle Abfragen, die länger als 30 Sekunden dauern, in das Log der PostgreSQL®-Datenbank geschrieben. Der Administrator hat dann die Möglichkeit, diese Abfrage aus dem Logfile zu ermitteln oder aber über weitere Tools wie bspw. pgfouine zu analysieren. Allerdings kann es unter Umständen passieren, dass bei der späteren Analyse andere Pläne entstehen, die es schwer machen, das tatsächliche Problem zu spezifizieren. Solche Abhängigkeiten machen es dem Entwickler schwer, das tatsächliche Problem genau einzugrenzen.

Das Modul auto_explain

Seit PostgreSQL® 8.4 gibt es das contrib-Modul auto_explain, dass die Ausgabe von Abfrageplänen während der Testphase von Abfragen gestattet. Beispielsweise lassen sich damit Läufe von umfangreichen Batchjobs protokollieren, die Pläne später analysieren und entsprechende Optimierungen an den entsprechenden Abfragen vornehmen. auto_explain kann permanent oder nur zur Fehlersuche in die Datenbank geladen werden.

Zunächst müssen die contrib-Module von PostgreSQL® 8.4 oder höher installiert sein. Dies ist von Distribution zu Distribution unterschiedlich, in der Regel sollte man nach einen Paket postgresql-contrib Ausschau halten. Wenn man PostgreSQL® selbst aus den Tarballs baut, wechselt man in der Verzeichnis des entpackten Quelltextes und von dort aus in das entsprechende contrib-Verzeichnis (die folgenden Schritte erfordern in der Regel Rootrechte auf dem System):

$ cd 
$ cd contrib/auto_explain

 

Je nachdem. ob bereits PostgreSQL® komplett gebaut wurde (in der Regel hat man dann ja noch alle benötigten Sourcen), kann man dann auto_explain zusätzlich bauen:

$ make install

 

Sollte der Quelltextbaum bereits bereinigt worden (make clean), aber eine komplette Installation zur Verfügung stehen, so kann man mit PGXS-Unterstützung, ohne den kompletten Quelltextbaum nochmals kompilieren zu müssen, das Modul wie folgt bauen:

$ USE_PGXS=1 make install

 

Dies erfordert jedoch mindestens die Präsenz des Tools pg_config im Pfad der aktuellen Umgebung.
Ist alles installiert, so kann das Modul direkt in eine Datenbankverbindung geladen werden. Dies ist nur als Superuser möglich, wie in diesem Beispiel über eine lokale Verbindung:

$ psql -U  
#= LOAD 'auto_explain';
LOAD

 

Ist das Modul erfolgreich geladen worden, so steht es nur in dieser Datenbankverbindung zur Verfügung und kann auch nur von dort aus verwendet werden. Interessant ist dies, um nur Abfragen aus speziellen Verbindungen heraus zu protokollieren. Mit der folgenden SQL-Abfrage können die nun hinzugekommenen Konfigurationsparameter für auto_explain abgefragt werden:

#= SELECT name, setting FROM pg_settings WHERE name LIKE 'auto_explain%';

 

Dies sollte folgende Liste liefern:

                 name                   | setting 
------------------------------------+---------
 auto_explain.log_analyze           | off
 auto_explain.log_buffers           | off
 auto_explain.log_format            | text
 auto_explain.log_min_duration      | -1
 auto_explain.log_nested_statements | off
 auto_explain.log_verbose           | off
(6 rows)

 

Der wichtigste Parameter hier ist

auto_explain.log_min_duration

Dieser aktiviert (Werte ab 0ms) oder deaktiviert (Wert -1) das Protokollieren von Abfrageplänen. Die weiteren Einstellungen sind im Einzelnen:

  • auto_explain.log_analyze = true|false: Aktiviert oder deaktiviert das Loggen von EXPLAIN ANALYZE. Dies bedeutet das Timinginformationen aller Abfragen erfasst werden (auch diejenigen, die schneller ausgeführt werden als auto_explain.log_min_duration). Dies hat einen signifikanten Einfluss auf die Ausführungsgeschwindigkeit und sollte mit Bedacht gewählt werden.
  • auto_explain.log_verbose = true|false: Ausgabeformat mit zusätzlichen Informationen für EXPLAIN.
  • auto_explain.log_nested_statements = true|on: Hiermit werden auch Ausführungspläne von Statements innerhalb von Funktionen mitprotokolliert. So ist es nun auch möglich, die Pläne von SQL-Abfragen, die bspw. aus pl/pgsql-Prozeduren heraus ausgeführt werden, genauer zu untersuchen.

Mit PostgreSQL® 9.0 kommen zwei weitere Konfigurationsmöglichkeiten hinzu:

  • auto_explain.log_format = ‚text’|’xml’|’json’|’yaml‘: Ermöglicht die Ausgabe der Abfragepläne im XML, JSON, oder YAML Format. text entspricht dem Standardformat.
  • auto_explain.log_buffers = true|false: Aktiviert oder deaktiviert die Ausgabe von Bufferinformationen in der Ausgabe des Planes. Dies enthält u.a. Informationen über Bufferhits (Treffer im Shared Buffer Pool). Voraussetzung hierfür ist das gleichzeitige Aktivieren des Parameters log_analyze.

Anwendungsbeispiel

Im folgenden betrachten wir ein wegen der Übersichtlichkeit ein stark vereinfachtes Anwendungsbeispiel. In einer Datenbank gibt es seit kurzem ein Geschwindigkeitsproblem mit einer Funktion, die plötzlich stark variierende Ausführungszeiten aufweist. Die Funktion wird vielfältig eingesetzt, da sie bestimmte ID-Nummern einem Datum zuordnet. Die Definition dieser Funktion sei wie folgt:

CREATE OR REPLACE FUNCTION get_test_datum_ids(p_datum timestamp) 
RETURNS SETOF integer 
STABLE 
LANGUAGE plpgsql
AS 
$$ 
DECLARE 
   v_id int; 
BEGIN 
   FOR v_id IN SELECT * FROM test WHERE datum < p_datum 
   LOOP 
      RETURN 
         NEXT v_id; 
   END LOOP; 
 
   RETURN; 
END; 
$$;

 

Geübte PostgreSQL®-Anwender werden schnell bemerken, dass diese Funktion deutlich effizienter implementiert werden kann, für dieses Beispiel jedoch ist eine derartige Implementierung gut geeignet. Der Administrator kann nun über log_min_duration_statement langsame Funktionsaufrufe zwar protokollieren, muss jedoch um dem Geschwindigkeitsproblem auf den Grund zu gehen, u.U. auf das System übertragen oder von Hand ausführen. Bei näherer Betrachtungsweise entsteht dann der Verdacht, dass die Schleife und die dort enthaltene Abfrage suboptimal sein könnte. Üblicherweise wird dann die Abfrage mit EXPLAIN geprüft:

#= \timing on
#= EXPLAIN ANALYZE SELECT id FROM test WHERE datum < '01.02.2008'::timestamp;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=4.50..16.90 rows=32 width=4) (actual time=0.039..0.054 rows=31 loops=1)
   Recheck Cond: (datum < '2008-02-01 00:00:00'::timestamp without time zone)
   ->  Bitmap Index Scan on test_datum_idx  (cost=0.00..4.49 rows=32 width=0) (actual time=0.025..0.025 rows=31 loops=1)
         Index Cond: (datum < '2008-02-01 00:00:00'::timestamp without time zone)
 Total runtime: 0.114 ms
(5 rows)

 

Insofern nichts Verdächtiges, die Abfrage nutzt einen vorhandenen Index auf dem Feld datum. Mit auto_explain können wir nun jedoch ebenfalls direkt die Pläne aus dem Funktionskörper heraus prüfen:

#= SET auto_explain.log_analyze TO on;
SET
#= SET auto_explain.log_nested_statements TO on;
SET
#= SET auto_explain.log_min_duration TO '0ms';
SET
#= SELECT get_test_datum_ids('01.02.2008'::timestamp);

 

Durch das Setzen von auto_explain.log_analyze TO on wird die Funktion tatsächlich ausgeführt und alle Timingparameter erfasst. Nach dem Ausführen sollten sich folgende Zeilen auf STDOUT, im Logfile oder syslog finden, je nach dem was für ein log_destination verwendet wird:

LOG:  duration: 0.616 ms  plan:
	Query Text: SELECT * FROM test WHERE datum < p_datum
	Seq Scan on test  (cost=0.00..25.70 rows=365 width=12) (actual time=0.424..0.597 rows=31 loops=1)
	  Filter: (datum < $1)
ZUSAMMENHANG:  PL/pgSQL function "get_test_datum_ids" line 1 at FOR über SELECT-Zeilen

 

Dieser Plan sieht schon deutlich anders aus. Zwar ist die Ausführungsgeschwindigkeit aufgrund der in diesem Beispiel recht kleinen Datenmengen noch überschaubar, jedoch kann man sich jetzt schon vorstellen, dass bei einer größeren Datenmenge dieser Plan schnell ineffizient werden kann. Doch warum wird an dieser Stelle ein anderer Plan verwendet?

Des Rätsels Lösung liegt an der parametrisierten Form dieser Abfrage, die in der FOR-Schleife verwendet wird. Der Optimizer kann nur einen generischen Plan für diese Art der WHERE-Bedingung erzeugen. Da der Offset für den Bereich innerhalb der Bedingung nicht zur Planungszeit zur Verfügung steht, muss der Optimizer den Plan auf einem möglichst allgemeingültigen Kostenmodell berechnen, der effizient für jeden Wert in der WHERE-Bedingung ist.

Konfiguration über postgresql.conf

auto_explain lässt sich auch global über die postgresql.conf konfigurieren. Möchte man als DBA beispielsweise das Modul auf jeden Fall für jede Datenbankverbindung laden, so benötigt man einen entsprechend konfigurierten Parameter shared_preload_libraries in der postgresql.conf (diese befindet sich in der Regel im Datenbankverzeichnis ihrer PostgreSQL®-Installation, kann aber bei einigen Distributionen abweichen):

## globales Aktivieren von auto_explain
shared_preload_libraries = 'auto_explain'

 

Dies lädt das Modul bereits beim Start für jede Datenbankverbindung. Da PostgreSQL® noch nicht die Konfigurationsparameter beim Laden der Konfigurationsdatei kennt, muss dies noch zusätzlich über den Parameter custom_variable_classes dem Server bekannt gemacht werden:

custom_variable_classes = 'auto_explain'

 

Nun kann in der Datei postgresql.conf der Parameter global konfiguriert werden, wie an folgendem Listing beispielhaft gezeigt:

auto_explain.log_min_duration = '30s'
auto_explain.log_format = 'xml'

 

Zusammenfassung

auto_explain ist ein nützliches Tool, um Geschwindigkeitsproblemen innerhalb der Datenbank anhand der EXPLAIN-Ausgaben auf den Grund zu gehen. Als wertvoll stellt sich die Möglichkeit heraus, eingebettete Abfragen innerhalb von SQL- oder PL/pgsql-Prozeduren mitprotokollieren zu können, um so auch die Abfragen im entsprechenden Kontext auf Fehler oder unterschiedliche Pläne hin untersuchen zu können.
auto_explain eignet sich jedoch nicht, um dauerhaft auf produktiven Datenbankmaschinen eingeschaltet zu sein, hierfür ist der zusätzliche Aufwand für das Ausschreiben der Pläne zu groß. Insofern sollte auf jeden Fall Gebrauch von auto_explain.log_min_duration gemacht werden, so dass wirklich nur sehr problematische Abfragen bei Überschreiten einer bestimmten Zeitschwelle protokolliert werden. Auch sollte dann auf produktiven Maschinen auf jeden Fall auto_explain.log_analyze deaktiviert sein, da dies auch Abfragen, die noch unterhalb der Zeitschwelle von auto_explain.log_min_duration liegen, negativ beeinflusst.

Der OOM-Killer kann auf stark ausgelasteten Maschinen für böse Überraschungen sorgen: Prozesse werden plötzlich und unerwartet beendet. Dieses Verhalten lässt sich aber mit Kernel-Bord-Mitteln sehr genau beeinflussen. Administratoren auf Linuxmaschinen mit hoher RAM-Nutzung erleben oft eine Begegnung der unheimlichen Art: den Linux OOM-Killer (OOM = Out Of Memory). Der Administrator findet in diesem Szenario eine „abgestürzte“ PostgreSQL®-Instanz vor, im Serverlog finden sich dann einer oder meist mehrere Einträge der Form

Out of Memory: Killed process PID (Prozessname)

Doch was genau steckt dahinter?

Virtueller Speicher und Overcommit

Virtueller Speicher in Linuxsystemen wird auf vielfältige Weise adressiert: RAM, mmap(), Swap oder Shared Memory, um ein paar Beispiele zu nennen. Es ist möglich, durch das sogenannte Overcommit-Verhalten bei Allokieren von Speicher mehr Ressourcen anzufordern, als tatsächlich im System aktuell vorhanden ist. In solchen Situationen spricht man von einer OOM-Situation, das System hat alle Ressourcen aufgebraucht und ist nicht mehr in der Lage, mehr virtuellen Speicher zu adressieren. Hier wird der OOM-Killer aktiv, der Prozesse nach festgelegten Kriterien auswählt und diese terminiert, um dem System ein wenig Luft zu verschaffen. Dieses Verhalten ist insbesondere für Datenbanksysteme zu berücksichtigen, die nicht auf dedizierter Hardware laufen. Der OOM-Killer bevorzugt in solchen Umgebungen häufig PostgreSQL®, da als Kandidaten zum Terminieren solche Prozesse ausgewählt werden, die mit aggressiver Speichernutzung auffallen. Da der OOM-Killer den gesamten Adressraum aller Kinder inklusive Shared Memory in Summe sieht, erkennt man recht schnell, dass PostgreSQL® auf jeden Fall weit oben in der Liste der Kandidaten auftauchen wird. Wie stark der zur Verfügung stehende Speicher genutzt wird, findet man am schnellsten über das /proc-Filesystem heraus:

$ grep Commit /proc/meminfo
CommitLimit:    376176 kB
Committed_AS:   265476 kB

In diesem Beispiel sind aktuell als Obergrenze 376176 kB(CommitLimit) an Speichernutzung möglich, zugewiesen wurden 265476 kB (Committed_AS). Nähert sich CommitLimit sehr stark an Committed_AS an oder übersteigt diesen sogar, dann ist der Einsatz des OOM-Killers wahrscheinlich. Der Linux-Kernel stellt einige Schnittstellen zur Verfügung, die das Verhalten des OOM-Killers gegenüber PostgreSQL® beeinflusst.

Overcommit abschalten

Die radikalste Methode ist, Overcommit generell im Kernel abzuschalten. Allerdings kommt dies nur für dedizierte Datenbanksysteme in Frage, auf denen PostgreSQL® exklusiv läuft. Das Overcommit-Verhalten lässt sich in modernen 2.6ern Kernel in drei Kategorien mit dem Parameter

vm.overcommit_memory = 0

konfigurieren. Die einzelnen Kategorien hierbei sind:

  • 0: Vorsichtiges Overcommitverhalten. Während gemäßigte Allokierungen erlaubt sind, werden extrem große Allokierungen, die zu übermäßigem Overcommit führen, abgelehnt. In diesem Modus kann root auch mehr Speicher allokieren als ein unprivilegierter Benutzer. Dieser Modus ist auch die Standardeinstellung des Kernels.
  • 1: Overcommit unterliegt keinen Einschränkungen
  • 2: Schaltet Overcommitverhalten ab. Generell bedeutet dies, dass der maximale allokierbare tatsächliche Adressraum nicht größer werden kann, als swap + ein konfigurierbarer Anteil an Prozent des physkalischen RAM.

Der Anteil des physikalischen RAM bei Modus 2 wird über den zusätzlichen Parameter

vm.overcommit_ratio = 50

kontrolliert. Während vm.overcommit_memory=1 für Spezialanwendungen interessant sein könnte, wird es im Praxiseinsatz eher zum Einsatz für die Parameterwerte 0 oder 2 kommen. Wird Overcommit über vm.overcommit_memory=2 abgeschaltet, so wird ein Prozess (in Abhängigkeit von vm_overcommit_ratio) sofort eine „Out Of Memory“-Bedingung beim Allokieren von Speicher erhalten. Abhängig von der Distribution sollte man die Einstellungen permanent in die Datei /etc/sysctl.conf speichern, so dass diese auch nach einem Neustart des Systems aktiv sind:

$ echo "vm.overcommit_memory=2 >> /etc/sysctl.conf
$ echo "vm.overcommit_ratio=60 >> /etc/sysctl.conf
$ sysctl -p /etc/sysctl.conf

Die Änderungen wirken sich sofort auf den virtuellen Speicher aus, man kann dies erneut durch Abrufen von /proc/meminfo überprüfen:

$ grep Commit /proc/meminfo
CommitLimit:    401440 kB
Committed_AS:   266456 kB

Die Maschine verfügt über 249848 kB Swap und 252656 kB physikalischen RAM. Nach der Formel Swap + vm.overcommit_ratio * RAM ergibt dies ein CommitLimit von 401440 kB.

OOM-Killer auf Prozessebene konfigurieren

Ist PostgreSQL® nicht auf einem dedizierten Server installiert und wird mit einer speicherhungrigen Middleware (bspw. JBoss- oder Tomcat-Installation) auf demselben System betrieben, so ist es wünschenswert, Overcommit-Verhalten zwar zu erlauben, im Falle einer „Out Of Memory“-Situation aber PostgreSQL® vom OOM-Killer auszunehmen. Seit Kernel 2.6.11 bietet Linux daher ein Interface an, um den OOM-Score eines Prozesses zu tunen, so dass dieser vom OOM-Killer weniger oder stärker berücksichtigt wird. Dies erlaubt ein sehr feinfühliges Einstellen des Systems auf die Speicherbedürfnisse einzelner Prozesse. Die Konfiguration wird über eine Datei im /proc-Filesystem des Kernel vorgenommen, beispielsweise hier für den PostgreSQL®-Hauptprozess unter Debian (0 ist die Standardeinstellung für Prozesse):

$ cat /proc/$(cat /var/run/postgresql/8.4-main.pid)/oom_adj
0

Die erlaubten Werte sind von -17 bis +15, negative Werte verringern die Affinität des Prozesses gegenüber den OOM-Killer, positive Werte erhöhen diese. -17 schaltet den OOM-Killer für den jeweiligen Prozess komplett ab. Die Einstellung wird vom Parent an etwaige Kindprozesse weitervererbt. Da PostgreSQL® sich für eine Datenbankverbindung forked, reicht es, diese Einstellung dem PostgreSQL®-Hauptprozess mitzugeben:

$ echo -17 >> /proc/$(cat /var/run/postgresql/8.4-main.pid)/oom_adj
$ psql -q postgres
test=# SELECT pg_backend_pid();
 pg_backend_pid
----------------
           3429
(1 Zeile)
 
test=#
[1]+  Stopped                 psql -q test
$ cat /proc/3429/oom_adj
-17

Der Nachteil dieser Methode ist, dass dies nun für alle Kindprozesse des PostgreSQL®-Hauptprozesses gilt, was eventuell vom DBA nicht mehr gewünscht ist. Beispielsweise möchte man zwar gerne die PostgreSQl-Systemprozesse wie Background Writer oder Autovacuum vor dem OOM-Killer schützen, nicht jedoch normale Datenbankverbindungen. Das Setzen von /proc/PID/oom_adj erfordert jedoch einen privilegierten Benutzer, so dass man am Besten die Einstellung direkt im Startskript der PostgreSQL®-Datenbank vornimmt.

Erweiterungen in PostgreSQL® 9.0

PostgreSQL® 9.0 wird hinsichtlich der Zusammenarbeit mit dem /proc-Interface ebenfalls einige Neuerungen mitbringen. Zum einen wurde das im Quelltext mitgelieferte Linux-Startskript dahingehend erweitert, zum anderen bietet das Backend nun auch Unterstützung, falls man die /proc-Einstellungen eben nicht an normale Datenbankverbindungen weitervererben möchte. Hierzu kann der PostgreSQL®-Server mit dem Makro LINUX_OOM_ADJ=0 kompiliert werden, beispielsweise:

$ ./configure CC="ccache gcc" CFLAGS="-DLINUX_OOM_ADJ=0"

Diese Methode schützt dann die PostgreSQL®-Systemprozesse effektiv, erlaubt aber dem OOM-Killer etwaige Amoklaufende Backends trotzdem zu terminieren.

Alternativen

Eine alternative Lösung gibt es auch in Form eines Kernelpatches. Dies ergänzt das /proc-Filesystem um eine Liste an Prozessnamen, die explizit vom OOM-Killer nicht berücksichtigt werden dürfen. Da dies jedoch eine inoffizielle Erweiterung des Kernels ist, muss man seinen eigenen Kernel damit pflegen, auch ist diese Erweiterung bei weitem nicht so flexibel wie das Interface über oom_adj. Des weiteren sind Prozessnamen relativ ungeeignet, um spezifische Prozesse eindeutig zu identifizieren (z.B. Java- oder Perlbasierte Prozesse).

Zusammenfassung

Der Linuxkernel bietet mittlerweile umfassende Möglichkeiten, die Speichernutzung von Prozessen an das Memory Management des Kernels anzupassen. Die flexibelste Lösung stellt das /proc-Filesystem mit dem oom_adj-Interface dar. PostgreSQL® 9.0 ergänzt dies durch weitere Maßnahmen. Dedizierte Datenbanksysteme können vom Administrator dahingehend angepasst werden, gar kein Overcommit des virtuellen Speichers zuzulassen, hier muss jedoch sorgfältig abgewogen werden, welche Anforderungen die PostgreSQL®-Instanz an die VM des Kernels stellt. Alle Blog-Artikel zum Thema PostgreSQL® werden auch als Kategorie PostgreSQL® samt eigenem Feed angeboten – und falls ihr nach Support und Services für PostgreSQL® sucht, seit ihr bei uns ebenfalls richtig.

In der Serie „PostgreSQL® Optimizer Bits“ werden Strategien und Besonderheiten des PostgreSQL® Optimizers vorgestellt. Heute beschäftigt sich die Serie mit dem Feature Join Removal des Optimizers in der kommenden Version 9.0. Nachdem im letzten Beitrag unserer Serie Semi und Anti Joins besprochen wurden, setzen wir uns heute mit Join Removal auseinander. Join Removal ist eine Optimierungsstrategie des Optimizers, die Tabellen der rechten Seite in LEFT JOINs aus der Join-Evaluierung ausschließen kann, wenn keine Spalten aus dieser Tabelle in der Ergebnismenge qualifiziert wurden und die rechte Seite des LEFT JOINs eindeutig ist, so dass hierdurch keine weiteren Tupel hinzukommen. Dies macht auch erforderlich, dass mindestens ein UNIQUE CONSTRAINT existiert, der beide Seiten entsprechend eindeutig qualifiziert wie gefordert. Betrachten wir folgendes kleines Beispiel mit zwei Tabellen a und b, wobei b Detaildatensätze verknüpft mit a enthält.

EXPLAIN SELECT
   a.name
FROM
   a LEFT JOIN b ON (b.id = a.id)
WHERE
   a.name LIKE 'M%';

Diese Query selektiert aus einem LEFT JOIN den Namen einer Person aus der Relation a. Man erkennt schnell, dass der LEFT JOIN auf die Relation b in diesem Fall nutzlos ist, wenn die verknüpften Felder a.id und b.id eindeutig sind. In diesem Fall würde auch bei Auftreten eines verknüpften Tupels kein weiteres Tupel erzeugt werden. In PostgreSQL® 8.4 wird dennoch der Join ausformuliert:

Nested Loop Left Join  (cost=0.00..9.33 rows=1 width=6)
   ->  Seq Scan on a  (cost=0.00..1.05 rows=1 width=10)
         Filter: (name ~~ 'M%'::text)
   ->  Index Scan using b_pkey on b  (cost=0.00..8.27 rows=1 width=4)
         Index Cond: (b.id = a.id)

In PostgreSQL® 9.0alpha4 hingegen wird der Join eliminiert: dadurch vereinfacht sich der Plan wie folgt:

Seq Scan on b  (cost=0.00..1.05 rows=1 width=11) (actual time=0.012..0.013 rows=1 loops=1)
   Filter: (name ~~ 'M%'::text)
 Total runtime: 0.045 ms

Der Optimizer kann an dieser Stelle nur die linke Seite berücksichtigen und daher die Relation b komplett aus dem Join eliminieren. Interessant wird diese Optimierungsmöglichkeit bei Verknüpfungen mit vielen LEFT JOINs (bspw. a LEFT JOIN b LEFT JOIN c LEFT JOIN d ...), wo mehrere Relationen aufgrund der Zusammensetzung der Abfrage aus dem Join entfernt werden können. Dadurch verbessern sich auch die Möglichkeiten des Optimizers, der weniger Tabellen bei der Optimierung der Verknüpfungspfade berücksichtigen muss. Auch für Views ist diese Optimierungsmöglichkeit interessant, da nicht qualifizierte Spalten aus der zugrundeliegenden Definition des Views eliminiert werden und daher implizit zu derartigen Abfragen führen können. So weit zum aktuellen Artikel der Serie, der nächste wird bald folgen. Alle Blog-Artikel zum Thema PostgresQL werden auch als Kategorie PostgreSQL® samt eigenem Feed angeboten – und falls ihr nach Support und Services für PostgreSQL® sucht, seit ihr bei uns ebenfalls richtig.

In der Serie „PostgreSQL® Optimizer Bits“ werden Strategien und Besonderheiten des PostgreSQL® Optimizers vorgestellt. Den Startpunkt setzt ein neues Feature aus der Version 8.4: Semi und Anti Joins.

PostgreSQL® bietet seit Version 8.4 eine neue Optimizerstrategie für die Optimierung von bestimmten Abfragen an: Semi und Anti Joins.
Ein Semi Join ist eine spezielle Form eines Joins, die nur die Schlüssel einer Relation a berücksichtigt, sobald diese ebenfalls in der verknüpften Tabelle b auftreten.

Ein Anti Join ist die negative Form eines Semi Join: Tritt ein in Tabelle a gewählter Schlüssel in Tabelle b nicht auf, so wird er bei dieser speziellen Form berücksichtigt. Ein Semi- bzw. Anti-Join sind also spezielle Formen eines Joins, die einen bestimmten Schlüssel nur auf der linken Seite berücksichtigen. Interessant wird dies nun für Abfragen, die nur ein Vorhandensein eines bestimmten Schlüssels prüfen wollen bzw. dies als Filterprädikat nutzen. Bekannt ist ein solches Vorgehen teils von Object Relation Mapper (ORM), die entsprechende Anfragen mit EXISTS() bzw. NOT EXISTS() formulieren.

Im Vergleich zu PostgreSQL® 8.3 ergibt sich für dieselbe Anfrage ein unterschiedlicher und deutlich effizienter Abfrageplan, wie im folgenden Beispiel (mit relativ einfacher Abfrage) dargestellt. Gegeben seien zwei Tabellen a,b und eine Anfrage über EXISTS(). Es soll ein bestimmter Datensatz aus a ermittelt werden, der eine Entsprechung über a.id2 = b.id in b hat. Selbstverständlich lässt sich dies auch über einen Join lösen, beispielhaft soll dies jedoch zeigen, wie der Optimizer eine derartig formulierte Anfrage auflösen kann:

EXPLAIN SELECT id FROM a WHERE a.id = 200 AND EXISTS(SELECT id FROM b WHERE a.id2 = b.id);

Der Optimizer in PostgreSQL® 8.3 ermittelt für dieses Beispiel in der Regel folgenden Plan (zu beachten ist, dass die beiden Tabellen a,b jeweils einen Index auf den Spalten id bzw. id2 haben):

                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using a_id_idx on a  (cost=0.00..8355.27 rows=503 width=4)
   Index Cond: (id = 200)
   Filter: (subplan)
   SubPlan
     ->  Index Scan using b_id_idx on b  (cost=0.00..8.27 rows=1 width=4)
           Index Cond: ($0 = id)

Im Gegensatz dazu kann PostgreSQL® 8.4 hash Semi Join nutzen:

                                QUERY PLAN
---------------------------------------------------------------------------
 Hash Semi Join  (cost=27.52..78.16 rows=969 width=4)
   Hash Cond: (a.id2 = b.id)
   ->  Index Scan using a_id_idx on a  (cost=0.00..37.32 rows=969 width=8)
         Index Cond: (id = 200)
   ->  Hash  (cost=15.01..15.01 rows=1001 width=4)
         ->  Seq Scan on b  (cost=0.00..15.01 rows=1001 width=4)

Man sieht deutlich die reduzierten Kosten des Planes, die auch weniger I/O-Zugriffe bedeuten. Für derartige Abfragen lohnt sich also ein genauerer Blick. So weit zum aktuellen Artikel der Serie, der nächste wird bald folgen. Alle Blog-Artikel zum Thema PostgresQL werden auch als Kategorie PostgreSQL® samt eigenem Feed angeboten – und falls ihr nach Support und Services für PostgreSQL® sucht, seit Ihr bei uns ebenfalls richtig.